Skip to main content

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.2'
  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.

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 has to reset the role passwords and for that the roles with login have to be dropped.

Delete the database resources.

kubectl delete postgresqldatabases.database.entigo.com database-two-test
kubectl delete postgresqlusers.database.entigo.com test-user
kubectl delete pginstances.database.entigo.com postgresql-instance-test

Create the same objects, except the PostgreSQLUser test-user and add the snapshotIdentifier field to the PostgreSQLInstance object with the snapshot identifier value of the snapshot we created previously.

apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLInstance
metadata:
  name: postgresql-instance-test
spec:
  deletionProtection: false
  engineVersion: '17.2'
  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: 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 SQL provider cannot provide a connection Secret with correct credentials. (Not yet, see https://github.com/crossplane-contrib/provider-sql/issues/227) Drop the test-user role so the PostgreSQLUser object can create new credentials.

Create a pod with dbadmin privileges.

apiVersion: v1
kind: Pod
metadata:
  name: postgres-admin
spec:
  terminationGracePeriodSeconds: 1
  containers:
    - name: postgres
      image: postgres:alpine
      command: ['sleep', 'infinity']
      env:
        - name: PGHOST
          valueFrom:
            secretKeyRef:
              name: postgresql-instance-test-dbadmin
              key: endpoint
        - name: PGPORT
          valueFrom:
            secretKeyRef:
              name: postgresql-instance-test-dbadmin
              key: port
        - name: PGUSER
          valueFrom:
            secretKeyRef:
              name: postgresql-instance-test-dbadmin
              key: username
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              name: postgresql-instance-test-dbadmin
              key: password
        - name: PGSSLMODE
          value: 'require'

Drop the existing role.

kubectl exec -it postgres-admin -- bash
psql postgres

Grant dbadmin the "test-user" role and reassign any objects to the test_owner. Then drop the test-user role.

GRANT "test-user" TO dbadmin;
\c database-two-test
REASSIGN OWNED BY "test-user" TO "test_owner";
DROP OWNED BY "test-user";
\c postgres
DROP ROLE "test-user";

Once the test-user role is dropped, create the PostgreSQLUser test-user object.

apiVersion: database.entigo.com/v1alpha1
kind: PostgreSQLUser
metadata:
  name: test-user
spec:
  grant:
    roles:
      - test_owner
  instanceRef:
    name: postgresql-instance-test
  login: true

Now new credentials are stored in the secret and you can connect to the database. Create the postgres pod again as described in step 1. Connect to PostgreSQL database.

kubectl exec -it postgres -- bash
psql database-two-test

Verify the data is present.

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)