Methods to quiesce PostgreSQL DB without configuring log archival

On Nov 22, 2016, at 01:23, MEERA <meeranair89@gmail.com> wrote:

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.

Personally, I like archiving because it removes all of the maybes, but, that's me :)

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

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

On 11/21/2016 10:23 PM, MEERA 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?

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

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 users upgrade their scripts to
use non-exclusive backups.

Does this mean select pg_start_backup('label', false, *true*) is deprecated?
In a future version, will it not allow the third argument to be true?

--
thanks and regards,
Meera R Nair

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 users upgrade 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/

Hi Magnus,

Thank you.

On Wed, May 19, 2021 at 3:30 PM Magnus Hagander <magnus@hagander.net> wrote:

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 users upgrade 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/

--
thanks and regards,
Meera R Nair