Move to PostgreSQL

An Astrolabe started in one command (a trial license, for example) keeps its data in SQLite files, in the astrolabe-data Docker volume. That is fine for one person. For a team, or before you go on with a paid license, move it to PostgreSQL. This page is the migration procedure: what it does, what you need, the steps, how to check the result and how to go back.

Your accounts, password, catalog, dashboards, settings and the tokens entered in Administration come along. The SQLite files are never modified: they stay your way back.

⚙️ How the migration works

The migration is a command of the Astrolabe image, migrate-to-postgres. It runs only when you type it: an Astrolabe started normally never migrates, and an Astrolabe that stays on SQLite is never affected.

You run it once, with the container stopped, through docker compose run. The container then does this task instead of starting the server:

1. It checks the target. It refuses a PostgreSQL that already holds Astrolabe data, and names the first database and table it found. It never overwrites anything. The only exception is a migration it left half-way itself: it resumes it.
2. It creates the schema. It starts the Astrolabe backend once on PostgreSQL, so that every plugin creates its own database (backstage_plugin_<plugin>) with its own migrations: the same schema as an Astrolabe installed on PostgreSQL from day one. Then it stops that backend.
3. It copies the data, plugin by plugin, one transaction per plugin. It empties what the first start wrote, then copies every table of every SQLite file, converting each value to the type of its PostgreSQL column (booleans, dates, JSON, integers kept exact).
4. It checks each table. A table or a column that exists in SQLite with data but not in PostgreSQL, or a row count that differs between the two, rolls the plugin back and stops the migration, naming the plugin and the table.
5. It moves the sequences past the copied identifiers, so that new rows get new ids.

The SQLite files are opened read-only for the whole migration.

The tokens and passwords entered in Administration are copied as they are, still encrypted. They stay readable because the key that opens them, backend-secret, is in the same astrolabe-data volume, which Astrolabe keeps using on PostgreSQL.

✅ What you need

• The machine where Astrolabe runs, with Docker and Docker Compose.
• The name of the volume of your Astrolabe: astrolabe-data with the command of the trial e-mail. To read it: docker inspect astrolabe --format '{{range .Mounts}}{{.Name}} {{end}}'.
• Your license key.
• An Astrolabe version that has the migration: 3.2.0 or later. The migration also updates you to that version if you were on an older one.
• A few minutes of downtime: Astrolabe is stopped during the copy. On a trial instance, the copy itself takes less than a minute.
• Free disk space for PostgreSQL, about the size of the astrolabe-data volume.

💾 Step 1 — Back up the volume

1. Open a terminal in an empty folder of your home directory.

2. Copy the volume into an archive.

   docker run --rm -v astrolabe-data:/data -v "$PWD":/backup alpine tar czf /backup/astrolabe-data.tar.gz -C /data .

   The folder now holds astrolabe-data.tar.gz, with the SQLite files (db/) and backend-secret.

📄 Step 2 — Prepare the two files

1. In the same folder, save this file as docker-compose.postgres.yml.

   name: astrolabe
   
   services:
     postgres:
       image: postgres:14-alpine
       command: postgres -c max_connections=300
       environment:
         POSTGRES_USER: ${POSTGRES_USER:-backstage}
         POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-backstage}
       volumes:
         - astrolabe-postgres:/var/lib/postgresql/data
       healthcheck:
         test: ['CMD-SHELL', 'pg_isready -U ${POSTGRES_USER:-backstage}']
         interval: 5s
         timeout: 5s
         retries: 10
   
     astrolabe:
       image: forgeitnc/astrolabe:${ASTROLABE_VERSION:?set ASTROLABE_VERSION}
       depends_on:
         postgres:
           condition: service_healthy
       ports:
         - '${ASTROLABE_PORT:-7007}:7007'
       environment:
         ENVIRONMENT: production
         FRONTEND_BASE_URL: http://127.0.0.1:${ASTROLABE_PORT:-7007}
         BACKEND_BASE_URL: http://127.0.0.1:${ASTROLABE_PORT:-7007}
         POSTGRES_HOST: postgres
         POSTGRES_PORT: 5432
         POSTGRES_USER: ${POSTGRES_USER:-backstage}
         POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-backstage}
         ADMIN_USERNAME: ${ADMIN_USERNAME:-administrator}
         ADMIN_EMAIL: ${ADMIN_EMAIL:-admin@astrolabe.local}
         ORGANIZATION_NAME: ${ORGANIZATION_NAME:-Astrolabe}
         ASTROLABE_LICENCE: ${ASTROLABE_LICENCE:-}
       volumes:
         - astrolabe-data:/app/data
   
   volumes:
     astrolabe-postgres:
     astrolabe-data:
       name: astrolabe-data
       external: true

   postgres is a new PostgreSQL 14, with its data in the new astrolabe-postgres volume. astrolabe uses your existing astrolabe-data volume (external: true: Compose does not create it, it takes the one of your trial). If your volume has another name, put it in the two astrolabe-data lines at the end.

2. Next to it, create a file named .env.

   ASTROLABE_LICENCE=<your license key>
   ASTROLABE_VERSION=3.2.0
   POSTGRES_PASSWORD=<a password for PostgreSQL>

   Compose reads it on its own. Keep this file: you need it at every start.

🐘 Step 3 — Migrate

1. Stop Astrolabe.

   docker stop astrolabe

2. Run the migration.

   docker compose -f docker-compose.postgres.yml run --rm astrolabe migrate-to-postgres

   Compose starts PostgreSQL, then runs the migration in a one-off Astrolabe container that is removed at the end (--rm). The output gives one line per plugin, then:

   🗄️  Creating the PostgreSQL schema (one start of the backend)…
     ✓ schema created
   📦 Copying … SQLite databases to PostgreSQL postgres:5432
     ✓ account: … tables, … rows
     …
   ✅ Migration done. Start Astrolabe on PostgreSQL: docker compose -f docker-compose.postgres.yml up -d
      The SQLite files are untouched in the volume: they are your way back.

3. Start Astrolabe on PostgreSQL.

   docker compose -f docker-compose.postgres.yml up -d

🔍 Step 4 — Check

1. Wait until Astrolabe is ready. This command answers 200 when it is.

   curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:7007/.backstage/health/v1/readiness

2. Check it runs on PostgreSQL. This command prints a line that says Database is PostgreSQL.

   docker compose -f docker-compose.postgres.yml logs astrolabe | grep "Database is PostgreSQL"

3. Open http://127.0.0.1:7007. In Username, type administrator, in Password the password you already use, then click SIGN IN. Astrolabe does not ask for a new password: it is the same account.

4. Click Administration at the bottom of the sidebar, then Licensing: your license shows Active and the same end date.

5. Open Catalog and your dashboards: they hold what you had on SQLite.

✅ The migration is done.

🧹 Step 5 — Remove the old container

Once you have checked, remove the old container. Its volume stays: it still holds the SQLite files, and backend-secret, which Astrolabe on PostgreSQL keeps using.

docker rm astrolabe

From now on, start and stop Astrolabe from the folder of the two files:

docker compose -f docker-compose.postgres.yml up -d
docker compose -f docker-compose.postgres.yml down

Back up both volumes from now on

astrolabe-postgres holds the data. astrolabe-data holds backend-secret, the key that opens the tokens entered in Administration: without it, those tokens are lost. Back up the PostgreSQL with docker compose -f docker-compose.postgres.yml exec postgres pg_dumpall -U backstage | gzip > astrolabe-postgres.sql.gz, and astrolabe-data with the command of step 1.

↩️ Go back to SQLite

As long as you have not removed the old container:

1. Stop Astrolabe on PostgreSQL.

   docker compose -f docker-compose.postgres.yml down

2. Start the old container again.

   docker start astrolabe

Astrolabe opens on its SQLite files, as it was before the migration. What you changed on PostgreSQL in the meantime is not in it.

If you already removed the old container, run the command of your trial e-mail again: it starts a new container on the same astrolabe-data volume, with the same data.

To migrate again later, start from an empty PostgreSQL: docker compose -f docker-compose.postgres.yml down -v removes the astrolabe-postgres volume (and only it, astrolabe-data is external), then go back to step 3.

🏢 Use a PostgreSQL you already run

The migration works with any PostgreSQL 14 or later, as long as Astrolabe has no database in it yet.

1. In docker-compose.postgres.yml, remove the postgres service, the depends_on block of astrolabe and the astrolabe-postgres: volume.

2. Set the four variables of the astrolabe service to your server: POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD.

3. Give that user the right to create databases (CREATEDB): Astrolabe creates one database per plugin, named backstage_plugin_<plugin>.

4. Raise max_connections to 300 or more: each plugin opens its own pool of connections.

Then follow steps 3 to 5.

📚 Reference

Item	Value
Command	migrate-to-postgres, argument of the Astrolabe image (3.2.0 or later)
Variables read	POSTGRES_HOST, POSTGRES_USER, POSTGRES_PASSWORD (required), POSTGRES_PORT (default 5432)
Source	the .sqlite files of /app/data/db, opened read-only
Target	one PostgreSQL database per plugin, backstage_plugin_<plugin>; the RBAC rules (rbac.sqlite on SQLite) go to backstage_plugin_permission
Not copied	the migration tables of each engine (knex_migrations, …__knex_migrations): PostgreSQL keeps its own
Progress file	/app/data/migration-to-postgres.json, which lets an interrupted migration resume on the same PostgreSQL
Time limit	the backend must be ready on PostgreSQL within 10 minutes
Exit	✅ Migration done. and code 0; otherwise a line starting with ❌ and a non-zero code

🛠️ Troubleshooting

What you see	Cause and what to do
❌ PostgreSQL … already holds Astrolabe data (database …, table …). The migration only fills an empty PostgreSQL: give it a new, empty one.	The PostgreSQL is not empty: a migration already ran, or Astrolabe already started on it. To start over, run docker compose -f docker-compose.postgres.yml down -v, then step 3 again.
❌ No SQLite database in /app/data/db: mount the volume of the instance to migrate (-v astrolabe-data:/app/data).	The volume is not the one of your Astrolabe. Read its name (see « What you need ») and put it in the two astrolabe-data lines of the file.
external volume "astrolabe-data" not found	Same cause: your Astrolabe uses another volume name.
required variable ASTROLABE_VERSION is missing a value: set ASTROLABE_VERSION	The .env file is missing, or it is not in the folder you run the command from.
Bind for 0.0.0.0:7007 failed: port is already allocated	The old container still runs. Run docker stop astrolabe, then the command again.
❌ <plugin>: table <table> has … rows in SQLite and … in PostgreSQL. or … does not exist in PostgreSQL.	The copy of that plugin did not match and was rolled back. Your Astrolabe on SQLite is intact: docker start astrolabe brings it back. Send the whole output to support@forgeit.fr.
❌ The backend was not ready on PostgreSQL after 10 minutes. followed by the last lines of the backend	Astrolabe could not start on that PostgreSQL. The lines below say why (connection refused, wrong password, too few connections). Fix it, run docker compose -f docker-compose.postgres.yml down -v, then step 3 again.
After the migration, SIGN IN answers Authentication failed	ADMIN_USERNAME is set to another name in your terminal or in .env, and Compose passes it on. Remove it (unset ADMIN_USERNAME), then docker compose -f docker-compose.postgres.yml up -d --force-recreate.

Still stuck?

Write to support@forgeit.fr with the whole output of the command.