imported>Busk at 14:27, 13 June 2019

2019-06-13T14:27:55Z

New page

# 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).
```

PostgreSQL Replication, Redundancy & Backup - Revision history

imported>Busk at 14:27, 13 June 2019