From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Move Tables From One Database to Another |
Date: | 2012-03-29 19:49:25 |
Message-ID: | 4F74BCC5.2040508@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2012-03-29 20:27:24 | Re: Move Tables From One Database to Another |
Previous Message | Jeff Davis | 2012-03-29 19:39:38 | Re: PANIC: corrupted item pointer |