Postgres Point in time Recovery (PITR),

On 18 October 2019 07:59:21 CEST, Daulat Ram <Daulat.Ram@exponential.com> wrote:

Hello All,
Can you please share some ideas and scenarios how we can do the PITR in
case of disaster.

Thanks,

Consider Barman.

--
2ndQuadrant - The PostgreSQL Support Company

On Fri, Oct 18, 2019 at 7:59 AM Daulat Ram <Daulat.Ram@exponential.com> wrote:

Can you please share some ideas and scenarios how we can do the PITR in case of disaster.

In order to be able to do PITR you need:
- a base backup of your database
- WALs from the backup going on

See <https://www.postgresql.org/docs/12/continuous-archiving.html&gt;.
Then, to keep it simple, if the disaster happens in the time window
between the backup and the last WAL archiving, you can restore an
instance at any point in time previously the disaster itself.

Luca

Exactly,

in our environment, we have a full backup of directory PGDATA and every 15
minutes we do archival backup where there are archival file.

So we can restore in a specific time. Before the archival backup we forced
switch wal file to generate archival file so we don't lost any transaction.

Emanuele

Il giorno ven 18 ott 2019 alle ore 10:12 Luca Ferrari <fluca1978@gmail.com>
ha scritto:

På fredag 18. oktober 2019 kl. 07:59:21, skrev Daulat Ram <
Daulat.Ram@exponential.com <mailto:Daulat.Ram@exponential.com>>:

Hello All,

Can you please share some ideas and scenarios how we can do the PITR in case
of disaster.

We use barman (https://www.pgbarman.org/ <https://www.pgbarman.org/index.html&gt;
) for continuous streaming backup and I had to restore from it once, and it
went like this:

======================8<===================

$ barman recover --target-time "2018-12-06 12:20:00" --remote-ssh-command "ssh
andreak@192.168.0.184 <mailto:andreak@192.168.0.184>" db01_11 20181130T190002
"/home/andreak/barman-restore"
Processing xlog segments from streaming for db01_11
00000001000001740000002E
00000001000001740000002F
000000010000017400000030
Starting remote restore for server db01_11 using backup 20181130T190002
Destination directory: /home/andreak/barman-restore
Doing PITR. Recovery target time: '2018-12-06 12:20:00+01:00'
17445, dbname1, /storage/fast_ssd/11/tablespaces/dbname1
29218, dbname2, /storage/fast_ssd/11/tablespaces/dbname2
...
29235503, dbnameX, /storage/fast_ssd/11/tablespaces/dbnameX
Copying the base backup.
Copying required WAL segments.
Generating recovery.conf
Identify dangerous settings in destination directory.

WARNING
The following configuration files have not been saved during backup, hence
they have not been restored.
You need to manually restore them in order to start the recovered PostgreSQL
instance:

postgresql.conf
pg_hba.conf
pg_ident.conf

Recovery completed (start time: 2018-12-06 13:14:53.220043, elapsed time: 4
hours, 52 minutes, 47 seconds)

Your PostgreSQL server has been successfully prepared for recovery!
======================8<===================

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh
<andreas@visena.com> wrote:

We use barman (https://www.pgbarman.org/) for continuous streaming backup and I had to restore from it once, and it went like this:

Just for the records, here's an example of restore with pgbackrest:

% sudo -u postgres pgbackrest --stanza=miguel \
--log-level-console=info --delta restore
...
INFO: restore backup set 20190916-125652F
INFO: remove invalid files/paths/links from /postgres/pgdata/11
INFO: cleanup removed 148 files, 3 paths
...
INFO: write /postgres/pgdata/11/recovery.conf
INFO: restore global/pg_control (performed last
to ensure aborted restores cannot be started)
INFO: restore command end: completed successfully (5113ms)

On 10/18/19 11:29 AM, Luca Ferrari wrote:

On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh
<andreas@visena.com> wrote:

We use barman (https://www.pgbarman.org/) for continuous streaming backup and I had to restore from it once, and it went like this:

Just for the records, here's an example of restore with pgbackrest:

% sudo -u postgres pgbackrest --stanza=miguel \
--log-level-console=info --delta restore
...
INFO: restore backup set 20190916-125652F
INFO: remove invalid files/paths/links from /postgres/pgdata/11
INFO: cleanup removed 148 files, 3 paths
...
INFO: write /postgres/pgdata/11/recovery.conf
INFO: restore global/pg_control (performed last
to ensure aborted restores cannot be started)
INFO: restore command end: completed successfully (5113ms)

pgBackRest also has a tutorial on PITR:
https://pgbackrest.org/user-guide.html#pitr

--
-David
david@pgmasters.net

Hi Daulat,

PITR entirely depends on what type of backups you choose.
Sometimes, to reduce the amount of downtime involved while restoring and
recovering a backup, you may also use a additional delayed standby.
You could use the PG built-in feature to delay the replication and
fast-forward it to the safest point to achieve PITR. But this requires you
to have an additional standby.
https://www.percona.com/blog/2018/06/28/faster-point-in-time-recovery-pitr-postgresql-using-delayed-standby/

If you have several TBs of database, pgBackRest is of course a way to go
for backups (there are few more open source solutions), but also consider
the amount of time it takes for recovery. Keeping all of this in mind, your
approach to PITR changes.

So i would ask you this question, what is the backup tool you use and what
is your backup strategy ? Are you taking a physical backup and performing
continuous archiving of WALs ? The answer to your question entirely depends
on this. :)

Regards,
Avinash Vallarapu.

On Fri, Oct 18, 2019 at 5:17 PM David Steele <david@pgmasters.net> wrote:

On 10/18/19 11:29 AM, Luca Ferrari wrote:

On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh
<andreas@visena.com> wrote:

We use barman (https://www.pgbarman.org/) for continuous streaming

backup and I had to restore from it once, and it went like this:

Just for the records, here's an example of restore with pgbackrest:

% sudo -u postgres pgbackrest --stanza=miguel \
--log-level-console=info --delta restore
...
INFO: restore backup set 20190916-125652F
INFO: remove invalid files/paths/links from /postgres/pgdata/11
INFO: cleanup removed 148 files, 3 paths
...
INFO: write /postgres/pgdata/11/recovery.conf
INFO: restore global/pg_control (performed last
to ensure aborted restores cannot be started)
INFO: restore command end: completed successfully (5113ms)

pgBackRest also has a tutorial on PITR:
https://pgbackrest.org/user-guide.html#pitr

--
-David
david@pgmasters.net

--
9000799060

Hi All,

Thanks for your suggestions.
One more questions is, how backups are useful if we have streaming replication . As I know, we can promote the standby as primary in case of disaster at primary side. Do we need to schedule backups if we have streaming replication?

Thanks

From: Avinash Kumar <avinash.vallarapu@gmail.com>
Sent: Friday, October 18, 2019 5:28 PM
To: David Steele <david@pgmasters.net>
Cc: Luca Ferrari <fluca1978@gmail.com>; Andreas Joseph Krogh <andreas@visena.com>; Daulat Ram <Daulat.Ram@exponential.com>; pgsql-general@lists.postgresql.org
Subject: Re: Postgres Point in time Recovery (PITR),

Hi Daulat,

PITR entirely depends on what type of backups you choose.
Sometimes, to reduce the amount of downtime involved while restoring and recovering a backup, you may also use a additional delayed standby.
You could use the PG built-in feature to delay the replication and fast-forward it to the safest point to achieve PITR. But this requires you to have an additional standby.
https://www.percona.com/blog/2018/06/28/faster-point-in-time-recovery-pitr-postgresql-using-delayed-standby/

If you have several TBs of database, pgBackRest is of course a way to go for backups (there are few more open source solutions), but also consider the amount of time it takes for recovery. Keeping all of this in mind, your approach to PITR changes.

So i would ask you this question, what is the backup tool you use and what is your backup strategy ? Are you taking a physical backup and performing continuous archiving of WALs ? The answer to your question entirely depends on this. :)

Regards,
Avinash Vallarapu.

On Fri, Oct 18, 2019 at 5:17 PM David Steele <david@pgmasters.net<mailto:david@pgmasters.net>> wrote:
On 10/18/19 11:29 AM, Luca Ferrari wrote:

On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh
<andreas@visena.com<mailto:andreas@visena.com>> wrote:

We use barman (https://www.pgbarman.org/) for continuous streaming backup and I had to restore from it once, and it went like this:

Just for the records, here's an example of restore with pgbackrest:

% sudo -u postgres pgbackrest --stanza=miguel \
--log-level-console=info --delta restore
...
INFO: restore backup set 20190916-125652F
INFO: remove invalid files/paths/links from /postgres/pgdata/11
INFO: cleanup removed 148 files, 3 paths
...
INFO: write /postgres/pgdata/11/recovery.conf
INFO: restore global/pg_control (performed last
to ensure aborted restores cannot be started)
INFO: restore command end: completed successfully (5113ms)

pgBackRest also has a tutorial on PITR:
https://pgbackrest.org/user-guide.html#pitr

--
-David
david@pgmasters.net<mailto:david@pgmasters.net>

--
9000799060

On Fri, Oct 18, 2019 at 1:59 AM Daulat Ram <Daulat.Ram@exponential.com>
wrote:

Hello All,

Can you please share some ideas and scenarios how we can do the PITR in
case of disaster.

It depends on what you mean by "disaster". Usually I think that would mean
your server (or entire data center) was destroyed. In this case, you would
want to restore to the latest time available. I would say that this is not
PITR at all, that is just regular recovery.

If someone truncated a table 3 weeks ago, and you didn't realize it until
today, that is a scenario for PITR. Are you using "disaster" to cover this
scenario?

Cheers,

Jeff

Hi,

On Sat, Oct 19, 2019 at 11:16 PM Daulat Ram <Daulat.Ram@exponential.com>
wrote:

Hi All,

Thanks for your suggestions.

One more questions is, how backups are useful if we have streaming
replication . As I know, we can promote the standby as primary in case of
disaster at primary side. Do we need to schedule backups if we have
streaming replication?

1. What if you realized that someone has dropped a table or accidentally
made a change that requires you to recover some table/database from old
backups ?
2. Some organizations requires you to store backups for a few days/months
and even years. This is because, you should be able to perform recovery
from past at any given point of time.

Streaming Replication (unless delayed explicitly) applies the changes
immediately. So, it may be very late by the time you realize that some
accidental change has made some damage. Because, the damage has happened on
both Master & Standby.

Regards,
Avinash Vallarapu.

Thanks

*From:* Avinash Kumar <avinash.vallarapu@gmail.com>
*Sent:* Friday, October 18, 2019 5:28 PM
*To:* David Steele <david@pgmasters.net>
*Cc:* Luca Ferrari <fluca1978@gmail.com>; Andreas Joseph Krogh <
andreas@visena.com>; Daulat Ram <Daulat.Ram@exponential.com>;
pgsql-general@lists.postgresql.org
*Subject:* Re: Postgres Point in time Recovery (PITR),

Hi Daulat,

PITR entirely depends on what type of backups you choose.
Sometimes, to reduce the amount of downtime involved while restoring and
recovering a backup, you may also use a additional delayed standby.

You could use the PG built-in feature to delay the replication and
fast-forward it to the safest point to achieve PITR. But this requires you
to have an additional standby.

https://www.percona.com/blog/2018/06/28/faster-point-in-time-recovery-pitr-postgresql-using-delayed-standby/

If you have several TBs of database, pgBackRest is of course a way to go
for backups (there are few more open source solutions), but also consider
the amount of time it takes for recovery. Keeping all of this in mind, your
approach to PITR changes.

So i would ask you this question, what is the backup tool you use and what
is your backup strategy ? Are you taking a physical backup and performing
continuous archiving of WALs ? The answer to your question entirely depends
on this. :)

Regards,
Avinash Vallarapu.

On Fri, Oct 18, 2019 at 5:17 PM David Steele <david@pgmasters.net> wrote:

On 10/18/19 11:29 AM, Luca Ferrari wrote:

On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh
<andreas@visena.com> wrote:

We use barman (https://www.pgbarman.org/) for continuous streaming

backup and I had to restore from it once, and it went like this:

Just for the records, here's an example of restore with pgbackrest:

% sudo -u postgres pgbackrest --stanza=miguel \
--log-level-console=info --delta restore
...
INFO: restore backup set 20190916-125652F
INFO: remove invalid files/paths/links from /postgres/pgdata/11
INFO: cleanup removed 148 files, 3 paths
...
INFO: write /postgres/pgdata/11/recovery.conf
INFO: restore global/pg_control (performed last
to ensure aborted restores cannot be started)
INFO: restore command end: completed successfully (5113ms)

pgBackRest also has a tutorial on PITR:
https://pgbackrest.org/user-guide.html#pitr

--
-David
david@pgmasters.net

--

9000799060

--
9000799060

On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram <Daulat.Ram@exponential.com> wrote:

One more questions is, how backups are useful if we have streaming replication . As I know, we can promote the standby as primary in case of disaster at primary side. Do we need to schedule backups if we have streaming replication?

Let's speculate a little on that: do you need backups if you have a
RAID-1 configuration?
Replication helps you reduce almost to zero the time to handle a
disaster, backups allow you to recover in a more large time window.

Luca

On 21/10/2019 09:52, Luca Ferrari wrote:

On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram <Daulat.Ram@exponential.com> wrote:

One more questions is, how backups are useful if we have streaming replication . As I know, we can promote the standby as primary in case of disaster at primary side. Do we need to schedule backups if we have streaming replication?

Let's speculate a little on that: do you need backups if you have a
RAID-1 configuration?
Replication helps you reduce almost to zero the time to handle a
disaster, backups allow you to recover in a more large time window.

Luca

TBH I hear this argument more often than I wish.

Offline backups and data replication are nearly entirely orthogonal.

Any form of instantaneous redundancy (RAID, instantaneous replication
and so on) primary is a mitigation measure to protect data &
availability against loss of infrastructure.

Backups (preferably with PITR) also do that, but that's not their
primary purpose unless you can't afford live redundancy on top of them.

Offline backups address many failure scenarios that any form of live
replication is defenseless against (eg: logical damage to the data as a
result of human errors/bugs/vandalism would hit all your replicas, but
you always can perform DR from a backup).

Delayed replicas, or any online rollback capability (such as pg_rewind
off the server's own pg_wal or Oracle's flashback), somewhat live in a
grey area in between, and their effectiveness varies depending on which
level was compromised.

--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--

Notice:
This email is confidential and may contain copyright material of
members of the Ocado Group. Opinions and views expressed in this message
may not necessarily reflect the opinions and views of the members of the
Ocado Group.

If you are not the intended recipient, please notify us
immediately and delete all copies of this message. Please note that it is
your responsibility to scan this message for viruses.

References to the
"Ocado Group" are to Ocado Group plc (registered in England and Wales with
number 7098618) and its subsidiary undertakings (as that expression is
defined in the Companies Act 2006) from time to time. The registered office
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way,
Hatfield, Hertfordshire, AL10 9UL.

On Mon, Oct 21, 2019 at 4:19 PM Fabio Ugo Venchiarutti <
f.venchiarutti@ocado.com> wrote:

On 21/10/2019 09:52, Luca Ferrari wrote:

On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram <Daulat.Ram@exponential.com>

wrote:

One more questions is, how backups are useful if we have streaming

replication . As I know, we can promote the standby as primary in case of
disaster at primary side. Do we need to schedule backups if we have
streaming replication?

Let's speculate a little on that: do you need backups if you have a
RAID-1 configuration?
Replication helps you reduce almost to zero the time to handle a
disaster, backups allow you to recover in a more large time window.

Luca

TBH I hear this argument more often than I wish.

Offline backups and data replication are nearly entirely orthogonal.

Any form of instantaneous redundancy (RAID, instantaneous replication
and so on) primary is a mitigation measure to protect data &
availability against loss of infrastructure.

Backups (preferably with PITR) also do that, but that's not their
primary purpose unless you can't afford live redundancy on top of them.

Offline backups address many failure scenarios that any form of live
replication is defenseless against (eg: logical damage to the data as a
result of human errors/bugs/vandalism would hit all your replicas, but
you always can perform DR from a backup).

I think we are way ahead of the days where we need to live on Offline
backups involving downtimes.
Today, we have several Online Consistent Backup solutions such as
pg_basebackup (built-in with PG), pgBackRest, BARMAN, WAL-G etc ....
Online Consistent Backups + Archiving of WALs are perfectly reliable.
We need to ensure that we have safe backup locations, for example, push
them to AWS S3 and forget about redundancy.
Why do you think only Offline Backups are reliable today ?

Delayed replicas, or any online rollback capability (such as pg_rewind
off the server's own pg_wal or Oracle's flashback), somewhat live in a
grey area in between, and their effectiveness varies depending on which
level was compromised.

What if you delay your replica by a day, before you making a change
tomorrow (that may need a rollback). Delayed Replica is for emergencies IMO
and of course does not satisfy every possible scenario.

--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--

Notice:
This email is confidential and may contain copyright material of
members of the Ocado Group. Opinions and views expressed in this message
may not necessarily reflect the opinions and views of the members of the
Ocado Group.

If you are not the intended recipient, please notify us
immediately and delete all copies of this message. Please note that it is
your responsibility to scan this message for viruses.

References to the
"Ocado Group" are to Ocado Group plc (registered in England and Wales with
number 7098618) and its subsidiary undertakings (as that expression is
defined in the Companies Act 2006) from time to time. The registered
office
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way,
Hatfield, Hertfordshire, AL10 9UL.

Thanks,
Avinash Vallarapu.

On 21/10/2019 12:10, Avinash Kumar wrote:

On Mon, Oct 21, 2019 at 4:19 PM Fabio Ugo Venchiarutti
<f.venchiarutti@ocado.com <mailto:f.venchiarutti@ocado.com>> wrote:

On 21/10/2019 09:52, Luca Ferrari wrote:

On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram

<Daulat.Ram@exponential.com <mailto:Daulat.Ram@exponential.com>> wrote:

One more questions is, how backups are useful if we have

streaming replication . As I know, we can promote the standby as
primary in case of disaster at primary side. Do we need to schedule
backups if we have streaming replication?

Let's speculate a little on that: do you need backups if you have a
RAID-1 configuration?
Replication helps you reduce almost to zero the time to handle a
disaster, backups allow you to recover in a more large time window.

Luca

TBH I hear this argument more often than I wish.

Offline backups and data replication are nearly entirely orthogonal.

Any form of instantaneous redundancy (RAID, instantaneous replication
and so on) primary is a mitigation measure to protect data &
availability against loss of infrastructure.

Backups (preferably with PITR) also do that, but that's not their
primary purpose unless you can't afford live redundancy on top of them.

Offline backups address many failure scenarios that any form of live
replication is defenseless against (eg: logical damage to the data as a
result of human errors/bugs/vandalism would hit all your replicas, but
you always can perform DR from a backup).

I think we are way ahead of the days where we need to live on Offline
backups involving downtimes.
Today, we have several Online Consistent Backup solutions such as
pg_basebackup (built-in with PG), pgBackRest, BARMAN, WAL-G etc ....
Online Consistent Backups + Archiving of WALs are perfectly reliable.
We need to ensure that we have safe backup locations, for example, push
them to AWS S3 and forget about redundancy.
Why do you think only Offline Backups are reliable today ?

Delayed replicas, or any online rollback capability (such as pg_rewind
off the server's own pg_wal or Oracle's flashback), somewhat live in a
grey area in between, and their effectiveness varies depending on which
level was compromised.

What if you delay your replica by a day, before you making a change
tomorrow (that may need a rollback). Delayed Replica is for emergencies
IMO and of course does not satisfy every possible scenario.

Trade-offs come down to individual implementers, so fair enough.

Our biases must come from different use cases/experience: a good
fraction of recovery/sanitisation operations I had to carry out were
very selective/application specific and involved buildup of anomalies
over time, sometimes months.

Wouldn't have been possible without an old frozen reference for users to
compare with, and that hat to come from very cold storage.

--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--

Notice:
This email is confidential and may contain copyright material of
members of the Ocado Group. Opinions and views expressed in this message
may not necessarily reflect the opinions and views of the members of the
Ocado Group.

If you are not the intended recipient, please notify us
immediately and delete all copies of this message. Please note that it is
your responsibility to scan this message for viruses.

References to the
"Ocado Group" are to Ocado Group plc (registered in England and Wales with
number 7098618) and its subsidiary undertakings (as that expression is
defined in the Companies Act 2006) from time to time. The registered office
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way,
Hatfield, Hertfordshire, AL10 9UL.

On Mon, 2019-10-21 at 16:40 +0530, Avinash Kumar wrote:

We need to ensure that we have safe backup locations, for example,
push them to AWS S3 and forget about redundancy.
Why do you think only Offline Backups are reliable today ?

There have been examples of hackers gaining control of an organization's
servers or cloud accounts and not only destroying their online systems
but also methodically deleting all their backups.

There are fewer things that can go catastrophically wrong if one has
actual offline backups. You have to be a lot more careful about
protecting anything attached to the Internet.

Hi,

On Mon, Oct 21, 2019 at 8:16 PM Alan Hodgson <ahodgson@lists.simkin.ca>
wrote:

On Mon, 2019-10-21 at 16:40 +0530, Avinash Kumar wrote:

We need to ensure that we have safe backup locations, for example, push
them to AWS S3 and forget about redundancy.
Why do you think only Offline Backups are reliable today ?

There have been examples of hackers gaining control of an organization's
servers or cloud accounts and not only destroying their online systems but
also methodically deleting all their backups.

There are fewer things that can go catastrophically wrong if one has
actual offline backups. You have to be a lot more careful about protecting
anything attached to the Internet.

I do not agree with this. If a hacker is gaining control of your
organizational servers to destroy your Online backups, can't he destroy the
offline backups and your database ?
This is not a right justification to encouraging Offline Backups over
Online Backups.
If you are worried about storing your online backups through internet on
cloud (i do not agree as you can still secure your data on cloud), store it
in on a server in your Organizational network and do not push them through
internet.
Taking Offline Backups is not the only right way to ensure Reliable
Backups.
We are way ahead of the days where you need to face downtime to take
backups.
Online Backups are reliable in PostgreSQL.

On 10/21/19 8:10 AM, Avinash Kumar wrote:

Hi,

On Mon, Oct 21, 2019 at 8:16 PM Alan Hodgson <ahodgson@lists.simkin.ca
<mailto:ahodgson@lists.simkin.ca>> wrote:

On Mon, 2019-10-21 at 16:40 +0530, Avinash Kumar wrote:

We need to ensure that we have safe backup locations, for example,
push them to AWS S3 and forget about redundancy.
Why do you think only Offline Backups are reliable today ?

There have been examples of hackers gaining control of an
organization's servers or cloud accounts and not only destroying
their online systems but also methodically deleting all their backups.

There are fewer things that can go catastrophically wrong if one has
actual offline backups. You have to be a lot more careful about
protecting anything attached to the Internet.

I do not agree with this. If a hacker is gaining control of your
organizational servers to destroy your Online backups, can't he destroy
the offline backups and your database ?

Well to me off-line means you have introduced an air gap between your
on-line presence and your off-line backups. This would prevent an
intruder from accessing the off-line backups.

This is not a right justification to encouraging Offline Backups over
Online Backups.
If you are worried about storing your online backups through internet on
cloud (i do not agree as you can still secure your data on cloud), store
it in on a server in your Organizational network and do not push them
through internet.
Taking Offline Backups is not the only right way to ensure Reliable
Backups.
We are way ahead of the days where you need to face downtime to take
backups.
Online Backups are reliable in PostgreSQL.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, 2019-10-21 at 20:40 +0530, Avinash Kumar wrote:

can't he destroy the offline backups and your database ?
This is not a right justification to encouraging Offline Backups over
Online Backups.
If you are worried about storing your online backups through internet
on cloud (i do not agree as you can still secure your data on cloud),
store it in on a server in your Organizational network and do not push
them through internet.
Taking Offline Backups is not the only right way to ensure Reliable
Backups.
We are way ahead of the days where you need to face downtime to take
backups.
Online Backups are reliable in PostgreSQL.

I apologize, I think we have a difference in terminology here. Obviously
you don't need to take PostgreSQL out of service to take a backup. I
don't know that you ever did; pg_dump even has always worked fine while
the database is available.

When I say offline backup I mean a backup that is stored in a way that
it cannot be accessed via the Internet.

On Mon, Oct 21, 2019 at 8:47 PM Alan Hodgson <ahodgson@lists.simkin.ca>
wrote:

On Mon, 2019-10-21 at 20:40 +0530, Avinash Kumar wrote:

can't he destroy the offline backups and your database ?
This is not a right justification to encouraging Offline Backups over
Online Backups.
If you are worried about storing your online backups through internet on
cloud (i do not agree as you can still secure your data on cloud), store it
in on a server in your Organizational network and do not push them through
internet.
Taking Offline Backups is not the only right way to ensure Reliable
Backups.
We are way ahead of the days where you need to face downtime to take
backups.
Online Backups are reliable in PostgreSQL.

I apologize, I think we have a difference in terminology here. Obviously
you don't need to take PostgreSQL out of service to take a backup. I don't
know that you ever did; pg_dump even has always worked fine while the
database is available.

When I say offline backup I mean a backup that is stored in a way that it
cannot be accessed via the Internet.

Okay. This gives a clarity, thanks. Mix-up of offline and online in terms
of backup and network.