Managing and migrating PostgreSQL on premise with Zercurity.

Configuring PostgreSQL

Configuring the Zercurity PostgreSQL docker container can be done one of two ways. Either by updating the environment variables parameter in the docker-compose file like so. Or within the production.env file which will persist Zercurity updates. Zercurity inherits the bitnami/postgresql container.

postgres:
restart: always
image: ghcr.io/zercurity/postgres:latest
environment:
- POSTGRESQL_POSTGRES_CONNECTION_LIMIT=100
- POSTGRESQL_STATEMENT_TIMEOUT=30000
postgres:
restart: always
image: ghcr.io/zercurity/postgres:latest
environment:
- POSTGRESQL_USERNAME=$DB_USERNAME
- POSTGRESQL_PASSWORD=$DB_PASSWORD
- POSTGRESQL_DATABASE=$DB_DATABASE
hostname: postgres.$ZERCURITY_DOMAIN
env_file:
- production.env
volumes:
- postgres:/bitnami/postgresql/data
- /local/path/to/conf.d:/bitnami/postgresql/conf.d/
networks:
- zercurity

Using managed or external PostgreSQL services

If you need to use an external managed database for Zercurity. You can update the /var/lib/zercurity/production.env file with the details of your new database. Zercurity will automatically initialize the new database. When docker-compose restarts. Please see our section below on migration options if you’re moving from an existing database.

DB_HOSTNAME=postgres.fqdn.com
DB_DATABASE=zercurity
DB_USERNAME=zercurity
DB_PASSWORD=zercurity
CREATE EXTENSION ip4r;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_prewarm;
CREATE EXTENSION btree_gist;
apt-get install -y postgresql-12-ip4r
Grafana monitoring the Zercurity PostgreSQL database container

Migrating Zercurity to a new PostgreSQL database

There are three primary ways to migrate Zercurity to another database either using the pg_dump and pg_restore command to copy the database from one server to another. Or if you’re performing a live migration then you have the option to replicate the database or use a tool like Bucardo.

Migrating using pg_dump

The simplest way to migrate Zercurity to a new database is using pg_dump. This will take a snapshot of the database and allow you to restore it to your new database server. This can take up-to several hours to complete depending on the size of your database.

pg_dump -d 'postgres://username:password@hostname:port/zercurity' \
-Fd -j 5 -f dump_dir
pg_restore --no-acl --no-owner \
-d 'postgres://username:password@hostname:port/zercurity' \
--data-only -Fd -j5 dump_dir

Migrating using replications

To avoid hours of downtime you can make use of PostgreSQL’s replication functionality to replicate the data from your primary database sever to a secondary one.

postgres:
restart: always
environment:
- POSTGRESQL_REPLICATION_MODE=master
- POSTGRESQL_REPLICATION_USER=repl_user
- POSTGRESQL_REPLICATION_PASSWORD=repl_password
- POSTGRESQL_SYNCHRONOUS_COMMIT_MODE=on
- POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=1
- POSTGRESQL_USERNAME=my_user
- POSTGRESQL_PASSWORD=my_password
- POSTGRESQL_DATABASE=my_database
postgresql-replica:
image: 'bitnami/postgresql:latest'
ports:
- '5432'
depends_on:
- postgresql-master
environment:
- POSTGRESQL_REPLICATION_MODE=slave
- POSTGRESQL_REPLICATION_USER=repl_user
- POSTGRESQL_REPLICATION_PASSWORD=repl_password
- POSTGRESQL_MASTER_HOST=postgresql
- POSTGRESQL_MASTER_PORT_NUMBER=5432
networks:
- zercurity
systemctl stop postgresql@12-main.service
rm -rf /var/lib/postgresql/12/main/*
pg_basebackup -h 192.168.1.202 -D /var/lib/postgresql/12/main/ \
-U repl_user -P -v -R -X stream -C -S postgresql_replica_1
ls -l /var/lib/postgresql/12/main
systemctl start postgresql@12-main.service
docker exec -it dev_postgres_1 psql -U zercurity \
-c "SELECT slot_name FROM pg_replication_slots;"
docker exec -it dev_postgres_1 psql -U zercurity \
-c "SELECT * FROM pg_stat_replication;"
su - postgres
SELECT * FROM pg_stat_wal_receiver;

Migration using Bucardo

Bucardo is an asynchronous PostgreSQL replication system, allowing for multi-source, multi-target operations. This method allows for primary-primary write replication allowing for a zero downtime migration. Bucardo is already installed within the Zercurity container. You just need to ensure its installed on your server too.

apt-get install bucardo postgresql-plperl sudo
pg_dump -U zercurity -h postgres -d zercurity --schema-only zercurity | psql -U zercurity
bucardo add db primary_zercurity dbname=zercurity host=postgres user=zercurity pass=zercurity
bucardo add db secondary_zercurity dbname=zercurity host=your_server user=zercurity pass=zercurity
bucardo add all tables
bucardo add all sequences
bucardo remove table public.migrationsbucardo add herd zercurity_herd public.zercurity
bucardo add dbgroup zercurity_dbs primary_zercurity:source secondary_zercurity:source
bucardo add sync zercurity_sync herd=zercurity_herd dbs=zercurity_dbs status=activebucardo start
bucardo status
ALTER SEQUENCE processes_id_seq  INCREMENT BY 2;

Its all over!

We hope you found this helpful. Please feel free to get in touch if you have any questions.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store