8

So I made a backup of a table using pg_dump:

pg_dump -U bob -F c -d commerce -t orders > orders.dump

This table had several listed indexes such as a primary key

However when I restore this table into a development database on another system using pg_restore:

pg_restore -U bob -d commerce -t orders > orders.dump

No primary key or indexes are listed

What am I doing wrong?

mk2000
  • 81
  • 1
  • 2
  • 1
    `> orders.dump` looks wrong for the `pg_restore` command. The input file should be specified as the last argument with the `>` –  Feb 19 '18 at 07:01
  • I tested, and when you dump a table with `-t tablename`, its constraints and indexes are included in the dump. – Laurenz Albe Feb 19 '18 at 08:20

1 Answers1

5

You are doing nothing wrong, unfortunately pg_restore -t restores only the table, nothing else, regardless of how you created the dump and what is inside the dump itself. This has been somehow clarified in V12 PostgreSQL docs, that states:

This flag does not behave identically to the -t flag of pg_dump. There is not currently any provision for wild-card matching in pg_restore, nor can you include a schema name within its -t. And, while pg_dump's -t flag will also dump subsidiary objects (such as indexes) of the selected table(s), pg_restore's -t flag does not include such subsidiary objects.

the only way to make sure that restoring a table will carry all the indexes is to address them by name, something like:

pg_restore -U bob -d commerce -t orders -I index1 -I index2 -I index3 > orders.dump
Edoardo
  • 4,485
  • 1
  • 27
  • 31
  • unfortunately (at least sometimes) there is no option for restoring a constraint, or a sequence, and maybe more, afaics. – Jan Jan 14 '23 at 20:52