Re: Trying to handle db corruption 9.6

On Tue, May 21, 2019 at 04:03:52PM +0000, Greg Clough wrote:
>> My restore command copy the wals from archive dir in the primary to an
>> archive dir in the secondary(different from the pg_xlog in the
>> secondary)
>
>I think that you're restore command puts them back into the archive, and
>then uncompresses them into pg_xlog, which is what %p represents.
>
>
>> Should I run it manually and see if the archives are copied to the
>> archive dir in the secondary or should I just copy all of them to the
>> xlog dir in the secondary ?
>
>That would be my first test, but as Thomas mentioned, you don't have any
>hint of WAL archives being restored in the postgresql.log... so it's not
>even trying. It's not likely that archive_command is your problem at the
>moment.
>
>
>> I tried to start the secondary as a primary (I have a backup..) but I
>> still got an error (invalid checkpoint record from primary./
>> secondary). Does it means that my backup is corrupted ?
>
>I think so, but Thomas could probably confirm if all hope is lost. Also,
>I'm not sure if there is a terminology difference but a "standby" is
>never considered a "backup". I realise it's late in the day, but even if
>you have a correctly configured Standby you should also take backups with
>pg_basebackup, Barman, pgBackRest, etc.
>

Well, I have no idea. We still got no information about how the standby
was created, if it was ever running fine, and so on. Considering it does
not seem to be getting data from the archive, it might be the case it was
created in some strange way and never really worked. And if there really
are no log messages about the restore_command, it probably fails before
the standby even tries to execute it.

So I don't know.

>Restoring backups is where I would be heading now, as things seem
>terribly broken.
>

Right. But my impression is there are no backups ...

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services