Re: AW: [Extern] Re: consistent postgresql snapshot

Talking about fsfreeze and blocksize are not relevant in your case at all.
You can't make a backup this way any way. According your mail,
you are playing with database recovery after crash. Is pg crash proof? Yes (
https://www.postgresql.org/docs/current/wal-intro.html).
You can use this solution for example to make a test environment and it
works,
but not for live database backup.
For backup use a pg_basebackup or pg_start_backup()/snap/pg_stop_backup()
solution
br
Kaido

On Thu, 12 May 2022 at 17:53, Nick Cleaton <nick(at)cleaton(dot)net> wrote:

> On Thu, 12 May 2022 at 14:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch> writes:
>> > I don't want to do use the normal backup algorithm where
>> pg_start_backup + pg_stop_backup will fix any fractured block and I am
>> required to have all archived logfiles, therefore.
>> > I want to produce an atomic consistent disk snapshot.
>>
>> [ shrug... ] You can't have that. [snip]
>>
>> The only way you could get a consistent on-disk image is to shut
>> the server down (being sure to do a clean not "immediate" shutdown)
>> and then take the snapshot.
>>
>
> I think you could work around that by taking a dirty snapshot, making a
> writable filesystem from it, waiting until you've archived enough WAL to
> get that to a consistent state, and then firing up a temporary postmaster
> on that filesystem to go through recovery and shut down cleanly.
>
>