PostgreSQL custom Restore

December 7th, 2011

On dumping and restoring PostgreSQL databases, there may be the case for restoring selected tables from a global db dump.
For this to happen one needs to have the right options when dumping the data.


$ pg_dump -Fc -U username database_name > db_dump

Now, we have a “restore customizable” compressed dump of the database.
From the postgres documentation:

  -F format
    Selects the format of the output. format can be one of the following:

      Output a custom-format archive suitable for input into pg_restore.
      Together with the directory output format, this is the most flexible
      output format in that it allows manual selection and reordering of
      archived items during restore. This format is also compressed by default.


To reorder database items, it is first necessary to dump the table of contents of the archive into a detailed file. This file will allow us to make a custom restore of the items (tables, etc) that we need.

$ pg_restore -l db_dump > db.list

Open the db.list file in your favorite editor (Vim) and proceed to comment or delete the lines of the item that you do not want to be restored.
From the postgres documentation there is a good example I changed here to comply with the previous dump:

; Archive created at Wed Dec 7 15:26:56 2011
; dbname: database_name
; TOC Entries: 306
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.1.1
; Dumped by pg_dump version: 9.1.1
; Selected TOC Entries:
3; 2615 2200 SCHEMA - public pasha
1861; 0 0 COMMENT - SCHEMA public pasha
1862; 0 0 ACL - public pasha
317; 1247 17715 TYPE public composite pasha
319; 1247 25899 DOMAIN public domain0 pasha

One can carefully edit the file. Semicolons start a comment, and the numbers at the start of lines refer to the internal archive ID assigned to each item. Lines in the file can be commented out, deleted, and reordered. According to the example, one could edit this section of the file:

10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres

This way, table species will no be restored, neither nt_header and ss_old.
If one try this, will see all the items like index information, relations, keys, etc.

After all set, one can restore the selected items from the dumped database:

$ pg_restore -L db.list db_dump