Point in time recovery using pgBackRest for PostgreSQL

By parwez on April 19, 2021
LinkedIn0

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: none
db (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: none
db (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

LinkedIn0

Leave a Reply

Your email address will not be published. Required fields are marked *