Column value gets reset to default on server restart

[nomanaziz2]

Hello, we are facing a really weird error. Our postgis instance is running inside docker, we have mounted a volume for data persistence. Now, when our system restarts due to power failure, all of our data is intact except the one column in our table which gets reset to its default value. We don't know what is the issue since only this column of a table from the whole db gets reset.

I have attached the latest docker logs upon server restart, kindly guide me to help fix this issue.

PostgreSQL Database directory appears to contain a database; Skipping initialization

2022-11-07 04:32:32.219 UTC [1] LOG:  starting PostgreSQL 14.5 (Debian 14.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-11-07 04:32:32.219 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-11-07 04:32:32.219 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2022-11-07 04:32:32.220 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-11-07 04:32:32.224 UTC [28] LOG:  database system was interrupted; last known up at 2022-11-04 14:32:52 UTC
2022-11-07 04:32:32.292 UTC [28] LOG:  database system was not properly shut down; automatic recovery in progress
2022-11-07 04:32:32.293 UTC [28] LOG:  redo starts at 0/AFF71A0
2022-11-07 04:32:32.293 UTC [28] LOG:  invalid record length at 0/AFF7288: wanted 24, got 0
2022-11-07 04:32:32.293 UTC [28] LOG:  redo done at 0/AFF7250 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2022-11-07 04:32:32.352 UTC [1] LOG:  database system is ready to accept connections
2022-11-07 04:32:58.613 UTC [36] ERROR:  extension "postgis" already exists
2022-11-07 04:32:58.613 UTC [36] STATEMENT:  CREATE EXTENSION postgis;

We are using the latest postgis docker image.

[ImreSamu]

STATEMENT: CREATE EXTENSION postgis;

It's interesting,

• based on your log : "Skipping initialization"
• on the other hand, the repo initialization uses the CREATE EXTENSION IF NOT EXISTS postgis; SQL command!
  • see https://github.com/postgis/docker-postgis/blob/master/14-3.3/initdb-postgis.sh#L17
    • or https://github.com/postgis/docker-postgis/blob/master/initdb-postgis.sh#L17

So Is it possible that you are using an extra initialization code in your codebase?

all of our data is intact except the one column in our table which gets reset to its default value.
We don't know what is the issue since only this column of a table from the whole db gets reset.

I have no better theory than the extra initialization code.