Incident Library

IL-0431 Hatena · 2017-08-07databaseaws

During Mackerel's move to AWS, PostgreSQL failed over to a replica that had stopped replicating, losing an hour of data

Hatena moved Mackerel from an on-premises data center to AWS in a maintenance window that began at 14:30 JST on 7 August 2017. The PostgreSQL 9.3 servers used standard streaming replication in a chain: db001 replicated from db000, and db002 replicated from db001, making it a grandchild of db000. The database servers ran on c4.8xlarge EC2 instances with gp2 EBS volumes (EBS-optimised), Linux 3.16 and Debian 8. The migration cut connections to db000, promoted db001 to primary and pointed the application at db001. When db001 was promoted, replication from db001 to db002 stopped.

PostgreSQL increments the timeline ID on promotion, and with the 9.3 defaults a replica refuses WAL carrying the new timeline ID and stops replicating, unless recovery.conf sets recovery_target_timeline = 'latest'. In normal Mackerel operation that setting was not needed, so it was set only when needed through an option of the in-house replica creation script, and that option was forgotten for db002. A migration test in staging, with the same cluster layout and the same steps as production, had the setting right on the grandchild replica, so replication did not stop there and the problem was not found.

At 15:30 db001 lost network connectivity for reasons never established; Hatena considered saturation of the Linux network stack or disk I/O stack from overload, or a problem on the EC2 hypervisor side. The failover went to db002, which then took updates on top of data that lacked everything since db001's promotion. Manual repair from the tables left on db001 was abandoned because much data had already changed and keeping the tables consistent was judged too hard. Point-in-time recovery from the pre-maintenance base backup, the WAL on db001 and the archived WAL was also abandoned, because recovery can follow only one of timeline 16 (db001) or timeline 17 (db002), not both. Hatena also judged that even a consistent database would not look consistent to customers who had already re-created the lost data. Right after the failover a new replica db003 was built from db002, db001 was kept for investigation, and the maintenance was completed after watching db002 for some time.

Severity
–
Scope
All data updated on the PostgreSQL servers between 14:30 and 15:30 JST on 7 August 2017 was lost; time series data was lost only for metrics newly created in that window and for metrics posted by the AWS and Azure integrations
Users affected
–
Region
–
Time to detect
–
Time to resolve
–

Symptoms

On 7 August 2017, while checking the application after the maintenance that moved Mackerel from an on-premises data center to AWS, the team found that data updated during a specific time window was missing. Everything written to the PostgreSQL database between 14:30 and 15:30 JST was gone.

At 15:30, one hour after connections had been switched to the new primary db001, db001 lost network connectivity and the database failed over, without anyone intending it, to the standby db002. db002 had silently stopped replicating when db001 was promoted, so it held the data as of the start of the maintenance, and update queries then ran on db002.

The cause of the network loss was not found. Mackerel's own CPU graph showed that contact with the inside of the instance was lost around 15:30 with no change in load just before, and the same held for the other metrics. CloudWatch showed CPUUtilization rising from about 15:20 and staying at 100%, and StatusCheckFailed at 1 after the network loss. After a restart, syslog, dmesg and the PostgreSQL server process logs showed nothing related, and an AWS support ticket covering the EC2 instance, its EBS volume and ENI and the network infrastructure found no record of a problem.

Symptom Data lossReplication lagNetwork unreachable

Root cause

Root cause Operator error

Component DatabaseDatabase replicationDatabase failover manager
Contributing factor Overlapping failuresUnreviewed defaultsUnrepresentative stagingAutomation amplification
Trigger
One hour after the application was switched to the newly promoted primary db001, db001 lost network connectivity and the database failed over to the standby db002.

Detection

The team found the missing data while checking how the application behaved after the migration work.

Remediation

Action items

Lessons

A setting needed only for a rare layout, such as a grandchild replica during a migration, was applied by hand through a script option and was forgotten. Because recovery_target_timeline = 'latest' is harmless on ordinary standbys too, Hatena now sets it by default whenever a replica is created. The staging rehearsal used the same layout and steps but had the setting right, so it could not catch the mistake. Hatena notes that a similar layout may be used again for a future PostgreSQL upgrade or a move to another region.

Sources

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database, Database replication, Database failover manager among them, read-only, from Claude Code or Codex. Its checks cite public reports like this one.

Open kubegap

For AI tools and scripts

The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.