Thread: Restoring a table with a different name
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
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: >>> 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 >
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.
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