A Complete Guide to pg_dump With Examples, Tips, and Tricks


Let’s learn how to dump a PostgreSQL database with the pg_dump command-line utility.

Tools used in the tutorial
Tool Description Link

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:

  • An SQL script: A plain-text file containing the SQL commands required to reconstruct the database to the state it was in at the time of the backup. To execute the script, you can use psql or any PostgreSQL database client.
  • A directory-based archive file: The resulting format is a set of folders, and it is designed to be portable across different architectures. To rebuild the database, you must import these archive files with pg_restore.

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):

  • U <username> or --username=<username>: Specifies the PostgreSQL username to connect with.
  • h <hostname> or --host=<hostname> Specifies the host where the database server is running. If set, the default is taken from the PGHOST environment variable.
  • p <port> or --port=<port> Specifies the port to use for the connection to the database. If set, the default value is read from the PGPORT environment variable.
  • d <dbname> or --dbname=*dbname*: Specifies the name of the database to dump. <dbname> can also be a connection string.
  • F <format> or --format=<format>: Specifies the file format for the export. Common values include:
    • t or text: For an SQL-like plain text script (default option).
  • c or custom: For a custom format.
  • d or directory: For the directory format suitable for input into pg_restore.
  • t or tar: For the tar archive directory format suitable for input into pg_restore.
  • f <filename> or --file=<filename>: Specifies the name of the output file.
  • t <pattern> or --table=<pattern>: Dumps only the tables specified by the given pattern.
  • T <pattern> or --exclude-table=<*pattern*>: Excludes the tables identified by the specified pattern from the dump.
  • n <pattern> or --schema=<pattern>: Dumps only the schemas matching the pattern.
  • a or --data-only: Dumps only the data, not the schema.
  • s or --schema-only: Exports only the schema, not the data.
  • c or --clean: Adds SQL commands to drop database objects before recreating them.
  • C or --create: Adds SQL commands to create the database.
  • -inserts: Dumps data as INSERT commands rather than COPY instructions.
  • -no-password: Skips the password prompt by assuming that no password is required.

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.


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!


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:

  1. Use the -h option to specify the hostname or IP address of the PostgreSQL server.
  2. Provide the -U option to specify the PostgreSQL username.
  3. Include the -d option to specify the name of the remote database.

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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09

The content provided on, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗