Data loss/corruption, but database recovery in progress

[r...@sectigo.com]

Following some maintenance this week on the storage array used by the crt.sh primary and replica DBs, the array's management interface reported an extremely low figure for the total amount of disk space used.  We had expected the maintenance to slightly improve rates of compression and data deduplication, but the actual reduction was orders of magnitude greater than our most optimistic predictions.  Therefore, we think it's highly likely that some data loss/corruption has occurred on both the primary and replica DBs.  Although we haven't yet observed any problematic behaviour from the primary DB, we've decided to err on the side of caution and regard it as broken.  We have seen several errors on https://crt.sh/ pages that demonstrate problems with the replica DBs:

Example:

"xlog flush request 2AF57/3E266BD8 is not satisfied --- flushed only to 2AF56/C0C210B8
writing block 27298112 of relation base/16386/1137585692"

Thankfully, we are confident that restoring from the most recent backup, then replaying the WALs generated since then, will get the primary DB back to a good state, with no data loss or corruption.  Unfortunately this won't be quick.  Current estimate: ~1 week.

Once the primary DB is restored, it shouldn't take very long to reseed the replica DBs.  Until then, sadly crt.sh:443 and crt.sh:5432 will have to limp along in their current degraded state.

[Soporte Macroseguridad]

Hey Rob, 

I'm using almost daily your CeRTSearch tool. But this week has been failing. Is that because of the DB recovery you are performing? Do you have more idea of when it's going to be solved? 

Thanks!

[r...@sectigo.com]

Hi.  We're still working on it.  No clearer ETA yet.

[r...@sectigo.com]

The primary DB has been recovered from a recent (December 1st) backup and is currently ingesting the WALs generated since then.  Judging by progress so far, it's likely to take ~2 days to ingest all of the WALs.

I expect the reseeding of the replicas may be delayed due to staff availability over the holiday period, but everything should be back to normal by very early January.

[Jaime Hablutzel]

Hi Rob. This problem affects the CRLs download as well, no? Looking directly in the crt.sh database I can see no downloaded CRLs after 2023-12-13.

This is affecting the reported status for certificates like https://crt.sh/?id=8913351873, which now report "Unknown" for the CRL revocation.

[r...@sectigo.com]

Yes, we've been treating the primary database as read-only since 2023-12-13.  The crl_monitor, ct_monitor, etc, applications have all been stopped since that time.

Once the primary database has been fully restored from the 2023-12-01 backup plus the WALs generated between then and 2023-12-13, we will start the applications again.

[r...@sectigo.com]

The restore of the primary database (from the 2023-12-01 backup plus the WALs generated between then and 2023-12-13) completed on 2023-12-27.  ct_monitor has been running again since then, busily catching up on a log entry ingestion backlog of ~357MM.  With the database replication still broken, ct_monitor is able to hammer the disk array and catch up a lot more quickly.  I estimate that the backlog will drop to zero and we'll be able to reseed the replicas before the end of this working week.

[r...@sectigo.com]

The log entry ingestion backlog dropped to zero yesterday.  However, reseeding the replicas will have to wait until Monday.  (A backup of the primary DB is currently in progress, which should complete over the weekend).

[Lukas Tribus]

Hello,

I'm wondering if it would make sense to partition this one huge database, to keep an outage like this more contained.

For example everything older than 1 year is implicitly expired at this point, and as such interesting for research but not actually operationally relevant (like revocations or urgent troubleshooting re misissued, suspicious certs are). At the same time I'd imagine this historic data is an enormous part of the database, blowing up recovery times *a lot*.

Not saying that this is particularly easy or straightforward to do, especially considering that it's all rendered by PL/pgSQL code on the replica DBs, but maybe partitioning the UI too (even though it's a minor UX annoyance) could be a less far fetched idea (for example: [crt.sh/?sha256=<sha256> ] "404 not found ; crt.sh does not show historic data, would you like to check crt-archive at https://crt-archive.sh/?sha256=<sha256> instead?").

I realize of course that there's a finite amount of manpower available, I just consider this project to be important especially during mass revocations [1] and CAB controversies [2].

Best Regards,

Lukas

[1] https://bugzilla.mozilla.org/show_bug.cgi?id=1872738#c3

[2] https://bugzilla.mozilla.org/show_bug.cgi?id=1872371#c5

[r...@sectigo.com]

Good news!  The replica DBs powering crt.sh:443 and crt.sh:5432 have all now been reseeded, so these services are operating normally again.

[r...@sectigo.com]

Hi Lukas.  Thanks for the suggestion.  I'll bear it in mind.

> I realize of course that there's a finite amount of manpower available

Yep.

On Friday, January 5, 2024 at 10:42:52 PM UTC Lukas Tribus wrote: