Move Tables From One Database to Another

Hi Rich,

Il 29/03/12 21:10, Rich Shepard ha scritto:

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.

pg_restore works exclusively with custom or tar format archives from
pg_dump (see -F option).

I suggest that you look at the -l and -L options in pg_restore, which
allow you to select which dump entries to restore (selective restore).

Cheers,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

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

On Thu, 29 Mar 2012, Gabriele Bartolini wrote:

I suggest that you look at the -l and -L options in pg_restore, which
allow you to select which dump entries to restore (selective restore).

Gabriele,

After sending the message I realized the proper syntax is 'psql -d
database -f table.sql'. That works.

Thanks,

Rich

On Thu, 29 Mar 2012, Andy Colson wrote:

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.

Andy,

This will work just fine. Thanks for the insight.

Rich

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.