PostgreSQL comes with pg_dump, a built-in tool for making backups of the databases. It allows admins to create backups in different manners: full, incremental, or continuous backups (locally/remotely).
In this guide, we’ll showcase using pg_dump to make backups of our PostgreSQL databases.
Prerequisites
To perform the steps demonstrated in this guide, you will need the following components:
- A properly-configured Linux system. Learn more about installing Ubuntu.
- The latest version of PostgreSQL installed and configured. Check out installing and configuring PostgreSQL on Ubuntu.
In addition, we will also be using a sample PostgreSQL database. For ease of use, I will be using phpPgAdmin, a web front-end for PostgreSQL.
Using pg_dump
The pg_dump command exports a database in either a script file (containing SQL commands to reconstruct the database) or an archive file. The primary goal of this tool is to back up databases. While using pg_dump, the database will still be accessible for reads and writes.
Note that pg_dump works with a single database only.
If the database was extracted as an SQL script, then you can import the database into any other SQL-based database. However, if it was extracted as an archive file, then it can only be restored using the pg_restore tool. One cool thing with pg_restore is, you can define what part of the database to restore.
pg_dump command structure
The primary command structure of pg_dump looks like this:
1 | $ pg_dump <options> <db_name> |
Exporting a database (as SQL script)
As mentioned earlier, pg_dump can export the database in different formats. To export the database into an SQL file, use the following command:
1 | $ pg_dump <db_name> > <filename>.sql |
In this example, we are exporting the database dvdrental to dvdrental-backup.sql:
1 | $ pg_dump -U postgres dvdrental > dvdrental-backup.sql |
Note that the export operation will fail if the user you’re running pg_dump doesn’t have permission to read the database. Here, because postgres is the superuser of PostgreSQL, the command was executed successfully.
Exporting a database (as a TAR archive)
Next, we will export the database as an archive. The following command will export it in a TAR format:
1 | $ pg_dump -U postgres -F c dvdrental > dvdrental.tar |
Exporting a database (as a compressed archive)
If you’re exporting a large database, then compression will help in reducing the file size. In the following example, the exported database will be compressed using the gzip tool:
1 | $ pg_dump -U postgres dvdrental | gzip > dvdrental.gz |
Alternatively, we can use the built-in compression of pg_dump to compress the output:
1 | $ pg_dump -U postgres --compress=9 dvdrental > dvdrental.tar |
Exporting a database (directory format)
Instead of exporting the database into a single file, pg_dump offers the option to dump it into a directory.
In the following example, we are exporting the database dvdrental to the directory dvdrental-backup:
1 | $ pg_dump -U postgres -F d dvdrental -f dvdrental-backup/ |
Exporting all databases
If you need to make a complete backup of the PostgreSQL database server, then you can use pg_dumpall.
1 | $ pg_dumpall -f full_backup.sql |
The backup will include everything: roles, schemas, and data. However, pg_dumpall allows specific components to be exported.
The following command will export the roles only:
1 | $ pg_dumpall --roles-only -U postgres > all_db_roles.sql |
The following command will export schemas only:
1 | $ pg_dumpall --schema-only -U postgres > all_db_schema.sql |
The following command will export only the tablespace definitions:
1 | $ pg_dumpall --tablespaces-only -U postgres > all_db_tablespaces.sql |
Final thoughts
In this guide, we explored various ways of using the pg_dump command to export databases from PostgreSQL. We exported a sample database as an SQL script and archive. Using pg_dumpall, we also showcased exporting various components of all the databases in PostgreSQL.
PostgreSQL is a powerful database engine with tons of functionalities. The PostgreSQL sub-category contains numerous guides. Learn more on listing users, dropping users, fuzzy search, etc.
