Restoring a table with a different name

Mike Nolan said:

I have a 600K row table on my production system (running 7.3.3)
that I dump and load on my development system (7.4.1) every night using
cron jobs.

I would like to be able to restore the table under a different table name
without first having to play games with the development copy of the table.

If I edit the dump file with 'sed' to change the table name, I get
'invalid command \N' errors trying to reload it.

What sed syntax are you using?

Best,

Jim Wilson

If I edit the dump file with 'sed' to change the table name, I get
'invalid command \N' errors trying to reload it.

What sed syntax are you using?

Here's the command line I used:

sed -e 's/memmast/wk_memmast/' memmast.dmp > wk_memmast.dmp

I see two potential problems here, and it took both of them to bite me.

One is that I'm not changing all occurrences of 'memmast' to 'wk_memmast'.
The other is that the string 'memmast' can and does occur within the
name of another column, so the name of that column was edited by sed
in the CREATE TABLE statement but not in the LOAD command.

Changing the command line to:

sed -e 's/ memmast / wk_memmast /' memmast.dmp > wk_memmast.dmp

works, and without changing that column name.

I think, however, that I may need to go with the other method (copying
the table and dumping/restoring the copy), because the restore runs into
name conflicts with several indexes and there is a trigger procedure
on that table.
--
Mike Nolan

You may be better off with renaming the existing table, importing, then
doing more renaming to get everything in the right place:

alter table memmast rename to memmast_temp;

<import table>

alter table memmast rename to wk_memmast;

alter table memmast_temp rename to memmast;

That way you don't need to muck with the dump file.

On Feb 27, 2004, at 7:03 PM, Mike Nolan wrote:

Mike Nolan said:

If I edit the dump file with 'sed' to change the table name, I get
'invalid command \N' errors trying to reload it.

What sed syntax are you using?

Here's the command line I used:

sed -e 's/memmast/wk_memmast/' memmast.dmp > wk_memmast.dmp

I see two potential problems here, and it took both of them to bite me.

One is that I'm not changing all occurrences of 'memmast' to 'wk_memmast'.
The other is that the string 'memmast' can and does occur within the
name of another column, so the name of that column was edited by sed
in the CREATE TABLE statement but not in the LOAD command.

Changing the command line to:

sed -e 's/ memmast / wk_memmast /' memmast.dmp > wk_memmast.dmp

works, and without changing that column name.

I think, however, that I may need to go with the other method (copying
the table and dumping/restoring the copy), because the restore runs into
name conflicts with several indexes and there is a trigger procedure
on that table.

Also you could try (assuming you have perl, which you should):

perl -pi -e 's/ memmast / wk_memmast /' memmast.dmp

to replace all occurances in the file w/o making a copy.

And:

perl -pi -e 's/CREATE UNIQUE INDEX /CREATE UNIQUE INDEX wk_/' memmast.dmp
perl -pi -e 's/CREATE INDEX /CREATE INDEX wk_/' memmast.dmp

It looks like you've got the idea anyway. There are ways to get even fancier
with the perl tool, but for now keep it simple and have fun with it.

Best,

Jim Wilson

Alternatively, you can do the renaming on the db
and then dump the renamed table. But you'd need
enough room and time for it. (Not a good solution for
very large tables...) A four line shell
script might do it. Validate and add correct syntax
to taste.

psql olddb ... -c "create table deleteme as select * from account;"
pg_dump olddb ... --table=deleteme > delme.sql
psql newdb < delme.sql
psql olddb ... -c "drop table deleteme;"
rm delme.sql ; # OK, 5 lines

elein
============================================================
elein@varlena.com Varlena, LLC www.varlena.com
1-866-VARLENA
PostgreSQL Consulting, Support & Training

PostgreSQL General Bits http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.