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?
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
