Arun Gavhane Senior Systems Engineer Mar 23, 2020
This article discusses how to make a backup of a database using pg_dump in PostgreSQL and provides examples of how to make and restore the backups in different formats.
1. Database SQL Dump
2. Dump Formats
a. Tar format
b. Compressed format
c. Directory format
In this article, we are going to cover the types of backups in PostgreSQL that we can use to load a sample database with schemas and data.
Database SQL Dump
The pg_dump utility takes a SQL script backup of a single database from your database cluster. The pg_dump runs when the server is up and running without blocking any reading or writing activity on the servers. It does not require any special privilege; even a normal user can run it provided that user has permission on that particular database. The dumps taken by pg_dump are totally consistent, because the dump is a snapshot of your database and its data at the time when the pg_dump begins running.
pg_dump [options] [dbname]
For more options, you can use the following command:
<PG_BIN>/pg_dump --help [postgres@localhost bin]$ pwd /usr/pgsql-12/bin [postgres@localhost bin]$ ./pg_dump --version pg_dump (PostgreSQL) 12.2
Backup a single database with its schema and data.
$ ./pg_dump -h localhost -p 5432 -U postgres db1 -f /tmp/plaintxtdump.sql
Since the above dump is a plain text dump, it can be restored using psql.
To restore a plain text dump the following command can be used:
$ ./psql -f /tmp/plaintxtdump.sql -d restoredb -p 5432 -U postgres SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET SET SET CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE COPY 100 COPY 200
In the examples below, we will see how to take dump in various formats like tar, custom, and directory format:
-F, --format=c|d|t output file format (custom, directory, tar)
The default format for pg_dump is plain text. You can also use the option -Fp.
To dump a database into tar format, use the following command:
$ ./pg_dump -h localhost -p 5432 -U postgres db1 -Ft -f /tmp/tarformatdump.tar
To restore the above tar format dump:
$ ./pg_restore -Ft -d restoredb1 -U postgres /tmp/tarformatdump.tar
After restoring, you can cross-validate the restored database objects.
postgres=# \c restoredb1 You are now connected to database "restoredb1" as user "postgres". restoredb1=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | postgres public | t2 | table | postgres (2 rows)
To dump a database into compressed format, use the following command:
$ ./pg_dump -Fc -h localhost -p 5432 -U postgres db1 -f /tmp/compressedformatdump.dump
NOTE: You can use the -Z option for compression level. The level options are 0 to 9.
To restore the compressed format dump:
$ ./pg_restore -Fc -d restoredb2 -U postgres /tmp/compressedformatdump.dump
To dump a database into directory format, use the following command:
$ ./pg_dump -Fd -h localhost -p 5432 -U postgres db1 -f /tmp/backupdirformat
To restore the directory format, tell pg_restore to create the named database for restoring. To achieve this use option -C.
The -l command will list the table of contents, which you can use to find the db name.
[postgres@localhost bin]$ ./pg_restore -Fd -l /tmp/backupdirformat ; ; Archive created at 2020-03-03 12:43:45 IST ; dbname: db1 ; TOC Entries: 8 ; Compression: -1 ; Dump Version: 1.14-0 ; Format: DIRECTORY ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 12.2 ; Dumped by pg_dump version: 12.2 ; ; ; Selected TOC Entries: ; 202; 1259 16385 TABLE public t1 postgres 203; 1259 16388 TABLE public t2 postgres 3682; 0 16385 TABLE DATA public t1 postgres 3683; 0 16388 TABLE DATA public t2 postgres
You can either drop the existing database or restore on another port.
$ ./pg_restore -Fd -C -d postgres -p 5432 -U postgres /tmp/backupdirformat
NOTE: In the above example, the database ‘db1’ will be created with the use of -C option, and -d is to just specify the existing database for connection.
$ ./psql -d postgres -p 5432 psql (12.2) Type "help" for help. postgres=# \c db1 You are now connected to database "db1" as user "postgres". db1=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | postgres public | t2 | table | postgres (2 rows)