We discussed a topic on how to create simple backups using pg_rman. In this blog post, we will demonstrate how to restore PostgreSQL using a backup generated by pg_rman.
In continuation of the previous blog, let’s execute a simple query before simulating an error.
postgres=# select count(*) from t; count ---------- 20000010 (1 row)
Now, let’s insert some new records into the database.
postgres=# insert into t values(generate_series(1, 2000)); INSERT 0 2000 postgres=# select count(*) from t; count ---------- 20002010 (1 row)
At this point, let’s consider the insertion of 2000 records as a mistake. Instead of correcting or deleting these records individually, we aim to roll back the entire database to the previous stage, i.e., before inserting the last 2000 records.
Now, let’s start by checking our backup.
$ pg_rman show -B /tmp/rman/backup ===================================================================== StartTime EndTime Mode Size TLI Status ===================================================================== 2023-11-13 19:15:27 2023-11-13 19:15:29 FULL 789MB 1 OK 2023-11-13 13:48:53 2023-11-13 13:49:04 ARCH 132MB 1 OK 2023-11-13 13:48:26 2023-11-13 13:48:27 ARCH 18kB 1 OK 2023-11-13 13:43:13 2023-11-13 13:43:14 ARCH 654MB 1 OK 2023-11-13 13:40:32 2023-11-13 13:40:34 INCR 34MB 1 OK 2023-11-13 13:34:22 2023-11-13 13:34:24 FULL 49MB 1 OK
According to the pg_rman records, we can identify the last valid full backup record before our mistake, generated at 2023-11-13 19:15:29. We aim to use this backup to roll back our database cluster.
To accomplish this, we must first stop the PostgreSQL Server, as pg_rman does not support online restore.
$ pg_ctl -D /tmp/rman/pgdata -l /tmp/rman/pglog/logfile stop waiting for server to shut down.... done server stopped
After stopping the PostgreSQL Server, don’t delete any files within the cluster. Instead, proceed with the restore using pg_rman, as demonstrated below.
$ pg_rman restore -B /tmp/rman/backup -D /tmp/rman/pgdata –recovery-target-time=”2023-11-13 19:15:29″
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: “2023-11-13 19:15:27”
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: “2023-11-13 19:15:27” backup, archive log files and server log files by SIZE
INFO: backup “2023-11-13 19:15:27” is valid
INFO: restoring database files from the full mode backup “2023-11-13 19:15:27”
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup “2023-11-13 19:15:27” is valid
INFO: restoring WAL files from backup “2023-11-13 19:15:27”
INFO: restoring online WAL files and server log files
INFO: create pg_rman_recovery.conf for recovery-related parameters.
INFO: remove an ‘include’ directive added by pg_rman in postgresql.conf if exists
INFO: append an ‘include’ directive in postgresql.conf for pg_rman_recovery.conf
INFO: generating recovery.signal
INFO: removing standby.signal if exists to restore as primary
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.
After a successful restoration, restart the PostgreSQL Server to allow it to recover automatically.
$ pg_ctl -D /tmp/rman/pgdata -l /tmp/rman/pglog/logfile start waiting for server to start.... done server started
Now, in the PostgreSQL Server log file, we can observe the recovery log as shown below.
2023-11-13 19:42:02.752 PST  LOG: starting PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit 2023-11-13 19:42:02.752 PST  LOG: listening on IPv4 address "127.0.0.1", port 5432 2023-11-13 19:42:02.760 PST  LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2023-11-13 19:42:02.770 PST  LOG: database system was interrupted; last known up at 2023-11-13 19:15:27 PST 2023-11-13 19:42:02.855 PST  LOG: starting point-in-time recovery to 2023-11-13 19:15:00-08 2023-11-13 19:42:02.890 PST  LOG: restored log file "000000010000000000000056" from archive 2023-11-13 19:42:02.916 PST  LOG: redo starts at 0/56000028 2023-11-13 19:42:02.953 PST  LOG: restored log file "000000010000000000000057" from archive 2023-11-13 19:42:02.974 PST  LOG: consistent recovery state reached at 0/56000138 2023-11-13 19:42:02.974 PST  LOG: recovery stopping before commit of transaction 753, time 2023-11-13 19:15:29.39176-08 2023-11-13 19:42:02.974 PST  LOG: pausing at the end of recovery 2023-11-13 19:42:02.974 PST  HINT: Execute pg_wal_replay_resume() to promote. 2023-11-13 19:42:02.974 PST  LOG: database system is ready to accept read-only connections
The PostgreSQL Server has been successfully recovered and is now ready to accept connections. Let’s verify whether the database records have been rolled back to the state before the mistake was made.
postgres=# select count(*) from t; count ---------- 20000010 (1 row) postgres=# select from pg_wal_replay_resume(); -- (1 row) postgres=# insert into t values(generate_series(1, 5)); INSERT 0 5 postgres=# select count(*) from t; count ---------- 20000015 (1 row)
As we can see, the data records have been restored to 20,000,010, the expected number of records before the inadvertent insertion of an additional 2,000 records. After manually executing the command
SELECT pg_wal_replay_resume();, we can resume inserting and updating the PostgreSQL Server
Hello, this is David, one of the authors at techbuddies.io. I enjoy tinkering with software and have huge curiosity for all things tech, with over 15 years experience in software technology, please allow me to be your go-to guide for navigating this digital universe