Like other RDBMSes, the world’s most advanced opensource database PostgreSQL also comes with a wide range of backup solutions Logical & Physical. Logical backup is a method of dumping the database into single or multiple files which can be restored to the time of backup taken using a dump file. On the other hand, Physical backup in PostgreSQL is a backup of the base directory where database files are stored, this backup is useful for crash recovery and to perform point in time recovery. In this article, we’ll cover up the basics of backup types and options in PostgreSQL.
pg_dump and pg_dumpall utilities can be used to take logical backup in PostgreSQL.
1. Logical Backup
We can use pg_dump to take a backup of the database in the following backup requirements.
Backup of a single database from the database cluster without global objects like users, roles, privileges.
pg_dump -Ft Z=5 -d demunix -f /path/to/backup/location/demunix_dump [Will take tar backup with custom format with compressing the backup using zlib compression to level 5]
pg_dump -Fp -d demunix -f /path/to/backup/location/demunix_dump [Will take uncompressed backup in plain format]
Backup of single or multiple schemas of a particular database.
pg_dump -n schema1 -n schema2 -d databasename -f /path/to/backup/location/filemame [Will take backup in plain format]Backup of single or multiple tables of a particular database.
pg_dump -t schema1.table1 schema1.table2 -t schema2.table1 -f /path/to/backup/location/filename [Will take backup in plain format]Backup of single or multiple large objects of a particular database.
pg_dump -b schemaname.oid_of_large_object -b schemaname.oid_of_large_another_object -f /path/to/backup/location/filemame [Will take backup in plain format]Or all of above at once.
pg_dump -t schema1.tbl1 -t schema1.tbl2 -n schema3 -b schema4.oid_of_large_object -f /path/to/backup/location/mixed_backup [Will take backup in plain format]Restoration.
psql -U username -d nameoftargetdatabase -f /path/to/backup/location/filename -v [For plain format backup]
pg_restore -U username -d nameoftargetdatabase -f /path/to/backup/location/filename -v [For custom format backup]We can use pg_dumpall to take backup in following backup requirements.
Backup of entire database cluster including all databases including global objects.
pg_dumpall -f /path/to/backup/location/full -vBackup of global objects.
pg_dumpall -g -f /path/to/backup/location/globals -vBackup of roles.
pg_dumpall -r -f /path/to/backup/location/roles -vCan be restored using,
psql -U username -p port -f /path/to/backup/location/filename -v-U to give username, must be a superuser.
-p database port
-v will enable verbose mode.2. Physical Backup
We can use pg_basebackup utility to take a physical backup of the database server, which will backup all the database files from base including configuration files, archives. This type of backup is useful to overcome crash and to perform point in time recovery. This type of backup can also be used to bring the initial database from master to slave while setting up database replication. It is recommended to enable Archive mode on the database server in order to take pg_basebackup.
pg_basebackup -U username -h hostname -p port -d databasenametoconnect -Ft -Xs -z -v -P -D /path/to/empty/directory/-U to give username who'll run backup. User must have replication privilege and should be whitelisted in pg_hba.conf
-h hostname of the database server, just in case taking from remote machine.
-p database port
-d name of a database only for connectivity.
-Ft for tar format, -Fp can be used to take backup in plain format. -z option can only be used with tar format.
-Xs to wal method during the pg_basebackup process, -Xf can be used to for fetch method, in this wals generated during the backup process will be fetch at the end of backup. wal_keep_segment should be large enough to store all generated wals druing the backup. If pg_basebackup is not able to find all required wals in pg_wal in the end, backup would fail.
-z wil compress the backup in tar. Optionally Z=1-9 compression level can be supplied instead of -z to set a compression level.
-v will enable verbose mode.
-P will show progress.
-D to give target location to take backup. This directory should be emptyIt can be restored by extracting base.tar.gz to an empty directory (a data directory) and pg_wal.tar.gz to pg_wal directory of data_directory and then start the database cluster.
tar -xvzf base.tar.gz -C /pg_data/pgsql/restored/data/
tar -xvzf pg_wal.tar.gz -C /pg_data/pgsql/restored/data/pg_wal/That’s it for now, peace 🙂 Do share this article with someone who might benefit from this.