Reconciler error - database Init

[Eric-zch]

Overview

I installed the PostgreSQL cluster c1 in dev-c1 namespace on an OpenShift environment. Recently, OpenShift was upgraded from version 4.12 to 4.14. After the upgrade, I restored data for the entire dev-c1 namespace. Once all the pods were started, I noticed the following error messages in the pgo pod logs:

time="2024-09-06T10:01:23Z" level=error msg="Reconciler error" controller=postgrescluster controllerGroup=postgres-operator.crunchydata.com controllerKind=PostgresCluster error="command terminated with exit code 3" file="internal/controller/postgrescluster/postgres.go:753" func="postgrescluster.(*Reconciler).reconcileDatabaseInitSQL" name=c1 namespace=dev-c1 postgresCluster=dev-c1/c1 reconcileID=c814d418-30aa-4032-b3d9-b1d10ceec16b version=5.2.0-0

It seems that the database initialization was being executed repeatedly, but I’m not sure why this was happening. After I commented out the databaseInitSQL section in the postgrescluster configuration, the error was gone.

Environment

• Platform: OpenShift
• Platform Version: 4.14
• PGO Image Tag: ubi8-5.2.0-0
• Postgresql version: 14.6

[andrewlecuyer]

Hi @Eric-zch, it sounds like your Database Initialization SQL was erroring when it was being run for the newly restored PostgresCluster, indicating an issue with the SQL script. And my guess is that your script is not idempotent.

There is a warning in the Database Initialization SQL docs that specifically calls out this requirement:

Warning

In some cases, due to how Kubernetes treats PostgresCluster status, PGO may run your SQL commands more than once. Please ensure that the commands defined in your init SQL are idempotent.