Restore an RDS PostgreSQL blueprint to a point in time

​

Before you start

• Point-in-time recovery is on. backup_retention_period is above 0 (default 14). The restore window goes back that many days. The latest restorable time trails real time by about 5 to 7 minutes.
• AWS CLI access to the account and region of the environment’s cluster, with permission to describe RDS instances and their automated backups, to restore an instance to a point in time, and to modify, reboot and delete the temporary copy and its automated backups.
• A PostgreSQL client inside the cluster network. The restored copy is private, so use qovery shell on a running container that has a PostgreSQL client, or create a temporary container from the postgres:<major> image with its command set to sleep infinity, then delete it afterwards. Use a client of the database’s major version or newer.
• The database details, from the blueprint’s outputs. Each output is a variable QOVERY_OUTPUT_TERRAFORM_Z<id>_<OUTPUT>, and Qovery also gives it a short alias named after the blueprint service, <PREFIX>_<OUTPUT> (see Aliases Qovery creates for outputs). This guide uses the aliases: <PREFIX>_DB_IDENTIFIER (the RDS identifier, called <primary> below), <PREFIX>_DB_ADDRESS, <PREFIX>_DB_PORT, <PREFIX>_DB_NAME, <PREFIX>_DB_USERNAME and <PREFIX>_DB_PASSWORD. Check the actual names in your environment’s variables: they carry a _1, _2… suffix when the name was already taken, keep the former name of a blueprint service renamed after its first deployment, and a database migrated from a Qovery managed database gets no alias, so use the QOVERY_OUTPUT_TERRAFORM_... variables or its built-in database variables instead. The password is a secret: read it from the environment of a container in the same environment.

​

Choose the approach

​

Step 1: Check the restore time

aws rds describe-db-instance-automated-backups --region <region> \
  --db-instance-identifier <primary> \
  --query "DBInstanceAutomatedBackups[?Status=='active'].RestoreWindow"
aws rds describe-db-instances --region <region> --db-instance-identifier <primary> \
  --query 'DBInstances[0].{latest:LatestRestorableTime,engine:EngineVersion,class:DBInstanceClass,subnet:DBSubnetGroup.DBSubnetGroupName,sgs:VpcSecurityGroups[].VpcSecurityGroupId}'

​

Step 2: Restore a temporary copy

aws rds restore-db-instance-to-point-in-time --region <region> \
  --source-db-instance-identifier <primary> \
  --target-db-instance-identifier <primary>-pitr-<YYYYMMDDHHMM> \
  --restore-time <restore time, e.g. 2026-11-09T14:00:00Z> \
  --db-subnet-group-name <subnet> \
  --vpc-security-group-ids <sgs, space separated> \
  --db-parameter-group-name default.postgres<major> \
  --no-publicly-accessible --no-multi-az --no-deletion-protection
aws rds wait db-instance-available --region <region> \
  --db-instance-identifier <primary>-pitr-<YYYYMMDDHHMM>
aws rds describe-db-instances --region <region> \
  --db-instance-identifier <primary>-pitr-<YYYYMMDDHHMM> \
  --query 'DBInstances[0].[DBInstanceStatus,Endpoint.Address,DBParameterGroups[0].DBParameterGroupName,DBParameterGroups[0].ParameterApplyStatus]' --output text

​

Step 3: Copy the data back

SIDE="host=<copy endpoint> port=<port> dbname=<database> user=<username> sslmode=require"
PRIMARY="host=$<PREFIX>_DB_ADDRESS port=<port> dbname=<database> user=<username> sslmode=require"
export PGPASSWORD="$<PREFIX>_DB_PASSWORD"

​

Deleted rows: put them back

psql "$PRIMARY" -c "select conrelid::regclass as child_table, confdeltype from pg_constraint where contype = 'f' and confdeltype in ('c', 'n', 'd') and confrelid = 'public.<table>'::regclass"

pg_dump "$SIDE" --data-only --table='public.<table>' --column-inserts --on-conflict-do-nothing \
    -f restore-<table>.raw.sql \
  && grep -v '^SELECT pg_catalog.setval' restore-<table>.raw.sql > restore-<table>.sql \
  && grep -c '^INSERT INTO' restore-<table>.sql \
  && { psql "$PRIMARY" --single-transaction -v ON_ERROR_STOP=1 -f restore-<table>.sql \
         > restore-<table>.log 2>&1; echo "exit=$?"; }
grep -c '^INSERT 0 1' restore-<table>.log

• Each command runs only if the previous one succeeded, so a failed dump never reaches your database, and a count of 0 (no rows in the copy) stops it too. The first count is the rows in the copy, the second the rows re-inserted. An exit other than 0 means nothing was applied: the end of restore-<table>.log says why.
• The grep -v drops the sequence reset, which would move your id sequence back in time and make the next inserts collide.
• This brings back every row the table had at the restore time and no longer has, including rows your application deleted on purpose since then. To bring back only some rows, use the staging approach below.
• Restore parent tables before the tables that reference them. Triggers on the table fire for every re-inserted row.
• Rows created between the restore time and the incident, then deleted by it, are not in the copy. With a numeric key, list the ids still missing above the copy’s highest id and check them against your application logs. A second copy at a later time is possible while that time is still in the window.

​

Overwritten rows: stage, compare, repair

psql "$SIDE" -v ON_ERROR_STOP=1 \
  -c 'CREATE SCHEMA pitr_restore' -c 'ALTER TABLE public.<table> SET SCHEMA pitr_restore'
pg_dump "$SIDE" --schema=pitr_restore --no-owner --no-privileges -Fc -f pitr_restore.dump
pg_restore --section=pre-data --section=data --no-owner --no-privileges -d "$PRIMARY" pitr_restore.dump

-- preview
SELECT t.id, t.<column> AS now, r.<column> AS at_restore_time
FROM public.<table> t JOIN pitr_restore.<table> r ON r.id = t.id
WHERE <condition that matches only the damaged rows> LIMIT 20;

BEGIN;
UPDATE public.<table> t SET <column> = r.<column>
FROM pitr_restore.<table> r
WHERE r.id = t.id AND <condition that matches only the damaged rows>;
-- check the UPDATE count, then COMMIT; or ROLLBACK;

​

Step 4: Delete the temporary copy

aws rds delete-db-instance --region <region> \
  --db-instance-identifier <primary>-pitr-<YYYYMMDDHHMM> \
  --skip-final-snapshot --delete-automated-backups

​

Replace the database with a new service

PGPASSWORD="$<PREFIX>_DB_PASSWORD" pg_dump "$SIDE" -Fc --no-owner -f full.dump
PGPASSWORD="$<NEW_PREFIX>_DB_PASSWORD" pg_restore --no-owner -j 4 -d "$NEW" full.dump

​

When to contact us

• The database instance was deleted. Do not deploy the blueprint service: it would create a new, empty instance under the same name. Automated backups survive the instance only when delete_automated_backups was false.
• The database was migrated from a Qovery managed database and you need to replace it. Its applications read the managed database’s variables, which a new blueprint service does not take over. Copying data back works as described above.
• The restore time is outside the window, or backup_retention_period is 0.
• You would rather be guided.

​

After the restore

• Raise backup_retention_period if the window was short. It goes up to 35 days.
• Test this procedure regularly, as part of your disaster recovery plan.