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.
Syntax
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
Example
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
Dump Formats
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.
Tar format
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)
Compressed format
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
Directory format
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)