Logical PostgreSQL Backup – Export

June 15, 2021

Logical PostgreSQL Backup – Export

Logical (SQL DUMP) Database Backup is an extract of some or all data or structure of objects within the database. This is a dump in the form of an SQL command that can be executed on the server in order to recreate database objects.

Advantage:

1) Flexibility: Dump files are OS-independent.
2) Cross Version Compatibility: Take backup from Higher Version and import in Lower Version or vice versa.
3) Consistent backup at the time pg_dump begins.
4) Selective restoration options are possible.

Disadvantage:

1) Slower restore compare to Physical backup.
2) No Point-In-Time-Recovery (PITR) possible with Logical Backup.
3) Will not allow DDL during backup.
4) Incremental backup is not possible.

Tools (Utilities) for Logical Backup:

pg_dump
1) Single Database backup only.
2) Consistence backup.
3) No backup of global objects like role and tablespace definition.

pg_dumpall
1) Support dumping entire content of database cluster including Clusterware data like role and tablespace. definition and access permission.
2) Only plain format is supported with pg_dumpall.
3) Consistence backup.

For demonstration, we have already created a sample database click here to read more.

Help Command: Use option –help to get full available options for both pg_dump & pg_dumpall command.

[root@test-machine02 ~]# pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

[root@test-machine02 ~]# pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:
  pg_dumpall [OPTION]...

Single DB Backup: Use the below command to perform Single DB Full backup in Plain Text file format.

-bash-4.2$ pg_dump -U postgres -W -d dvdrental -f /u01/backup/dvdrental.sql
Password:
-bash-4.2$
-bash-4.2$ ls -ltr /u01/backup/dvdrental.sql
-rw-r--r--. 1 postgres postgres 2766115 Jun 10 15:15 /u01/backup/dvdrental.sql
-bash-4.2$

Single DB Backup Tar Format: Use the below command to perform Single DB Full backup in Tar Format (option used: -F t).

-bash-4.2$ pg_dump -F t -U postgres -W -d dvdrental -f /u01/backup/dvdrental_tar
Password:
-bash-4.2$
-bash-4.2$ ls -ltr /u01/backup/dvdrental_tar
-rw-r--r--. 1 postgres postgres 2843136 Jun 10 15:23 /u01/backup/dvdrental_tar
-bash-4.2$

Single DB Backup Custom Format: Use the below command to perform Single DB Full backup in Custom Format (option used: -F c).

-bash-4.2$ pg_dump -F c -U postgres -W -d dvdrental -f /u01/backup/dvdrental_custom
Password:
-bash-4.2$
-bash-4.2$ ls -ltr  /u01/backup/dvdrental_custom
-rw-r--r--. 1 postgres postgres 664040 Jun 10 15:24 /u01/backup/dvdrental_custom
-bash-4.2$

Single DB Backup Directory Format: Use the below command to perform Single DB Full backup in Directory Format (option used: -F d).

-bash-4.2$ pg_dump -F d -U postgres -W -d dvdrental -f /u01/backup/dvdrental_dir
Password:
-bash-4.2$
-bash-4.2$ ls -l /u01/backup/dvdrental_dir
total 700
-rw-r--r--. 1 postgres postgres  12946 Jun 10 15:24 3977.dat.gz
-rw-r--r--. 1 postgres postgres   2003 Jun 10 15:24 3979.dat.gz
-rw-r--r--. 1 postgres postgres    184 Jun 10 15:24 3981.dat.gz
-rw-r--r--. 1 postgres postgres  69526 Jun 10 15:24 3983.dat.gz
-rw-r--r--. 1 postgres postgres  17769 Jun 10 15:24 3984.dat.gz
-rw-r--r--. 1 postgres postgres   3096 Jun 10 15:24 3985.dat.gz
-rw-r--r--. 1 postgres postgres  18310 Jun 10 15:24 3987.dat.gz
--rw-r--r--. 1 postgres postgres  58428 Jun 10 15:24 toc.dat
-bash-4.2$

Table Level Backup: Use option -t to perform Table level backup.

-bash-4.2$ pg_dump -U postgres -W -t actor -t address -d dvdrental -f /u01/backup/table_bkp.sql
Password:
-bash-4.2$
-bash-4.2$ ls -ltr /u01/backup/table_bkp.sql
-rw-r--r--. 1 postgres postgres 57919 Jun 10 15:35 /u01/backup/table_bkp.sql
-bash-4.2$

Schema Level Backup: Use option -n to perform Schema level backup.

-bash-4.2$ pg_dump -U postgres -W -n test2_new -d dvdrental -f /u01/backup/schema_bkp.sql
Password:
-bash-4.2$
-bash-4.2$ ls -ltr /u01/backup/schema_bkp.sql
-rw-r--r--. 1 postgres postgres 627 Jun 13 10:33 /u01/backup/schema_bkp.sql
-bash-4.2$

Schema definition only Backup: Use option -s to perform Schema definition level backup.

-bash-4.2$ pg_dump -U postgres -W -s  -d dvdrental -f /u01/backup/schema-only.sql
Password:
-bash-4.2$
-bash-4.2$ ls -ltr /u01/backup/schema-only.sql
-rw-r--r--. 1 postgres postgres 40011 Jun 10 15:39 /u01/backup/schema-only.sql
-bash-4.2$

Data-Only Backup: Use option -a to perform data-only level backup.

-bash-4.2$ pg_dump -U postgres -W -a  -d dvdrental -f /u01/backup/data-only.sql
Password:
-bash-4.2$
-bash-4.2$ ls -l /u01/backup/data-only.sql
-rw-r--r--. 1 postgres postgres 2726593 Jun 10 15:40 /u01/backup/data-only.sql
-bash-4.2$

Drop Object Command: Use option -c to add DROP object command before CREATE command.

-bash-4.2$  pg_dump -U postgres -W -c -d dvdrental -f /u01/backup/dropobject_dvdrental.sql
Password:
-bash-4.2$
-bash-4.2$ ls -l /u01/backup/dropobject_dvdrental.sql
-rw-r--r--. 1 postgres postgres 2771911 Jun 10 16:08 /u01/backup/dropobject_dvdrental.sql
-bash-4.2$

Drop Database & Object Command: Use option -C -c to add DROP database & object command before CREATE command.

-bash-4.2$  pg_dump -U postgres -W -c -C -d dvdrental -f /u01/backup/drop_dvdrental.sql
Password:
-bash-4.2$
-bash-4.2$ ls -l /u01/backup/drop_dvdrental.sql
-rw-r--r--. 1 postgres postgres 2766729 Jun 10 16:08 /u01/backup/drop_dvdrental.sql
-bash-4.2$

Full Cluster Backup: Use pg_dumpall for full cluster level backup.

-bash-4.2$ pg_dumpall -U postgres -W  -f /u01/backup/dumpall.sql
Password:
-bash-4.2$
-bash-4.2$ ls -l /u01/backup/dumpall.sql
-rw-r--r--. 1 postgres postgres 2769994 Jun 10 16:44 /u01/backup/dumpall.sql
-bash-4.2$

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Recent articles

BACKUP, BACKUP AND RECOVERY, EXPORT, POSTGRESQL