Differences

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

--- postgresql_backup_recovery [2019/08/01 12:30] – andonovj
+++ postgresql_backup_recovery [2025/05/12 15:48] (current) – andonovj
@@ Line 7: / Line 7: @@
-======Backup======
+======Backup & Recovery======
 Basically there are two ways how a PostgreSQL database can be backed up:
   * [[PostgreSQL_backup_recovery_physical|Physical]]
   * [[PostgreSQL_backup_recovery_logical|Logical]]
+  * [[postgresql_barman_configuration| Barman]]
+  * [[postgresql_pgbackrest_configuration | Pgbackrest]]
-=====Logical=====
-Logical backup refers to simple export of a data, achieved by the pg_dump. It is important to note that logical backup cannot be used in conjunction of XLOG or other incremental features, but it is very convenient for data migration.
-Logical backup can also be configured in many ways, as simple as to dump a particular database in SQL script file.
-In nutshell there are two things which you should remember:
-  - Dump global data: like roles and users with pg_dumpall
-  - Dump a particular database: with pg_dump
-Furthermore, the best way is to use a customer format "-Fc" in order to preserve consistency across versions:
-<sxh bash>
--bash-4.2$ pg_dump -Fc -C hunter_dev > hunter_dev_backup.sql   <- This will dump database "hunter_dev" with "c"ustom format and it will include "C"reate statement.
--bash-4.2$
--bash-4.2$ ls -lart
--rw-r--r--.  1 postgres postgres 17187218 Aug  1 12:43 hunter_dev_backup.sql
-</sxh>
-Our backup will not be so much readable with text editors:
-<sxh bash>
--bash-4.2$ head hunter_dev_backup.sql                                            +
-PGDMP
-w
-hunter_dev10.310.3p0ENCODINENCODINGSET client_encoding = 'UTF8';
-falseq00
-STDSTRINGS
-STDSTRINGS(SET standard_conforming_strings = 'on';
-falser00
-SEARCHPATH
-SEARCHPATH8SELECT pg_catalog.set_config('search_path', '', false);
-falses126232979
-hunter_deDATABASE|CREATE DATABASE hunter_dev WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE = 'en_GB.UTF-8';
-</sxh>
-Or even, further to dump the database in a directory using 5 slaves:
-<sxh bash>
--bash-4.1$ pg_dump -Fd sales -j 5 -f salesdir
--bash-4.1$
--bash-4.1$ ls -lart
-total 20
-dr-xr-xr-x. 23 root     root     4096 Mar 20 09:53 ..
--rw-r--r--.  1 postgres postgres 1313 Mar 20 10:04 sales.sql
-drwx------.  2 postgres postgres 4096 Mar 20 10:05 dumpdir
-drwxr-xr-x.  4 postgres postgres 4096 Mar 20 10:05 .
-drwx------.  2 postgres postgres 4096 Mar 20 10:05 salesdir
--bash-4.1$ cd salesdir/
--bash-4.1$ ls -lart
-total 20
--rw-r--r--. 1 postgres postgres 2048 Mar 20 10:05 toc.dat
--rw-r--r--. 1 postgres postgres   27 Mar 20 10:05 2993.dat.gz
--rw-r--r--. 1 postgres postgres   25 Mar 20 10:05 2992.dat.gz
-drwx------. 2 postgres postgres 4096 Mar 20 10:05 .
-drwxr-xr-x. 3 postgres postgres 4096 Mar 20 10:05 ..
-</sxh>
-======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.
-=====Physical=====
-====Whole server=====
-With Barman we can easily restore your, 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 [email protected]" 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
-----
-</sxh>
-====Tablespace=====
-====Table=====
-=====Logical=====
-The recovery depends on the backup, like any other database, backup and recovery tools come in pairs.
-So let's see how to recover a file generated by pg_dump.
-<sxh bash>
--bash-4.2$ psql -a -f hunter_dev_pgsql.sql
---
--- PostgreSQL database dump
---
--- Dumped from database version 10.0
--- Dumped by pg_dump version 10.0
-SET statement_timeout = 0;
-SET
-SET lock_timeout = 0;
-SET
-SET idle_in_transaction_session_timeout = 0;
-SET
-SET client_encoding = 'UTF8';
-SET
-SET standard_conforming_strings = on;
-SET
-SET check_function_bodies = false;
-SET
-SET client_min_messages = warning;
-SET
-SET row_security = off;
-SET
-</sxh>
-This command will execute and show all the input of file: hunter_dev_pgsql.sql
-====== Appendix ======
-----
-Here is a nice script to automate the export of all databases on a server:
-<sxh bash>
-#!/bin/bash
-# => location to place backups
-backup_dir="/dati/backups/"
-backup_file="/var/lib/pgsql/croncmd/backup/logs/backup.log"
-# => string to append to the name of the backup files
-backup_date=`date +%F`
-# => number of days you want to keep copies of your databases
-#number_of_days=2
-number_of_days=1
-#elimina il file di log vecchio
-rm -rf $backup_file
-# get a list of databases to back up
-databases=`psql -l -t | egrep -v "^ ( |template[01])" | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
-echo $databases >> $backup_file
-# starting time
-echo [`date`] START PostgreSQL backups >> $backup_file
-# back up each database
-numero_database=0
-for i in $databases; do
-numero_database=$(( $numero_database + 1 ))
-outfile=$backup_dir$backup_date-$i.dump.gz
-echo -n "–> Dumping $i to $outfile" >> $backup_file
-/usr/pgsql-9.1/bin/pg_dump -Fc $i | gzip > $outfile
-outfilesize=`ls -lh $outfile | awk '{ print $5 }'`
-echo " ($outfilesize)"  >> $backup_file
-done
-# clean old backups
-echo "–> Cleaning up backups older than $number_of_days days" >> $backup_file
-rm -f `find $backup_dir -type f -prune -mtime +$number_of_days`
-# finish time
-echo [`date`] STOP PostgreSQL backups >> $backup_file
-data=`date +%Y-%m-%d`
-backup_file_today=$backup_dir$data"*"
-backup_creati=`find $backup_file_today -maxdepth 1 -type f -print | wc -l`
-#Controllo che il numero di backup creati sia uguale al numero dei database
-if [ $numero_database == $backup_creati ] ; then
-        echo "PROCEDURA BACKUP TERMINATA SENZA ERRORI" >> $backup_file
-else
-        echo "ERRORE NELLA PROCEDURA BACKUP" >> $backup_file
-fi
-</sxh>
-Which can be put in crontab as follows:
-<sxh bash>
-# Minute   Hour   Day of Month       Month          Day of Week        Command
-# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)
-       4          *             *                   *              /path/startCron.sh
-</sxh>