Search Postgresql Archives

Re: File system level copy

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux