Re: Copy database to another machine

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