Differences

This shows you the differences between two versions of the page.

--- postgresql_backup_recovery_physical [2019/09/16 07:06] – andonovj
+++ postgresql_backup_recovery_physical [2020/01/27 17:45] (current) – external edit 127.0.0.1
@@ Line 10: / Line 10: @@
 Base backup in PostgreSQL can be done using two different methods:
+**Old** one is using the standard Linux commands: scp, cp, rsync, etc:
-First one is using the standard Linux commands: scp, cp, rsync, etc:
   - Ensure that WAL archiving is enabled and working.
@@ Line 19: / Line 18: @@
   - SELECT pg_stop_backup();
-Second one is newer and it is using utility called pg_basebackup.
+**Newer** one is newer and it is using utility called pg_basebackup.
 Let's start with the first one:
@@ Line 117: / Line 116: @@
-=====Restore & Recovery=====
+===== Restore & Recovery =====
 Restore and Recovery have been ALWAYS a different topics in the eyes of a DBA:
@@ Line 123: / Line 122: @@
   * Recovery - Relates to the synchronization between the datafiles and log files, in other words making everything consistent.
-====Whole Server====
+====Restore====
+Restore of pg_basebackup is done by copying the content of the backup in the data folder. That's all. Pg_basebackup performs consistent backup and as such it is enough just to copy the content on empty data dir. Please be sure to keep the OLD WAL files prior any deletion. They are extremely important.
+<sxh bash>
+-bash-4.2$ rm -rf /var/lib/pgsql/10/data
+-bash-4.2$ mkdir /var/lib/pgsql/10/data
+-bash-4.2$ chmod 700 /var/lib/pgsql/10/data  <-This is important, otherwise, your postgresql won't start
+-bash-4.2$ cp -R * /var/lib/pgsql/10/data
+</sxh>
+====Recovery====
+Manual recovery is done by creating recovery.conf file, for example:
+<sxh bash>
+restore_command = 'cp barman_xlog/%f %p'
+recovery_end_command = 'rm -fr barman_xlog'
+recovery_target_time = '2018-06-03 13:48:58.647049+02:00'
+</sxh>
+And restart the service:
+<sxh bash>
+-bash-4.2$ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data start
+waiting for server to start....2020-01-22 09:23:30.111 EST [4130] LOG:  listening on IPv4 address "0.0.0.0", port 5432
+-01-22 09:23:30.111 EST [4130] LOG:  listening on IPv6 address "::", port 5432
+-01-22 09:23:30.114 EST [4130] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
+-01-22 09:23:30.129 EST [4130] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
+-01-22 09:23:30.149 EST [4130] LOG:  redirecting log output to logging collector process
+-01-22 09:23:30.149 EST [4130] HINT:  Future log output will appear in directory "log".
+</sxh>
+Alternatively, you can use Barman, to perform both operation:
+====Using Barman====
 With Barman we can easily restore your server, if you have one :). Please ensure that **YOU NEVER RESTORE IN YOUR ORIGINAL DATA DIRECTORY**, this might cause problems.