Quick Tip: PostgreSQL Dump Database and Restore PostgreSQL Database

Tags
4 October 2022
Complete Guide for CTO & IT Directors
Microservices under X-Ray Three books image Download free ebook

Making regular backups is an essential aspect of working with the PostgreSQL database in any production environment. No matter the size, you must take precautions to address potential data loss, and regular backups of the entire database are the best thing you can do. That way, in case of any problems with the database, such as data loss or corruption, you can simply load data from the latest version of the PostgreSQL backup file.

PostgreSQL includes native tools that can allow to easily dump, manage, and restore a PostgreSQL database.

How to dump a PostgreSQL database

PostgreSQL provides the pg_dump which is a utility that helps you efficiently do a backup of your database. It will generate an SQL database file which will make it easy to restore data should you need it.

To back up a PostgreSQL database you have to log in to your database server. Then switch to the Postgres user account and run pg_dump in the following manner:

pg_dump database_name > database_name.sql

It is generally a good habit to employ a system of database name building, as it’s good to be able to know what each individual database is. A good idea is to always include the date of the backup file so that when you want to restore PostgreSQL database you will know at a glance which one simply from the name of the database.

Changing the format of the PostgreSQL data dump

PostgreSQL database dump is, by default, a plain SQL script file, but this is not the only format that’s available as you can also do a custom format dump. You can specify the output file format with -F option (c = custom format archive file, d = directory format archive, t = tar file).

As an example you can do it like that:

pg_dump -F c database_name > database_name.tar

or:

pg_dump -F c database_name > database_name.dump

Specifying directory for the database dump

When dumping data in the directory output format, you can use -f flag to specify the target directory (instead of a file). Please mind, that the specified directory that the pg_dump will create must not exist. You can dump data into a specified directory by using the following command:

pg_dump -F d database_name -f database_directory	

Dumping all PostgreSQL databases

You can back up more than one database at a time. You can use pg_dumpall to dump all PostgreSQL databases.

pg_dumpall > all_databases.sql

Restoring a PostgreSQL Database

If you want to restore the desired database, you can use pg_restore (restores a PostgreSQL database from an archive created in one of the other formats such as tar or directory) or psql (restores the text files that were created by pg_dump).

The following command is the example of psql use:

psql database_name < database_name.sql

A custom format dump is not a SQL script file, so it must be restored with pg_restore. You use it like that:

pg_restore -d database_name database_name.dump 

or

pg_restore -d database_name database_name.tar

or like this for directory format archive

pg_restore -d database_name database_directory

pg_dump and psql utilities as shown.

Dealing with large PostgreSQL databases

If you want to back up a large database and you wish to get a smaller output file, you can compress the dump. However, you will have to filter the output format with a compression tool, e.g. gzip.

pg_dump database_name | gzip > database_name.gz

If your database is extensively large, you can do a parallel dump by dumping number_of_jobs simultaneously. We use -j flag for this:

pg_dump -F d -j 8 -f database_namedir

Please remember that though a parallel dump reduces the time of the database dump it will inevitably increase the load on the database server.

Talk with experts

We look forward to hearing from you to start expanding your business together.

Email icon [email protected] Phone icon +1 (888) 413 3806