Re: 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 them all.

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

Yours,
Laurenz Albe