Pg_resetwal can be used to start the PostgreSQL Docker container when WAL is broken and cannot be started.

The battery of the local machine that was running the PostgreSQL Docker container ran out and fell. When I secured the power supply and restarted it, I got an error and couldn't start up.

Note that the container data is mounted in the directory of the host machine and used persistently. Definition part from docker-compose.yml.

services:
  db:
    image: postgres:12-alpine
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=foobar
    volumes:
      - ./dataDir:/var/lib/postgresql/data

It is a log at startup.

db_1                |
db_1                | PostgreSQL Database directory appears to contain a database; Skipping initialization
db_1                |
db_1                | 2020-02-07 01:36:06.106 UTC [1] LOG:  starting PostgreSQL 12.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit
db_1                | 2020-02-07 01:36:06.107 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db_1                | 2020-02-07 01:36:06.107 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1                | 2020-02-07 01:36:06.270 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1                | 2020-02-07 01:36:06.434 UTC [21] LOG:  database system was interrupted; last known up at 2020-02-07 01:19:57 UTC
db_1                | 2020-02-07 01:36:10.655 UTC [21] LOG:  invalid record length at 1/94338ED0: wanted 24, got 0
db_1                | 2020-02-07 01:36:10.655 UTC [21] LOG:  invalid primary checkpoint record
db_1                | 2020-02-07 01:36:10.655 UTC [21] PANIC:  could not locate a valid checkpoint record
db_1                | 2020-02-07 01:36:10.656 UTC [1] LOG:  startup process (PID 21) was terminated by signal 6: Aborted
db_1                | 2020-02-07 01:36:10.656 UTC [1] LOG:  aborting startup due to startup process failure
db_1                | 2020-02-07 01:36:10.661 UTC [1] LOG:  database system is shut down

db_1                | 2020-02-07 01:36:10.655 UTC [21] PANIC:  could not locate a valid checkpoint record

I spit out this log and the startup stopped. When I looked it up, I heard that the WAL (Write ahead log) was damaged.

To recover from this state, use the command pg_resetxlog.

pg_resetxlog clears the write-ahead log (WAL) and optionally initializes some of the control information stored in the pg_control file. This feature may be needed if these files become corrupted. Use this feature only as a last resort if you cannot start the server due to such corruption.

From Postgres10, pg_resetxlog was renamed to pg_resetwal. Change the command name depending on the version.

docker run --rm -it -v $(pwd)/dataDir:/var/lib/postgres/data/ postgres:12 /bin/bash

Since the data was mounted on the host machine side, mount it.

gosu postgres pg_resetwal -f /var/lib/postgres/data

When the container starts, execute the log initialization command. Execution started successfully.

This is just a forced initialization of the log, so the data integrity may be corrupted. It seems better to make a backup and check the consistency such as reinserting the data.

This time it was the development environment at hand, and at worst it was okay if everything disappeared, As long as it can be recovered, we have decided to take measures up to this point.