Backup Command:
/usr/pgsql-12/bin/pg_basebackup -r 1024 --wal-method=stream -h localhost -p 5432 -U postgres -W -F t -R -D /kuppala/postgres/backup --verbose
- recovery.conf is no more available in PostgreSQL 12.
- It is replaced with recovery.signal and standby.signal(both are empty).All parameters are available in postgresql.conf
Command to get time:
psql> select current_date;[OR]
psql> select current_time;
[OR]
postgres@test=# select now();
| now |
+-------------------------------+
| 2021-09-06 02:37:46.778036-04 |
+-------------------------------+
Restore/Recovery Steps:
1) Stop Postgres service, if it is running
2) Untar files
tar -xvf pg_wal.tar $PGDATA/
3) Remove standby.signal and keep recovery. signal in the data directory
4) Add/update restore & recovery parameters to postgresql.conf, Start the Postgres service, and monitor the log
restore_command = 'cp /kuppala/postgres/archivedir/%f %p'
recovery_target_time = '2021-05-09 05:18:06.58366-04' (Remove this, if you want to restore to the latest backup time)
Sample Output:
2021-05-09 05:58:54.282 EDT [71278] LOG: restored log file "00000002.history" from archive
2021-05-09 05:58:54.284 EDT [71278] LOG: restored log file "00000003.history" from archive
cp: cannot stat ‘/kuppala/postgres/archivedir/00000004.history’: No such file or directory
2021-05-09 05:58:54.287 EDT [71278] LOG: starting point-in-time recovery to 2021-05-09 05:18:06.58366-04
2021-05-09 05:58:54.290 EDT [71278] LOG: restored log file "00000003.history" from archive
2021-05-09 05:58:54.309 EDT [71278] LOG: restored log file "00000001000000050000005C" from archive
2021-05-09 05:58:54.378 EDT [71278] LOG: restored log file "00000002.history" from archive
2021-05-09 05:58:54.386 EDT [71278] LOG: redo starts at 5/5C000028
2021-05-09 05:58:54.388 EDT [71278] LOG: consistent recovery state reached at 5/5C000138
2021-05-09 05:58:54.388 EDT [71271] LOG: database system is ready to accept read only connections
2021-05-09 05:58:54.407 EDT [71278] LOG: restored log file "00000002000000050000005D" from archive
2021-05-09 05:58:54.509 EDT [71278] LOG: restored log file "00000002000000050000005E" from archive
2021-05-09 05:58:54.590 EDT [71278] LOG: restored log file "00000002000000050000005F" from archive
2021-05-09 05:58:54.673 EDT [71278] LOG: restored log file "000000020000000500000060" from archive
2021-05-09 05:58:54.757 EDT [71278] LOG: restored log file "000000030000000500000061" from archive
2021-05-09 05:58:54.823 EDT [71278] LOG: recovery stopping before commit of transaction 535, time 2021-05-09 05:18:42.855822-04
2021-05-09 05:58:54.823 EDT [71278] LOG: recovery has paused
2021-05-09 05:58:54.823 EDT [71278] HINT: Execute pg_wal_replay_resume() to continue.
DISCLAIMER
The purpose of sharing the content on this website is to Educate. The author/owner of the content does not warrant that the information provided on this website is fully complete and shall not be responsible for any errors or omissions. The author/owner shall have neither liability nor responsibility to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the contents of this website. Any implementation by you based on the information given in this blog (Best Tech Reads) is strictly at your own risk. So, use the content of this website at your own risk.