This post was originally published in 2023 and was updated in 2025.
PostgreSQL is a natural fit for modern cloud-native environments, but running it on Kubernetes can be more complicated than it looks. From provisioning to backups, there’s a lot to get right before you can start building on top of it. That’s where the Percona Operator for PostgreSQL comes in. It removes much of the manual work by automating deployment and cluster management, making it easier to get a production-ready database up and running.
A common need, especially in CI/CD pipelines, is to bootstrap clusters with data so applications can run right away. In this post, we’ll look at how to use the Percona Operator’s bootstrap features to:
- Start a cluster with an init SQL script
- Bootstrap a cluster from an existing cluster or backup
Getting started
You’ll need the Percona Operator for PostgreSQL deployed. Follow our installation instructions using whichever method you prefer.
You can find all examples from this post in this GitHub repository. A single command to deploy the operator would be:
|
1 |
kubectl apply -f https://raw.githubusercontent.com/spron-in/blog-data/master/bootstrap-postgresql-k8s/00-bundle.yaml --server-side |
Init SQL script
Init SQL lets you create a database cluster with some initial data. Everything is created with the postgres admin user. The process works like this:
- Create the ConfigMap resource with the SQL script.
- Reference it in the PerconaPGCluster Custom Resource.
- The Operator applies the SQL during cluster creation.
This is often paired with user creation.
Example ConfigMap from 01-demo-init.yaml:
The init.sql script:
- Connects to the demo-db database
- Creates schema media for user myuser
- Creates two tables—BLOG and AUTHORS—in the schema
User and database creation in 02-deploy-cr.yaml:
|
1 2 3 4 |
users: - name: myuser databases: - demo-db |
Reference the ConfigMap in the custom resource:
|
1 2 3 |
databaseInitSQL: key: init.sql name: demo-cluster-init |
Applying the manifest would do the trick:
|
1 |
kubectl apply -f https://raw.githubusercontent.com/spron-in/blog-data/master/bootstrap-postgresql-k8s/02-deploy-cr.yaml |
Troubleshooting
To verify if init SQL was executed or check errors, look at the Operator’s logs. Search for init SQL. For example, the following tells me that I had a syntax error in my SQL script for demo-cluster:
|
1 2 |
$ kubectl logs --tail=2000 percona-postgresql-operator-6f96ffd8d4-ddzth | grep 'init SQL' time="2023-08-14T09:37:37Z" level=debug msg="applied init SQL" PostgresCluster=default/demo-cluster controller=postgrescluster 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 SCHEMAnCREATE TABLEn" version= |
Bootstrap from cluster or backup
ConfigMaps can’t store more than 1 MB of data, so init SQL is best for small bootstraps. For larger datasets, you can bootstrap from:
- An existing cluster in Kubernetes
- A backup
From the cluster
You’ll need a running cluster and a pgBackRest repo configured.
03-deploy-cr2.yaml provisions demo-cluster-2. The spec.databaseInitSQL section is removed, but spec.users remains. Add the dataSource section:
|
1 2 3 4 |
dataSource: postgresCluster: clusterName: demo-cluster repoName: repo1 |
The new cluster will be created once the manifest is applied:
|
1 2 3 4 5 |
$ kubectl apply -f https://raw.githubusercontent.com/spron-in/blog-data/master/bootstrap-postgresql-k8s/03-deploy-cr2.yaml $ kubectl get pg NAME ENDPOINT STATUS POSTGRES PGBOUNCER AGE demo-cluster demo-cluster-pgbouncer.default.svc ready 1 1 14m demo-cluster-2 demo-cluster-2-pgbouncer.default.svc ready 1 1 13m |
demo-cluster-2 will have the same data as demo-cluster. Keep in mind that even if data is the same, the user passwords would be different by default. You can change this; please see users documentation.
From the backup
If the original cluster isn’t running—or is in a different Kubernetes environment—you can bootstrap from backups stored in object storage. Please use our documentation to configure backups.
Example 04-deploy-cr.yaml config for Google Cloud Storage (GCS):
|
1 2 3 4 5 6 7 8 9 10 11 |
pgbackrest: global: - secret: name: demo-cluster-gcs ... repos: - name: repo1 schedules: full: "0 0 * * 6" gcs: bucket: "my-demo-bucket" |
Once you have backups stored in the object storage, you can delete the cluster and reference it in the manifest anytime for bootstrapping. For example, in 05-deploy-cr3.yaml, dataSource section looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
dataSource: pgbackrest: stanza: db configuration: - secret: name: demo-cluster-gcs global: repo1-path: /pgbackrest/demo/repo1 repo: name: repo1 gcs: bucket: "my-demo-bucket" |
The fields have the same structure and reference the same Secret resource where GCS configuration is stored.
Troubleshooting
When bootstrapping from pgBackRest, the Operator creates a restore pod. If it fails:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$ kubectl get pods NAME READY STATUS RESTARTS AGE demo-cluster-3-pgbackrest-restore-74dg5 0/1 Error 0 27s $ kubectl logs demo-cluster-3-pgbackrest-restore-74dg5 Defaulted container "pgbackrest-restore" out of: pgbackrest-restore, nss-wrapper-init (init) + pgbackrest restore --stanza=db --pg1-path=/pgdata/pg15 --repo=1 --delta --link-map=pg_wal=/pgdata/pg15_wal WARN: unable to open log file '/pgdata/pgbackrest/log/db-restore.log': No such file or directory NOTE: process will continue without log file. WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/pgdata/pg15' to confirm that this is a valid $PGDATA directory. --delta and --force have been disabled and if any files exist in the destination directories the restore will be aborted. WARN: repo1: [FileMissingError] unable to load info file '/pgbackrest/demo/repo1/backup/db/backup.info' or '/pgbackrest/demo/repo1/backup/db/backup.info.copy': FileMissingError: unable to open missing file '/pgbackrest/demo/repo1/backup/db/backup.info' for read FileMissingError: unable to open missing file '/pgbackrest/demo/repo1/backup/db/backup.info.copy' for read HINT: backup.info cannot be opened and is required to perform a backup. HINT: has a stanza-create been performed? ERROR: [075]: no backup set found to restore |
Check for missing files, misconfigured stanzas, or object storage issues.
Conclusion
Bootstrapping PostgreSQL clusters with the Percona Operator saves significant time, whether you’re initializing with SQL, cloning from an existing cluster, or restoring from backups. It also fits neatly into CI/CD pipelines, letting teams automate provisioning, updates, and rollbacks with fewer risks and less downtime.
If you want to go further, we’ve put together a resource that shows how Percona makes PostgreSQL on Kubernetes easier. It covers high availability, observability, automation, and more, so you can run PostgreSQL at scale with confidence.
Discover how Percona simplifies PostgreSQL on Kubernetes
About the Author
Sergey ProninSergey is a product leader at Percona focusing on delivering robust open-source database and cloud-native solutions. Prior to Percona Sergey led product management and engineering teams in other organizations with a primary focus on products in infrastructure and platforms space.
