Managed PostgreSQL: backup, restore and fork, timed

A managed database promises backups, restores to the minute and instant copies. This article puts those three promises to the test on a PostgreSQL 18 database of the smallest size (1 GB, 0.020 CAD per hour), loaded with 5 million rows, then checks every copy with row counts and checksums. The test ran on November 3, 2026; times are Montréal time and only passwords are masked.

Creating the database, connecting over the private network and moving to a larger size are already covered in Managed databases in Montréal, a real walkthrough. This one times what protects your data.

1. The test setup

A 1 GB PostgreSQL database ordered through the API at 12:06:09 and active at 12:12:43. Its first full backup was done at 12:12:49 and its restore test passed at 12:13:14: the database is protected before the first write.

The client ran on a machine in Montréal outside the database's private network, so public access was opened and restricted to that machine's two addresses (IPv4 and IPv6). It takes about thirty seconds to apply:

curl -s -X PUT https://api.ffxf.net/v1/databases/34/public-access \
     -H "Authorization: Bearer $FFXF_TOKEN" -H "Content-Type: application/json" \
     -d '{"enabled": true, "sources": ["203.0.113.10", "2001:db8::10"]}'

For an application hosted at FFxF, use the private network instead: nothing is exposed.

2. TLS required and verified

The connection string from GET /v1/databases/34/credentials ends with sslmode=verify-full&sslrootcert=system, so the client checks both the certificate and the server name:

$ psql "$DB" -c "SELECT ssl, version AS tls, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();"
 ssl |   tls   |         cipher
-----+---------+------------------------
 t   | TLSv1.3 | TLS_AES_256_GCM_SHA384

Without encryption, the server turns the connection away:

$ psql "postgresql://ffxfadmin:••••@dsghpaczji.db.ffxf.cloud:28772/defaultdb?sslmode=disable"
psql: error: … FATAL:  no pg_hba.conf entry for host "2602:f3a4:c0:ffee::5", user "ffxfadmin", database "defaultdb", no encryption

One trap on the way: the official postgres:18 Docker image, used here as the client, ships without the system's certificate authorities. With sslrootcert=system the connection fails with SSL error: certificate verify failed, even though the Let's Encrypt certificate is fine (openssl s_client reports Verify return code: 0 (ok)). Mount the host's store into the container, or install ca-certificates:

docker run --rm -it -e DB="$DB" -v /etc/ssl/certs:/etc/ssl/certs:ro postgres:18 psql "$DB"

3. A dataset of known size

pgbench, which ships with PostgreSQL, creates four banking tables. At scale 50 that is 5,000,000 accounts, 500 tellers and 50 branches.

$ pgbench -i -s 50 "$DB"
5000000 of 5000000 tuples (100%) of pgbench_accounts done (elapsed 7.03 s, remaining 0.00 s)
done in 12.78 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 7.17 s, vacuum 0.90 s, primary keys 4.70 s).

The database then weighs 755 MB. One minute of transactions from 4 clients adds some history: 40,223 transactions, 671.8 per second, 5.95 ms average latency including the network. The last ones were written at 12:16:02.

To compare copies, one query returns, per table, the row count, the sum of balances and a fingerprint: the sum of hashtext() over each whole row. Change a single value and the fingerprint changes.

SELECT 'accounts' t, count(*) n, sum(abalance) s, sum(hashtext(a::text)::bigint) h FROM pgbench_accounts a
UNION ALL SELECT 'branches', count(*), sum(bbalance), sum(hashtext(b::text)::bigint) FROM pgbench_branches b
UNION ALL SELECT 'tellers',  count(*), sum(tbalance), sum(hashtext(x::text)::bigint) FROM pgbench_tellers x
UNION ALL SELECT 'history',  count(*), sum(delta),    sum(hashtext(y::text)::bigint) FROM pgbench_history y;

The reference state, at 12:16:09:

    t     |    n    |    s    |       h
----------+---------+---------+---------------
 accounts | 5000000 | -383125 | 1430067750301
 branches |      50 | -383125 |  -12480793191
 tellers  |     500 | -383125 |  -86711664516
 history  |   40223 | -383125 |   80768475562

4. An on-demand backup

Backups run on their own. Before a risky change, you can start one right away:

curl -s -X POST https://api.ffxf.net/v1/databases/34/backups \
     -H "Authorization: Bearer $FFXF_TOKEN" -H "Idempotency-Key: $(uuidgen)"

Requested at 12:16:15, it started at 12:16:22 and finished at 12:16:52: 30 seconds. It is a differential against the 12:12:40 full backup, and it takes 40.5 MB for 755 MB of data (pgbench tables compress well). Later on, the first full backup of a database with the same content took 34 seconds for 43.2 MB.

{ "label": "20261103-171240F_20261103-171622D", "kind": "diff",
  "started_at": "2026-11-03T17:16:22Z", "finished_at": "2026-11-03T17:16:52Z", "size_bytes": 40461008 }

5. The mistake, then a restore to the minute

At 12:18:31, an update goes out without its WHERE:

UPDATE pgbench_accounts SET abalance = 0;
UPDATE 5000000
Time: 49896.622 ms (00:49.897)

A restore replays the full backup and the transaction log, which is archived continuously. Any instant inside restore_window works. At 12:19:39 the window ended at 12:17:51: the log reaches off-site storage a minute or two behind (43 seconds on another reading). The chosen instant is 12:17:30, after the last transactions and before the mistake, given in UTC:

curl -s -X POST https://api.ffxf.net/v1/databases/34/restore \
     -H "Authorization: Bearer $FFXF_TOKEN" \
     -H "Idempotency-Key: $(uuidgen)" \
     -H "Content-Type: application/json" \
     -d '{"at": "2026-11-03T17:17:30Z", "name": "bank-before-mistake"}'

Most of those six minutes go to preparing the machine. The restored database's fingerprints match the reference state digit for digit, and its last history row is from 12:16:02.576:

 accounts | 5000000 | -383125 | 1430067750301
 branches |      50 | -383125 |  -12480793191
 tellers  |     500 | -383125 |  -86711664516
 history  |   40223 | -383125 |   80768475562

The restored database keeps the source's user and password; only the host changes. The source is never overwritten: its balances are still zero, ready to be looked into.

6. A fork: a copy in 35 seconds

A fork copies the database as it stands now, mistake included:

curl -s -X POST https://api.ffxf.net/v1/databases/34/forks \
     -H "Authorization: Bearer $FFXF_TOKEN" \
     -H "Idempotency-Key: $(uuidgen)" \
     -H "Content-Type: application/json" \
     -d '{"name": "bank-staging"}'

Requested at 12:20:04, the fork was linked to its data at 12:20:39, 35 seconds later for 755 MB. It went active at 12:29:30, after 9 min 26 s; it was requested 19 seconds after the restore and prepared alongside it. Its fingerprints are the source's after the mistake (accounts | 5000000 | 0 | 1464243819646, the other three tables unchanged).

A write on the fork leaves the source alone:

-- on the fork
UPDATE pgbench_accounts SET abalance = 1 WHERE aid <= 1000;
UPDATE 1000
-- on the source
SELECT count(*) FILTER (WHERE abalance <> 0), sum(abalance) FROM pgbench_accounts;
0|0

While it is linked, the fork has no backups (GET …/backups returns an empty list) and the source cannot be deleted:

{"error":{"code":"action_conflict","message":"Forks of this database still depend on it: make them independent or delete them first."}}

POST /v1/databases/36/detach gives it its own copy. A probe queried the fork every second: last answer at 12:34:08, refused connections after that, first answer back at 12:34:35. About 27 seconds of downtime; the 1,000 modified rows are still there. According to the documentation, the fork now gets its own backups.

7. pgvector and PostGIS

Extensions from the GET /v1/databases/options list are enabled through the API, one logical database at a time. The answer is immediate:

curl -s -X POST https://api.ffxf.net/v1/databases/35/databases/defaultdb/extensions \
     -H "Authorization: Bearer $FFXF_TOKEN" -H "Content-Type: application/json" \
     -d '{"extension": "vector"}'
{"data":{"name":"defaultdb","extensions":["vector"]}}

With postgis added the same way, the database had vector 0.8.7, postgis 3.7.0 and pg_stat_statements 1.12, which is there from the start. An HNSW index and a distance query work as is:

CREATE TABLE docs (id int PRIMARY KEY, embedding vector(3));
INSERT INTO docs VALUES (1, '[1,0,0]'), (2, '[0.9,0.1,0]'), (3, '[0,0,1]');
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
SELECT id, round((embedding <=> '[1,0,0]')::numeric, 4) AS distance
  FROM docs ORDER BY embedding <=> '[1,0,0]' LIMIT 2;
 id | distance
----+----------
  1 |   0.0000
  2 |   0.0061

SELECT round(ST_Distance('SRID=4326;POINT(-73.5674 45.5019)'::geography,
                         'SRID=4326;POINT(-71.2080 46.8139)'::geography)::numeric / 1000, 1) AS montreal_quebec_km;
 montreal_quebec_km
--------------------
              233.4

The ffxfadmin administrator is not a superuser. A trusted extension such as hstore can also be created in SQL; dblink and file_fdw are refused (Must be superuser to create this extension).

8. The limits, as measured

• 7-day retention. Past that, no restore. To keep a copy longer, export it yourself: a pg_dump -Fc of this 755 MB database took 7.7 seconds and produced a 15 MB file.
• No backup download. The API lists backups and restores them into a new database; it does not hand them over as files.
• A restore brings back the whole database. To recover one table, restore, copy the table out with pg_dump -t, then delete the restored database.
• The last few minutes. The restore window trails the present by one to two minutes.
• History does not carry over. The restored database starts its own window at its first backup (12:26:23); the source's window stays available while the source exists, then for 7 days after it is deleted.
• Every copy is a billed database. Restores and forks cost the rate of their size, by the hour, until you delete them.
• 1 GB is small. 25 connections at most and 241 MB of shared_buffers: plan for a connection pool in the application.

9. Summary

The three databases lived between 15 and 29 minutes. Billed by the hour, the whole test costs about 0.06 CAD. Every call used here is described in the databases documentation.