Thread: Move Tables From One Database to Another
I'm storing vector map attribute data in postgres tables and somehow
managed to create two databases (of similar names) rather than one. I want
to combine the two.
For tables that exist in the one database I want to eliminate, I thought
to use pg_dump to create .sql files, then use pg_restore to add the table to
the other database. Did this for one table (with 4201 rows), but 'pg_restore
-d database_name -t table_name' appears to not complete; it seems to have
hung up somewhere. While I see nothing specific in the output file or the
pg_restore man page this must not be the proper approach.
Also, I need suggestions on how to combine tables that exist in both
databases by adding rows from the source database not in the target database
and modifying rows that differ.
As I'm not a professional or full-time DBA I'm probably missing really
simple syntax and approaches. Your advice will be appreciated.
Rich
Hi Rich, Il 29/03/12 21:10, Rich Shepard ha scritto: > For tables that exist in the one database I want to eliminate, I > thought > to use pg_dump to create .sql files, then use pg_restore to add the > table to > the other database. Did this for one table (with 4201 rows), but > 'pg_restore > -d database_name -t table_name' appears to not complete; it seems to have > hung up somewhere. While I see nothing specific in the output file or the > pg_restore man page this must not be the proper approach. pg_restore works exclusively with custom or tar format archives from pg_dump (see -F option). I suggest that you look at the -l and -L options in pg_restore, which allow you to select which dump entries to restore (selective restore). Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
On 3/29/2012 2:10 PM, Rich Shepard wrote: > I'm storing vector map attribute data in postgres tables and somehow > managed to create two databases (of similar names) rather than one. I want > to combine the two. > > For tables that exist in the one database I want to eliminate, I thought > to use pg_dump to create .sql files, then use pg_restore to add the > table to > the other database. Did this for one table (with 4201 rows), but > 'pg_restore > -d database_name -t table_name' appears to not complete; it seems to have > hung up somewhere. While I see nothing specific in the output file or the > pg_restore man page this must not be the proper approach. > > Also, I need suggestions on how to combine tables that exist in both > databases by adding rows from the source database not in the target > database > and modifying rows that differ. > > As I'm not a professional or full-time DBA I'm probably missing really > simple syntax and approaches. Your advice will be appreciated. > > Rich > > How many tables are we talking about. If its a few tables, I'd rename them: alter table lake rename to lake_old; ... etc then dump it out and restore into the proper db. The proper db will now have to tables, lake and lake_old, which you can selective update some rows: update lake set foo = (select foo from lake_old where lake_old.id = lake.id) where exists (select foo from lake_old where lake_old.id = lake.id); !! The were exists is very important !! and insert missing: insert into lake select * from lake_old where not exists (select id from lake_old where lake_old.id = lake.id); > to use pg_dump to create .sql files, then use pg_restore to add the > table to <SNIP> > it seems to have > hung up somewhere. I wonder if a table was in use and pg_restore blocked on the drop table? If you don't mind replacing the entire table, this method should work. But if you want to merge the two tables, I would not go this route. if you try the restore again, you can do: ps ax|grep postg and see what statement its running. You can also do: select * from pg_locks where not granted; and see if anything is blocked. -Andy
On Thu, 29 Mar 2012, Gabriele Bartolini wrote: > I suggest that you look at the -l and -L options in pg_restore, which > allow you to select which dump entries to restore (selective restore). Gabriele, After sending the message I realized the proper syntax is 'psql -d database -f table.sql'. That works. Thanks, Rich
On Thu, 29 Mar 2012, Andy Colson wrote: > How many tables are we talking about. If its a few tables, I'd rename them: > alter table lake rename to lake_old; > ... etc > then dump it out and restore into the proper db. Andy, This will work just fine. Thanks for the insight. Rich
On Thu, 2012-03-29 at 14:49 -0500, Andy Colson wrote: > On 3/29/2012 2:10 PM, Rich Shepard wrote: > > I'm storing vector map attribute data in postgres tables and somehow > > managed to create two databases (of similar names) rather than one. I want > > to combine the two. > > > > For tables that exist in the one database I want to eliminate, I thought > > to use pg_dump to create .sql files, then use pg_restore to add the > > table to > > the other database. Did this for one table (with 4201 rows), but > > 'pg_restore > > -d database_name -t table_name' appears to not complete; it seems to have > > hung up somewhere. While I see nothing specific in the output file or the > > pg_restore man page this must not be the proper approach. > > > > Also, I need suggestions on how to combine tables that exist in both > > databases by adding rows from the source database not in the target > > database > > and modifying rows that differ. > > > > As I'm not a professional or full-time DBA I'm probably missing really > > simple syntax and approaches. Your advice will be appreciated. > > > > Rich > > > > > > > How many tables are we talking about. If its a few tables, I'd rename them: > > alter table lake rename to lake_old; > ... etc > > then dump it out and restore into the proper db. > > The proper db will now have to tables, lake and lake_old, which you can > selective update some rows: > > update lake > set foo = (select foo from lake_old where lake_old.id = lake.id) > where exists (select foo from lake_old where lake_old.id = lake.id); > > !! The were exists is very important !! > > and insert missing: > > insert into lake > select * from lake_old > where not exists (select id from lake_old where lake_old.id = lake.id); > > > to use pg_dump to create .sql files, then use pg_restore to add the > > table to <SNIP> > > it seems to have > > hung up somewhere. > > > I wonder if a table was in use and pg_restore blocked on the drop table? > If you don't mind replacing the entire table, this method should work. > But if you want to merge the two tables, I would not go this route. > > if you try the restore again, you can do: > > ps ax|grep postg > and see what statement its running. You can also do: > > select * from pg_locks where not granted; > > and see if anything is blocked. > > -Andy > Good info. I think i'll plagiarize this thinking if you don't mind. Thanks for the broad explanation.