By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,773 Members | 961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,773 IT Pros & Developers. It's quick & easy.

Restoring a table with a different name

P: n/a
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.

If I create the dump using inserts instead of load, inserting 600K
records takes too long.

The best solution I've come up with so far is to copy the table on the
production system to the new table name, then dump and restore it.

Is there an easier way to do this?
--
Mike Nolan

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

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

P: n/a
> > 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3

P: n/a
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:
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

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #4

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5

P: n/a
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
================================================== ==========
el***@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.


On Fri, Feb 27, 2004 at 07:06:58PM -0700, Adam Ruth wrote:
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:
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

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.