Re: Move Tables From One Database to Another

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.