CODEWITHSUNDEEP
postgresqldockerdata-corruption
Jackk-Doe
Jackk-Doe

Reputation: 179

Postgres docker error "invalid page in block 0 of relation global/2676"

I have a postgresql docker running on a docker-compose file

version: '3.6'
services:

  healthdb:
    image: postgres:14
    restart: always
    volumes:
    - health_db_data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: SOMETHING
    expose:
      - 5433
    command: -p 5433
    ports:
      - "5433:5433"
    logging:
      options:
        max-size: 50m
    
volumes:
  health_db_data:

Started from around Saturday, although no one did nothing, the healthdb is now unaccessible. Not with psql or pg_dump command line.

From the log it says :

PostgreSQL Database directory appears to contain a database; Skipping initialization

2024-01-02 04:11:37.959 UTC [1] LOG:  starting PostgreSQL 14.9 (Debian 14.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-01-02 04:11:37.959 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5433
2024-01-02 04:11:37.960 UTC [1] LOG:  listening on IPv6 address "::", port 5433
2024-01-02 04:11:37.962 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2024-01-02 04:11:37.967 UTC [25] LOG:  database system was shut down at 2024-01-02 04:11:27 UTC
2024-01-02 04:11:37.977 UTC [1] LOG:  database system is ready to accept connections
2024-01-02 04:12:00.667 UTC [33] FATAL:  invalid page in block 0 of relation global/2676
2024-01-02 04:12:00.774 UTC [34] FATAL:  invalid page in block 0 of relation global/2676
2024-01-02 04:12:00.780 UTC [35] FATAL:  invalid page in block 0 of relation global/2676

Could someone please health me fixing this database issue, I did some research people said that this has something to do with data corruption or something ?

Since it is not possible to connect to the database, I could not export it schemas to create a new one, also cannot dump current existed datas.

Upvotes: 0

Views: 981

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246358

This is data corruption: the file behind the system index pg_authid_rolname_index is broken. You should restore from a backup.

Since you seem to have no backup (learn your lesson for the next time), you can try to rebuild that index. The following may work if that index is the only thing that's broken.

  1. Shut down PostgreSQL.

  2. Start PostgreSQL with the system indexes disabled (replace /path/to/pg with the path to your PostgreSQL binaries, /path/to/pgdata with the path to your data directory and dbname with your database name):

    /path/to/pg/bin/postgres --single -P -D /path/to/pgdata dbname

  3. Rebuild the index:

    REINDEX INDEX pg_catalog.pg_authid_rolname_index

  4. Enter Ctrl+D (or whatever sends an end-of-file) to shutdown PostgreSQL and start the server in normal mode.

Then see if you can run pg_dumpall successfully. Don't continue working with the broken database cluster! Try to figure out how you managed to corrupt the file, so you can avoid it in the future.

Since you are using a docker container, things will be somewhat more complicated for you. You might need to install PostgreSQL and access the volume outside the container.

Upvotes: 0

Related Questions