Re: consistent postgresql snapshot - Mailing list pgsql-general

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