Postgres Operator - Startup SQL and disable backup

PostgreSQL Percona Operator for PostgreSQL
1

I can’t find in the docs where I can asing a startup sql to the databases, which we do some operations to prepare the cluster and install extensions (postgis)
Also because the use case is for seeded databases, backup is not needed and I would prefer it disabled but I can’t see that it’s possible.

2

Hello,

I apologize but I don’t quite understand what it is that you’ve written and am hoping you can reiterate your question.

Have I understood that you want to install the postgis extension into the datacluster(s)?

thank you.

3

Hello,

Apologies for being unclear.
I’ve already found the InitSQL script but that doesnt work and I will probably fix it somehow.

Issues are:

  1. I need to install postgis extension on the cluster
  2. Need to disable backups altogether because I don’t need them for the certain instances.

I hope i made it clearer

Thanks.

4

In the cr.yaml file, simply comment the entire ‘backup::schedule’ block to disable automatic backups.

5

Thank you, that worked. Would it be possible to have
spec.backup.enabled boolean in some future release? It would be pretty handy.

Also is there any way I can debug the InitSQL script? I’ve set a configmap but nothing gets applied, probably doing something wrong

Thanks

1 Like
6

@Mitre_Angjelkoski init script logs can be found in the operators Pod logs. Do smth like:

kubectl logs --tail=2000 percona-postgresql-operator-6f96ffd8d4-82m2r

Search for (successful entry). Just search for init SQL:

time="2023-08-10T13:56:02Z" level=debug msg="applied init SQL" PostgresCluster=default/demo-cluster controller=postgrescluster controllerGroup=postgres-operator.crunchydata.com controllerKind=PostgresCluster key=init.sql name=demo-cluster-init namespace=default reconcileID=fcbe3798-2327-404b-8379-beb12826c102 stderr= stdout="You are now connected to database \"demo-db\" as user \"postgres\".\nCREATE SCHEMA\nCREATE TABLE\nCREATE TABLE\n" version=

UPD:
this is how the error would look like:

time="2023-08-14T09:37:37Z" level=debug msg="applied init SQL" PostgresCluster=default/demo-cluster controller=postgrescluster controllerGroup=postgres-operator.crunchydata.com controllerKind=PostgresCluster key=init.sql name=demo-cluster-init namespace=default reconcileID=1d0cfdcc-0464-459a-be6e-b25eb46ed2c9 stderr="psql:<stdin>:11: ERROR:  syntax error at or near \"KEYS\"\nLINE 2:    ID INT PRIMARY KEYS     NOT NULL,\n                          ^\n" stdout="You are now connected to database \"demo-db\" as user \"postgres\".\nCREATE SCHEMA\nCREATE TABLE\n" version=
7

I wrote a blog post about bootstrapping: https://www.percona.com/blog/bootstrap-postgresql-on-kubernetes/

8

Thanks that was really helpful!
Although still there is an issue with installing postgis. As the extension is not even available to manually install, which means we cant use it…
Any ideas?

9

@Mitre_Angjelkoski we will have PostGIS support in the next release (November).

Also we are working on a feature that would allow users to use any custom extension of their choice. PoC should be also available in November.

As for now, the only workaround is to build your own image with PostGIS.