Thread: Online backup of PostgreSQL data.

Hi All,

 

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!

 

With best regards,
Karthik R

 

 

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

John R Pierce wrote:
> 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....

I apologize for my fussiness, but this is a misconception I encounter so frequently
that I have to speak up.

The file system copy does not become consistent (isn't that what you meant?) if it
is surrounded by pg_start_backup() and pg_stop_backup().  What happens is that
a) a backup.label file is created that tells recovery where to start
b) more WAL is generated so that all changes can be replayed safely.

Yours,
Laurenz Albe