My purpose is not to do backup for my database. I just want to copy the whole db3 database to another machine and restore it. That database could be very large so I think directly copy is more efficient than pg_dump. So I'd like to do some test to see if this way works. If it doesn't work, I will consider to use pg_dump. Thank you for your feedback. -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Albe Laurenz Sent: Thursday, November 15, 2012 4:52 PM To: Wang, Hao; pgsql-general@xxxxxxxxxxxxxx Subject: 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. > 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 want to 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 so large that pg_dump will probably have bad performance. That's a whole lot of arbitrary restrictions. If all you want is a copy of the database, pg_dump is what you should use. Besides, it is the only way to get a copy of just one database. What's the problem if pg_dump takes a few hours or days (I don't know how big you DB is)? A side thought: if the DB is not configured for PITR and pg_dump takes too long, how do you perform your backups? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
