Troubleshooting Managed PostgreSQL
Symptoms you can hit when creating, connecting to and running a database, with their cause and fix.
A new database goes to failed or waiting-for-capacity right after creation
Cause: the machine type is sold out where the database goes. The create request does not
check stock; the check runs when the database is built. Without a fallback (the API default) the
database fails before anything is built; with onStockOut: "wait" (the console's default) it
waits, unbilled.
Fix: delete it and create it with another machine type or zone, or create it with
capacityFallback or onStockOut: "wait"; see
Create a database.
verify-full fails with "root certificate file … does not exist"
Cause: no sslrootcert was given.
Fix: download the CA bundle and point sslrootcert at it; see Connect to a database.
A Node.js app fails with "self-signed certificate in certificate chain"
Cause: the client does not trust the platform CA. For a bound app, the app sets its own
NODE_EXTRA_CA_CERTS, or has not been redeployed since the CA was delivered. From your computer,
node-postgres treats sslmode=require as verify-full.
Fix: redeploy; an app with its own bundle adds /etc/ews/ca/platform-ca.pem to it (see
Bind a managed database in the Apps User Guide). From your
computer, run the command under dcloud rds exec -- <command>, which sets
NODE_EXTRA_CA_CERTS and PGSSLROOTCERT to the CA bundle.
password authentication failed / SASL authentication failed
Cause: the wrong master password.
Fix: read it from Secrets; it is not one you typed anywhere. See
Manage the master password. A password changed by hand with
ALTER ROLE is not the one in Secrets.
A client using plaintext is refused
Cause: Require TLS is on: no pg_hba.conf entry … no encryption on 5432, SSL required
on 6432.
Fix: connect with sslmode=require or stricter; see Require TLS.
The connection times out from your laptop
Cause: the database is private.
Fix: connect from a machine in its network, or use the console's data browser (Browse the data).
The data browser, extensions or a binding time out on a private database
Cause: the platform's control plane cannot reach the database's public endpoint.
Fix: contact support.
The status is failed
Cause: provisioning stopped.
Fix: read the last Activity line; delete the database and create it again.
"could not open extension control file …" or Not installed on server
Cause: the database predates extension packages.
Fix: ask support to install them; it needs no restart.
CREATE EXTENSION postgis fails with "undefined symbol"
Cause: a machine built before extension builds were pinned to its minor has a PostGIS built for a newer minor than the one it runs.
Fix: upgrade the minor (Upgrade the minor version), or ask support to reinstall the database's extension builds, which needs no restart.
An extension shows Restart required
Cause: its library is configured in shared_preload_libraries but loads only at the next
restart.
Fix: reboot the database, then switch it on again.
A migration fails with "extension … already exists"
Cause: the extension was switched on in the console, and the migration creates it with a plain
CREATE EXTENSION.
Fix: use CREATE EXTENSION IF NOT EXISTS in the migration, or do not switch it on beforehand.
Activity says "The server certificate expires in N days"
Cause: the machine has not renewed it yet. The warning is written once a day from 30 days before expiry, and as an error inside 7 days; within 14 days it is also mailed as a health problem.
Fix: renewal is automatic, so first wait for it. If the certificate keeps approaching expiry,
redeploy the database to issue a new one, as the event says; clients using sslmode=verify-full
refuse to connect after the date.
"server closed the connection unexpectedly" or "terminating connection"
Cause: a failover or a restart.
Fix: reconnect and retry the transaction; see How Managed PostgreSQL behaves when things fail.
Nodes shows synchronous replication degraded
Cause: no standby is acknowledging commits (one is restarting, rebuilding or unreachable).
Fix: nothing; it holds again as soon as a standby streams. Until then a failover could lose the latest commits.
A bulk load on a sync cluster is slower than on async
Cause: each commit waits for a standby.
Fix: load in larger transactions, or switch to async for the load and back afterwards (see
Choose synchronous or asynchronous replication).
Throughput after a resize or restart is far below the benchmarks
Cause: the cache is cold: the first minutes read from the volume.
Fix: measure after a warm-up run; see Benchmarks.
Write throughput does not improve on a bigger machine
Cause: writes are bounded by the volume's flush time.
Fix: batch more work per transaction, use more concurrent clients, and on a cluster consider
async for bulk loads.
Turning logical replication off is refused
Cause: a logical replication slot still exists: 409 "Delete the logical replication slots
first (…): PostgreSQL does not start with wal_level replica while a logical slot exists."
Fix: delete the slots first; see Use logical replication.