How to split/move a pg_auto_failover cluster?

[s4ke]

One of our pg_auto_failover clusters has to be split into separate stages because we have quite a lot of concurrent connections to a lot of separate dbs.

Now I want to do the following:

1. Get a pg_basebackup from the primary data node
2. Restore the data into a new data node
3. Create new monitor for new cluster
4. Register new data node to new cluster

Is this possible? Or should we do it differently?

What is the simplest way to get data from a pg_auto_failover managed cluster into a new set of machines?

[DimCitus]

Hi @s4ke ;

Given current pg_auto_failover releases it's possible to create a node from a pre-existing backup, either a primary or a standby node. Given that capability, your list seems okay to me. It might also be the simplest way around. Note that using 1.5.1 it might be even simpler:

1. pg_autoctl create monitor # the new monitor
2. pg_autoctl create postgres --replication-quorum false --candidate-priority 0 ... # to create a new standby
3. pg_autoctl disable monitor --force
4. pg_autoctl enable monitor postgres://autoctl_node@newmonitor/pg_auto_failover

If you have a small enough amount of data, you might want to see about using pg_dump -Fd which can then be restored in parallel. In that case though you need to first create the “global” objects first, as you might know already.

[s4ke]

What you describe here is the process for creating the "split" by first using normal replication to copy the data into the target cluster, right?

How would I go about if I only wanted to do it based on a pg_basebackup?

1. create monitor
2. create new node and point it to monitor from 1
3. disable monitor on node
4. restore the pg_basebackup
5. enable monitor
6. create secondary for new cluster

This should set up all state on monitor perfectly, then i "swap out" the postgres data, and add it back to the monitor.

This way I could simply use my playbooks to setup a new cluster and all hba settings as I will require them, replace the data and add the node back to the monitor. Should work, no?

Thanks for the tip with pg_dump, but our daily pg_basebackup is > 60GB already.

[s4ke]

Steps that work (might be suboptimal, but work in the case of a disaster or you want to move your data from prod to testing):

1. Create new cluster from scratch with only the primary and the secondary (we use our playbooks for this, this sets up all firewall rules, pg_hba etc.)
2. disable monitor
3. Stop the systemd service on the new primary
4. configure restory_command in postgresql.auto.conf restore_command = 'cp /backup/backup_wal/%f "%p" '
5. Start systemd service on primary
6. Run postgres_cluster_database_users.yml (from our ansible repo) to ensure all pg_hba settings/firewalls are set up properly
7. Enable monitor again

[s4ke]

Note: The above proved to not work properly. I will post the steps how to do it properly here in a bit.

We ended up going directly to 1.5 and simply restoring from the pg_base_backup.

[s4ke]

This is the complete "playbook":

1. create cluster as is with a single primary using the playbooks from our repo
2. disable monitor on primary with --force on it pg_autoctl disable monitor --force --pgdata /var/lib/postgresql/12/main_cluster/
3. stop pg_autoctl service with sudo service postgresql_main_cluster stop
4. ensure postgres is actually stopped by running /usr/lib/postgresql/12/bin/pg_ctl stop --pgdata /var/lib/postgresql/12/main_cluster/ and double check it with psql -p 5433
5. keep a backup of the old pgdata dir cp -r /var/lib/postgresql/12/main_cluster/ /var/lib/postgresql/12/main_cluster_backup/
6. destroy data with pg_autoctl drop node --destroy --pgdata /var/lib/postgresql/12/main_cluster/
7. rm -rf /var/lib/postgresql/12/main_cluster/
8. mkdir /var/lib/postgresql/12/main_cluster/ && chmod 700 /var/lib/postgresql/12/main_cluster/
9. tar xvf /home/vagrant/basebackup_test/pg_basebackup/base.tar.gz -C /var/lib/postgresql/12/main_cluster/
10. tar xvf /home/vagrant/basebackup_test/pg_basebackup/pg_wal.tar.gz -C /home/postgres/backup_wal
11. rm /var/lib/postgresql/12/main_cluster/postgresql.auto.conf && touch /var/lib/postgresql/12/main_cluster/postgresql.auto.conf
12. rm /var/lib/postgresql/12/main_cluster/postgresql-auto-failover-standby.conf
13. rm /var/lib/postgresql/12/main_cluster/postgresql-auto-failover.conf && touch /var/lib/postgresql/12/main_cluster/postgresql-auto-failover.conf
14. remove lines in postgresql.conf that include postgresql-auto-failover-standby.conf and postgresql-auto-failover.conf
15. put restore_command = 'cp /home/postgres/backup_wal/%f "%p" ' into postgresql.auto.conf
16. rm /var/lib/postgresql/12/main_cluster/standby.signal
17. touch /var/lib/postgresql/12/main_cluster/recovery.signal
18. cp /var/lib/postgresql/12/main_cluster_backup/pg_ident.conf /var/lib/postgresql/12/main_cluster/pg_ident.conf
19. cp /var/lib/postgresql/12/main_cluster_backup/pg_hba.conf /var/lib/postgresql/12/main_cluster/pg_hba.conf
20. PATH=$PATH:/usr/lib/postgresql/12/bin/ pg_autoctl create postgres --pgdata /var/lib/postgresql/12/main_cluster --skip-pg-hba --formation default --ssl-ca-file /data/ansible/certs/postgres_server/rootCA.crt --server-key /data/ansible/certs/postgres_server/server.key --server-cert /data/ansible/certs/postgres_server/server.crt --hostname 10.0.0.11 --name 10.0.0.11 --pghost 10.0.0.11 --pgport 5433 --monitor postgres://autoctl_node@10.0.0.10:5433/pg_auto_failover
21. sudo service postgresql_main_cluster start
22. verify primary and monitor can talk to each other
23. create secondary (optional)

[s4ke]

This should get you a completely newly minted pg_auto_failover cluster with your data in there. This is also probably the simplest way to start clean in the case of an emergency.