Re: pgbackrest - question about restoring cluster to a new cluster on same server

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.