41

UPDATE: Was able to exclude the data in the table durning the pg_dump command. Makes it even faster than trying to not load the data because you don't have to wait for that data to be dumped.

--exclude-table-data=event_logs

(PostgreSQL) 9.4.4

Anyone know how to exclude a table when doing a pg_restore? I can find how to do it when doing a pg_dump. However I am not the one doing the dump and can't exclude them.

There are 2 tables in the dump that are really big and take forever when I do a restore so I want to skip them.

covard
  • 1,667
  • 2
  • 18
  • 33

4 Answers4

76

I had the same problem. A long table list, and I want exclude the data from a few of the tables.

What I did was the following:

Run

pg_restore -l $pgdump_file > restore.pgdump.list

Open that restore.pgdump.list file in an editor, and insert an ; in front of the line saying 

;2429; 0 27550 TABLE DATA public <table_to_explore> <database>

After saving the that file, it can now be used for importing, where all lines starting with ; are ignored.

pg_restore -L restore.pgdump.list | psql

You could make an one-liner to add ; in front of lines having a specific table name, if you completely want to ignore a specific table.

man pg_restore is also telling about this in an example in the end of the documentation.

Greg Sadetsky
  • 4,863
  • 1
  • 38
  • 48
Jesper Grann Laursen
  • 2,357
  • 1
  • 20
  • 21
  • 7
    Thank you, what a life saver! As noted in the `man pg_restore`, once you've edited the `restore.pgdump.list` file (from your example), you can call `pg_restore -L db.list db.dump` (in your example, you call `pg_restore` and pipe it to `psql` -- however, you do not specify the path to the dump file..?) – Greg Sadetsky Mar 26 '17 at 16:54
  • 1
    Note that this saved me because I was trying to exclude 2 tables from a list of 99 tables that were imported. `pg_restore` does not allow to specifying tables to exclude during import, so I had to call it: `pg_restore -t a_table -t another_table -t yet_another_table`, etc. Attempting to call it with 97 `-t` arguments broke with a "too many arguments" message... :-) Your `restore.pgdump.list` solution worked!! – Greg Sadetsky Mar 26 '17 at 16:56
  • 1
    This was very helpful! I needed to only exclude data from a single table: `reports`. I was able to do an inline `grep -v` when creating the list file. ```pg_restore -l $pgdump_file | grep -v "TABLE DATA public reports" > restore.pgdump.list``` – Jackson Miller Apr 09 '17 at 21:12
  • 1
    Very nice now I want to tell my old coworkers to implement this to help save them time. This is awesome thanks for posting this. – covard May 15 '18 at 21:45
28

TL;DR One-liner

pg_restore -L <(pg_restore -l /path/to/db/dump | grep -v 'TABLE DATA public table_to_ignore ') -d db_name_where_to_restore /path/to/db/dump

The following returns the "todo list" for a restore:

pg_restore -l /path/to/db/dump

The following will return all except table_to_ignore (grep option -v makes it inverse the match):

pg_restore -l /path/to/db/dump | grep -v 'TABLE DATA public table_to_ignore '

This can be used in combination with pg_restore option -L which expects a input todo list:

pg_restore -L <(pg_restore -l /path/to/db/dump | grep -v 'TABLE DATA public table_to_ignore ') -d db_name_where_to_restore /path/to/db/dump

If you have several tables to ignore, you can the grep to:

pg_restore -l /path/to/db/dump | grep -vE 'TABLE DATA public (table_1 |table_2 |table_3 )'

Notice the presence of -E option for grep to use an extended regular expression.

user3132194
  • 2,381
  • 23
  • 17
  • 2
    you need to add a space after the table name like so `'TABLE DATA public (table_1|table_2|table_3) '` because otherwise `table_1` would also match `table_11` etc. – Pyrocks Jun 16 '22 at 13:39
10

pg_restore does not have an exclude table parameter, what it does have is an include table parameter.

-t table

--table=table

Restore definition and/or data of named table only. Multiple tables may be specified with multiple -t switches. This can be combined with the -n option to specify a schema.

If you have a large number of tables it does call for a litte bit of typing, but it does allow you to exclude specific tables by just leaving their names out of the list.

Community
  • 1
  • 1
e4c5
  • 52,766
  • 11
  • 101
  • 134
  • 2
    Thank you. Yeah we have a lot of tables but I am making a rake task to automate this so I will just have to do it once. Could loop over the tables and have an exclude list of the 2 tables. – covard May 05 '16 at 15:33
  • 1
    @covard would you be so kind as to share your rake task? it would be extremely useful. – Guillermo Siliceo Trueba Oct 01 '18 at 21:07
6

here the command did not work:

pg_restore -L restore.pgdump.list | psql

answered by Jesper Grann Laursen!

Here it worked by following the following sequence:

pg_restore -l $pgdump_file > restore.pgdump.list

;2429; 0 27550 TABLE DATA public <table_to_explore> <database>

pg_restore -v -L restore.pgdump.list -d dbname pgdump.file
Fred William Torno Junior
  • 69
  • 1
  • 4