Обсуждение: consistent postgresql snapshot

On 5/11/22 10:41, Zwettler Markus (OIZ) wrote:

@font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0;}@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}h1 {mso-style-priority:9; mso-style-link:"\00DCberschrift 1 Zchn"; margin-top:12.0pt; margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; page-break-after:avoid; font-size:16.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}h2 {mso-style-priority:9; mso-style-link:"\00DCberschrift 2 Zchn"; margin-top:12.0pt; margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; page-break-after:avoid; font-size:14.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}h3 {mso-style-priority:9; mso-style-link:"\00DCberschrift 3 Zchn"; margin-top:12.0pt; margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; page-break-after:avoid; font-size:12.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}h4 {mso-style-priority:9; mso-style-link:"\00DCberschrift 4 Zchn"; margin-top:12.0pt; margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; page-break-after:avoid; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}h5 {mso-style-priority:9; mso-style-link:"\00DCberschrift 5 Zchn"; margin-top:12.0pt; margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; page-break-after:avoid; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoHeader, li.MsoHeader, div.MsoHeader {mso-style-priority:99; mso-style-link:"Kopfzeile Zchn"; margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoFooter, li.MsoFooter, div.MsoFooter {mso-style-priority:99; mso-style-link:"Fu\00DFzeile Zchn"; margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoListBullet, li.MsoListBullet, div.MsoListBullet {mso-style-priority:99; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; text-indent:-18.0pt; mso-list:l2 level1 lfo2; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoListBulletCxSpFirst, li.MsoListBulletCxSpFirst, div.MsoListBulletCxSpFirst {mso-style-priority:99; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; text-indent:-18.0pt; mso-list:l2 level1 lfo2; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoListBulletCxSpMiddle, li.MsoListBulletCxSpMiddle, div.MsoListBulletCxSpMiddle {mso-style-priority:99; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; text-indent:-18.0pt; mso-list:l2 level1 lfo2; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoListBulletCxSpLast, li.MsoListBulletCxSpLast, div.MsoListBulletCxSpLast {mso-style-priority:99; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; text-indent:-18.0pt; mso-list:l2 level1 lfo2; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoListNumber, li.MsoListNumber, div.MsoListNumber {mso-style-priority:99; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:18.0pt; margin-bottom:.0001pt; mso-add-space:auto; text-indent:-18.0pt; mso-list:l0 level1 lfo4; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoListNumberCxSpFirst, li.MsoListNumberCxSpFirst, div.MsoListNumberCxSpFirst {mso-style-priority:99; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:18.0pt; margin-bottom:.0001pt; mso-add-space:auto; text-indent:-18.0pt; mso-list:l0 level1 lfo4; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoListNumberCxSpMiddle, li.MsoListNumberCxSpMiddle, div.MsoListNumberCxSpMiddle {mso-style-priority:99; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:18.0pt; margin-bottom:.0001pt; mso-add-space:auto; text-indent:-18.0pt; mso-list:l0 level1 lfo4; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoListNumberCxSpLast, li.MsoListNumberCxSpLast, div.MsoListNumberCxSpLast {mso-style-priority:99; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:18.0pt; margin-bottom:.0001pt; mso-add-space:auto; text-indent:-18.0pt; mso-list:l0 level1 lfo4; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.MsoTitle, li.MsoTitle, div.MsoTitle {mso-style-priority:10; mso-style-link:"Titel Zchn"; margin-top:24.0pt; margin-right:0cm; margin-bottom:12.0pt; margin-left:0cm; mso-add-space:auto; border:none; padding:0cm; font-size:20.0pt; font-family:"Arial",sans-serif; letter-spacing:.25pt; mso-fareast-language:EN-US; font-weight:bold;}p.MsoTitleCxSpFirst, li.MsoTitleCxSpFirst, div.MsoTitleCxSpFirst {mso-style-priority:10; mso-style-link:"Titel Zchn"; mso-style-type:export-only; margin-top:24.0pt; margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; mso-add-space:auto; border:none; padding:0cm; font-size:20.0pt; font-family:"Arial",sans-serif; letter-spacing:.25pt; mso-fareast-language:EN-US; font-weight:bold;}p.MsoTitleCxSpMiddle, li.MsoTitleCxSpMiddle, div.MsoTitleCxSpMiddle {mso-style-priority:10; mso-style-link:"Titel Zchn"; mso-style-type:export-only; margin:0cm; margin-bottom:.0001pt; mso-add-space:auto; border:none; padding:0cm; font-size:20.0pt; font-family:"Arial",sans-serif; letter-spacing:.25pt; mso-fareast-language:EN-US; font-weight:bold;}p.MsoTitleCxSpLast, li.MsoTitleCxSpLast, div.MsoTitleCxSpLast {mso-style-priority:10; mso-style-link:"Titel Zchn"; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:12.0pt; margin-left:0cm; mso-add-space:auto; border:none; padding:0cm; font-size:20.0pt; font-family:"Arial",sans-serif; letter-spacing:.25pt; mso-fareast-language:EN-US; font-weight:bold;}p.MsoSubtitle, li.MsoSubtitle, div.MsoSubtitle {mso-style-priority:11; mso-style-link:"Untertitel Zchn"; margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Arial",sans-serif; letter-spacing:.75pt; mso-fareast-language:EN-US; font-weight:bold;}a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:purple; text-decoration:underline;}p.MsoNoSpacing, li.MsoNoSpacing, div.MsoNoSpacing {mso-style-priority:1; margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}p.Standard85, li.Standard85, div.Standard85 {mso-style-name:"Standard 8\.5"; mso-style-link:"Standard 8\.5 Zchn"; margin:0cm; margin-bottom:.0001pt; font-size:8.5pt; font-family:"Arial",sans-serif; mso-fareast-language:EN-US;}span.Standard85Zchn {mso-style-name:"Standard 8\.5 Zchn"; mso-style-link:"Standard 8\.5"; font-family:"Arial",sans-serif;}span.berschrift1Zchn {mso-style-name:"\00DCberschrift 1 Zchn"; mso-style-priority:9; mso-style-link:"\00DCberschrift 1"; font-family:"Arial",sans-serif; font-weight:bold;}span.berschrift2Zchn {mso-style-name:"\00DCberschrift 2 Zchn"; mso-style-priority:9; mso-style-link:"\00DCberschrift 2"; font-family:"Arial",sans-serif; font-weight:bold;}span.berschrift3Zchn {mso-style-name:"\00DCberschrift 3 Zchn"; mso-style-priority:9; mso-style-link:"\00DCberschrift 3"; font-family:"Arial",sans-serif; font-weight:bold;}span.berschrift4Zchn {mso-style-name:"\00DCberschrift 4 Zchn"; mso-style-priority:9; mso-style-link:"\00DCberschrift 4"; font-family:"Arial",sans-serif; font-weight:bold;}span.berschrift5Zchn {mso-style-name:"\00DCberschrift 5 Zchn"; mso-style-priority:9; mso-style-link:"\00DCberschrift 5"; font-family:"Arial",sans-serif; font-weight:bold;}span.UntertitelZchn {mso-style-name:"Untertitel Zchn"; mso-style-priority:11; mso-style-link:Untertitel; font-family:"Arial",sans-serif; letter-spacing:.75pt; font-weight:bold;}span.TitelZchn {mso-style-name:"Titel Zchn"; mso-style-priority:10; mso-style-link:Titel; font-family:"Arial",sans-serif; letter-spacing:.25pt; font-weight:bold;}span.KopfzeileZchn {mso-style-name:"Kopfzeile Zchn"; mso-style-priority:99; mso-style-link:Kopfzeile; font-family:"Arial",sans-serif;}span.FuzeileZchn {mso-style-name:"Fu\00DFzeile Zchn"; mso-style-priority:99; mso-style-link:Fu\00DFzeile; font-family:"Arial",sans-serif;}span.E-MailFormatvorlage35 {mso-style-type:personal-compose; font-family:"Arial",sans-serif; font-variant:normal !important; color:windowtext; text-transform:none; position:relative; top:0pt; mso-text-raise:0pt; letter-spacing:0pt; mso-ligatures:none; mso-number-form:default; mso-number-spacing:default; mso-stylistic-set:0; mso-contextual-alternates:no; font-weight:normal; font-style:normal; text-decoration:none none; vertical-align:baseline;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}.MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;}div.WordSection1 {page:WordSection1;}ol {margin-bottom:0cm;}ul {margin-bottom:0cm;}

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();"

"Zwettler Markus (OIZ)" <Markus.Zwettler@zuerich.ch> writes:
> I don't want to do use the normal backup algorithm where pg_start_backup + pg_stop_backup will fix any fractured
blockand I am required to have all archived logfiles, therefore. 
> I want to produce an atomic consistent disk snapshot.

[ shrug... ]  You can't have that.  There is never any guarantee that
the on-disk database files are fully up-to-date while the Postgres
server is running, because there may be updates sitting in buffers
in shared memory that have not been written out yet.  We achieve
crash safety by ensuring that the WAL log contains sufficient info to
recreate any such updates by replaying WAL from the last checkpoint.
But without going through that replay process, the data visible in
the filesystem may be inconsistent.  Even taking a disk snapshot
immediately after a checkpoint won't help, because we use spread
checkpoints.  These points are independent of the possibility that
any one block write is non-atomic, although that's surely a factor
as well.

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.

            regards, tom lane

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.