Re: Does PostgreSQL check database integrity at startup?

Pavel Stehule <pavel.stehule@xxxxxxxxx> · Tue, 26 Dec 2017 16:57:43 +0100

2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter <richter@xxxxxxxxxxxxxx>:

    Em 26/12/2017 13:40, Pavel Stehule escreveu:

          2017-12-26 16:37 GMT+01:00 Edson
            Carlos Ericksson Richter <richter@xxxxxxxxxxxxxx>:

                   Em 26/12/2017 12:25, Pavel Stehule
                    escreveu:

                          2017-12-26 14:44
                            GMT+01:00 Martin Marques <martin.marques@xxxxxxxxxxxxxxm>:

                            El 26/12/17 a
                              las 09:52, Edson Carlos Ericksson Richter
                              escribió:

                              > Recently I had a problem with a base
                              file with size 0 in a standby server.

                              >

                              > This raised one question: does
                              PostgreSQL (9.6.6) check base integrity

                              > at startup?

                              >

                              > At least if there are 0 byte size
                              files in base dir? Or CRC? Something?

                              Yes it has CRC check, but only if you
                              initialize the cluster with

                              --data-checksums, and there's a price to
                              pay in performance.

                            It has CRC check, but it is used in
                              runtime - when data are necessary 

                            So Postgres usually check nothing on
                              start - few system tables and indexes

                            Regards

                            Pavel

                                  --

                                  Martín Marqués                http://www.2ndQuadrant.com/

                                  PostgreSQL Development, 24x7 Support,
                                  Training & Services

                Any tips to make database server don't start if corrupt?

                If I can change the startup script to make some checks
                before effectively starting the database, what would be
                the recommendations?

                One that seems obvious to me are empty data files
                (something like "find -size 0 $PG_DATA/base")...

                But I'm sure that more experienced PostgreSQL DBA would
                have more tests to check before startup.

            I don't think so anybody does it. Reading 1TB database
              needs more then few hours.

            Regards

                Thanks,

                Edson

    I'm rebuilding the standby server for two days already, with 23% of
    completion status...

    If lost the database and backups because of that failure, it would
    be a giant disaster.

    Few hours checking integrity would be acceptable... Specially if I
    can run it on standby only.

very simple check

pgdumpall > /dev/null

but this doesn't check indexes.

Regards

Pavel

    Regards,

    Edson