Online backup of PostgreSQL data.

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

You can use pg_dump to creating a backup.

pg_dump -U db_username db_name -f file_name.dump

On Thu, Sep 17, 2015 at 1:45 PM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote: