Managing and migrating PostgreSQL on premise with Zercurity.

Configuring PostgreSQL

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

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

Migrating using pg_dump

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

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

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!

--

--

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