Re: File system level copy

This is PITR, right?
I don't want to use this way because I'm not allowed to change the configuration parameter of database server. I just
wantto use some whole DB copy to restore db3 in another machine. And I don't want to use pg_dump because I think db3 is
solarge that pg_dump will probably have bad performance. 

-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Sent: Wednesday, November 14, 2012 6:49 PM
To: Wang, Hao; pgsql-general@postgresql.org
Subject: RE: [GENERAL] File system level copy

Hao Wang wrote:
> I installed PostgresSQL-8.3 on my linux machine.
>
> The cluster directory is /usr/local/data and I created three databases
named db1, db2, and db3. db1 is
> in the default tablespace 'pg_default'. db2 is in
'/home/tablespace/space1/' and db3 is in
> '/home/tablespace/space2/'.  I want to copy the cluster directory and
the db3  tablespace
> folder('/home/tablespace/space2/') without stopping the database
server. Then I want to use the
> cluster directory and db3's tablespace in another linux machine to
recover 'db3' database. Does this
> way work? If not, why?

First, you need a correct backup for recovery.
Before copying, run pg_start_backup, and pg_stop_backup afterwards.

Then you need to have recovery.conf and WAL archives (or be lucky and all WALs are still in pg_xlog).

WAL contains changes to all databases in the cluster, so you cannot recover only one database, you'll have to recover
themall. 

Read
http://www.postgresql.org/docs/current/static/continuous-archiving.html
for background and details.

Yours,
Laurenz Albe