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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
