intro
Let’s learn how to dump a PostgreSQL database with the pg_dump command-line utility.
pg_dump is a versatile PostgreSQL tool for creating database backups. In this guide backed by extensive examples, you will learn how to use it to master the art of data management with PostgreSQL.
Let's dive in!
What Is pg_dump?
pg_dump is a command-line utility for creating backups of a PostgreSQL database. Specifically, it can dump an entire database or specific parts of it, such as individual tables or schemas. The output of the utility can be:
Note: The PostgreSQL pg_dump command does not prevent other users from accessing the database during the backup. So, the database will remain available for concurrent usage. Even if other users are accessing the databases, the command will always produce consistent results thanks to ACID properties.
How PostgreSQL pg_dump Works: Syntax and Options
To retrieve the required information and data to export, the Postgrespg_dump command internally executes SELECT statements. This means that to generate a backup you need to have read access to all tables.
The basic syntax of the PostgreSQL pg_dump command is:
1
pg_dump [options] [dbname]
There the arguments are:
pg_dump Option List
Here is a list of the most important and commonly used options for the pg_dump command in PostgreSQL (bear in mind that specifying options over the CLI can be considered unsafe as these can be observed with anyone with access to the history of the commands that have been issued):
For the complete list of options available, check out the official documentation.
Postgres pg_dump exmaple
Now, consider the pg_dump example below:
1
pg_dump -U admin -d company -f company_backup.sql
That is equivalent to:
1
pg_dump -U admin -f company_backup.sql company
The above commands will:
Keep in mind that options can also be specified in a .pgpass file to avoid them being logged in the history of commands issued via the CLI.
pg_dump Example List
Now that you know how the PostgreSQL dumping utility works, it is time to explore a complete pg_dump example list!
Note: In the following examples, the user will always be named admin and the database name will always be company. Change those fields accordingly to make the instructions below work in your specific scenario.
Dump a Database Into an SQL Script Format
1
pg_dump -U admin -d company -f company_backup.sql
The result will be a company.backup.sql file.
Given that SQL script, you can use it to populate a new_company database with psql as follows:
1
psql -d new_company -f company_backup.sql
If you add the --create flag to the pg_dump example as in this command:
1
pg_dump -U admin -d company -f company_backup.sql --create
You can then import the script as below:
1
psql -f company_backup.sql
company_backup.sql will now create the company database for you.
Dump a Database Into a Directory-Format Archive
1
pg_dump -U admin -d company -F d -f company_backup
The result will be a company_backup folder containing .dat.gz files.
To generate a .tar file with the same structure, run instead:
1
pg_dump -U admin -d company -F t -f company_backup.tar
Export Data Only
1
pg_dump -U admin -d company -f company_backup.sql --data-only
The resulting SQL script will only contain COPY statements.
Export Schema Only
1
pg_dump -U admin -d company -f company_backup.sql --schema-only
This pg_dump schema only command produces an SQL script that contains only CREATE and ALTER TABLE statements.
Include Only Some Schemas
Suppose you only want to export schemas that begin with “p.” You can achieve that with:
1
pg_dump -U admin -d company -n 'p*' -f company_backup.sql
The wildcard * means “every other character.”
Note that the same pg_dump instruction can contain more than one -n flag. With this approach, you can select multiple schemas.
Exclude Specific Tables
Consider that you want to exclude some specific tables from your PostgreSQL pg_dump backup. You can do that with:
1
pg_dump -U admin -d company -T logs -f company_backup.sql
The above command dumps the database while excluding the logs table.
Include Only a Few Tables
Assume you only want to dump tables that contain the word “order.” Achieve that goal with:
1
pg_dump -U admin -d company -t '*order*' -f company_backup.sql
Keep in mind that -n and -N flags have no effect when specifying a -t option.
Dump a Remote Database Over SSH
You can create a backup over a PostgreSQL remote database using an SSH tunnel with this command:
1
ssh user@remote_host "pg_dump -U admin -d company" > company_backup.sql
Replace user@remote_host with the correct username and host. This securely dumps a remote database and saves it locally.
pg_dump Environment Variables
These are the pg_admin environment variables:
Conclusion
In this guide, you understood what pg_dump is and what it offers. This powerful PostgreSQL command-line utility helps you generate backups of single databases. The tool supports a lot of options, and here you had the opportunity to see the most important ones with examples.
Creating effective dumps is not always easy, and you may prefer a more advanced tool like DbVisualizer! In addition to offering all the features of a powerful database client, DbVisulizer allows you to export your databases with just a few clicks. It also offers advanced query optimization capabilities and can generate ERD-like schemas. Try DbVisualizer for free today!
FAQ
Where does the output of pg_dump go?
By default, the output of pg_dump goes to the stdout. In particular, it produces a string in SQL format you can redirect to a file with:
1
pg_dump [options] [dbname] > myscript.sql
Otherwise, you can export the output to a local file with the pg_dump -f flag.
What is the path to pg_dump.exe?
On Windows, you can usually find pg_dump.exe in the bin directory of your PostgreSQL installation. The target path should be something like this:
1
C:\Program Files\PostgreSQL\<version>\bin\pg_dump.exe
Replace <version> with the version number of your PostgreSQL server.
What does pg_dump -f do?
The -f option in pg_dump specifies the output file where the dump will be saved. For example:
1
pg_dump -f backup.sql my_database
This command exports the my_database dump into backup.sql. The format of the dump depends on additional flags like -F (e.g., plain SQL, custom, directory, or tar format). If -f is omitted, pg_dump outputs the dump to stdout.
Is it possible to run pg_dump from remote server?
Yes, you can run pg_dump from remote server using the procedure below:
Ensure that the PostgreSQL server allows remote connections. If required, adjust the firewall settings. You will also typically need to provide authentication details, such as a password. Otherwise, consider using advanced methods like SSH tunneling for secure connections.
What is the difference between the PostgreSQL pg_dump or pg_dumpall tools?
pg_dump is used to create backups of individual PostgreSQL databases. pg_dumpall is instead used to create backups of all PostgreSQL databases on a server. It generates a single script containing the SQL statements to recreate all databases, users, and other global objects. pg_dump or pg_dumpall? Now, you know which tool to use!
How to perform a parallel dump with pg_dump?
To perform a parallel dump with pg_dump in PostgreSQL, use the -j or --jobs option followed by the number of parallel jobs. For example, to run two parallel jobs, add -j 2 to your pg_dump command. Bear in mind that parallel dumping can significantly speed up the backup process, especially for large databases. At the same time, it usually takes more memory resources.
How to safely access pg_dump?
Consider specifying your username and password in a file called .pgpass instead of specifying those options via the CLI. Why? Because options specified in the command line are logged in its history and can be observed by anyone who has access to it.
How to deal with the “pg_dump: error: query failed: error: permission denied for table db_lock” error?
Th “pg_dump: error: query failed: error: permission denied for table db_lock” error is a permission error that generally happens when the users your are performing pg_dump with does not have permissions on the public schema.
You can fix that with this queries:
1
GRANT USAGE ON SCHEMA public TO your_user_name;
2
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user_name;
Also if there is any sequence, you should give access to them, too:
1
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO your_user_name;
Replace your_user_name with the name of the PostgreSQL user you intend to write the perform the dump with.
What is pg_dumpall and what is the difference with pg_dump?
pg_dumpall is a PostgreSQL utility that dumps an entire database cluster, including all databases, roles, and global objects. In contrast, the Postgres pg_dump command only exports a single database and excludes roles and global settings
