Re: Methods to quiesce PostgreSQL DB without configuring log archival

On Wed, Nov 23, 2016 at 11:53 AM, Scott Mead <scottm(at)openscg(dot)com> wrote:
> On Nov 22, 2016, at 01:23, MEERA <meeranair89(at)gmail(dot)com> wrote:

>> If archive_mode is not configured, and i use snapshot solution for backup of
>> the server, how can i ensure data consistency? Is there a way to quiesce all
>> the connections to DB?
>
> If your snapshot solution is atomic, then you are *probably* okay. I would
> do a few restores to test, but atomic snapshots give you a point in time and
> should be consistent.

You should be OK *if all files in the backup are part of a single
atomic snapshot*. You can't put pg_xlog or selected tablespaces on
a separate filesystem that has a separate snapshot and expect the
various snapshot to be combined to be used as a coherent backup
unless you are archiving WAL and use pg_start_backup() (before the
first snapshot is initiated) and pg_stop_backup() (after the last
snapshot is completes) to take a PITR-style recovery.

Be sure to follow all the rules for PITR-style backup and recovery,
like deleting the postmaster.pid file and all files under pg_xlog
before starting the recovery. And of course, do NOT delete the
backup_label file created by pg_start_backup().

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company