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

Whether or not you want to use the "normal backup algorithm" is irrelevant,
since if you try and start that snapshot Postgresql /*will*/ see the
instance as /*needing*/ to be recovered.

Having said that... our storage team does SAN snapshots of the *single LUN*
where */all/**of the Postgres* files (regardless of mountpoint) reside on
"my" Postgresql server.  When needed, the Linux guys restored the relevant
snapshot to a new server, changed the host name and IP address and Postgres
started right up, recovering just as if there had been a system crash.

On 5/12/22 04:10, Zwettler Markus (OIZ) wrote:
> Email Signature
>
> 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.
>
> I am not sure if freeze/unfreeze will be enough with default values (8k db
> blocksize vs. 4k fs blocksize).
>
> I am quite sure it should be safe when I also change the fs blocksize to
> 8k because 1 IO = 1 db block = 1 fs block than.
>
> Any thoughts?
>
> *Von:*Ron <ronljohnsonjr(at)gmail(dot)com>
> *Gesendet:* Donnerstag, 12. Mai 2022 01:51
> *An:* pgsql-general(at)lists(dot)postgresql(dot)org
> *Betreff:* [Extern] Re: consistent postgresql snapshot
>
> On 5/11/22 10:41, Zwettler Markus (OIZ) wrote:
>
> PostgreSQL12 running on CentOS7 + ext4.
>
> Would it be save to do a "fsfreeze –freeze" + disk snapshot +
> "fsfreeze –unfreeze" of the PGDATA filesystem to get a consistent
> snapshot?
>
> I am wondering as PostgreSQL default blocksize = 8k while ext4 default
> blocksize = 4k, resulting in 2 fs blocks per db block.
>
>
> If data/base and pg_xlog are on the same filesystem, then it might work.
>
> Be sure to select pg_start_backup('something informative');beforehand, and
> select pg_stop_backup();afterwards.
>
> For example:
> psql -c "select pg_start_backup('something informative');"
> fsfreeze –freeze
> /disk snapshot/
> fsfreeze –unfreeze
> psql -c "select pg_stop_backup();"
>
> --
> Angular momentum makes the world go 'round.
>
>
> Achtung: Diese E-Mail wurde von einer externen Adresse verschickt. Klicken
> Sie auf keine Links und öffnen Sie keine angehängten Dateien, wenn Sie den
> Absender bzw. die Absenderin nicht kennen. Sind Sie sich unsicher,
> kontaktieren Sie den Service Desk der Stadt Zürich.
>

--
Angular momentum makes the world go 'round.