PostgreSQL Replication, Redundancy & Backup

Common Command Examples in Postgres Logical Replication

Good example and intro.

https://www.digitalocean.com/community/tutorials/how-to-set-up-logical-replication-with-postgresql-10-on-ubuntu-18-04

You only need the schema so use pg_dump pdns --schema-only > pdns.sql from master. Updates will have to be made on both on schema changes.

Note that it works like traditional pub/sub where it will pull from queue. Use unique replication slots for different subscriptions if you want them all to be the same.

On publication host

echo "wal_level = logical" >> /etc/postgresql/10/main/postgresql.conf

CREATE PUBLICATION pdns_slave WITH (publish = 'insert, update, delete');
ALTER PUBLICATION pdns_slave OWNER TO postgres;
ALTER PUBLICATION pdns_slave ADD TABLE ONLY public.cryptokeys;
ALTER PUBLICATION pdns_slave ADD TABLE ONLY public.domainmetadata;
ALTER PUBLICATION pdns_slave ADD TABLE ONLY public.domains;
ALTER PUBLICATION pdns_slave ADD TABLE ONLY public.records;
ALTER PUBLICATION pdns_slave ADD TABLE ONLY public.supermasters;
ALTER PUBLICATION pdns_slave ADD TABLE ONLY public.tsigkeys;


select * from pg_publication;
select * from pg_replication_slots

Replication hosts

CREATE SUBSCRIPTION pdns_slave CONNECTION 'host=10.50.4.140 port=5432 user=replicator dbname=pdns' PUBLICATION pdns_slave WITH (connect = true, slot_name = 'pdns_ns1');^C
pdns=# drop subscription pdns_slave;

select * from pg_subscription;

If publisher ip address changes

update pg_subscription set subconninfo = 'host=10.50.4.144 port=5432 user=replicator dbname=pdns' where subdbid=17061;

Clean-up If Needed

delete from pg_publication;
delete from pg_subscription;

select pg_drop_replication_slot('pdns_ns1');

https://www.postgresql.org/docs/10/logical-replication-subscription.html https://www.digitalocean.com/community/tutorials/how-to-set-up-logical-replication-with-postgresql-10-on-ubuntu-18-04

Replication Methods

These can be really nice for redundancy and backups

Logical Replication

This is new in 10. Has some nice advantages over streaming

• https://www.postgresql.org/docs/11/logical-replication.html
• https://www.postgresql.org/docs/11/logical-replication-quick-setup.html

Streaming Replication

• https://www.postgresql.org/docs/11/protocol-replication.html

Comparisions

https://www.postgresql.org/docs/11/different-replication-solutions.html

Backup

You can do your full backups from one of your secondary replicated servers so it doesn't impact main database. Here are some methods. Choose the one that works best for your situation.

• https://www.postgresql.org/docs/11/backup.html

Using filesystem

You can always snap, send & clone your file system, like in zfs. Depending on situation, you probably want to shutdown database first to make sure it is quiesced to make sure you get a clean backup.

ZFS

• https://blog.programster.org/send-and-receive-zfs-snapshots
• https://docs.oracle.com/cd/E18752_01/html/819-5461/gbchx.html
• https://www.howtoforge.com/tutorial/how-to-use-snapshots-clones-and-replication-in-zfs-on-linux/
• https://serverfault.com/questions/842531/how-to-perform-incremental-continuous-backups-of-zfs-pool

More Resources

• https://medium.com/leboncoin-engineering-blog/managing-postgresql-backup-and-replication-for-very-large-databases-61fb36e815a0

Working with Logical Replication and Dropping subscriptions

https://dba.stackexchange.com/questions/207653/cannot-drop-database-with-logical-replication

ALTER SUBSCRIPTION ... SET (slot_name = NONE).