Skip site navigation (1) Skip section navigation (2)

Re: Move Tables From One Database to Another

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 (view raw or flat)
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

In response to

Responses

pgsql-general by date

Next:From: Rich ShepardDate: 2012-03-29 20:27:24
Subject: Re: Move Tables From One Database to Another
Previous:From: Jeff DavisDate: 2012-03-29 19:39:38
Subject: Re: PANIC: corrupted item pointer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group