Thread: Copy database to another machine

Copy database to another machine

From
Nuno Ferreira
Date:
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?  

--
Best regards
Nuno Ferreira
914663795

Re: Copy database to another machine

From
Andreas Kretschmer
Date:
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°

Re: Copy database to another machine

From
Thom Brown
Date:
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

Re: Copy database to another machine

From
ScientificWare-Luigi D'Angelo
Date:
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


Re: Copy database to another machine

From
jjurban
Date:
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


Re: Copy database to another machine

From
Thom Brown
Date:
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

Re: Copy database to another machine

From
jjurban
Date:
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



Re: Copy database to another machine

From
Nuno Ferreira
Date:
Imagine I have 999 entries in the table in one machine and 1000 in the other machine. There are any easy way of copy only the data that doesn't exist?

2011/10/26 jjurban <jjurban@attglobal.net>
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





--
Com os Melhores Cumprimentos
Nuno Ferreira
914663795