Re: How to move data from 1 database to another?

Thomas LeBlanc wrote:

> Actually, the database db2 has all ready been created, plus the table
> names are the same, so I can not use pg_dump and restore.
>
> INSERT INTO db1.public.tables SELECT * FROM db2.public.table1
>
> Thanks,
> Thomas
>
>
>> From: "scott.marlowe" <scott.marlowe@ihs.com>
>> To: Thomas LeBlanc <thomasatiem@hotmail.com>
>> CC: <pgsql-general@postgresql.org>
>> Subject: Re: [GENERAL] How to move data from 1 database to another?
>> Date: Tue, 11 May 2004 10:13:22 -0600 (MDT)
>>
>> On Tue, 11 May 2004, Thomas LeBlanc wrote:
>>
>> > I have 2 databases. I want to move data from table table1 in
>> database db1 to
>> > table2 in db2.
>> >
>> > When I query 1 table from another database, I get a cross-database
>> > references are not implemented.
>> >
>> > server1% psql db1
>> > emdata=# select * from db2.public.table1;
>> > ERROR: Cross-database references are not implemented
>> >
>> >
>> > What can I do to get the data into another database?
>>
>> You can usually use pg_dump to accomplish such tasks. It's not something
>> likely to be implemented any time soon now that schemas have been
>> implemented. If you have no great reason to have two seperate databases
>> versus two schemas, you may want to investigate using schemas in the
>> future.
>>
>> For now, you can do something like:
>>
>> pg_dump -d db1 -t table1 |psql db2
>>
>> then psql and do
>>
>> insert into table2 (select * from table1);
>>
>>
>
> _________________________________________________________________
> FREE pop-up blocking with the new MSN Toolbar – get it now!
> http://toolbar.msn.com/go/onm00200415ave/direct/01/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

Yes in fact you can. Use pg_dump to dump the whole database. Then just
do a data only pg_restore (--data-only is the command line switch). Bear
in mind if the restore goes wrong then you are going to have to
dropdb/createdb and then run in the schema again. I hope you have that
schema in a file.

Nick