Part-24 pg_dump

How to create PostgreSQL dump file

pg_dump database_name > database.sql

or:

pg_dump database_name -f database.sql

pg_dump -U postgres database_name > database.sql   

Backing up a single table

To dump a single table, use the -t option:

pg_dump -t table_name database_name > table.sql

How to backup database object definitions

pg_dumpall --schema-only > definitions.sql

Use the following command to backup the role definition only:

pg_dumpall --roles-only > roles.sql

Use the following command to backup the tablespaces definition:

pg_dumpall --tablespaces-only > tablespaces.sql

 Compressing the backup script

If you need to compress the output file, you must use the -Z option:

pg_dump -Z6 database_name > database.gz

Backing up a remote server

If you need to back up a remote server add -h and -p options:

pg_dump -h host_name -p port_number database_name > database.sql

Backing up a single table

To dump a single table, use the -t option:

pg_dump -t table_name database_name > table.sql

Compressing the backup script

If you need to compress the output file, you must use the -Z option:

pg_dump -Z6 database_name > database.gz

Restoring a remote database

If you need to restore a database on a remote server, you can connect psql to it using -h and -p options:

psql -h host_name -p port_number database_name < database.sql

Error handling

If an SQL error occurs, the psql script continues to be executed; this is by default. Such behavior can be changed by running psql with the ON_ERROR_STOP variable, and if an SQL error is encountered, psql exit with an exit status of 3.

psql --set ON_ERROR_STOP=on database_name < database.sql

Other PostgreSQL backup formats

The pg_dump provides two other output file formats: directory and tar. Both of them are restored with pg_restore utility.

To create a directory-format archive, you need to use the -Fd option:

pg_dump -Fd database_name -f database.dump

 

File System Level backup

An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database.

 xcopy “C:\Program Files\PostgreSQL\11\data” “D:\backup” /

Then, you will be able to start a new server instance with the same release name on this folder (note that you have to run this command at the administrator level):

pg_ctl start -D “D:\backup”

This method gives you the following advantages: