PostgreSQL Backup

Origin: Restore One Table with psql from a pg_dump PostgreSQL Backup

Backing up and restoring PostgreSQL is fairly easy using pg_dump andpsql. 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.

First lets look at the syntax required to backup a PostgreSQL database using pg_dump which I have done below. The first command will backup the entire database and output to a SQL file in the default format.

        pg_dump -U postgres dbname > data-dump.sql

Some people understand INSERT commands better so the second command shows how the -d switch can be added to output INSERT commands instead of COPY commands.

        pg_dump -U postgres -d dbname> data-dump.sql

The third example below shows dumping the data only using the -a switch.

The last example using pg_dump shows the -a switch for data only, the -d switch for INSERT commands, and the -t switch for dumping only one tables data.

       pg_dump -U postgres -a -d -t tablename dbname > data-dump.sql

The above examples provide you numerous examples of how you could be backing up your PostgreSQL database. The point behind this article though is to demonstrate how you could split out one tables data from a backup file that was created using pg_dump and backing up an entire database. First lets look at how you would restore an entire database which can be done using the syntax below.

       psql -U postgres dbname < data-dump.sql

Now if you only needed to restore one tables data you would first need to create a file with only that tables data instead of data for the entire database. I recommend issuing the pg_dump command to backup only one tables data so it can be understood what format the data needs to be in. Below I show an example of the syntax used to dump one tables data using the default COPY command format followed by what the contents of that file would look like.

      pg_dump -U postgres -a -t tablename dbname > data-dump.sql

This is a great example of what needs to be obtained and split from the backup of the entire database. You will need to use the less, cat, or similar Linux command to locate the data you need within the file. You could also use vi and search for the table name within the file. Using the table only example you will understand what commands are necessary to have what is needed to restore the data into the PostgreSQL database. You will need to add the SET commands that are displayed at the top of the table data only file you created as an example as those will not be located in the complete database backup searching by table name. Once you have created the new file which again should mirror the example you created for dumping only one tables data then you can import using psql as shown below.

      psql -U postgres dbname < new-one-tables-data.sql

That is all you need to do. If you do receive an error during the import study any differences between the example table data SQL file and the new SQL file you have created. While all of the above steps seem like a bit of a pain it is nice to know it is not to complex to restore only one tables data into PostgreSQL.

Snippet

dump db & restore db

       pg_dump -U postgres dbname > dbname-dump.sql
       psql -U postgres dbname < dbname-dump.sql

dump table & restore table

      pg_dump -U postgres -t tablename > table-dump.sql
      psql -U postgres dbname < tablename.sql