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 [2389760] 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 [2389760] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-11-13 19:42:02.760 PST [2389760] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-11-13 19:42:02.770 PST [2389763] LOG: database system was interrupted; last known up at 2023-11-13 19:15:27 PST
2023-11-13 19:42:02.855 PST [2389763] LOG: starting point-in-time recovery to 2023-11-13 19:15:00-08
2023-11-13 19:42:02.890 PST [2389763] LOG: restored log file "000000010000000000000056" from archive
2023-11-13 19:42:02.916 PST [2389763] LOG: redo starts at 0/56000028
2023-11-13 19:42:02.953 PST [2389763] LOG: restored log file "000000010000000000000057" from archive
2023-11-13 19:42:02.974 PST [2389763] LOG: consistent recovery state reached at 0/56000138
2023-11-13 19:42:02.974 PST [2389763] 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 [2389763] LOG: pausing at the end of recovery
2023-11-13 19:42:02.974 PST [2389763] HINT: Execute pg_wal_replay_resume() to promote.
2023-11-13 19:42:02.974 PST [2389760] 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. Currently working as a software architect at Highgo Software Canada, 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. Find more blogs from me at highgo.ca