intro
Let’s learn how to dump a PostgreSQL database with the pg_dump command-line utility.
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 that pg_dump does not prevent other users from accessing the database during the backup, producing consistent results even if the database is currently under use.
How PostgreSQL pg_dump Works: Syntax and Options
To retrieve the required information and data to export, pg_dump 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]
options can include various flags to customize the behavior of the dump, such as authentication details, file format, compression, and more. dbname is instead the name of the database you want to dump. If not specified, the environment variable PGDATABASE or the database specified in the options is used.
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.
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 connect to the company database in the local PostgreSQL server using the admin user. You will be prompted for the user's password. Then, it will produce the company_backup.sql file. 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 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.
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.
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 -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.
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.
