Technology Insights

Backup and Restore a Postgres Database Using pg_dump and psql

Insights1 Comment

Backing up and restoring a postgreSQL database is easy from the CLI. Some people prefer using pgAdmin III however through the GUI and over a network will use a lot more resources so it is recommended to backup and restore via your Linux CLI. Follow the simple steps below to backup and restore one Postgres database from a Linux server to another Linux server.

  1. Backup One PostgreSQL Database: Login to your server and issue the below command from the CLI. In the below syntax change username to the correct username (such as postgres), the database_name to the proper database, and the filename to something meaningful. The filename should end in .sql so you know it is a postgreSQL backup file which will eventually end in .gz once you compress it using gzip.

Technology Insights

How to Dump the Data From One PostgreSQL Table

Insights13 Comments

Below is the command to use when you would like to dump only the data from a postgreSQL table. There are other options to use to dump the schema, multiple tables, exclude certain tables, or backup the entire database but these command line switches will dump only the data from one postgreSQL table.

In the below example replace table_name with the name of the table you are backing up, file_name with the name of the output file (optionally add a path to the file name), and database_name with the name of the database where the table is located.

Technology Insights

Restore One Table with psql from a pg_dump PostgreSQL Backup

Insights9 Comments

Backing up and restoring PostgreSQL is fairly easy using pg_dump and psql. One more complicated scenario I have run into is doing a complete database backup with pg_dump and at some point down the road needing to just split out one table and restore it. This can be a pain because of how the pg_dump organizes the .sql file it outputs. I have found the best way to do this is to use pg_dump to backup one table to understand the format of what is needed for a restore. Then search the .sql file that was output from the full pg_dump output and split out the necessary data to restore into one table. Below I provide some examples as well as syntax to use for backing up and/or restoring PostgreSQL using pg_dump and psql.