pg_dump
POSTGRESQL

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

intro

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

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

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:

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

Copy
        
1 pg_dump -U admin -d company -f company_backup.sql

That is equivalent to:

Copy
        
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

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

Copy
        
1 psql -d new_company -f company_backup.sql

If you add the --create flag to the pg_dump example as in this command:

Copy
        
1 pg_dump -U admin -d company -f company_backup.sql --create

You can then import the script as below:

Copy
        
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

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

Copy
        
1 pg_dump -U admin -d company -F t -f company_backup.tar

Export Data Only

Copy
        
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

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

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

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

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

Copy
        
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
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

A Comprehensive Guide to Data Types in Postgres

author Leslie S. Gyamfi tags DATA TYPES POSTGRESQL 10 min 2024-01-23
title

SQL DISTINCT: A Comprehensive Guide

author Bonnie tags DISTINCT POSTGRESQL SQL 5 MINS 2024-01-11
title

Date Formatting in Postgres: A Comprehensive Guide

author Leslie S. Gyamfi tags DATE POSTGRESQL 7 MINS 2023-12-11
title

Casting in PostgreSQL: Handling Data Type Conversions Effectively

author Leslie S. Gyamfi tags CAST POSTGRESQL 5 MINS 2023-12-07
title

PostgreSQL Materialized Views: A Beginner's Guide

author Ochuko Onojakpor tags POSTGRESQL VIEWS 7 MINS 2023-12-04
title

Postgres List Users: Two Different Approaches 

author Antonello Zanini tags LIST USERS POSTGRESQL 3 MINS 2023-11-16
title

Using the EXPLAIN plan to analyze Query execution in PostgreSQL

author Bonnie tags EXPLAIN POSTGRESQL 6 MINS 2023-09-21

The content provided on dbvis.com/thetable, 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 ↗