Re: Copy database to another machine

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.

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

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