Re: Move Tables From One Database to Another

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