Re: copy / backup question - copying a data directory while the db is shutdown

2014-03-25 8:40 GMT+01:00 Jürgen Fuchsberger <
juergen(dot)fuchsberger(at)uni-graz(dot)at>:

> Hi,
>
> On 03/24/2014 11:46 PM, CS_DBA wrote:
> > Hi All;
> >
> > If I shutdown a HOT standby slave (via the -m fast command) can I then
> > copy the data directory to a "backup" location and be guaranteed that
> > the backup copy will start?
>
> Yes, but you must also backup the WAL archive from which you are
> recovering (in case you are using file based log shipping and not
> streaming replication). For streaming replication I don't know if it works.
>
> Also you should check if postgres really stopped, since on high load
> even the fast option can fail to stop it. Look here on how to do this:
> http://www.postgresql.org/message-id/53040588.9020803@coxinc.com
> >
> > For some reason I thought there were issues (or potential issues) with
> > this method and a PITR based recovery approach was a better guarantee
> > that the copy will start
> >
> >
> > Thoughts?
> >
> Info about other options appreciated.
> >
> >
>
> Best,
> Juergen
>
> --
> | Jürgen Fuchsberger, M.Sc.
> | Wegener Center for Climate and Global Change
> | University of Graz
>
>
>
If you pause the recovery ( pg_xlog_pause ) , the process try to take a
spin lock , and then pause the recovery , so it's consistent, at that point
if you close the database, the consistent is reached, so I think you have
no problem to have a "snapshoot" of the database.

Obviously you need also the archived checkpoint segments if you need to
make a PITR.

Have a nice day

Mat Dba