Managing and migrating PostgreSQL on premise with Zercurity.

By default the dockerised version of Zercurity provides a self contained PostgreSQL container as its primary database. Once your deployment starts to grow. The initial configuration of PostgreSQL will start to become a bottle neck. This post will go into detail about configuring PostgreSQL to provide your database with more resources as well as providing either an external self-hosted or managed database solution including the replication or migration of data.

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

Any bitnami parameters can be added directly to the production.env without changing the docker-compose.yml.

The second way is to modify, or provide your own bespoke PostgreSQL configuration file. If you’ve not configured PostgreSQL before. There are a few websites that’ll help you construct a configuration file automatically.

Once you’ve got your new configuration file. We need to let the docker-compose file know about the configuration file in order to override the one used by Zercurity.

By default the PostgreSQL container is configured to override settings provided by any configuration files placed or mounted within the /etc/postgresql/conf.d/path within the container. This configuration file will be loaded upon a restart of the container. You can mount your configuration file like so:

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

This will also override the default zercurity.conf which also resides within the conf.d direcotry.

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

Your managed database will require the following extensions installed in order for Zercurity to correctly set-up the database. Otherwise, the migrations container will fail. The logs for which can be seen using the command docker logs -f zercurity_prod_migrations

CREATE EXTENSION ip4r;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_prewarm;
CREATE EXTENSION btree_gist;

AWS RDS supports all the listed extensions. If you’re using Debian most of the extensions will already be installed. The only one you’ll be missing is the ip4r extension which you can install like so:

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.

This is also used for major PostgreSQL upgrades. Given the size of the database the command below makes use pg_dump’s job feature to dump tables in parallel -Fd. The number of jobs is specified by the -j flag. The dump_dir will be the output path for your dump.

pg_dump -d 'postgres://username:password@hostname:port/zercurity' \
  -Fd -j 5 -f dump_dir

Once completed you can then restore it like so:

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

Once the restore process has completed simply update the production.env file with the new database information and Zercurity will now use the database server. You can then remove the PostgreSQL container from the docker-compose configuration file.

Grafana and Prometheus should still continue to work as they’ll update themselves using the new environment variables.

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.

Once the two databases are more or less in sync. You can notify Zercurity of the new database sever. Causing new database changes to be sent to the new server rather than being replicated across. In order to accomplish this there are several configuration changes that need to be made to both our existing server and new database server.

As with the section above we need to add some additional configuration parameters to the docker-compose.yml or production.env .

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

If you’re using another container system you can make use of the bitnami/postgresql:latest container to create a replica:

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

If you’re simply looking to create a read replica or a hot standby make sure the networks parameter is set to provide “local” connectivity. Otherwise, in the MASTER_HOST parameter specify the external hostname of the Zercurity container hostname.

For an external host go ahead and install PostgreSQL. Once done stop the service and remove the default configuration files. As we’re going to use pg_basebackup to copy across our Zercurity configuration.

systemctl stop postgresql@12-main.service
rm -rf /var/lib/postgresql/12/main/*

Then lets sync across our database and its configuration to replace the deleted files.

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

You can check that the directory we cleared earlier will now contain our Zercurity configuration.

ls -l /var/lib/postgresql/12/main

If all looks good. Start back up replica postgres instance.

systemctl start postgresql@12-main.service

Back on our primary docker instance. Lets check the replication status. Using the command below. If our replica PostgreSQL server is connecting correctly you’ll see the hostname of the server as shown above postgresql_replica_1:

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;"

You can also test the status of the replica node with the following command:

su - postgres
SELECT * FROM pg_stat_wal_receiver;

As the replicate database continues to replicate data from the primary host. There will come a point at the end of the day or a window where there are fewer requests being sent to the backend where you wish to switch over from the primary database. To complete the migration simply update the production.env with the new database details and remove the postgres service from the docker-compose.yml .Both these files can be found /var/lib/zercurity/ . You can then restart the zercurity service.

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

From the secondary server you need to copy across the database structure of the primary database. This can also be done just by running zercurity on the secondary host and letting the migrations container initialize the database. Otherwise, pg_dump can be used to copy over the table schema.

pg_dump -U zercurity -h postgres -d zercurity --schema-only zercurity | psql -U zercurity

Once the table structure has been copied over we can now configure bucadro. This is being done in a primary-primary configuration.

Firstly, let bucardo know about the existing database server primary_zercurity (Zercurity’s docker instance) and your_server.

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

Next we’re going to let bucardo know we want to synchronize the following:

bucardo add all tables
bucardo add all sequences
bucardo remove table public.migrations
bucardo add herd zercurity_herd public.zercurity

Then we’re going to instantiate the configuration in a primary-primary configuration. Marking both database servers as “sources”.

bucardo add dbgroup zercurity_dbs primary_zercurity:source secondary_zercurity:source

Lastly, create the sync task and start bucardo.

bucardo add sync zercurity_sync herd=zercurity_herd dbs=zercurity_dbs status=active
bucardo start

The status can then be monitored with the command:

bucardo status

In the event that servers are under heavy load. There are some tables that are heavily written to such as the processes table. You can end up in a situation where there can by id conflicts. As both databases are actively being written too.

To mitigate this and provide a little more breathing room for these tables you can alter the sequence id within PostgreSQL to be incremented by 2 instead of 1.

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.