Back up and restore the gateway database

The gateway database holds the accounts, the access key digests, the spend plans and the consumption history used for billing. This page describes how to take a copy of it, and how to put it back.

A backup that has never been restored has not been verified. The “Test the backup” section describes the round trip that verifies it.

Back up

export DATABASE_URL="postgresql://<utilisateur>:<mot-de-passe>@<hôte>:<port>/<base>"
cd gateway-db
./sauvegarder.sh --sortie /var/sauvegardes/passerelle-$(date +%Y%m%d-%H%M%S).sql.gz

The script then prints an inventory of what it took, one line per table with its row count. Keep this output: it is what you compare against after a restore.

sauvegarde écrite : /var/sauvegardes/passerelle-20260801-030000.sql.gz (412K)

Contenu sauvegardé — ce sont ces nombres qu'on doit retrouver après restauration :
          table          | lignes
-------------------------+--------
 plans                   |      4
 request_logs            |  18742
 schema_migrations       |     20
 users                   |     37

--sortie is required, and the script does not overwrite an existing file. It does not generate a name for you: cadence and retention are operational decisions, and the script does not make them.

The file is a secret

It is created with mode 0600, readable by its owner only. Treat it as a key:

• encrypt it before moving it;
• get it off the machine that hosts the database;
• destroy copies you no longer need. A backup taken before the 015 migration contains the keys of all your holders in cleartext.

What the backup contains

Everything in the database: accounts and key digests, plans, providers, models, endpoints, role assignments, authorization policies, revocations, sessions, audit logs, and request_logs, the consumption history.

The schema_migrations registry is part of it. A database restored without its registry is a database the migration script believes to be empty: it would replay the whole chain from the first migration, on live data.

What it does not contain

Not in the backup	Why	What that leaves you to do
PostgreSQL roles and their passwords	pg_dump copies only the contents of a database, not the server’s global objects	recreate the login accounts on the target server
Owners and privileges	they name roles that may not exist on the destination	reapply any grant made by hand outside the product
The database itself	the file describes contents, not existence: no CREATE DATABASE inside	create the empty database before restoring
Your provider keys	the database stores the environment variable name, not the key	give the gateway its environment back
ADMIN_API_KEY	it lives in the gateway’s environment, not in the database	give that back too

A restored database is not an installation that starts again: it also needs its environment.

If the script refuses: “pg_dump 16 cannot back up a PostgreSQL 17 server”

pg_dump does not read a server whose major version is later than its own, and a containerized server is often more recent than the client shipped by the distribution.

Install the client matching the server version and put it at the front of PATH:

export PATH="/usr/lib/postgresql/17/bin:$PATH"
pg_dump --version

No file is written while this refusal is displayed.

Restore

Restoring requires only psql: no pg_restore, and no client matching the server version. A file taken from a 17 server restores with a 16 client.

The destination database must exist and be empty:

createdb -h <hôte> -U <utilisateur> proxy
export DATABASE_URL="postgresql://<utilisateur>:<mot-de-passe>@<hôte>:<port>/proxy"
cd gateway-db
./restaurer.sh /var/sauvegardes/passerelle-20260801-030000.sql.gz

Everything runs in a single transaction: an interrupted restore does not leave a half-filled database, it leaves nothing.

The script prints its own inventory in turn. Compare it to the backup’s: the numbers must be identical, table by table.

Restoring over a database that already contains something

Without an option, the script refuses and touches nothing:

erreur: la base de destination contient déjà 14 table(s).
Restaurez dans une base vide (createdb), ou relancez avec --ecraser pour remplacer
le contenu actuel. Rien n'a été touché.

--ecraser requests replacement. It erases the destination’s schema and data before restoring, in the same transaction as the restore:

./restaurer.sh /var/sauvegardes/passerelle-20260801-030000.sql.gz --ecraser

The destructive action is not the default one.

After a restore

1. Compare the two inventories, line by line.

2. Check that the schema is at the expected level. The expected message is this one:

   ./migrate.sh

   base à jour, aucune migration à appliquer.

   If the script announces migrations to apply, the restored database is not at the expected schema: stop and read Apply the database migrations before going further.

3. Give the gateway its environment back: ADMIN_API_KEY and your provider keys, which are not in the backup.

4. Check end to end: authenticate a known key against the gateway, and open the account list in the admin console.

Test the backup

Do this before you need it, and again after every server version change. The test runs on a database off to the side; production is not touched.

# 1. Une sauvegarde de la vraie base.
export DATABASE_URL="postgresql://<utilisateur>:<mot-de-passe>@<hôte>:<port>/proxy"
cd gateway-db
./sauvegarder.sh --sortie /tmp/essai-de-restauration.sql.gz

# 2. Une base vide, à côté, sur le même serveur.
createdb -h <hôte> -U <utilisateur> essai_de_restauration

# 3. La sauvegarde y est restaurée.
DATABASE_URL="postgresql://<utilisateur>:<mot-de-passe>@<hôte>:<port>/essai_de_restauration" \
  ./restaurer.sh /tmp/essai-de-restauration.sql.gz

# 4. Le schéma est complet : rien à rejouer.
DATABASE_URL="postgresql://<utilisateur>:<mot-de-passe>@<hôte>:<port>/essai_de_restauration" \
  ./migrate.sh

# 5. On efface la base d'essai et le fichier.
dropdb -h <hôte> -U <utilisateur> essai_de_restauration
shred -u /tmp/essai-de-restauration.sql.gz

The two inventories printed at steps 1 and 3 must match, and step 4 must answer “database up to date, no migration to apply.”.

When the backup is mandatory

Before any schema migration, and in particular before the one that replaces cleartext keys with their digest (015-hacher-les-cles-api). This migration is irreversible: after it, no query and no screen recovers a key. A mistake at that point loses the keys of all your holders, which are regenerated one by one.

The full migration procedure, and what 015 does, are in Apply the database migrations.

The other times a backup is due: before a PostgreSQL server upgrade, before moving the database from one machine to another, and at regular intervals.

What these scripts do not do

• They schedule nothing: no periodic job, no retention, no rotation. Cadence, retention period and the off-machine location are decided in your operations; the command is called from the scheduler of your choice.
• They neither encrypt nor transfer the file.
• They do not do continuous backup, so no point-in-time restore. That belongs to PostgreSQL transaction log archiving, which is configured on the server.