Thread: pgbackrest - question about restoring cluster to a new cluster onsame server

Hi,

(Thanks, Stephen, for helping with my earlier problem.)

Scenario: there's data corruption on production server, so we need to do a 
PITR restore from "a few days ago" of the cluster holding the prod databases 
to a second cluster on that same VM in order to try and find the missing 
data and load it back into the prod cluster.

Other than putting a high I/O load on the LUN where repo-path is located 
(from both writing WALs to it and reading the backed up files), will there 
be any problems when "pg_ctl start" processes recovery.conf and applies the 
WAL files to the new cluster while the prod cluster is writing new WAL files.

Does my question make sense?

Thanks
-- 
Angular momentum makes the world go 'round.

Ron <ronljohnsonjr@gmail.com> writes:

> Hi,
>
> (Thanks, Stephen, for helping with my earlier problem.)
>
> Scenario: there's data corruption on production server, so we need to
> do a PITR restore from "a few days ago" of the cluster holding the
> prod databases to a second cluster on that same VM in order to try and
> find the missing data and load it back into the prod cluster.
>
> Other than putting a high I/O load on the LUN where repo-path is
> located (from both writing WALs to it and reading the backed up
> files), will there be any problems when "pg_ctl start" processes
> recovery.conf and applies the WAL files to the new cluster while the
> prod cluster is writing new WAL files.

There should be no issues *if* you have insured that the 2 server
configurations do not overlap each other in any way.

HTH

> Does my question make sense?
>
> Thanks

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net

On 9/18/19 6:59 PM, Ron wrote:
> 
> Scenario: there's data corruption on production server, so we need to do
> a PITR restore from "a few days ago" of the cluster holding the prod
> databases to a second cluster on that same VM in order to try and find
> the missing data and load it back into the prod cluster.
> 
> Other than putting a high I/O load on the LUN where repo-path is located
> (from both writing WALs to it and reading the backed up files), will
> there be any problems when "pg_ctl start" processes recovery.conf and
> applies the WAL files to the new cluster while the prod cluster is
> writing new WAL files.
> 
> Does my question make sense?

It does, but the answer lies outside of pgBackRest.  "Can the repo
storage handle the load of archive-push and archive-get at the same
time" is really a question of storage and network throughput.

pgBackRest compresses everything by default which goes a long way
towards increasing throughput, but ultimately we don't control the
bandwidth.

Having said that, if the storage and network throughput are sufficient,
restoring and recovering a standby using pgBackRest will not impact the
primary as a direct pg_basebackup will.

Regards,

-- 
-David
david@pgmasters.net

On 9/18/19 8:31 PM, David Steele wrote:
> On 9/18/19 6:59 PM, Ron wrote:
>> Scenario: there's data corruption on production server, so we need to do
>> a PITR restore from "a few days ago" of the cluster holding the prod
>> databases to a second cluster on that same VM in order to try and find
>> the missing data and load it back into the prod cluster.
>>
>> Other than putting a high I/O load on the LUN where repo-path is located
>> (from both writing WALs to it and reading the backed up files), will
>> there be any problems when "pg_ctl start" processes recovery.conf and
>> applies the WAL files to the new cluster while the prod cluster is
>> writing new WAL files.
>>
>> Does my question make sense?
> It does, but the answer lies outside of pgBackRest.  "Can the repo
> storage handle the load of archive-push and archive-get at the same
> time" is really a question of storage and network throughput.

That's outside my control and will "just" slow things down.

> pgBackRest compresses everything by default which goes a long way
> towards increasing throughput, but ultimately we don't control the
> bandwidth.
>
> Having said that, if the storage and network throughput are sufficient,
> restoring and recovering a standby using pgBackRest will not impact the
> primary as a direct pg_basebackup will.

I'm concerned with one pgbackrest process stepping over another one and the 
restore (or the "pg_ctl start" recovery phase) accidentally corrupting the 
production database by writing WAL files to the original cluster.

-- 
Angular momentum makes the world go 'round.

On 9/18/19 9:40 PM, Ron wrote:
> 
> I'm concerned with one pgbackrest process stepping over another one and
> the restore (or the "pg_ctl start" recovery phase) accidentally
> corrupting the production database by writing WAL files to the original
> cluster.

This is not an issue unless you seriously game the system.  When a
cluster is promoted it selects a new timeline and all WAL will be
archived to the repo on that new timeline.  It's possible to promote a
cluster without a timeline switch by tricking it but this is obviously a
bad idea.

So, if you promote the new cluster and forget to disable archive_command
there will be no conflict because the clusters will be generating WAL on
separate timelines.

In the case of a future failover a higher timeline will be selected so
there still won't be a conflict.

Unfortunately, that dead WAL from the rogue cluster will persist in the
repo until an PostgreSQL upgrade because expire doesn't know when it can
be removed since it has no context.  We're not quite sure how to handle
this but it seems a relatively minor issue, at least as far as
consistency is concerned.

If you do have a split-brain situation where two primaries are archiving
on the same timeline then first-in wins.  WAL from the losing primary
will be rejected.

Regards,
-- 
-David
david@pgmasters.net

On 9/18/19 8:58 PM, David Steele wrote:
> On 9/18/19 9:40 PM, Ron wrote:
>> I'm concerned with one pgbackrest process stepping over another one and
>> the restore (or the "pg_ctl start" recovery phase) accidentally
>> corrupting the production database by writing WAL files to the original
>> cluster.
> This is not an issue unless you seriously game the system.  When a
> cluster is promoted it selects a new timeline and all WAL will be
> archived to the repo on that new timeline.  It's possible to promote a
> cluster without a timeline switch by tricking it but this is obviously a
> bad idea.

What's a timeline switchover?

> So, if you promote the new cluster and forget to disable archive_command
> there will be no conflict because the clusters will be generating WAL on
> separate timelines.

No cluster promotion even contemplated.

The point of the exercise would be to create an older copy of the cluster -- 
while the production cluster is still running, while production jobs are 
still pumping data into the production database -- from before the time of 
the data loss, and query it in an attempt to recover the records which were 
deleted.


> In the case of a future failover a higher timeline will be selected so
> there still won't be a conflict.
>
> Unfortunately, that dead WAL from the rogue cluster will persist in the
> repo until an PostgreSQL upgrade because expire doesn't know when it can
> be removed since it has no context.  We're not quite sure how to handle
> this but it seems a relatively minor issue, at least as far as
> consistency is concerned.
>
> If you do have a split-brain situation where two primaries are archiving
> on the same timeline then first-in wins.  WAL from the losing primary
> will be rejected.
>
> Regards,

-- 
Angular momentum makes the world go 'round.

David Steele <david@pgmasters.net> writes:

> On 9/18/19 9:40 PM, Ron wrote:
>
>> 
>> I'm concerned with one pgbackrest process stepping over another one and
>> the restore (or the "pg_ctl start" recovery phase) accidentally
>> corrupting the production database by writing WAL files to the original
>> cluster.
>
> This is not an issue unless you seriously game the system.  When a

And/or your recovery system is running archive_mode=always :-)

I don't know how popular that setting value is but that plus an
identical archive_command as the origin...  duplicate archival with
whatever consequences.

Disclaimer: I don't know if pgbackrest guards against such a
configuration.

> cluster is promoted it selects a new timeline and all WAL will be
> archived to the repo on that new timeline.  It's possible to promote a
> cluster without a timeline switch by tricking it but this is obviously a
> bad idea.
>
> So, if you promote the new cluster and forget to disable archive_command
> there will be no conflict because the clusters will be generating WAL on
> separate timelines.
>
> In the case of a future failover a higher timeline will be selected so
> there still won't be a conflict.
>
> Unfortunately, that dead WAL from the rogue cluster will persist in the
> repo until an PostgreSQL upgrade because expire doesn't know when it can
> be removed since it has no context.  We're not quite sure how to handle
> this but it seems a relatively minor issue, at least as far as
> consistency is concerned.
>
> If you do have a split-brain situation where two primaries are archiving
> on the same timeline then first-in wins.  WAL from the losing primary
> will be rejected.
>
> Regards,

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net

On 9/18/19 10:18 PM, Jerry Sievers wrote:
> David Steele <david@pgmasters.net> writes:
> 
>> This is not an issue unless you seriously game the system.  When a
> 
> And/or your recovery system is running archive_mode=always :-)
> 
> I don't know how popular that setting value is but that plus an
> identical archive_command as the origin...  duplicate archival with
> whatever consequences.
> 
> Disclaimer: I don't know if pgbackrest guards against such a
> configuration.

We current disallow archive_mode=always because the locking issues are
complex.  Also, standbys will not always push WAL which is binary
identical to the primary (even though they are logically the same) so it
can be really tricky to tell who is authoritative.

We have plans in this area but they are currently on the back-burner.

Regards,
-- 
-David
david@pgmasters.net

Greetings,

* Ron (ronljohnsonjr@gmail.com) wrote:
> On 9/18/19 8:58 PM, David Steele wrote:
> >On 9/18/19 9:40 PM, Ron wrote:
> >>I'm concerned with one pgbackrest process stepping over another one and
> >>the restore (or the "pg_ctl start" recovery phase) accidentally
> >>corrupting the production database by writing WAL files to the original
> >>cluster.
> >This is not an issue unless you seriously game the system.  When a
> >cluster is promoted it selects a new timeline and all WAL will be
> >archived to the repo on that new timeline.  It's possible to promote a
> >cluster without a timeline switch by tricking it but this is obviously a
> >bad idea.
>
> What's a timeline switchover?

Put simply, it's a branch off of the current WAL stream on to a new WAL
stream and it happens whenever there's a promotion.

Forgive the ASCII art, but-

----A---> timeline 1, where things start
     \-----> a promotion happened at time A, new WAL is on timeline 2

Consider an async replication scenario, where the network on the primary
is lost but it keeps writing out WAL and accepting new commits, but at a
time "A" we give up on it and promote the replica, so the replica
switches to timeline 2 and starts accepting writes.  Now we are in a
situation where two systems are generating WAL (the network partitioned
old primary, and the replica-now-primary).  Having the promotion switch
to a timeline makes it clear where that promotion happened and where the
replica-now-primary's WAL stream started.

This is actually what pg_rewind is based around too- to re-master the
old primary, it'll find that split point A and "rewind" (well, not
really, because it just grabs the pages, but whatever) the old primary
back to A and then the old primary can follow the new primary on
timeline 2.

> >So, if you promote the new cluster and forget to disable archive_command
> >there will be no conflict because the clusters will be generating WAL on
> >separate timelines.
>
> No cluster promotion even contemplated.

Ah, but you are talking about a cluster promotion, though you don't
realize it.  Any time there is a "at some point, I was to stop replaying
WAL and start accepting new changes", there's a timeline switch and
notionally a promotion.

> The point of the exercise would be to create an older copy of the cluster --
> while the production cluster is still running, while production jobs are
> still pumping data into the production database -- from before the time of
> the data loss, and query it in an attempt to recover the records which were
> deleted.

Sure, that's all entirely possible and shouldn't be an issue.  When you
go through the restore process and specify a point where you want the
restore to stop, so that you can connect and pull the down the table,
when PG reaches that point it'll promote and do a timeline switch.

Now, if you don't actually want that restore to promote and come up as a
system that you can write to, you could instead say 'pause', and then
connect to the database and grab whatever data you needed.  That should
also avoid the concern around archive command, provided you never
actually let that system finish recovery and instead just shut it down
while it's still read-only.

If you want to play around with this stuff and see what happens with a
promote, or try doing a pause instead, you might be interested in:

https://learn.crunchydata.com/

and specifically the pgbackrest one:

https://learn.crunchydata.com/pg-administration/courses/basic-postgresql-for-dbas/pgbackrest/

Basically, it's kind of like a blog post where you can play around on a
scratch system that's built into the page and click through the steps to
see what happens, and change things around if you want.

Thanks,

Stephen

On 9/19/19 9:17 AM, Stephen Frost wrote:
[snip]
> Ah, but you are talking about a cluster promotion, though you don't
> realize it.  Any time there is a "at some point, I was to stop replaying
> WAL and start accepting new changes", there's a timeline switch and
> notionally a promotion.
>
>> The point of the exercise would be to create an older copy of the cluster --
>> while the production cluster is still running, while production jobs are
>> still pumping data into the production database -- from before the time of
>> the data loss, and query it in an attempt to recover the records which were
>> deleted.
> Sure, that's all entirely possible and shouldn't be an issue.  When you
> go through the restore process and specify a point where you want the
> restore to stop, so that you can connect and pull the down the table,
> when PG reaches that point it'll promote and do a timeline switch.
>
> Now, if you don't actually want that restore to promote and come up as a
> system that you can write to, you could instead say 'pause', and then
> connect to the database and grab whatever data you needed.  That should
> also avoid the concern around archive command, provided you never
> actually let that system finish recovery and instead just shut it down
> while it's still read-only.
>
> If you want to play around with this stuff and see what happens with a
> promote, or try doing a pause instead, you might be interested in:
>
> https://learn.crunchydata.com/
>
> and specifically the pgbackrest one:
>
> https://learn.crunchydata.com/pg-administration/courses/basic-postgresql-for-dbas/pgbackrest/
>
> Basically, it's kind of like a blog post where you can play around on a
> scratch system that's built into the page and click through the steps to
> see what happens, and change things around if you want.

I've been a DBA for 20+ years, and restored a **lot** of **copies** of 
production databases.  PostgreSQL has some seriously different concepts. 
With every other system, it's: restore full backup to new location, restore 
differential backup, apply some roll-forward logs and you're done.  No 
pausing, promoting, etc.

-- 
Angular momentum makes the world go 'round.

Greetings,

* Ron (ronljohnsonjr@gmail.com) wrote:
> I've been a DBA for 20+ years, and restored a **lot** of **copies** of
> production databases.  PostgreSQL has some seriously different concepts.
> With every other system, it's: restore full backup to new location, restore
> differential backup, apply some roll-forward logs and you're done.  No
> pausing, promoting, etc.

Yup, I agree entirely, PostgreSQL is different.

Thanks,

Stephen