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.sqlIt 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.taror:
pg_dump -F c database_name > database_name.dumpSpecifying 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.sqlRestoring 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.sqlA 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.taror like this for directory format archive
pg_restore -d database_name database_directorypg_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.gzIf 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_namedirPlease remember that though a parallel dump reduces the time of the database dump it will inevitably increase the load on the database server.
+1 (888)
413 3806