I am dumping a large postgres table (~15m records) like this:

pg_dump -h myserver  -U my_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?


This doesn't sound like the right tool for the job in the first place. Couldn't you just COPY...TO, then COPY...FROM? or insert into new_table select * from old_table, if they are in the same database?

But given that you already have the dump file, the most straightforward way might be to load to a scratch database, rename the table, and retake the dump from there, then drop the scratch database.

Alternatively, you could use "pg_restore" without a "-d" option to dump the custom format file to a plain text dump file, then edit that file manually to change the name in each location it occurs. Editing a file with 15 millions rows can be quite a chore, but it should be possible with something like "vi", or perhaps "sed" (but there you have to be careful not to change more than you intended to).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.