Thread: Methods to quiesce PostgreSQL DB without configuring log archival

Hi all,

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. 

On Wed, Nov 23, 2016 at 11:53 AM, Scott Mead <scottm@openscg.com> wrote:
> On Nov 22, 2016, at 01:23, MEERA <meeranair89@gmail.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

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?

select pg_startbackup();    before taking the file system copy or snapshot, and

select pg_stopbackup();     when you're done.  

-- 
john r pierce, recycling bits in santa cruz

On Wed, May 19, 2021 at 11:58 AM MEERA <meeranair89@gmail.com> wrote:
>
> Hi team,
>
> https://www.postgresql.org/docs/12/continuous-archiving.html
>
> It is mentioned here that exclusive backup method is deprecated and should be avoided. it is now recommended that all
usersupgrade their scripts to use non-exclusive backups.
 
>
> Does this mean select pg_start_backup('label', false, true) is deprecated?

Yes.

> In a future version, will it not allow the third argument to be true?

The exact future has not been decided, but most likely yes. And as
most installations using it today are unsafe, it is recommended that
you don't use it even before then. It basically exists for backwards
compatibility with PostgreSQL prior to 9.6.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

--