While the Postgresql database is running, i.e., when the instance is up, it can be
backed up with pg_basebackup. Users are not affected during the backup process,
and both full recovery and point-in recovery can be performed with this backup. In
addition, this backup can be used for hot standby setup.
Basebackup automatically takes the database in backup mode and copies the entire
cluster directory. It cannot back up a single database or one or two objects, it backs up
all databases with all their objects. pg_dump can be used to backup selected objects.
Backup is done over the postgresql connection using the replication protocol. The user
to take the backup must have the replication or superuser privilege and access must be
given in the pg_hba.conf file.
More than one pg_basebackup can be run at the same time, but it is recommended
to run one by one to avoid IO bottlenecks. (Or the first backup can be copied.)
With pg_basebackup, it can take backup from the primary server or the standby
server. There are some limitations to take backup from standby server. See the official
manual page for details.
# Take full backup by compressing the backup directory pg_basebackup -D /RA/bb -c fast -l “`date`” -P -F tar -z -X fetch -R
# Backup to the directory containing today’s date and backup
time
CURDATE=`date +%H%M_%d%m%y`
BCKDIR=/RA/basebackup
LOG=$BCKDIR/basebackup_${CURDATE}.log
pg_basebackup -D $BCKDIR/$CURDATE -c fast -v -l “`date`” -P -F
tar -z -X fetch -R &>> $LOG
# or
pg_basebackup -D $BCKDIR/$CURDATE -c fast -v -l “`date`” -P -F
tar -z -X stream -R &>> $LOG
It takes a tar.gz backup to the folder named /RA/basebackup/$TimeMinute_
DayMonthYear and writes the log to the same directory.
In order for the backup to work, the WAL files created during and after the backup are
needed. It creates a text file by adding .backup extension to the name of the WAL file
to mark the starting point of the backup. Label writes information such as start and end
point.
Restore & Recovery
# Full backup is taken. pg_basebackup -D /RA/basebackup/fullbackup -l “`date`” -c fast -P -v -F tar -z -X fetch -R # Backup content postgres@srv1:~$ ls -hs1 /RA/basebackup/fullbackup/ 932K 33016.tar.gz 4.0K 33024.tar.gz 4.0K 33029.tar.gz 616K backup_manifest 54M base.tar.gz # Tablespace OIDs postgres=# select * from pg_tablespace ; oid | spcname | spcowner | spcacl | -------+------------+----------+--------+ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 33016 | erp_tbs | 10 | | ---> /erp 33024 | ik_tbs | 16486 | | ---> /ik 33029 | tmp_tbs1 | 10 | | ---> /tmp_tbs
The backup is taken in compressed in tar format. While the default tablespaces are
in base.tar.gz. Subsequently created tablespaces are written to separate files with their
own OIDs.
The cluster directory of the database i.e. PGDATA = /pg/data/13.6 and the wal
files were backed up to the /RA/archive directory.
If one or more of the PG_DATA, /erp and /ik directories are inaccessible,
If the WAL/data file being written is deleted or corrupted (corrupt), a full recovery is
required.
Full Restore from Backup (Full Recovery)
# As per the scenario, the directories /pg/data/13.6, /erp, /ik and / tmp_tbs are deleted. postgres@srv1:~$ rm -rf /pg/data/13.6/* postgres@srv1:~$ rm -rf /erp postgres@srv1:~$ rm -rf /ik postgres@srv1:~$ rm -rf /tmp_tbs postgres@srv1:~$ pkill postgres
Step 1
# Recovery processes are delayed. The owner of the folders must be the postgres user. postgres@srv1:/$ sudo mkdir /erp postgres@srv1:/$ sudo chown -R postgres:postgres /erp postgres@srv1:/$ sudo mkdir /ik postgres@srv1:/$ sudo chown -R postgres:postgres /ik postgres@srv1:/$ sudo mkdir /tmp_tbs postgres@srv1:/$ sudo chown -R postgres:postgres /tmp_tbs/
The necessary folders are created. If restoring from backup on a different machine,
postgresql version 13.6 (i.e., the same version as the backup postgresql) must be
installed first (without running initdb). Since the source code in the chapter 1 is
explained and the installation from ready packages with apt is explained in the chapter
16, it is not mentioned here again.
Step 2
# Data files are restored to original locations. tar xvf /RA/basebackup/fullbackup/base.tar.gz -C /pg/data/13.6/ tar xvf /RA/basebackup/fullbackup/33016.tar.gz -C /erp tar xvf /RA/basebackup/fullbackup/33024.tar.gz -C /ik tar xvf /RA/basebackup/fullbackup/33029.tar.gz -C /tmp_tbs/ # The wal files copied with the operating system are deleted and the archived wal files are copied. postgres@srv1:/$ rm -rf /pg/data/13.6/pg_wal/* postgres@srv1:/$ cp /RA/archive/* /pg/data/13.6/pg_wal/
tar files are extracted to their original locations. It should be noted that disk space
is needed for both backup and data. It can be a problem in environments with 100s
of terabytes of data. Therefore, the disk space needed should be calculated before
starting the process. The script should be created. If the network speed is sufficient,
the disks with the backups can be attached to the system over the network (NFS
mount).
Step 3
ln -s /erp /pg/data/13.6/pg_tblspc/33016 ln -s /ik /pg/data/13.6/pg_tblspc/33024 ln -s /tmp_tbs /pg/data/13.6/pg_tblspc/33029
Links are created. OID and folder information are available in /pg/data/13.6/
tablespace_map file.
Step 4
postgres@srv1:/$ rm /pg/data/13.6/standby.signal postgres@srv1:/$ touch /pg/data/13.6/recovery.signal postgres@srv1:/$ vim /pg/data/13.6/postgresql.conf restore_command = ‘cp /RA/archive/%f “%p”’ recovery_target_timeline = ‘latest’
To start in recovery mode, the recovery.signal file is created in the $PGDATA directory
and the standby.signal is deleted. If both files are found at the same time, standby
mode is enabled.The restore_command and recovery_target_timeline
parameters are added to the postgresql.conf file.
Note: Before version 12, these parameters were written to the recovery.conf file.
If recovery_target = ‘immediate’ is added, it will terminate the recovery
process and opens the database as soon as it reaches a consistent point.
Step 5
postgres@srv1:~$ which pg_ctl /pg/home/13.6/bin/pg_ctl pg_ctl -o ‘--config-file=/pg/data/13.6/postgresql.conf’ -D /pg/ data/13.6/ -l pg.log start “tail -f postgresql-Sat.log” düşen loglar incelenir. starting PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit listening on IPv4 address “0.0.0.0”, port 5434 listening on IPv6 address “::”, port 5434 listening on Unix socket “/tmp/.s.PGSQL.5434” database system was interrupted; last known up at 2022-08-13 12:20:41 +03 cp: cannot stat ‘/RA/archive/00000002.history’: No such file or directory starting archive recovery restored log file “00000001000000000000004D” from archive redo starts at 0/4D000028 consistent recovery state reached at 0/4D000138 database system is ready to accept read only connections cp: cannot stat ‘/RA/archive/00000001000000000000004E’: No such file or directory redo done at 0/4D000138 restored log file “00000001000000000000004D” from archive cp: cannot stat ‘/RA/archive/00000002.history’: No such file or directory selected new timeline ID: 2 archive recovery complete cp: cannot stat ‘/RA/archive/00000001.history’: No such file or directory database system is ready to accept connections
After all the preparations for archive recovery are completed, the instance is started
(with systemctl or manual pg_ctl depending on your installation).
It is recommended to add # before the parameters of restore_command and
recovery_target_timeline in the postgresql.conf file.