How To Dump And Restore A PostgreSQL Database

What Is A Database Dump?

A database dump is a file that contains a database's structure and data. It is essentially a snapshot of the database at a specific point in time. This file can be used to back up the database or to transfer it to another server. Dumps are typically created using tools like pg_dump for PostgreSQL.

How To Dump A PostgreSQL Database?

1. Install pg_dump if it is not already installed. You can do this by running the following command:

sudo apt-get install postgresql-client-common postgresql-client-15

2. Run the following command to dump the database:

pg_dump -p <port> -U <username> -d <database_name> -F c -b -v -f <dump_file_name>.sql

Example:

pg_dump -p 5432 -U postgres -d postgres -F c -b -v -f mydatabasedump.sql

How To Restore A PostgreSQL Database?

1. Install pg_restore if it is not already installed. You can do this by running the following command:

sudo apt-get install postgresql-client-common postgresql-client-15

2. Run the following command to restore the database:

pg_restore -p <port> -U <username> -d <database_name> -v <dump_file_name>.sql

Example:

pg_restore -p 5432 -U postgres -d postgres -v mydatabasedump.sql

How Can I Dump Only The Schema Of A PostgreSQL Database?

You can use the -s flag to dump only the schema of a PostgreSQL database.

pg_dump -p <port> -U <username> -d <database_name> -F c -b -v -s -f <dump_file_name>.sql

This may be helpful if you want to transfer only the schema of a database to another server, or for generating database entity diagrams.

What next?

Now that you know how to dump and restore a PostgreSQL database, you can use these skills to back up your database or to transfer it to another server.