Skip site navigation (1) Skip section navigation (2)

Re: Copy database to another machine

From: jjurban <jjurban(at)attglobal(dot)net>
To: Thom Brown <thom(at)linux(dot)com>
Cc: Nuno Ferreira <nunoadferreira(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Copy database to another machine
Date: 2011-10-26 17:10:13
Message-ID: 4EA83EF5.30203@attglobal.net (view raw or flat)
Thread:
Lists: pgsql-novice
Thom Brown wrote:
> On 26 October 2011 17:54, jjurban <jjurban(at)attglobal(dot)net> wrote:
>   
>> Thom Brown wrote:
>>     
>>> On 26 October 2011 14:38, Nuno Ferreira <nunoadferreira(at)gmail(dot)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



In response to

Responses

pgsql-novice by date

Next:From: ScientificWare-Luigi D'AngeloDate: 2011-10-26 17:34:41
Subject: Re: Copy database to another machine
Previous:From: jjurbanDate: 2011-10-26 16:54:56
Subject: Re: Copy database to another machine

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group