Re: Database corruption in cascaded replica, "pg_xact/003A" doesn't exist, reading as zeroes"

On 5/12/18 4:10 μ.μ., Dejan Petrovic wrote:
> I believe this is a result of my "broken" procedure for setting up a cascaded replica. I would love to know where the issue is.
>
> This is a report of a database corruption which was detected after promoting a replica server to a master server. We suspect the actual corruption occurred during replication of a two-level cascaded
> replica, however it was not detected until after it was promoted to master server when pg_dump and pg_restore failed. After re-checking postgresql logs on the corrupted node, we found this log line,
> which was the only indicator of a possible issue, but was overlooked: "2018-07-25 13:14:42 UTCLOG: file "pg_xact/003A" doesn't exist, reading as zeroes".
>
> After extensive investigation, we detected three different cases of data corruption:
> 1.)Issue with pg_toast - happens during pg_dump or when row with corrupted data is selected
> "pg_dump: Dumping the contents of table failed: PQgetResult() failed.
> pg_dump: Error message from server: ERROR: missing chunk number 2 for toast value 86123528 in pg_toast_41291242"
>
> 2.) Issue with uncommitted transactions, occurs when running VACUUM or pg_dump/pg_restore:
> "ERROR: uncommitted xmin 60817551 from before xid cutoff 61487222 needs to be frozen
> SQL state: XX001"
>
> 3.) 2 duplicated rows in different tables, violated PK unique constraint, occurs during pg_restore
>
> This is how we fixed the three issues:
> 1.) Using bisection and SELECT, we found the row/column with corrupted data. We nulled the corrupt column and deleted the row (it was not critical, had no FKs)
> 2.) We ran VACUUM on all tables to check where this issue occured and updated all rows between reported xmin and xid cutoff with same data - this generated a new xmin on the broken rows, which fixed
> the issue.
> 3.) We removed the duplicated rows in a transaction, disabled all triggers before and enabled them right after
> 4.) Our final step is to do a full dump and restore on master DB so that the DB is rebuilt
>
> This is the procedure we used to replicate the cascaded replica (master -> replica -> replica)
>
> Notes:
> Machines are running on Centos 7, Postgresql 10.2
> DB-1 = master
> DB-2 = replica of DB-1
> DB-3 = replica of DB-2
>
> --------------> DB-3 (new cascaded replica)
> systemctl disable postgresql-10
> systemctl stop postgresql-10
> --------------> DB-1 (master)
> psql -U postgres
> select pg_start_backup('clone',true);
> <------------- DB-3 (cascaded replica)
> rsync -azv -e --delete DB-2:/isl/pgsql10/data /isl/pgsql10/ --exclude pg_wal --exclude postgresql.pid
> assert rsync finished
> vi recovery.conf
>     standby_mode = 'on'
>     primary_conninfo = 'host=DB-2'
>     recovery_target_timeline = 'latest'

In addition to what Stephen wrote you, from : https://www.postgresql.org/docs/10/continuous-archiving.html
the low level backup method is relying on :
"1. Ensure that WAL archiving is enabled and working."
Have you setup this correctly?

Also there should at least be a restore_command inside your recovery.conf . Otherwise how do you know that DB-2 has the WALs you need? Whereas with your WALs archived it is guaranteed that the backup
is consistent.

But anyway, its better to either use pg_basebackup or invest in some higher level tool like Stephen said.

> -------------> DB-1
> select pg_stop_backup();
> <------------- DB-3
> rsync -azv -e --delete DB-2:/isl/pgsql10/data/pg_wal /isl/pgsql10/data/
> systemctl start postgresql-10
> systemctl enable postgresql-10
> tail log file
>
> Any comments regarding the cascaded replication procedure or database corruption detection or resolution are welcome.
>
> Best regards,
>
> Dejan Petrovic
> ISL Online
>
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt