Postgres crash loop caused by a tuple concurrently updated error

Question

Sometimes when starting Postgres POD in OpenShift following error code is being shown

   pg_ctl: another server might be running; trying to start server anyway
   waiting for server to start....LOG:  redirecting log output to logging 
   collector process
   HINT:  Future log output will appear in directory "pg_log".
   ..... done
   server started
   => sourcing /usr/share/container-scripts/postgresql/start/set_passwords.sh ...
   ERROR:  tuple concurrently updated

Answer 1

To fix the issue:

1. Find the name of the postgres pod that is in the crash loop.
2. Start an oc debug session with the pod.
3. Scale the associated Postgres deployment to zero pods.

4. From the cmd line of the debug session;

   • Run run-postgresql. This is the CMD for the docker image. As part of the start-up process the script creates a number of files that won't exist in the pod otherwise, namely /var/lib/pgsql/openshift-custom-postgresql.conf and /var/lib/pgsql/passwd, which will stop you from running any of the pg_ctl commands. When you run the command you should see the same error output listed above.

   • Run pg_ctl stop -D /var/lib/pgsql/data/userdata to cleanly shutdown Postgres. You should see;

     waiting for server to shut down.... done server stopped

   • Run pg_ctl start -D /var/lib/pgsql/data/userdata to start Postgres. You should see the following output and it should wait there indefinitly (no errors);

     server starting sh-4.2$ LOG: redirecting log output to logging collector process HINT: Future log output will appear in directory "pg_log".

   • Press enter a couple of times to get back to the cmd prompt.

   • Run pg_ctl stop -D /var/lib/pgsql/data/userdata, and wait for postgres to stop. This will ensure a clean shutdown.

     waiting for server to shut down.... done server stopped

   • Exit the debug session.

   • Scale the deployment to 1 pod. Postgres should start normally now.

Solution found after long fight on: https://pathfinder-faq-ocio-pathfinder-prod.pathfinder.gov.bc.ca/DB/PostgresqlCrashLoopTupleError.html Credits go to author: Wade Barnes

Answer 2

You might want to create and run the debug pod with the user it was originally planned for, otherwise you will receive permission denied while running commands inside the pod.

This is the sequence of steps I executed:

oc get -o yaml pod <postgresql-pod> | grep runAsUser
runAsUser: 1000650000

oc scale deployment/<postgresql-d> --replicas=0
deployment.apps/<postgresql-d> scaled

oc debug deployment/<postgresql-d> --as-user=1000650000
Starting pod/<postgresql-debug> ...
Pod IP: 10.128.2.75
If you don't see a command prompt, try pressing enter.

sh-4.2$ run-postgresql
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2021-11-17 09:09:46.428 UTC [25] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-11-17 09:09:46.429 UTC [25] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-11-17 09:09:46.445 UTC [25] LOG:  redirecting log output to logging collector process
2021-11-17 09:09:46.445 UTC [25] HINT:  Future log output will appear in directory "log".
. done
server started
/var/run/postgresql:5432 - accepting connections
=> sourcing /usr/share/container-scripts/postgresql/start/set_passwords.sh ...
ERROR:  tuple concurrently updated

sh-4.2$ pg_ctl stop -D /var/lib/pgsql/data/userdata
waiting for server to shut down.... done
server stopped

sh-4.2$ pg_ctl start -D /var/lib/pgsql/data/userdata
waiting for server to start....2021-11-17 09:10:19.359 UTC [45] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-11-17 09:10:19.359 UTC [45] LOG:  listening on IPv6 address "::", port 5432
2021-11-17 09:10:19.369 UTC [45] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-11-17 09:10:19.377 UTC [45] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-11-17 09:10:19.558 UTC [45] LOG:  redirecting log output to logging collector process
2021-11-17 09:10:19.558 UTC [45] HINT:  Future log output will appear in directory "log".
 done
server started
sh-4.2$ 
sh-4.2$ 
sh-4.2$ 

sh-4.2$ pg_ctl stop -D /var/lib/pgsql/data/userdata
waiting for server to shut down.... done
server stopped

sh-4.2$ exit
exit

Removing debug pod ...

oc scale deployment/<postgresql-d> --replicas=1
deployment.apps/<postgresql-d> scaled