Exporting and Importing a PostgreSQL Database

Exporting and importing a PostgreSQL database involves creating a dump of the database and then restoring it to another database. This guide provides the necessary steps and commands to perform these actions.

Exporting the Database

To export a PostgreSQL database, you use the pg_dump utility. This command creates a dump file that contains the database schema and data.

Command:

pg_dump -U db_user -h db_host -p 5432 -d my_db -F c -f my_db.dump

Explanation:

• -U db_user: Specifies the username to connect to the database.
• -h db_host: Specifies the hostname of the database server.
• -p 5432: Specifies the port number (default PostgreSQL port is 5432).
• -d my_db: Specifies the name of the database to export.
• -F c: Specifies the format of the output file. The c stands for custom format.
  Custom format (-F c):
  – Using -F c or –format=custom means that the dump will be created in PostgreSQL’s custom format.
  – This format allows the dump to be saved as a single file, which can include compressed data.
  – Dumps in this format can be partially restored, allowing for the restoration of only specific tables or schemas.
  – It also supports parallel restoration using the pg_restore utility.
  – This is the most flexible and commonly used format for backup and restoration of data.
• -f my_db.dump: Specifies the name of the dump file to create.

Importing the Database

To import a PostgreSQL database, you use the pg_restore utility. This command restores the database from the dump file.

To ensure a smooth restoration process, it’s often useful to restore the dump in sections: pre-data, data, and post-data.

Step 1: Restore Pre-data

Restore the pre-data section, which includes the schema, sequences, and table definitions.

Command:

pg_restore -U db_user -h db_host -p 5432 -d my_db --section=pre-data my_db.dump

Explanation:

• –section=pre-data: Restores only the pre-data section.

Step 2: Restore Data

Restore the data section, which includes the actual data for the tables.

Command:

pg_restore -U db_user -h db_host -p 5432 -d my_db --section=data my_db.dump

Explanation:

• –section=data: Restores only the data section.

Step 3: Restore Post-data

Restore the post-data section, which includes indexes, constraints, and triggers.

Command:

pg_restore -U db_user -h db_host -p 5432 -d my_db --section=post-data my_db.dump

Explanation:

• –section=post-data: Restores only the post-data section.

Complete Example of PostgreSQL Database Export and Import

1. Export the Database:

pg_dump -U db_user -h db_host -p 5432 -d my_db -F c -f my_db.dump

2. Import the Database in Sections:

pg_restore -U db_user -h db_host -p 5432 -d my_db --section=pre-data my_db.dump
pg_restore -U db_user -h db_host -p 5432 -d my_db --section=data my_db.dump
pg_restore -U db_user -h db_host -p 5432 -d my_db --section=post-data my_db.dump

Summary for PostgreSQL Database Export and Import

By following these steps, you can effectively export and import a PostgreSQL database, ensuring that all necessary components are correctly restored. Using the pg_dump and pg_restore utilities in this manner helps maintain the integrity of your database during the migration process. The custom format (-F c) is particularly useful due to its flexibility, support for compression, and ability to be restored in parallel.