restoring a .dmp file to another table name

[Mike Nolan]

I've probably asked this question before, but what do people do when they
need to restore a table that has been dumped but as a different table
name? That doesn't appear to be an option in pg_restore. (Is that just
a front end to psql?)

I need both the old table and the live one in the same database so
that I can compare a set of values to see what changed, and I don't have
a spare system to do it on at the moment, nor can I rename the live file
since it is in use most of the day.

This is a fairly large table (2.8M rows, 500MB dump file) and I think
it may have some data in it that would get messed up if I were to try to
extract the DDL leading up to the COPY statement using head and tail
statements to change the table name.
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tino Wildenhain]

Hi,

Am Mo, den 23.08.2004 schrieb Mike Nolan um 6:15:

I've probably asked this question before, but what do people do when they
need to restore a table that has been dumped but as a different table
name? That doesn't appear to be an option in pg_restore. (Is that just
a front end to psql?)

I need both the old table and the live one in the same database so
that I can compare a set of values to see what changed, and I don't have
a spare system to do it on at the moment, nor can I rename the live file
since it is in use most of the day.

This is a fairly large table (2.8M rows, 500MB dump file) and I think
it may have some data in it that would get messed up if I were to try to
extract the DDL leading up to the COPY statement using head and tail
statements to change the table name.

Just use pg_restore -t tablename (and the other options according to
your pg_dump) and write the results in a file. Use an editor to change
tablename to your new tablename in the result. If you are absolute
sure your tablename does not appear as data payload in the table,
you can use sed like this:

pg_restore -t tablename | sed -e "s/tablename/newtablename/" | psql

HTH
Tino Wildenhain
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org