restoring table with pg_restore does not include primary key or indexes

Question

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?

Egide

> orders.dump looks wrong for the pg_restore command. The input file should be specified as the last argument with the > – Molton

I tested, and when you dump a table with -t tablename, its constraints and indexes are included in the dump. – Annamarieannamese

Answer 1

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

Shortly

unfortunately (at least sometimes) there is no option for restoring a constraint, or a sequence, and maybe more, afaics. – Unwitnessed