pg_dump

Summary: In this tutorial, you’ll learn how to use the pg_dump tool to export a single PostgreSQL database into a file.

Introduction to the pg_dump tool #

pg_dump is a client tool that allows you to export a PostgreSQL database to an SQL file or other formats.

After having an SQL dump file, you can restore it on the same PostgreSQL server or transfer it to another one.

You can use pg_dump to connect to a local PostgreSQL server or a remote one.

Here’s the basic syntax for using the pg_dump to export a database to an SQL file:

pg_dump -h host -p port -U role target_db > file_pathCode language: plaintext (plaintext)

In this syntax:

• -h host specifies the database host to which you want to connect. If you omit it, pg_dump will connect to the local PostgreSQL server.
• -p port requires the port on which the PostgreSQL server listens. It defaults to 5432.
• -U role requests the role (or user) you use to connect to PostgreSQL. The default is the current user of the Operating System (OS).
• target_db is the database you want to export.
• file_path is the path to the output file.

The pg_dump tool respects PostgreSQL’s privilege system.

For example, to export a database, you are likely to run it as a superuser. To dump a table, you need to have the select privilege on the table to export it.

Dumping a single database into an SQL file #

First, open a terminal on your computer.

Second, connect to the local PostgreSQL and export the inventory database to the inventory.sql:

pg_dump -U postgres inventory > D:\backup\inventory.sqlCode language: plaintext (plaintext)

Since we don’t use the database host and port, the pg_dump will connect to the local PostgreSQL server and default port 5432.

It’ll prompt you to enter a password for the postgres user.

Once entering a valid password, you’ll see that pg_dump creates the inventory.sql file in the D:\backup directory.

Why Use pg_dump? #

• Backup: You can use pg_dump to back up your database and restore it later.
• Server Migration: You can export a database to one PostgreSQL server and transfer it to another.
• Version Upgrades: You can export a database from a PostgreSQL with a lower version and import it to a PostgreSQL server with a higher version.
• Selective Export: You can use pg_dump to export specific schemas or tables.

Summary #

• Use the pg_dump tool to export a single database to a file of different formats.