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.
