Skip to main content
Not yet released. This guide describes implemented changes awaiting rollout. Availability requires the corresponding backend and dashboard release.

​
Goal

Recover a Starter PostgreSQL database to the moment before a mistake.

​
Prerequisites

  • A running Starter PostgreSQL database on StackShift’s servers
  • Connection pooling and read replicas turned off on that database
  • Write access to the database

​
Workflow

1
Turn it on: PUT /api/v1/databases/{id}/pitr with {"enabled": true, "retention_days": 14, "apply": "window"}. apply is now (default) or window (your maintenance window).
2
Expect one short restart of PostgreSQL, a few seconds, when it turns on.
3
If the answer is 402 payment_required, open checkout_url and pay. Nothing is built until the payment clears.
4
Check it with GET /api/v1/databases/{id}/pitr until state is active. restorable shows the earliest and latest moment you can restore to.
5
To restore, run stackshift database restore DATABASE --file restore.json (POST /api/v1/databases/{id}/restore) with a new name, a target_time in UTC (or a backup_id) and billing.
6
Wait until the new database is running, check the data, then copy back what you need or point your app at it.

​
Turn it on or off

  • retention_days is 7 to 35 (default 7). It is how far back you can restore. Changing only the retention doesn’t restart the database.
  • Turn it off with {"enabled": false}.
  • States: off, waiting_for_window, turning_on, active, turning_off and restoring. restart_at shows when a restart waiting for your window will happen.
  • The first time you turn on an add-on you can choose how to pay with billing: monthly (default), yearly or hourly. See Dedicated billing for how those work.

​
Restore

  • A restore always creates a new Starter database. It is created like any other Starter database and is usable when its status is running.
  • name is 3 to 50 characters. Send target_time or backup_id, not both. A target_time outside the restorable window is refused with 400.
  • A restore is paid for like a new database, so it can answer 402 payment_required with a checkout_url.

​
Cost

Point-in-time restore is included when your account is on a paid plan. On the free plan it is a priced add-on. See https://stackshift.cloud/pricing. If a payment for it fails, point-in-time restore is turned off, but your database keeps running.

​
Limits

  • Only PostgreSQL Starter databases on StackShift’s servers. Databases on your own servers aren’t supported.
  • Turn off connection pooling and remove read replicas first.
  • While it is on, scaling the database isn’t available.
  • A database that hasn’t been moved to our newer database image yet can’t turn it on. We move databases in announced windows.
  • Dedicated databases have their own restore. See Restore to any second.

​
Availability

This is rolling out for Starter PostgreSQL databases. If your database page doesn’t offer it, it isn’t switched on for your account yet, and the API answers 403.

​
Expected result

A new Starter database of the same size and with the same database name holds your data as it was at the time you chose. The original is unchanged.

​
Common failures

  • 409 “Turn off connection pooling before turning this on.” or “Remove this database’s read replicas before turning this on.”: do that first.
  • 409 “Start the database before changing its backups.”: start the database, then try again.
  • 409 “This database has not moved to our new database image yet…”: wait for the announced window.
  • 400 for the restore time: choose a time inside restorable, in UTC.
  • 400 for retention_days: use 7 to 35.

Starter standby

Add a standby to a Starter PostgreSQL database. Redis and Valkey Starter databases can add an asynchronous standby.

Restore to any second

Dedicated databases archive every change, so you can restore to an exact second. A restore always creates a new database.

Back up and restore a database

Use durable recovery storage correctly and understand why backups are independent from portable SQL transfers.

Move a Starter database to Dedicated

Copy a Starter PostgreSQL database to a new Dedicated database, switch your apps over and keep the original read-only for 7 days.