Restore a PostgreSQL Instance
This is an example of how to restore a PostgreSQLInstance from a Snapshot.
1. Create test scenario
Create a database instance. Then create two roles: test-owner and test-user. The first will be the owner of the database database-two-test but no login. The second role will have login and a grant to use the database.
apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLInstance
metadata:
name: postgresql-instance-test
spec:
deletionProtection: false
engineVersion: '17.10'
instanceType: db.t3.micro
---
apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLUser
metadata:
name: test-owner
spec:
instanceRef:
name: postgresql-instance-test
login: false
name: test_owner
---
apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLUser
metadata:
name: test-user
spec:
grant:
roles:
- test_owner
instanceRef:
name: postgresql-instance-test
login: true
---
apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLDatabase
metadata:
name: database-two-test
spec:
instanceRef:
name: postgresql-instance-test
owner: test_owner
Start a pod to verify that connection to the database using the test-user credentials works.
apiVersion: v1
kind: Pod
metadata:
name: postgres
spec:
terminationGracePeriodSeconds: 1
containers:
- name: postgres
image: postgres:alpine
command: ['sleep', 'infinity']
env:
- name: PGHOST
valueFrom:
secretKeyRef:
name: postgresql-instance-test-test-user
key: endpoint
- name: PGPORT
valueFrom:
secretKeyRef:
name: postgresql-instance-test-test-user
key: port
- name: PGUSER
valueFrom:
secretKeyRef:
name: postgresql-instance-test-test-user
key: username
- name: PGPASSWORD
valueFrom:
secretKeyRef:
name: postgresql-instance-test-test-user
key: password
- name: PGSSLMODE
value: 'require'
Execute into the pod and connect to the PostgreSQL database.
kubectl exec -it postgres -- bash
psql database-two-test
Insert custom example data.
CREATE TABLE IF NOT EXISTS example (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO example (name, email) VALUES
('Alice Johnson', 'alice@example.com'),
('Bob Smith', 'bob@example.com'),
('Carol White', 'carol@example.com');
SELECT * FROM example;
Verify that custom example data is present.
id | name | email | created_at
----+---------------+-------------------+----------------------------
1 | Alice Johnson | alice@example.com | 2026-03-10 14:02:12.007906
2 | Bob Smith | bob@example.com | 2026-03-10 14:02:12.007906
3 | Carol White | carol@example.com | 2026-03-10 14:02:12.007906
(3 rows)
Create a snapshot named test-restoration of the database instance and verify it exists.
aws rds describe-db-snapshots --no-paginate --query "DBSnapshots[].[DBSnapshotIdentifier]" --output table --region eu-north-1
# Shows test-restoration
Remove the test pod.
kubectl delete pod postgres
2. Option A) Only restore instance from snapshot, Objects are not deleted/restored
In this scenario the Kubernetes Objects are kept, only the database instance is restored to a snapshot.
Make sure the deletionProtection is set to false before attempting to restore the Instance.
kubectl patch pginstances.database.entigo.com postgresql-instance-test --type=merge -p '{"spec":{"deletionProtection":false}}'
Patch the pginstances object and set the spec.snapshotIdentifier to the value of our snapshot ID.
Do not remove the spec.snapshotIdentifier after the database instance is restored. It has to stay in place. Removing or changing this field will remove the restored database instance and create a new one.
kubectl patch pginstances.database.entigo.com postgresql-instance-test --type=merge -p '{"spec":{"snapshotIdentifier":"test-restoration"}}'
Wait for the PostgreSQL instance to be re-created from the snapshot. The object is automatically re-created and it will create a new PostgreSQL instance that is restored from the snapshot.
kubectl get instances.rds.aws.m.upbound.io -w -l crossplane.io/composite=postgresql-instance-test
#NAME SYNCED READY EXTERNAL-NAME AGE
#postgresql-instance-test-instance-338f591f True False 9s
Once the instance is Ready, re-create the postgres pod and verify the data exists:
kubectl exec -it postgres -- bash
postgres:/# psql database-two-test
Verify the data is there.
SELECT * FROM example;
Result
id | name | email | created_at
----+---------------+-------------------+----------------------------
1 | Alice Johnson | alice@example.com | 2026-03-10 14:02:12.007906
2 | Bob Smith | bob@example.com | 2026-03-10 14:02:12.007906
3 | Carol White | carol@example.com | 2026-03-10 14:02:12.007906
(3 rows)
3. Option B) Restore instance from snapshot and objects from scratch
In this scenario the Kubernetes Objects are also deleted and the database instance is deleted in the cloud provider. Everything is restored from scratch. Note: The restoration resets the role passwords automatically.
Delete the database resources.
kubectl delete postgresqldatabases.database.entigo.com database-two-test
kubectl delete postgresqlusers.database.entigo.com test-user
kubectl delete postgresqlusers.database.entigo.com owner-user
kubectl delete pginstances.database.entigo.com postgresql-instance-test
Add the snapshotIdentifier field to the PostgreSQLInstance object with the snapshot identifier value of the snapshot created previously and create the objects.
Do not remove the spec.snapshotIdentifier after the database instance is restored. It has to stay in place. Removing or changing this field will remove the restored database instance and create a new one.
apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLInstance
metadata:
name: postgresql-instance-test
spec:
deletionProtection: false
engineVersion: '17.10'
instanceType: db.t3.micro
snapshotIdentifier: test-restoration
---
apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLUser
metadata:
name: test-owner
spec:
instanceRef:
name: postgresql-instance-test
login: false
name: test_owner
---
apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLUser
metadata:
name: test-user
spec:
grant:
roles:
- test_owner
instanceRef:
name: postgresql-instance-test
login: true
---
apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLDatabase
metadata:
name: database-two-test
spec:
instanceRef:
name: postgresql-instance-test
owner: test_owner
Wait for the objects to become ready. Since the User/Role already exists in the restored instance, the password rotation by SQL provider will be triggered automatically.
Re-create the postgres pod and verify the data exists:
kubectl exec -it postgres -- bash
postgres:/# psql database-two-test
Verify the data is present.
```sql
SELECT * FROM example;
Result
id | name | email | created_at
----+---------------+-------------------+----------------------------
1 | Alice Johnson | alice@example.com | 2026-03-10 14:02:12.007906
2 | Bob Smith | bob@example.com | 2026-03-10 14:02:12.007906
3 | Carol White | carol@example.com | 2026-03-10 14:02:12.007906
(3 rows)
EnvironmentConfig
Whether a snapshot exists to restore from is decided by cluster-wide settings, not by the PostgreSQLInstance object: backupRetentionPeriod controls how far back you can restore, and postgresBackupBeforeDeletion controls whether a deleted instance leaves a final snapshot behind.