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.

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.

[root@server backups]# 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.

[root@server backups]# pg_dump -U postgres -d dbname> data-dump.sql

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

[root@server backups]# pg_dump -U postgres -a dbname > data-dump.sql

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.

[root@server backups]# 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.

[root@server backups]# 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.

[root@server backups]# pg_dump -U postgres -a -t tablename dbname > data-dump.sql

Now what the contents of the data-dump.sql file might look like.

-- PostgreSQL database dump

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

-- Name: user_options_data_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres

SELECT pg_catalog.setval('user_options_data_id_seq', 39, true);

-- Data for Name: user_options_data; Type: TABLE DATA; Schema: public; Owner: postgres

COPY user_options_data (id, user_data_id, removed, active, phone_active, email_active, text_active, "position", relationship) FROM stdin;
18 55 f t f f f 2 N
6 17 f t f f f 2 N
20 58 f t f f f 1 N
1 4 f t f f f 1 N
2 1 f t t t f 1
4 15 f t f t f 1 N
7 29 t f t f t 0 N
9 35 f t f f f 1 N
10 38 f t f f f 1 N
11 40 f t f f f 1 N
12 42 f t f f t 1 N
13 45 f t f f f 1 N
23 64 f t f t t 1 N
21 60 f t f t f 1 N
24 66 f t f t f 1 N
15 46 f t f f f 0 N
22 61 f t f t f 0 N
17 53 t t f f f 0 N
25 68 f f f f f 1 N
26 72 f f f f f 1 N
14 44 f f f f f 0 N
16 49 f t f f f 0 N
37 99 f t f f f 1 N
38 100 t t f t t 0 N
27 73 f f f f f 1 N
36 97 t t f t t 0 N
39 102 f t f t t 1 N
30 77 f t f f f 1 N
31 80 t f f f f 0 N
33 89 f t f f f 1 N
29 76 f f f f f 1 N
28 75 t f f t f 0 N
32 87 t f f f f 0 N
34 92 f t f f f 1 N
8 32 t t t t t 0 N
35 96 t t f f f 0 N
19 56 f t f f f 1 N
5 16 f f f f f 0 N
3 10 f t f t f 2 N

-- PostgreSQL database dump complete

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.

[root@server backups]# 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.

Tags: , , , , , , , ,
8 Responses to “Restore One Table with psql from a pg_dump PostgreSQL Backup”
  1. chamoW says:

    Thanks man….you saved me…


    alex Reply:

    Hello chamoW,

    Glad it helped. Thanks for the comment.


  2. dehn says:


    im new in pgsql… i want to backup and restore my db.., dont know what to do…

    what do you mean by “[root@server backups]#”???

    where do i write that???



    alex Reply:

    Hello dehn,

    Thats just a login prompt. The actual command comes after that.



    dehn Reply:

    got an error when using pg_dump…


    alex Reply:

    Hello dehn,

    Not going to be much help without the actual error.



  3. George says:

    I an restoring single table that I backup yesterday byt command “pg_dump -a -t tbl1 DB1 > tbl1.sql”.
    it is huge table (millions of rows). it took about 3 minutes. But the restore “psql DB1 < tbl1" had ran over 3 hours and still running. I had to kill the job.
    My question: How can I tell if the restore is running? I tried command "select count(*) from tbl1;" and it returns zero after few seconds.


    alex Reply:

    Hello George,

    It is very likely that a table of this size with this many rows can take this long to restore. One of the reasons that the backup can be so quick and the restore can take so long is likely because of the indexes that have been created on the table. One easy way to verify the restore is still operational and not *hung* is by going into the PostgreSQL data folder on a Linux server and using “du -h” or just “du” to see if the data is still growing fairly rapidly. Also check the process by using “ps -ef | grep psql”. Typically the data folder on Linux is /var/lib/pgsql.

    Hope that helps.



Leave a Reply

*Type the letter/number combination in the abvoe field before clicking submit.