Point in time recovery using pgBackRest for PostgreSQL
pgBackRest aims to be a reliable, easy-to-use backup and restore solution that can seamlessly scale up to the largest databases and workloads by utilizing algorithms that are optimized for database-specific requirements .
It is used to create physical backup and have multiple advantages over native pgBaseBackup utility like Parallel Backup & Restore,Full, Incremental and Differential Backups, Backup Rotation and Archive Expiration, Backup Integrity check, encryption and delta restore
Main Features of pgBackRest :
1.Parallel Backup & Restore
2.Local or Remote Operation (A custom protocol allows pgBackRest to backup, restore, and archive locally or remotely via SSH with minimal configuration)
3.Full, Incremental, & Differential Backups 4.Backup Rotation & Archive Expiration
5.Backup Integrity(Checksums are calculated for every file in the backup and rechecked during a restore)
6.Backup Resume(An aborted backup can be resumed from the point where it was stopped)
7.Parallel, Asynchronous WAL Push & Get
8.S3, Azure, and GCS Compatible Object Store Support
9.Encryption
Installtion
Installation is very simple , just use the yum command and download the software from repo
yum install pgbackrest
Check if the download was successful
[root@MyPosgreHost ~]# which pgbackrest /bin/pgbackrest [root@MyPosgreHost ~]# pgbackrest version pgBackRest 2.33
Create the config file for this backup tool called pgbackrest.conf
Create a directory and inside create conf file pgbackrest which essentially points to two folders the postgres data dir and the directory where the backup pieces will be stored [root@MyPosgreHost pgbackrest]# mkdir -p /etc/pgbackrest/ [root@MyPosgreHost pgbackrest]# cd /etc/pgbackrest/ [root@MyPosgreHost pgbackrest]# vi pgbackrest.conf [root@MyPosgreHost pgbackrest]# ls -ltr total 4 -rwxrwxr-x. 1 root root 123 Apr 16 20:00 pgbackrest.conf [root@MyPosgreHost pgbackrest]# cat pgbackrest.conf [global] repo1-path=/var/lib/pgbackrest ( dir where backup will be stored) repo1-retention-full=2 [openpgsql] pg1-path=/var/lib/pgsql/13/data/ ( Postgres data dir) pg1-port=5432
Change ownership of all pgbackrest related files to postgres
[root@MyPosgreHost ]chmod 775 /var/lib/pgbackrest [root@MyPosgreHost ] chown -R postgres:postgres /var/lib/pgbackrest [root@MyPosgreHost ] hmod 775 /var/log/pgbackrest/ ( This dir will be created once we add the stanza in later point [root@MyPosgreHost ] chown -r postgres:postgres /var/log/pgbackrest
Change the postgres.conf file to add entry for archive command to make sure pgbackuprest will archive all the wal file immediately
[root@MyPosgreHost ~]# grep -i archive /var/lib/pgsql/13/data/postgresql.conf archive_mode = on # enables archiving; off, on, or always archive_command = '' # command to use to archive a logfile archive_command = 'pgbackrest --stanza=openpgsql archive-push %p' # placeholders: %p = path of file to archive
Restart psogres to make the chnage effective
-bash-4.2$ ./pg_ctl -D /var/lib/pgsql/13/data/ stop waiting for server to shut down…. done server stopped -bash-4.2$ ./pg_ctl -D /var/lib/pgsql/13/data/ start waiting for server to start….2021-04-17 12:17:59.722 GMT [16776] LOG: redirecting log output to logging collector process 2021-04-17 12:17:59.722 GMT [16776] HINT: Future log output will appear in directory "log". done server started
Create Stanza
Stanza defines backup configuration for a specific PostgreSQL or TimescaleDB database cluster.
[postgres@MyPosgreHost ~]$ pgbackrest stanza-create --stanza=openpgsql -- log-level-console=info 2021-04-16 20:17:59 P00 INFO: stanza-create command begin 2.32: --exec-id=24839-da2916aa --log-level-console=info --pg1-path=/var/lib/pgsql/13/data/ --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --stanza=openpgsql 2021-04-16 20:17:59 P00 INFO: stanza-create for stanza 'openpgsql' on repo1 2021-04-16 20:17:59 P00 INFO: stanza-create command end: completed successfully (543ms)
Now check pgbackrest log dir and see files generated there
[root@MyPosgreHost pgbackrest]# cd /var/log/pgbackrest/ [root@MyPosgreHost pgbackrest]# ls -ltr total 164 -rw-r-----. 1 postgres postgres 316 Apr 16 20:28 openpgsql-stanza- create.log.1.gz -rw-r-----. 1 postgres postgres 0 Apr 18 03:28 openpgsql-backup.log -rw-r-----. 1 postgres postgres 0 Apr 18 03:28 openpgsql-expire.log -rw-r-----. 1 postgres postgres 0 Apr 18 03:28 openpgsql-restore.log -rw-r-----. 1 postgres postgres 0 Apr 18 03:28 openpgsql-stanza-create.log
Now use check command to verify all configuration created for cluster
-bash-4.2$ pgbackrest --stanza=openpgsql check --log-level-console=info 2021-04-18 22:38:15.152 P00 INFO: check command begin 2.33: --exec-id=713-5783c110 --log-level-console=info --pg1-path=/var/lib/pgsql/13/data --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --stanza=openpgsql 2021-04-18 22:38:15.758 P00 INFO: check repo1 configuration (primary) 2021-04-18 22:38:16.274 P00 INFO: check repo1 archive for WAL (primary) 2021-04-18 22:38:16.676 P00 INFO: WAL segment 00000004000000000000000E successfully archived to '/var/lib/pgbackrest/archive/openpgsql/13-1/0000000400000000/00000004000000000000000E-fe52d642220236e5221c36158fd95c0cc8fddd10.gz' on repo1 2021-04-18 22:38:16.676 P00 INFO: check command end: completed successfully (1525ms)
Now everything looks good , we will try to perform all kind of backups with pgbackrest , type is the argument which is used to specify the type of backup like full,incr,diff backups . –-log-level-console=info will print all the messages of the backups ( use this to monitor the backup operation)
Try full backup
[postgres@MyPosgreHost ~]$ pgbackrest --stanza=openpgsql --type=full backup --log-level-console=info 2021-04-17 19:17:41.465 P00 INFO: backup command begin 2.33: --exec-id=19891-10293389 --log-level-console=info --pg1-path=/var/lib/pgsql/13/data --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=openpgsql --type=full 2021-04-17 19:17:42.176 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes 2021-04-17 19:17:43.179 P00 INFO: backup start archive = 00000004000000000000000D, lsn = 0/D000060 2021-04-17 19:17:44.277 P01 INFO: backup file /var/lib/pgsql/13/data/base/16394/1255 (648KB, 1%) checksum 4752f25689f9f34d4ae9962093bb210167e3fc0c 2021-04-17 19:17:52.742 P00 INFO: full backup size = 39.6MB 2021-04-17 19:17:52.742 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive 2021-04-17 19:17:53.275 P00 INFO: backup stop archive = 00000004000000000000000D, lsn = 0/D000138 2021-04-17 19:17:53.279 P00 INFO: check archive for segment(s) 00000004000000000000000D:00000004000000000000000D 2021-04-17 19:17:53.583 P00 INFO: new backup label = 20210417-191742F 2021-04-17 19:17:53.633 P00 INFO: backup command end: completed successfully (12169ms) 2021-04-17 19:17:53.634 P00 INFO: expire command begin 2.33: --exec-id=19891-10293389 --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=openpgsql 2021-04-17 19:17:53.635 P00 INFO: repo1: expire full backup 20210416-202921F 2021-04-17 19:17:53.640 P00 INFO: repo1: remove expired backup 20210416-202921F 2021-04-17 19:17:53.828 P00 INFO: expire command end: completed successfully (195ms)
The full backup is completed successfully , now verify the backup details using info command
[postgres@MyPosgreHost ~]$ pgbackrest info stanza: openpgsql status: ok cipher: nonedb (current) wal archive min/max (13): 000000020000000000000007/00000002000000000000000B
full backup: 20210416-202921F
timestamp start/stop: 2021-04-17 19:16:21 / 2021-04-17 19:17:53
wal start/stop: 000000020000000000000007 / 000000020000000000000007
database size: 39.6MB, database backup size: 39.6MB
repo1: backup set size: 3.9MB, backup size: 3.9MB
Now take incremental backup , Incremental backup will only take the backup of whatever has been changed since last backup ( size of the backup will be small as compared to full backup)
[postgres@MyPosgreHost ~]$ pgbackrest --stanza=openpgsql --type=incr --log-level-console=info backup 2021-04-16 20:31:03.204 P00 INFO: backup command begin 2.33: --exec-id=10314-7539eda6 --log-level-console=info --pg1-path=/var/lib/pgsql/13/data --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=openpgsql --type=incr 2021-04-16 20:31:03.928 P00 INFO: last backup label = 20210416-203020F, version = 2.33 2021-04-16 20:31:03.928 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes 2021-04-16 20:31:04.675 P00 INFO: backup start archive = 00000002000000000000000B, lsn = 0/B000028
Check the backup incr backp details
[postgres@MyPosgreHost ~]$ pgbackrest info stanza: openpgsql status: ok cipher: nonedb (current) wal archive min/max (13): 000000020000000000000007/00000002000000000000000B
full backup: 20210416-203020F
timestamp start/stop: 2021-04-16 20:30:20 / 2021-04-16 20:30:30
wal start/stop: 000000020000000000000009 / 000000020000000000000009
database size: 39.6MB, database backup size: 39.6MB
repo1: backup set size: 3.9MB, backup size: 3.9MB
incr backup: 20210416-203020F_20210416-203103I
timestamp start/stop: 2021-04-16 20:31:03 / 2021-04-16 20:31:05
wal start/stop: 00000002000000000000000B / 00000002000000000000000B
database size: 39.6MB, database backup size: 8.3KB
repo1: backup set size: 3.9MB, backup size: 427B
backup reference list: 20210416-203020F
Check the backup sizes of full and incremental backup also check the backup reference list for incremental backup which will indicate its parent full backup

Now go to the directory we have specified to store out full and archive backup and check the backup files created there
[postgres@MyPosgreHost ~]$ cd /var/lib/pgbackrest [postgres@MyPosgreHost ~]$ ls -ltr total 0 drwxr-x---. 3 postgres postgres 23 Apr 16 20:06 archive drwxr-x---. 3 postgres postgres 23 Apr 16 20:06 backup [postgres@MyPosgreHost ~]$ cd backup/ [postgres@MyPosgreHost ~]$ ls -ltr total 4 drwxr-x---. 5 postgres postgres 4096 Apr 16 20:11 openpgsql [postgres@MyPosgreHost ~]$ cd openpgsql/ [postgres@MyPosgreHost ~]$ ls -ltr total 8 drwxr-x---. 3 postgres postgres 72 Apr 16 20:07 20210416-200740F drwxr-x---. 3 postgres postgres 18 Apr 16 20:07 backup.history lrwxrwxrwx. 1 postgres postgres 33 Apr 16 20:11 latest -> 20210416- 200740F_20210416-201104I -rw-r-----. 1 postgres postgres 1640 Apr 16 20:11 backup.info -rw-r-----. 1 postgres postgres 1640 Apr 16 20:11 backup.info.copy [postgres@MyPosgreHost ~]$ cd archive/ [postgres@MyPosgreHost ~]$ ls -ltr total 0 drwxr-x---. 3 postgres postgres 63 Apr 16 20:07 openpgsql [postgres@MyPosgreHost ~]$ cd openpgsql/ [postgres@MyPosgreHost ~]$ ls -ltr total 8 -rw-r-----. 1 postgres postgres 253 Apr 16 20:06 archive.info -rw-r-----. 1 postgres postgres 253 Apr 16 20:06 archive.info.copy drwxr-x---. 3 postgres postgres 54 Apr 16 20:19 13-1
Now , since we have out full backup and archive backup in place , we will destroy our existing PostgreSQL cluster and try to restore and recover to point in time from the backup we have
Go to data directory of postgres server
[postgres@MyPosgreHost ~]$ cd /var/lib/pgsql/13/data [postgres@MyPosgreHost ~]$ rm -rf data [postgres@MyPosgreHost ~]$ pwd /var/lib/pgsql/13/data Check if cluster destroyed properly [postgres@MyPosgreHost ~]$ psql psql: error: FATAL: could not open file "global/pg_filenode.map": No such file or directory
The cluster is destroyed now , try to restore the cluster from the backup we have taken in earlier steps
[postgres@MyPosgreHost ~]$ pgbackrest --stanza=openpgsql --db-include=mypgdb --type=immediate --target-action=promote restore --log-level-console=detail 2021-04-17 12:31:44.649 P00 INFO: restore command begin 2.33: --db-include=mypgdb --exec-id=16874-0b7ad930 --log-level-console=detail --pg1-path=/var/lib/pgsql/13/data --repo1-path=/var/lib/pgbackrest --stanza=openpgsql --target-action=promote --type=immediate ERROR: [073]: $PGDATA directory '/var/lib/pgsql/13/data' does not exist 2021-04-17 12:31:44.650 P00 INFO: restore command end: aborted with exception [073] We need to create empty data directory , since we deleted all during rm -rf [postgres@MyPosgreHost ~]$ mkdir -p /var/lib/pgsql/13/data
Again fire the restore command
[postgres@MyPosgreHost ~]$ pgbackrest --stanza=openpgsql --db-include=diff_backup --type=immediate --target-action=promote restore --log-level-console=detail 2021-04-17 12:32:33.296 P00 INFO: restore command begin 2.33: --db-include=diff_backup --exec-id=16895-195fe226 --log-level-console=detail --pg1-path=/var/lib/pgsql/13/data --repo1-path=/var/lib/pgbackrest --stanza=openpgsql --target-action=promote --type=immediate 2021-04-17 12:32:33.321 P00 INFO: repo1: restore backup set 20210416-203020F_20210417-121926I 2021-04-17 12:32:33.321 P00 DETAIL: databases found for selective restore (1, 14174, 14175, 16384, 16394) 2021-04-17 12:32:33.321 P00 DETAIL: databases excluded (zeroed) from selective restore (16384) 2021-04-17 12:32:33.322 P00 DETAIL: check '/var/lib/pgsql/13/data' exists 2021-04-17 12:32:33.322 P00 DETAIL: update mode for '/var/lib/pgsql/13/data' to 0700 2021-04-17 12:32:33.322 P00 DETAIL: create path '/var/lib/pgsql/13/data/base' 2021-04-17 12:32:33.322 P00 DETAIL: create path '/var/lib/pgsql/13/data/base/1' 2021-04-17 12:32:33.322 P00 DETAIL: create path '/var/lib/pgsql/13/data/base/14174' 2021-04-17 12:32:33.322 P00 DETAIL: create path '/var/lib/pgsql/13/data/base/14175' 2021-04-17 12:32:33.322 P00 DETAIL: create path '/var/lib/pgsql/13/data/base/16384' 2021-04-17 12:32:33.322 P00 DETAIL: create path '/var/lib/pgsql/13/data/base/16394' 2021-04-17 12:32:33.417 P01 INFO: restore file /var/lib/pgsql/13/data/base/16394/1255 (648KB, 1%) checksum 4752f25689f9f34d4ae9962093bb210167e3fc0c 2021-04-17 12:32:33.418 P01 DETAIL: restore zeroed file /var/lib/pgsql/13/data/base/16384/1255 (648KB, 3%) 2021-04-17 12:32:38.429 P00 DETAIL: sync path '/var/lib/pgsql/13/data/pg_wal/archive_status' 2021-04-17 12:32:38.429 P00 DETAIL: sync path '/var/lib/pgsql/13/data/pg_xact' 2021-04-17 12:32:38.429 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2021-04-17 12:32:38.429 P00 DETAIL: sync path '/var/lib/pgsql/13/data/global' 2021-04-17 12:32:38.431 P00 INFO: restore command end: completed successfully (5137ms)
Now restore is completed , we can check by logging into the server and verify the details
start the postgres server [postgres@MyPosgreHost ~]$ ./pg_ctl -D /var/lib/pgsql/13/data start waiting for server to start….2021-04-17 12:35:11.773 GMT [17048] LOG: redirecting log output to logging collector process 2021-04-17 12:35:11.773 GMT [17048] HINT: Future log output will appear in directory "log". done server started Check server alert log ,while its starting 2021-04-17 12:35:11.773 GMT [17048] LOG: starting PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2021-04-17 12:35:11.774 GMT [17048] LOG: listening on IPv4 address "127.0.0.1", port 5432 2021-04-17 12:35:11.774 GMT [17048] LOG: could not bind IPv6 address "::1": Cannot assign requested address 2021-04-17 12:35:11.774 GMT [17048] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2021-04-17 12:35:11.776 GMT [17048] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2021-04-17 12:35:11.779 GMT [17048] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2021-04-17 12:35:11.783 GMT [17050] LOG: database system was interrupted; last known up at 2021-04-17 12:19:27 GMT 2021-04-17 12:35:11.806 GMT [17050] LOG: starting point-in-time recovery to earliest consistent point 2021-04-17 12:35:12.001 GMT [17050] LOG: restored log file "00000002000000000000000E" from archive 2021-04-17 12:35:12.213 GMT [17050] LOG: redo starts at 0/E000060 2021-04-17 12:35:12.215 GMT [17050] LOG: consistent recovery state reached at 0/E000138 2021-04-17 12:35:12.215 GMT [17050] LOG: recovery stopping after reaching consistency 2021-04-17 12:35:12.215 GMT [17050] LOG: redo done at 0/E000138 2021-04-17 12:35:12.215 GMT [17048] LOG: database system is ready to accept read only connections 2021-04-17 12:35:12.224 GMT [17050] LOG: selected new timeline ID: 3 2021-04-17 12:35:12.508 GMT [17050] LOG: archive recovery complete 2021-04-17 12:35:12.528 GMT [17048] LOG: database system is ready to accept connections
log in to database and check for databases and tables
[postgres@MyPosgreHost ~]$ psql psql (13.2) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+----------+----------+-------------+-------------+----------------------- diff_backup | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | pgbackrest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows) postgres=# postgres=# postgres=# \c diff_backup You are now connected to database "diff_backup" as user "postgres". diff_backup=# diff_backup=# diff_backup=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | attendees | table | postgres public | couples | table | postgres public | diff_backup | table | postgres public | diff_backup_2 | table | postgres (4 rows)
The database is restored and recover properly , but it doesn’t have the tables which I have created after taking full and incr backup , so we will be restoring the archive (wal) to certain point in time , where we have have created the tables
[postgres@MyPosgreHost ~]$ pgbackrest --stanza=openpgsql --delta --type=time "--target=2021-04-17 12:30:28" restore ; Check the logs if it recovering the db correctly 2021-04-17 12:51:07.711 GMT [17255] LOG: starting point-in-time recovery to 2021-04-17 12:30:28+00 2021-04-17 12:51:07.719 GMT [17255] LOG: restored log file "00000003.history" from archive 2021-04-17 12:51:07.887 GMT [17255] LOG: restored log file "00000002000000000000000B" from archive 2021-04-17 12:51:08.173 GMT [17255] LOG: redo starts at 0/B000028 2021-04-17 12:51:08.175 GMT [17255] LOG: consistent recovery state reached at 0/B000138 2021-04-17 12:51:08.176 GMT [17253] LOG: database system is ready to accept read only connections 2021-04-17 12:51:08.316 GMT [17255] LOG: restored log file "00000002000000000000000C" from archive 2021-04-17 12:51:08.722 GMT [17255] LOG: restored log file "00000002000000000000000D" from archive 2021-04-17 12:51:09.111 GMT [17255] LOG: restored log file "00000003000000000000000E" from archive 2021-04-17 12:51:09.394 GMT [17255] LOG: redo done at 0/E0001E8 2021-04-17 12:51:09.394 GMT [17255] LOG: last completed transaction was at log time 2021-04-17 10:28:32.158471+00 2021-04-17 12:51:09.394 GMT [17255] FATAL: recovery ended before configured recovery target was reached 2021-04-17 12:51:09.396 GMT [17253] LOG: startup process (PID 17255) exited with exit code 1 2021-04-17 12:51:09.396 GMT [17253] LOG: terminating any other active server processes 2021-04-17 12:51:09.399 GMT [17253] LOG: database system is shut down
So the recovery fail, since it doesnt have the wal log for the time we have specified , but the log files give you the time where we have a consistent wal transaction backup
last completed transaction was at log time 2021-04-17 10:28:32.158471+00
We can use this point in time to restore and check
[postgres@MyPosgreHost ~]$ pgbackrest --stanza=openpgsql --delta --type=time "--target=2021-04-17 10:28:32" restore ; Restart the postgres server [postgres@MyPosgreHost ~]$ ./pg_ctl -D /var/lib/pgsql/13/data start waiting for server to start….2021-04-17 12:52:47.331 GMT [17284] LOG: redirecting log output to logging collector process 2021-04-17 12:52:47.331 GMT [17284] HINT: Future log output will appear in directory "log". done server started [postgres@MyPosgreHost ~]$ [postgres@MyPosgreHost ~]$ [postgres@MyPosgreHost ~]$ ./pg_ctl -D /var/lib/pgsql/13/data status pg_ctl: server is running (PID: 17284) /usr/pgsql-13/bin/postgres "-D" "/var/lib/pgsql/13/data"
Go and check the server log file now
[postgres@MyPosgreHost ~]$ cd /var/lib/pgsql/13/data/log/ [postgres@MyPosgreHost ~]$ ls -ltr total 12 -rw-------. 1 postgres postgres 4941 Apr 16 20:28 postgresql-Fri.log -rw-------. 1 postgres postgres 1822 Apr 17 12:52 postgresql-Sat.log [postgres@MyPosgreHost ~]$ tail -20f postgresql-Sat.log 2021-04-17 12:52:47.332 GMT [17284] LOG: starting PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2021-04-17 12:52:47.332 GMT [17284] LOG: listening on IPv4 address "127.0.0.1", port 5432 2021-04-17 12:52:47.332 GMT [17284] LOG: could not bind IPv6 address "::1": Cannot assign requested address 2021-04-17 12:52:47.332 GMT [17284] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2021-04-17 12:52:47.334 GMT [17284] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2021-04-17 12:52:47.337 GMT [17284] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2021-04-17 12:52:47.340 GMT [17286] LOG: database system was interrupted; last known up at 2021-04-16 20:31:04 GMT 2021-04-17 12:52:47.380 GMT [17286] LOG: restored log file "00000003.history" from archive 2021-04-17 12:52:47.388 GMT [17286] LOG: starting point-in-time recovery to 2021-04-17 10:28:32+00 2021-04-17 12:52:47.397 GMT [17286] LOG: restored log file "00000003.history" from archive 2021-04-17 12:52:47.587 GMT [17286] LOG: restored log file "00000002000000000000000B" from archive 2021-04-17 12:52:47.873 GMT [17286] LOG: redo starts at 0/B000028 2021-04-17 12:52:47.875 GMT [17286] LOG: consistent recovery state reached at 0/B000138 2021-04-17 12:52:47.876 GMT [17284] LOG: database system is ready to accept read only connections 2021-04-17 12:52:48.009 GMT [17286] LOG: restored log file "00000002000000000000000C" from archive 2021-04-17 12:52:48.287 GMT [17286] LOG: recovery stopping before commit of transaction 500, time 2021-04-17 10:28:32.158471+00 2021-04-17 12:52:48.287 GMT [17286] LOG: pausing at the end of recovery 2021-04-17 12:52:48.287 GMT [17286] HINT: Execute pg_wal_replay_resume() to promote.
Execute pg_wal_replay_resume()
[postgres@MyPosgreHost ~]$ psql psql (13.2) Type "help" for help. postgres=# postgres=# postgres=# select pg_wal_replay_resume() ; pg_wal_replay_resume (1 row)
2021-04-17 12:59:56.943 GMT [17319] HINT: Execute pg_wal_replay_resume() to promote. 2021-04-17 12:59:56.943 GMT [17317] LOG: database system is ready to accept read only connections 2021-04-17 13:00:36.015 GMT [17319] LOG: redo done at 0/C038340 2021-04-17 13:00:36.015 GMT [17319] LOG: last completed transaction was at log time 2021-04-17 10:27:01.622391+00 2021-04-17 13:00:36.025 GMT [17319] LOG: selected new timeline ID: 4 2021-04-17 13:00:36.311 GMT [17319] LOG: archive recovery complete 2021-04-17 13:00:36.319 GMT [17319] LOG: restored log file "00000003.history" from archive 2021-04-17 13:00:36.339 GMT [17317] LOG: database system is ready to accept connections
Now check the db and tables
postgres=# \c diff_backup You are now connected to database "diff_backup" as user "postgres". diff_backup=# \dt List of relations Schema | Name | Type | Owner -----------+---------------+-------+---------- hollywood | films | table | postgres parwez | attendees_new | table | postgres public | attendees | table | postgres public | couples | table | postgres public | diff_backup | table | postgres public | diff_backup_2 | table | postgres (6 rows)
638 total views, 1 views today