copying between Postgres databases

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.

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

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