Re: pg_rewind - enable wal_log_hints or data-checksums

g_rewind requires that the target server either has the wal_log_hints > > option enabled in postgresql.conf or data checksums enabled when the > cluster > > was initialized with initdb. > > Yes, this is to make sure that you don't finish with a corrupted > target server if

Re: data checksums

le it is? > > > > That line is probably somewhat out of date at this point. We haven't > seen a significant slowdown in enabling them on any modern hardware. I > always turn them on, except on the type of filesystems/hardware mentioned > above. > > The last in-d

Re: data checksums

ven't seen a > significant slowdown in enabling them on any modern hardware. I always turn > them on, except on the type of filesystems/hardware mentioned above. The last in-depth analysis of data checksums (and hint bits) overhead that I can remember is from 2019: https://www.po

Re: data checksums

and undetected errors, definitely. ZFS, for example, can correct those (within limits); XFS doesn't try. I have been told that SSDs can have uncorrected/undetected errors as well, but I don't know details on that. > Turning data checksums on will write WAL for hint bits, which can > sign

Re: data checksums

On Tue, 2024-08-06 at 09:29 -0700, Christophe Pettus wrote: > > > On Aug 6, 2024, at 08:11, bruno vieira da silva > > wrote: > > > > so my question is why data checksums aren't enabled by default on pg? > > At this point, mostly historical reas

Re: data checksums

The penalty is not "considerable", but it is not completely trivial either. But it's more on the trivial side. Part of the problem is that it is hard to measure, as it is very workload dependent. As to why it is not the default, Postgres is very careful and conservative by default, and not everybod

Re: data checksums

> On Aug 6, 2024, at 08:11, bruno vieira da silva wrote: > > so my question is why data checksums aren't enabled by default on pg? At this point, mostly historical reasons. They're also superfluous if your underlying file system or storage hardware does storage-leve

Re: data checksums

On Tue, Aug 6, 2024 at 11:12 AM bruno vieira da silva wrote: > Hello. > I've been dealing with some database corruption events, so i've been > wondering to enable data checksums on my deployments. > > so my question is why data checksums aren't enabled by default on

data checksums

Hello. I've been dealing with some database corruption events, so i've been wondering to enable data checksums on my deployments. so my question is why data checksums aren't enabled by default on pg? the pg doc mentions a considerable performance penality, how considerable

Re: initdb --data-checksums

On Mon, Nov 09, 2020 at 06:03:43PM +0100, Paul Förster wrote: > indeed, it is. Have a look at: > > https://www.postgresql.org/docs/12/app-pgchecksums.html > > Make sure the database is cleanly shut down before doing it. This tool is really useful with upgrades after pg_upgrade. Please note that

Re: initdb --data-checksums

Hi Matt, > On 09. Nov, 2020, at 18:00, Matt Zagrabelny wrote: > > Hello, > > I see the --data-checksums option for initdb. Is it possible to use > --data-checksums after the cluster has been initialized? I'm guessing "not", > but thought I'd ask. &g

initdb --data-checksums

Hello, I see the --data-checksums option for initdb. Is it possible to use --data-checksums after the cluster has been initialized? I'm guessing "not", but thought I'd ask. I'm running v12 on Debian. Thanks for any help! -m

Re: Using of --data-checksums

On Thu, Apr 16, 2020 at 03:47:34PM -0700, Jeremy Schneider wrote: > Data checksums are a hard requirement across the entire RDS PostgreSQL > fleet - we do not allow it to be disabled in RDS. I've definitely seen a > lot of hard evidence (for example, customer cases I've persona

Re: Using of --data-checksums

On Sun, Apr 12, 2020 at 4:23 PM Tom Lane wrote: > Magnus Hagander writes: > > And FWIW, I do think we should change the default. And maybe spend some > > extra effort on the message coming out of pg_upgrade in this case to make > > it clear to people what their options are and exactly what to do

Re: Using of --data-checksums

On Sun, Apr 12, 2020 at 10:23:24AM -0400, Tom Lane wrote: > Magnus Hagander writes: >> And FWIW, I do think we should change the default. And maybe spend some >> extra effort on the message coming out of pg_upgrade in this case to make >> it clear to people what their options are and exactly what

Re: Using of --data-checksums

Magnus Hagander writes: > And FWIW, I do think we should change the default. And maybe spend some > extra effort on the message coming out of pg_upgrade in this case to make > it clear to people what their options are and exactly what to do. Is there any hard evidence of checksums catching proble

Re: Using of --data-checksums

t, and they'd re-initdb the > > new cluster and then re-run pg_upgrade to figure out what the next issue > > is.. > > We discussed that a couple of months ago, and we decided to keep that > out of the upgrade story, no? Anyway, if you want to enable or > disable data checksum

Re: Using of --data-checksums

xt issue > is.. We discussed that a couple of months ago, and we decided to keep that out of the upgrade story, no? Anyway, if you want to enable or disable data checksums on an existing cluster, you always have the possibility to use pg_checksums --enable. This exists in core since 12, and

Re: Using of --data-checksums

Greetings, * Bruce Momjian (br...@momjian.us) wrote: > On Wed, Apr 8, 2020 at 11:54:34AM -0400, Stephen Frost wrote: > > * BGoebel (b.goe...@prisma-computer.de) wrote: > > > initdb --data-checksums "... help to detect corruption by the I/O system" > > >

Re: Using of --data-checksums

On Wed, Apr 8, 2020 at 11:54:34AM -0400, Stephen Frost wrote: > Greetings, > > * BGoebel (b.goe...@prisma-computer.de) wrote: > > initdb --data-checksums "... help to detect corruption by the I/O system" > > There is an (negligible?) impact on performance, ok. &g

Re: Using of --data-checksums

Greetings, * BGoebel (b.goe...@prisma-computer.de) wrote: > initdb --data-checksums "... help to detect corruption by the I/O system" > There is an (negligible?) impact on performance, ok. > > Is there another reason NOT to use this feature ? Not in my view. > H

Re: Using of --data-checksums

On Tue, Apr 07, 2020 at 08:10:13AM -0700, BGoebel wrote: > initdb --data-checksums "... help to detect corruption by the I/O system" > There is an (negligible?) impact on performance, ok. > > Is there another reason NOT to use this feature ? > Has anyone had good or ba

Using of --data-checksums

initdb --data-checksums "... help to detect corruption by the I/O system" There is an (negligible?) impact on performance, ok. Is there another reason NOT to use this feature ? Has anyone had good or bad experience with the use of --data-checksums? Thanks in advance! Bernhard --

Re: Recommended way to enable data-checksums on Centos 7?

On 2/25/19 2:05 PM, Ken Tanzer wrote: On Mon, Feb 25, 2019 at 1:35 PM Adrian Klaver > wrote: If I am following correctly, what you want is something like the below from pg_ctl, correct?: https://www.postgresql.org/docs/11/app-pg-ctl.html pg_ctl

Re: Recommended way to enable data-checksums on Centos 7?

On Mon, Feb 25, 2019 at 1:35 PM Adrian Klaver wrote: > If I am following correctly, what you want is something > like the below from pg_ctl, correct?: > > https://www.postgresql.org/docs/11/app-pg-ctl.html > > pg_ctl init[db] [-D datadir] [-s] [-o initdb-options] ... > > > checksums are go

Re: Recommended way to enable data-checksums on Centos 7?

On 2/25/19 11:19 AM, Ken Tanzer wrote: On Sun, Feb 24, 2019 at 5:03 PM Adrian Klaver > wrote: On 2/24/19 2:39 PM, Ken Tanzer wrote: > I'm just wondering if there is a more preferred way to do this, Not seeing anything responsive to this question,

Re: Recommended way to enable data-checksums on Centos 7?

On Sun, Feb 24, 2019 at 5:03 PM Adrian Klaver wrote: > On 2/24/19 2:39 PM, Ken Tanzer wrote: > I'm just wondering if there is a more preferred way to do this, Not seeing anything responsive to this question, I'll assume that PGSETUP_INITDB_OPTIONS is the preferred method. > and/or > > any pa

Re: Recommended way to enable data-checksums on Centos 7?

On 2/24/19 2:39 PM, Ken Tanzer wrote: Hi.  I recently installed PG 11.2 on Centos 7, following the excellent directions at . I wanted to enable data-checksums.  I at first tried appending --data-checksums to the doc-specified command: /usr/pgsql-11/bin/postgresql-11-setup initdb --data

Recommended way to enable data-checksums on Centos 7?

Hi. I recently installed PG 11.2 on Centos 7, following the excellent directions at https://www.postgresql.org/download/linux/redhat/. I wanted to enable data-checksums. I at first tried appending --data-checksums to the doc-specified command: /usr/pgsql-11/bin/postgresql-11-setup initdb

Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

On 2018-01-10 11:39:21 -0800, Andres Freund wrote: > On 2018-01-09 20:51:17 -0500, Stephen Frost wrote: > > * Andreas Joseph Krogh (andr...@visena.com) wrote: > > > Aha, so enabling CRC causes hint-bits to be written causing extra > > > WAL-logging, > > > which woudn't be the case without CRC ena

Sv: Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

På onsdag 10. januar 2018 kl. 22:36:42, skrev Jeff Janes mailto:jeff.ja...@gmail.com>>: On Wed, Jan 10, 2018 at 12:23 PM, Thomas Poty mailto:thomas.p...@gmail.com>> wrote: Hello, A question seems to be, according to me, important : How a corruption, detected thanks to data-checksu

Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

On Wed, Jan 10, 2018 at 12:23 PM, Thomas Poty wrote: > Hello, > A question seems to be, according to me, important : > How a corruption, detected thanks to data-checksums, is fixed? > Take two full cold backups of the current mess you have, including the executables, and lock one

Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

Hello, A question seems to be, according to me, important : How a corruption, detected thanks to data-checksums, is fixed? Thank you, Thomas Le 10 janv. 2018 20:39, "Andres Freund" a écrit : > On 2018-01-09 20:51:17 -0500, Stephen Frost wrote: > > Greetings, > > &

Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

On 2018-01-09 20:51:17 -0500, Stephen Frost wrote: > Greetings, > > * Andreas Joseph Krogh (andr...@visena.com) wrote: > > Aha, so enabling CRC causes hint-bits to be written causing extra > > WAL-logging, > > which woudn't be the case without CRC enabled? > > Thanks for pointing that out. > >

Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

t bit > >  writes to a page now needs to be WAL logged. > > which isn't mitigated by SIMD / hardware CRC / whatnot. >   > Aha, so enabling CRC causes hint-bits to be written causing extra > WAL-logging, > which woudn't be the case without CRC enabled? &

Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

Greetings, * Andreas Joseph Krogh (andr...@visena.com) wrote: > Aha, so enabling CRC causes hint-bits to be written causing extra > WAL-logging, > which woudn't be the case without CRC enabled? > Thanks for pointing that out. Yes, having checksums enabled forces logging of hint bits. You can e

Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

På onsdag 10. januar 2018 kl. 01:01:26, skrev Andres Freund mailto:and...@anarazel.de>>: On 2018-01-10 00:25:08 +0100, Andreas Joseph Krogh wrote: > På tirsdag 09. januar 2018 kl. 23:42:45, skrev Rob Sargent < > robjsarg...@gmail.com >: >   > >    On 01/09/2018 03

Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

On 2018-01-10 00:25:08 +0100, Andreas Joseph Krogh wrote: > På tirsdag 09. januar 2018 kl. 23:42:45, skrev Rob Sargent < > robjsarg...@gmail.com >: >   > >   On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote: > På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres F

Sv: Re: Sv: Re: Sv: Re: data-checksums

ing explicit about usage of HW-accelerated (in CPU) instructions in algorithms used is helpfull, and might help users choose enabling data-checksums.   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>  

Re: Sv: Re: Sv: Re: data-checksums

On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote: På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund mailto:and...@anarazel.de>>: Hi, On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote: > Does PG use HW-accellerated crc if CPU supports it[1]? Yes we do, for

Sv: Re: Sv: Re: data-checksums

På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund mailto:and...@anarazel.de>>: Hi, On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote: > Does PG use HW-accellerated crc if CPU supports it[1]? Yes we do, for WAL checksums. The page checksums are a different algorithm though, o

Re: Sv: Re: data-checksums

Hi, On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote: > Does PG use HW-accellerated crc if CPU supports it[1]? Yes we do, for WAL checksums. The page checksums are a different algorithm though, one which has the advantage of being SIMD compatible. The checksum computations have some impa

Sv: Re: data-checksums

På tirsdag 09. januar 2018 kl. 21:37:16, skrev Joshua D. Drake < j...@commandprompt.com >: On 01/09/2018 12:22 PM, Andres Freund wrote: > On 2018-01-09 20:04:04 +0100, Rakesh Kumar wrote: I also would like to believe that the hit is small, but when PG off

Re: data-checksums

On 01/09/2018 12:22 PM, Andres Freund wrote: On 2018-01-09 20:04:04 +0100, Rakesh Kumar wrote: I also would like to believe that the hit is small, but when PG official document writes "noticeable performance penalty", it becomes difficult to convince management that the hit is small :-) Why bel

Re: data-checksums

On 2018-01-09 20:04:04 +0100, Rakesh Kumar wrote: > > > > I also would like to believe that the hit is small, but when PG > > > official document writes "noticeable performance penalty", it becomes > > > difficult to convince management that the hit is small :-) > > > > Why believe, when you can

Re: data-checksums

"On Tue, 9 Jan 2018 20:02:37 +0100, "Rakesh Kumar" wrote: >Hello Mr. Pedantic, > >> noticeable != huge. > >and noticeable != small/negligible either, at least from English >point of view. small != negligible. The word "noticable" does not imply any particular magnitude of event. It means only

Re: data-checksums

On Tue, Jan 9, 2018 at 12:58 PM, Rakesh Kumar wrote: > > > That said, imv anyway, the performance hit is small and having checksums > > is well worth it. > > I also would like to believe that the hit is small, but when PG official > document writes "noticeable performance penalty", it becomes dif

Re: data-checksums

> > I also would like to believe that the hit is small, but when PG > > official document writes "noticeable performance penalty", it becomes > > difficult to convince management that the hit is small :-) > > Why believe, when you can measure? yup doing that. But I still feel that PG documentat

Re: data-checksums

Hello Mr. Pedantic, > noticeable != huge. and noticeable != small/negligible either, at least from English point of view.

Re: data-checksums

Rakesh Kumar wrote: > > > That said, imv anyway, the performance hit is small and having > > checksums is well worth it. > > I also would like to believe that the hit is small, but when PG > official document writes "noticeable performance penalty", it becomes > difficult to convince management t

Re: data-checksums

On 2018-01-09 18:58:41 +0100, Rakesh Kumar wrote: > > > That said, imv anyway, the performance hit is small and having checksums > > is well worth it. > > I also would like to believe that the hit is small, but when PG > official document writes "noticeable performance penalty", it becomes > diff

Re: data-checksums

> That said, imv anyway, the performance hit is small and having checksums > is well worth it. I also would like to believe that the hit is small, but when PG official document writes "noticeable performance penalty", it becomes difficult to convince management that the hit is small :-)

Re: data-checksums

Greetings, * Rakesh Kumar (rakeshkumar...@mail.com) wrote: > --data-checksums > Use checksums on data pages to help detect corruption by the I/O system that > would otherwise be silent. Enabling checksums may incur a noticeable > performance penalty. This option can only b

data-checksums

--data-checksums Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. Enabling checksums may incur a noticeable performance penalty. This option can only be set during initialization, and cannot be changed later. If set, checksums are