Thread: Copy database to another machine

Nuno Ferreira <nunoadferreira@gmail.com> wrote:

> Good afternoon,
>
> I'm trying to copy one database from one machine to another. How can I do it?
> Can I just simply copy and paste the files? I need to close the database first
> or I can copy the files with it open?

You can do (and have to do) that while the database is up and running
with pg_dump and (on the destination) pg_restore or psql. Please read
the documentation for more information here:

http://www.postgresql.org/docs/9.1/interactive/backup.html

You CAN'T copy&paste the files!


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On 26 October 2011 14:38, Nuno Ferreira <nunoadferreira@gmail.com> wrote:
> Good afternoon,
> I'm trying to copy one database from one machine to another. How can I do
> it?
> Can I just simply copy and paste the files? I need to close the database
> first or I can copy the files with it open?

No, you can't copy and paste the files, but you can do it in one step:

pg_dump -t table_to_dump source_database_name | psql destination_database_name

Assuming the table does't already exist in the destination, it will
create and populate the table in the destination database.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Il 26/10/2011 15:38, Nuno Ferreira ha scritto:
> Good afternoon,
>
> I'm trying to copy one database from one machine to another. How can I
> do it?
> Can I just simply copy and paste the files? I need to close the
> database first or I can copy the files with it open?
>
> -
I'm a noob too, but I think I can help in this case.
I've moved from a server to another.
For me pg_dumpall (official postgre manual page 480) went fine.
Actually it copies the whole cluster at once, I had three little
databases and their users (roles) moved from a 32bit machine running
postgre 9.0 to a 64bit one running 9.1.

Hope this helps

Thom Brown wrote:
> On 26 October 2011 14:38, Nuno Ferreira <nunoadferreira@gmail.com> wrote:
>
>> Good afternoon,
>> I'm trying to copy one database from one machine to another. How can I do
>> it?
>> Can I just simply copy and paste the files? I need to close the database
>> first or I can copy the files with it open?
>>
>
> No, you can't copy and paste the files, but you can do it in one step:
>
> pg_dump -t table_to_dump source_database_name | psql destination_database_name
>
> Assuming the table does't already exist in the destination, it will
> create and populate the table in the destination database.
>
I have zipped the contents of PGSDATA (the entire database)  to a zip
file, copied that file to a memory stick, then unzipped it to another
machine.

This is on eCS (OS/2) machines.

I have done this many times without problems.

Am I doing something I shouldn't?

John

On 26 October 2011 17:54, jjurban <jjurban@attglobal.net> wrote:
> Thom Brown wrote:
>>
>> On 26 October 2011 14:38, Nuno Ferreira <nunoadferreira@gmail.com> wrote:
>>
>>>
>>> Good afternoon,
>>> I'm trying to copy one database from one machine to another. How can I do
>>> it?
>>> Can I just simply copy and paste the files? I need to close the database
>>> first or I can copy the files with it open?
>>>
>>
>> No, you can't copy and paste the files, but you can do it in one step:
>>
>> pg_dump -t table_to_dump source_database_name | psql
>> destination_database_name
>>
>> Assuming the table does't already exist in the destination, it will
>> create and populate the table in the destination database.
>>
>
> I have zipped the contents of PGSDATA (the entire database)  to a zip file,
> copied that file to a memory stick, then unzipped it to another machine.
>
> This is on eCS (OS/2) machines.
>
> I have done this many times without problems.
>
> Am I doing something I shouldn't?

That's copying an entire cluster.  You can't copy individual databases
or tables in this manner.  If you are copying your cluster at the
filesystem level (either by zipping, tar'ing or whatever). you would
also either need to shut down the
database whilst making such a copy, or use pg_start_backup('label')
and pg_stop_backup() with WAL archiving, or use pg_basebackup in order
to avoid risking data inconsistency.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Thom Brown wrote:
> On 26 October 2011 17:54, jjurban <jjurban@attglobal.net> wrote:
>
>> Thom Brown wrote:
>>
>>> On 26 October 2011 14:38, Nuno Ferreira <nunoadferreira@gmail.com> wrote:
>>>
>>>
>>>> Good afternoon,
>>>> I'm trying to copy one database from one machine to another. How can I do
>>>> it?
>>>> Can I just simply copy and paste the files? I need to close the database
>>>> first or I can copy the files with it open?
>>>>
>>>>
>>> No, you can't copy and paste the files, but you can do it in one step:
>>>
>>> pg_dump -t table_to_dump source_database_name | psql
>>> destination_database_name
>>>
>>> Assuming the table does't already exist in the destination, it will
>>> create and populate the table in the destination database.
>>>
>>>
>> I have zipped the contents of PGSDATA (the entire database)  to a zip file,
>> copied that file to a memory stick, then unzipped it to another machine.
>>
>> This is on eCS (OS/2) machines.
>>
>> I have done this many times without problems.
>>
>> Am I doing something I shouldn't?
>>
>
> That's copying an entire cluster.  You can't copy individual databases
> or tables in this manner.  If you are copying your cluster at the
> filesystem level (either by zipping, tar'ing or whatever). you would
> also either need to shut down the
> database whilst making such a copy, or use pg_start_backup('label')
> and pg_stop_backup() with WAL archiving, or use pg_basebackup in order
> to avoid risking data inconsistency.
>
>
Yes, I always shut down the database server with a call to

PG_CTL  stop -m smart

before zipping.

John