Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

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

2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter <rich...@simkorp.com.br <mailto:rich...@simkorp.com.br>>:

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

    2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter
    <rich...@simkorp.com.br <mailto:rich...@simkorp.com.br>>:

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

        2017-12-26 14:44 GMT+01:00 Martin Marques
        <martin.marq...@2ndquadrant.com
        <mailto:martin.marq...@2ndquadrant.com>>:

            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

I'll check it and make some tests.
Thanks!

Edson