Re: Copy database to another machine

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