How to restore output from pg_dump into a new table name

Question

I am dumping a large Postgres table like this:

pg_dump -h myserver  -U mt_user --table=mytable  -Fc -Z 9 --file mytable.dump mydb

The above creates a mytable.dump file. I now want to restore this dump into a new table called mytable_restored.

How can I use the pg_restore command to do this?

Answer 1

There is no pg_restore option to rename tables.

I would do it like this:

-- create a table that is defined like the original
CREATE TABLE mytable_restored (LIKE mytable INCLUDING ALL);

-- copy the table contents
COPY mytable TO '/tmp/mytable.dmp';
COPY mytable_restored FROM '/tmp/mytable.dmp';