Restore a database on a HA cluster(pg_auto_failover) with pgbackrest
Article ID: 296418
Updated On:
Products
Issue/Introduction
See KB article How to setup pgBackrest on a cluster using pg_auto_failover to setup the backups on the HA(pg_auto_failover) cluster.
This article describes how to perform restoration on pg_auto_failover cluster with pgbackrest.
Note:
- For the HA system, when any node fails, as long as we have at least one good copy of data, there would be NO restoration needed since the last copy can still be functional.
- The restoration is only needed when an extreme disaster occurs, like all the nodes are unavailable. if that is the case, a new cluster needs to be created and restore data to the new cluster.
- If we restore the data into a primary node on a live system. once the standby node starts it will not be functional due to the timeline change (we will demonstrate that in the next section). All the standby nodes must be dropped and recreated after the data has been restored from the primary
The article describes 2 scenarios to demonstrate how restores work
- Full restore when a disaster occurs with all data gone.
- PITR (point in time recovery)
For more details, we can refer to the document of pgbackrest Restore.
Resolution
Example#1: Full restore when a disaster occurs with all data gone.
After a disaster occurred and all nodes are lost, a new cluster has been created and there is a backup set named 20230726-151426F.
-- At this point, there is no node has been added yet.
$ pg_autoctl show state Name | Node | Host:Port | TLI: LSN | Connection | Reported State | Assigned State -----+-------+------------+-----------+--------------+---------------------+--------------------
-- On the first (primary) node that is to be added to the monitor, ensure that pgautofailover is stopped, and clean up all the old data and configuration files if exists.
# sudo systemctl stop pgautofailover; # rm -rf $PGDATA ~/.local ~/.config
-- Restore the data from the backup, with the backup set. Once the restoration is finished, review the data folder and confirmed all data has been restored.
# pgbackrest --stanza=main --set=20230726-151426F restore β # du -sh $PGDATA/ 84M /data/database/ # find $PGDATA/ | wc -l 1031
-- Review the $PGDATA/postgresql.auto.conf, It is best to update the restore_command parameter with the full path of pgBackRest (e.g.,/opt/vmware/postgres/xx/bin/pgbackrest) instead of just 'pgbackrest'. it may cause issue when starting the primary node.
# cat $PGDATA/postgresql.auto.conf ... # Recovery settings generated by pgBackRest restore on 2023-07-26 16:51:46 restore_command = '/opt/vmware/postgres/15/bin/pgbackrest --stanza=main archive-get %f "%p"'
-- Start the Postgres to confirm it can work, and shut it down after confirmation.
# pg_ctl start -D $PGDATA β # pg_isready /tmp:5432 - accepting connections β # pg_ctl stop -D $PGDATA
-- Add the node to the monitor. Adjust the parameters according to the environment.
pg_autoctl create postgres --pgdata $PGDATA --auth trust --ssl-self-signed --username postgres --hostname $hostname --monitor 'postgres://autoctl_node@monitor:5432/pg_auto_failover?sslmode=require'
-- Start the pgautofailover, and check the state on the monitor side. the node should be up and with the data from backup
# sudo systemctl start pgautofailover # pg_autoctl show state Name | Node | Host:Port | TLI: LSN | Connection | Reported State | Assigned State -------+-------+------------+-----------------+--------------+---------------------+-------------------- node_1 | 1 | node1:5432 | 14: 0/24002150 | read-write | single | single
Add the standby node(s) into the monitor and the data will get replicated to standby.
Name | Node | Host:Port | TLI: LSN | Connection | Reported State | Assigned State -------+-------+------------+-----------------+--------------+---------------------+-------------------- node_3 | 3 | node1:5432 | 17: 1/730000D8 | read-write | primary | primary node_2 | 4 | node2:5432 | 17: 1/730000D8 | read-only | secondary | secondary
Example#2: PITR (point in time recovery)
-- Let's assume the user has a table very_critical_table and it was deleted by mistake
postgres=# select count(*) from very_critical_table ; count ------- 1000 (1 row)
-- We have a backup set that already included the table, the backup set in the below example is 20230724-134049F_20230724-162414I
pgbackrest --log-level-console=info --stanza=main backup pgbackrest --log-level-console=info --stanza=main info --set 20230724-134049F_20230724-162414I stanza: main status: ok cipher: none db (current) wal archive min/max (15): 00000003000000000000000A/000000060000000000000018 incr backup: 20230724-134049F_20230724-162414I timestamp start/stop: 2023-07-24 16:24:14 / 2023-07-24 16:24:19 wal start/stop: 000000060000000000000018 / 000000060000000000000018 lsn start/stop: 0/18000028 / 0/18000138 database size: 99.2MB, database backup size: 17.5MB repo1: backup set size: 45.9MB, backup size: 9.0MB backup reference list: 20230724-134049F, 20230724-134049F_20230724-134702I database list: postgres (5)
-- The table was dropped by a user by mistake
postgres=# drop table very_critical_table ; DROP TABLE
-- To do a PITR to restore the lost table. First stop the pg_auto_failover service on ALL the data nodes. (both standby and primary)
sudo systemctl stop pgautofailover
-- On the primary node, recover the data from the backup set. Set target time as the time before mistake happen.
pgbackrest --stanza=main --delta --type=time "--target=2023-07-24 16:24:13" --target-action=promote --set 20230724-134049F_20230724-162414I restore
-- Double-check the postgresql.auto.conf, better to update the restore_command with the full path of pgBackRest (E.g,/opt/vmware/postgres/xx/bin/pgbackrest) instead of just 'pgbackrest'. it might cause issue when starting the primary node.
$ cat $PGDATA/postgresql.auto.conf | egrep "recovery|restore" # Recovery settings generated by pgBackRest restore on 2023-07-24 18:10:09 restore_command = '/opt/vmware/postgres/15/bin/pgbackrest --stanza=main archive-get %f "%p"' recovery_target_time = '2023-07-24 16:24:13' recovery_target_action = 'promote'
-- Start the pg_auto_failover on the primary node:
sudo systemctl start pgautofailover;
-- the primary node will be up and the table will back
postgres=# select count(*) from very_critical_table ; count ------- 1000 (1 row)
-- At this point, if we start the pgautofailover service on the standby node, the node will not be able to start. if we check the Postgres log we will see the below error:
2023-07-24 18:11:37.194 CST [38976] LOG: starting PostgreSQL 15.2 (VMware Postgres 15.2.0) on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2023-07-24 18:11:37.194 CST [38976] LOG: listening on IPv4 address "0.0.0.0", port 5432 2023-07-24 18:11:37.194 CST [38976] LOG: listening on IPv6 address "::", port 5432 2023-07-24 18:11:37.196 CST [38976] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2023-07-24 18:11:37.198 CST [38980] LOG: database system was shut down in recovery at 2023-07-24 18:07:38 CST 2023-07-24 18:11:37.442 CST [38980] LOG: restored log file "0000000C.history" from archive 2023-07-24 18:11:37.689 CST [38980] LOG: entering standby mode 2023-07-24 18:11:37.936 CST [38980] LOG: restored log file "0000000C.history" from archive 2023-07-24 18:11:38.442 CST [38980] FATAL: requested timeline 12 is not a child of this server's history 2023-07-24 18:11:38.442 CST [38980] DETAIL: Latest checkpoint is at 0/24000228 on timeline 11, but in the history of the requested timeline, the server forked off from that timeline at 0/1F007158. 2023-07-24 18:11:38.444 CST [38976] LOG: startup process (PID 38980) exited with exit code 1 2023-07-24 18:11:38.444 CST [38976] LOG: aborting startup due to startup process failure 2023-07-24 18:11:38.445 CST [38976] LOG: database system is shut down
-- That is because When we restore some physical backup we go back in time and then start a new timeline, it is a totally different life for the server data (like back to the future). If we have a replica, if that replica already replicated data after that point itβs not possible to keep replicating those new data, which is why Postgres is complaining.
Name | Node | Host:Port | TLI: LSN | Connection | Reported State | Assigned State -------+-------+------------+-----------------+--------------+---------------------+-------------------- node_2 | 6 | node2:5432 | 11: 0/240002A0 | read-only ! | secondary | catchingup node_1 | 7 | node1:5432 | 12: 0/1F00B4F0 | read-write | wait_primary | wait_primary
-- To bring up the standby node, please drop it from the monitor and re-add it back. the steps are like below:
1. renmae the $PGDATA folder (Data folder of database)
2. rename the ".local" and ".config" folder under the home folder
3. drop the standby node on the monitor (since the service is not able to start we had to use the force option)
pg_autoctl drop node --hostname node2 --pgport 5432 --force
4. create the standby node again, below command is just an example, please adjust the parameter accordingly
pg_autoctl create postgres --pgdata $PGDATA --auth trust --ssl-self-signed --username postgres --hostname node2 --pgctl /opt/vmware/postgres/15/bin/pg_ctl --monitor 'postgres://autoctl_node@monitor:5432/pg_auto_failover?sslmode=require'
5. start the pgautofailover on the standby node, the standby should able to start and sync data from primary now
sudo systemctl start pgautofailover
Feedback
