Show pageOld revisionsBacklinksODT exportBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. =====Overview===== Physical restore, refers to the physical representation of the data. In other words, the data as HOW IT IS, rather than how it can be RE-DONE. In that sense, physical backup is often just a copy paste of the data files. PostgreSQL has mainly 2 types of a backup: * BaseBackup * Other OS Backup =====Backup===== Base backup in PostgreSQL can be done using two different methods: **Old** one is using the standard Linux commands: scp, cp, rsync, etc: - Ensure that WAL archiving is enabled and working. - Connect to the database as a superuser and issue the command: - SELECT pg_start_backup('label'); you can use SELECT pg_start_backup('label', true); - to minimaze the checkpoint and start ASAP - Perform a backup (either via rsync or cp or others) - SELECT pg_stop_backup(); **Newer** one is newer and it is using utility called pg_basebackup. Let's start with the first one: ===Manually=== <sxh bash> -bash-4.1$ psql -c "select pg_start_backup('initial_backup');" pg_start_backup ----------------- 0/14000028 (1 row) rsync -cva --inplace --exclude=*pg_xlog* /var/lib/pgsql/9.6/data/ 192.168.0.11:/var/lib/pgsql/9.6/data/ postgres@192.168.0.11's password: sending incremental file list ./ backup_label postgresql.conf postmaster.pid base/13275/ base/13275/16384 base/13275/pg_internal.init global/ global/pg_control global/pg_internal.init pg_log/postgresql-Thu.log pg_notify/ pg_stat/ pg_stat_tmp/ pg_stat_tmp/db_0.stat pg_stat_tmp/db_13275.stat pg_stat_tmp/global.stat sent 152804 bytes received 2005 bytes 20641.20 bytes/sec total size is 22936284 speedup is 148.16 -bash-4.1$ psql -c "select pg_stop_backup();" NOTICE: pg_stop_backup complete, all required WAL segments have been archived pg_stop_backup ---------------- 0/14000130 (1 row) -bash-4.1$ </sxh> This will copy all files from the source folder into the destination folder, while the PostgreSQL server is running. Please do not forget to exit the backup mode. Even though it will be safe to leave it as it is. It is a good practice to finish the backup mode as soon as possible. ===Automatic=== You can use pg_basebackup which will perform all steps automatically. <sxh bash> -bash-4.1$ pg_basebackup -U postgres -D /backup/ NOTICE: pg_stop_backup complete, all required WAL segments have been archived -bash-4.1$ ls -lart total 128 dr-xr-xr-x. 23 root root 4096 Mar 20 09:53 .. -rwxrwx---. 1 postgres postgres 22417 Mar 20 10:49 postgresql.conf -rwxrwx---. 1 postgres postgres 4 Mar 20 10:49 PG_VERSION drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_snapshots drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_serial -rwxrwx---. 1 postgres postgres 1636 Mar 20 10:49 pg_ident.conf drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_dynshmem -rw-------. 1 postgres postgres 208 Mar 20 10:49 backup_label drwxrwx---. 6 postgres postgres 4096 Mar 20 10:49 base -rwxrwx---. 1 postgres postgres 88 Mar 20 10:49 postgresql.auto.conf drwxrwx---. 3 postgres postgres 4096 Mar 20 10:49 pg_xlog drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_twophase drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_tblspc drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_subtrans drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_stat_tmp drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_notify drwxrwx---. 4 postgres postgres 4096 Mar 20 10:49 pg_multixact drwxrwx---. 4 postgres postgres 4096 Mar 20 10:49 pg_logical -rwxrwx---. 1 postgres postgres 4403 Mar 20 10:49 pg_hba.conf drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_stat drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_replslot drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_log drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_commit_ts drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 pg_clog drwxrwx---. 2 postgres postgres 4096 Mar 20 10:49 global drwxr-xr-x. 20 postgres postgres 4096 Mar 20 10:49 . -bash-4.1$ pwd /backup -bash-4.1$ </sxh> You can of course configure more sophisticated options, such as a tar file using gzip utilities. After the base backup is done, it a copy of the database to a specific point of time, that copy can be pushed forward (recovered) via XLOGs. The barman application, which is configured in the next section, uses pg_basebackup in order to perform the backups. You can also use the pg_basebackup tool to take the backup, instead of manually copying the files. This tool will do the equivalent of pg_start_backup(), copy and pg_stop_backup() steps automatically, and transfers the backup over a regular PostgreSQL connection using the replication protocol, instead of requiring file system level access. pg_basebackup does not interfere with file system level backups taken using pg_start_backup()/pg_stop_backup(). Some file system backup tools emit warnings or errors if the files they are trying to copy change while the copy proceeds. When taking a base backup of an active database, this situation is normal and not an error. However, you need to ensure that you can distinguish complaints of this sort from real errors. For example, some versions of rsync return a separate exit code for "vanished source files", and you can write a driver script to accept this exit code as a non-error case. Also, some versions of GNU tar return an error code indistinguishable from a fatal error if a file was truncated while tar was copying it. Fortunately, GNU tar versions 1.16 and later exit with 1 if a file was changed during the backup, and 2 for other errors. It is not necessary to be concerned about the amount of time elapsed between pg_start_backup and the start of the actual backup, nor between the end of the backup and pg_stop_backup; a few minutes' delay won't hurt anything. (However, if you normally run the server with full_page_writes disabled, you might notice a drop in performance between pg_start_backup and pg_stop_backup, since full_page_writes is effectively forced on during backup mode.) You must ensure that these steps are carried out in sequence, without any possible overlap, or you will invalidate the backup. ===== Restore & Recovery ===== Restore and Recovery have been ALWAYS a different topics in the eyes of a DBA: * Restore - Relates to the physical restore of files, not for making the files work together and be consistent between themselves, but only to put some version of the dataifiles somewhere, that is all what restore stands for. * Recovery - Relates to the synchronization between the datafiles and log files, in other words making everything consistent. ====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 2020-01-22 09:23:30.111 EST [4130] LOG: listening on IPv6 address "::", port 5432 2020-01-22 09:23:30.114 EST [4130] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-01-22 09:23:30.129 EST [4130] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2020-01-22 09:23:30.149 EST [4130] LOG: redirecting log output to logging collector process 2020-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. To list existing backups, log in on your central barman server and issue the following command: <sxh bash> -bash-4.2$ barman list-backup pg pg 20180603T074928 - Sun Jun 3 13:48:58 2018 - Size: 28.9 MiB - WAL Size: 53.2 KiB pg 20180603T073804 - Sun Jun 3 13:37:35 2018 - Size: 28.9 MiB - WAL Size: 53.2 KiB pg 20180404T061948 - Wed Apr 4 12:19:20 2018 - Size: 28.9 MiB - WAL Size: 117.2 KiB -bash-4.2$ barman show-backup pg latest Backup 20180603T074928: Server Name : pg Status : DONE PostgreSQL Version : 90607 PGDATA directory : /var/lib/pgsql/9.6/data Base backup information: Disk usage : 28.9 MiB (28.9 MiB with WALs) Incremental size : 8.8 KiB (-99.97%) Timeline : 1 Begin WAL : 00000001000000000000003C End WAL : 00000001000000000000003C WAL number : 1 WAL compression ratio: 99.84% Begin time : 2018-06-03 13:48:57.072346+02:00 End time : 2018-06-03 13:48:58.647049+02:00 Copy time : less than one second Estimated throughput : 13.9 KiB/s Begin Offset : 40 End Offset : 248 Begin LSN : 0/3C000028 End LSN : 0/3C0000F8 WAL information: No of files : 2 Disk usage : 53.2 KiB WAL rate : 15.19/hour Compression ratio : 99.84% Last available : 00000001000000000000003E Catalog information: Retention Policy : VALID Previous Backup : 20180603T073804 Next Backup : - (this is the latest base backup) </sxh> This will show us the list of the backups, their status and the late command, will show us the latest one: <sxh bash> -bash-4.2$ barman recover --remote-ssh-command "ssh postgres@192.168.0.10" pg 20180603T074928 /var/lib/pgsql/9.6/data_restore --target-time '2018-06-03 13:48:58.647049+02:00' Starting remote restore for server pg using backup 20180603T074928 Destination directory: /var/lib/pgsql/9.6/data_restore Doing PITR. Recovery target time: '2018-06-03 13:48:58.647049+02:00' Copying the base backup. Copying required WAL segments. Generating recovery.conf Identify dangerous settings in destination directory. IMPORTANT These settings have been modified to prevent data losses postgresql.conf line 216: archive_command = false Your PostgreSQL server has been successfully prepared for recovery! </sxh> The barman will prepare also your recovery.conf script. <sxh bash> [root@postegresqlmaster data_restore]# cat recovery.conf 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> You can also instruct PostgreSQL to use barman for the recovery of the xlog files. Here example of such recovery.conf file. <sxh bash> standby_mode = 'on' primary_conninfo = 'host=192.168.18.101 port=5432 user=postgres' # The 'barman-wal-restore' command is provided in the 'barman-cli' package restore_command = 'barman-wal-restore -U barman tain-mt-backup1.net.tain.com tain-mt-cdb2 %f %p' </sxh> So in order to start the recovery, just rename the data folder and restart the service: <sxh bash> [root@postegresqlmaster data]# service postgresql-9.6 start Starting postgresql-9.6 service: [ OK ] [root@postegresqlmaster data]# </sxh> Once the recovery has performed, the server will change the recovery.conf file to recovery.done, signaling to the DBA that the recovery is completed: <sxh bash> [root@postegresqlmaster data]# cat recovery.done 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' [root@postegresqlmaster data]# su - postgres -bash-4.1$ -bash-4.1$ psql psql (9.6.7) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | sales | 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 (4 rows) postgres=# select * from testtable postgres-# ; id ---- 1 1 2 2 </sxh> ====Particular Database==== postgresql_backup_recovery_physical.txt Last modified: 2020/01/27 17:45by 127.0.0.1