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