Re: Online backup of PostgreSQL data.

[John R Pierce <pierce@xxxxxxxxxxxx>]

On 9/17/2015 12:31 AM, Raman, Karthik IN BLR STS wrote:

I need a small clarification. We are using PostgreSQL version 9.4.1

 

We have a requirement to take the online backup of a running PostgreSQL system (with out stopping the database).

As per the design / architecture of PostgreSQL system, is it technically allowed to dynamically copy the “Data” folder of PostgreSQL system (with out using any replication concept).

While restoring (we are allowed to stop the database!), can we directly restore this data folder? Are there any potential problem we will come across by doing this way?

(Or) Should I use some replication concept like using pg_basebackup (?) to achieve the same. (In our case, we do not need replication as a feature to do in different PC). It is sufficient for us to take the backup in the same PC.

 

Any suggestions / help is highly appreciated. Thanks in advance!

 

to copy the data directory and have it be useful you need to bracket the copy with calls to pg_start_backup() and pg_stop_backup() .... this ensures the data files are coherent.   this is in fact what pg_basebackup does for you....

if the data is large enough that regular full copies are expensive, you might consider implementing a WAL archive system.  you would periodically do a pg_basebackup as above, and also maintain a WAL archive of all wal files since the beginning of the base backup.     you can use thise base backup plus wal archive to implement 'Point In Time Recovery' or PITR, which will let you restore the database to just before any specified transaction number, so if bad data got introduced, you can restore it to just prior to that point.

you might read all of this chapter in the manual...
http://www.postgresql.org/docs/9.4/static/backup.html

-- 
john r pierce, recycling bits in santa cruz