Postgres Pro
Downloads
Home   >   mailing lists

Thread: copying between Postgres databases

copying between Postgres databases

From
javier garcia - CEBAS
Date:
Hi;
I've got two questions:

1) What is the best way to copy a Postgres table in one database into another
Postgres database? Is it possible to copy the structure and the data at the
same time?

2) Is there a way to do a short of pg_dump and to use the resulting file as
input to another database, but preserving existing tables in this latter?

Thanks and regards

Javier

Re: copying between Postgres databases

From
Joshua Moore-Oliva
Date:
On March 17, 2003 04:21 am, you wrote:
> Hi;
> I've got two questions:
>
> 1) What is the best way to copy a Postgres table in one database into
> another Postgres database? Is it possible to copy the structure and the
> data at the same time?

I believe that you can use pg_dump to just dump out one table.

>
> 2) Is there a way to do a short of pg_dump and to use the resulting file as
> input to another database, but preserving existing tables in this latter?

You can also specify the output of pg_dump to be all SQL commands..  and then
only execute the queries in the pg_dump that you want.


I'm not a huge expert on pg_dump myself, but try man pg_dump it should answer
a lot of questions for you.

Josh.

Re: copying between Postgres databases

From
Andrew Sullivan
Date:
On Mon, Mar 17, 2003 at 10:21:29AM +0100, javier garcia - CEBAS wrote:
> Hi;
> I've got two questions:
>
> 1) What is the best way to copy a Postgres table in one database into another
> Postgres database? Is it possible to copy the structure and the data at the
> same time?

pg_dump -t [tablename] -c -h [host1] database | psql -h [host2] database2

> 2) Is there a way to do a short of pg_dump and to use the resulting file as
> input to another database, but preserving existing tables in this latter?

The above will overwrite [tablename] in the target if it exists.  Use
it without -c if you want to preserve what's there, but be prepared
for not-quite-what-you-expected results.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Re: copying between Postgres databases

From
"scott.marlowe"
Date:
On Mon, 17 Mar 2003, javier garcia - CEBAS wrote:

> Hi;
> I've got two questions:
>
> 1) What is the best way to copy a Postgres table in one database into another
> Postgres database? Is it possible to copy the structure and the data at the
> same time?
>
> 2) Is there a way to do a short of pg_dump and to use the resulting file as
> input to another database, but preserving existing tables in this latter?

Sure, just pipe the output of pg_dump to psql:

pg_dump database1 -t tablename | psql database2 -e