Ghost 5.78+ PostgreSQL beta: migration lock behavior on managed instances

Confirmed Facts

Ghost 5.78 introduced experimental PostgreSQL support using Knex's migration runner, which relies on pg_advisory_lock for coordination. This mechanism has known issues on managed PostgreSQL services due to their connection pooling and statement timeout policies.

Likely Explanation

The advisory-lock deadlock occurs because managed PostgreSQL services (AWS RDS, Cloud SQL, Azure) often use connection pooling layers that don't properly propagate or maintain advisory locks across pooled connections. When knex-migrator migrate --init runs, it attempts to acquire an exclusive advisory lock; if the pool rejects or times out this operation during concurrent startup attempts, the migration hangs or fails. This is compounded by default statement timeouts (often 5s on RDS) that can interrupt long-running lock acquisition sequences.

Recommended Steps

1. Configure connection settings in config.production.json to disable connection pooling for migrations:

   
   "database": {
     "client": "pg",
     "connection": {
       "host": "your-db-host",
       "user": "ghost_user",
       "password": "your-password",
       "database": "ghost_db",
       "ssl": true,
       "statement_timeout": 30000,
       "connectionTimeoutMillis": 10000
     },
     "pool": {
       "min": 1,
       "max": 1,
       "acquireTimeoutMillis": 300000
     }
   }
   

2. Run migrations manually with a single connection to avoid pool interference:

   ghost knex-migrator migrate --init --sqlite3db=/dev/null --pg

3. Verify lock behavior before production deployment:

   pg_is_in_recovery();
   SELECT pg_try_advisory_lock(12345);

Zero-Downtime Migration Path

A true zero-downtime migration from MySQL to PostgreSQL is not currently supported. The schema differences between MySQL and PostgreSQL, combined with Ghost's lack of cross-database sync mechanisms, require a planned maintenance window. The recommended sequence:

1. Export content from MySQL via Ghost Admin or CLI
2. Provision PostgreSQL instance with adjusted connection settings
3. Import content to PostgreSQL
4. Update config.production.json and restart Ghost

Verification Required

Has the migration lock behavior been tested on your specific managed PostgreSQL version (e.g., AWS RDS PostgreSQL 14.x, Cloud SQL PostgreSQL 15.x)? The interaction between your database version's connection pooling implementation and Ghost's advisory lock usage may require version-specific tuning.