Skip to content

Matches page titles and page text. Forty pages, indexed at build time.

Compatibility notes

The differences between a self-managed PostgreSQL server and a Balta service that show up during a migration, and what to do about each.

Every item here is something a restore into a Balta service handles differently from a restore into a server you run yourself. None of them is exotic, and each one is cheaper to find before the restore than halfway through it.

Work down the list against your source before you start copying. Each section ends with what to do about it.

Versions

Services run PostgreSQL 16, 17 or 18, and new services default to 18. A source on an older major version moves the same way as any other: take the dump with the pg_dump from the target's major version, which can read older servers, and restore it with the matching pg_restore.

Superuser

You will not have it. Your service's managed elevated role has CREATEROLE and none of SUPERUSER, CREATEDB, REPLICATION or BYPASSRLS. Anything in your dump or your migration scripts that needs more than that fails:

  • untrusted procedural languages such as plpython3u, and functions written in them
  • COPY to or from a file on the server, as opposed to through your client
  • extensions outside the supported catalogue
  • CREATE TABLESPACE
  • event triggers

Restoring with --no-owner --no-privileges removes the most common cause, which is ownership by roles that do not exist on the target. Add --no-tablespaces if your source uses tablespaces, so the restore does not try to place objects in them. The privilege model explains where each boundary sits and why.

Logical replication does not come across. CREATE SUBSCRIPTION needs privileges your role does not hold, and replicating into a service is not part of the first version, so take the dump with --no-subscriptions.

Databases and roles

Create the target database in the dashboard before you restore. Your role cannot run CREATE DATABASE, so do not use --create when you dump or restore. Withholding CREATEDB is what makes the database count a limit.

Roles are not in a single-database dump. pg_dumpall --roles-only writes them out, and the script needs editing before it runs here: remove your source's superuser, and remove SUPERUSER, REPLICATION and BYPASSRLS from every other role, because a role cannot grant an attribute it does not hold. Role count is a guideline, not a limit, because CREATEROLE is granted.

Extensions

Check every extension in your source against the supported catalogue before you migrate, not after. A missing extension turns a restore into a partial one.

sql
SELECT extname, extversion FROM pg_extension ORDER BY extname;

pg_restore carries on past a failed statement by default and reports how many errors it ignored at the very end, so a partial restore can look finished. Read that last line.

If your source has an extension you can do without, leave it out of the restore:

bash
pg_restore --list appdb.dump > appdb.list
# Put a semicolon at the start of each line you do not want, then:
pg_restore --use-list=appdb.list --no-owner --no-privileges --dbname="postgresql://..." appdb.dump

pg_stat_statements is one to leave out. It is not in the customer-installable catalogue, so your role cannot create it and the statement fails the restore. See supported extensions.

Encoding and collation

A collation change alters sort order, which changes index ordering and can change query results.

You cannot choose either here. Every database on a service is created with the UTF8 encoding and the en_US.UTF-8 collation, and the dashboard's database form takes a name and an owner and nothing else. Check your source before you move it:

sql
SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate, datctype
FROM pg_database
WHERE datname = current_database();

A source in another encoding is converted on the way in, because the dump records the encoding it was taken in. SQL_ASCII is the difficult case. It stores bytes without checking them, and a byte that is not valid UTF-8 stops that table's data from loading with invalid byte sequence for encoding "UTF8".

A source on a different collation, C for example, sorts text differently here. The restore rebuilds every index, so the indexes match the new order. What changes is the order your queries return. A column that has to keep byte order can say so explicitly with COLLATE "C".

Connection semantics

Every service has a pooled endpoint that uses transaction pooling and a direct endpoint that does not. They share a hostname; the lower of your service's two ports is the pooled one, and both are on the Connect tab. If your application uses LISTEN and NOTIFY, session advisory locks, or session-level SET, the consumers that use them need the direct endpoint rather than the pooled one. Pooled vs direct has the full list.

Restore over the direct endpoint. A long restore on the pooled endpoint occupies a server connection for its duration.

Parameters

Parameters you set on your own server may be platform-managed here. Check the tunable list rather than assuming a setting carried over.

Server-wide settings in your source's postgresql.conf are not part of a dump, so none of them come across by accident, and your role cannot run ALTER SYSTEM. Per-role settings made with ALTER ROLE ... SET travel in the pg_dumpall role script, and they apply here for any parameter an ordinary role may set.

Scheduled jobs

cron on the database host is not available to you, because you do not have the host. pg_cron is not in the supported catalogue either. Run scheduled work from your application platform.

Check your source in one pass

sql
SELECT current_setting('server_version') AS version;

SELECT extname, extversion FROM pg_extension ORDER BY extname;

SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate, datctype
FROM pg_database WHERE datname = current_database();

SELECT rolname FROM pg_roles
WHERE rolsuper OR rolcreatedb OR rolreplication OR rolbypassrls
ORDER BY rolname;

SELECT lanname FROM pg_language WHERE NOT lanpltrusted;

Five answers: the major you need a pg_dump for, the extensions to check against the catalogue, the encoding and collation to compare against UTF8 and en_US.UTF-8, the role attributes you will have to strip from the pg_dumpall script, and any untrusted procedural language whose functions will not restore.

Troubleshooting

must be superuser to create this extension. The extension is outside the customer-installable catalogue. Leave it out of the restore with --use-list, or drop the dependency.

role "..." does not exist. You restored without --no-owner --no-privileges, or the roles were not created first.

invalid byte sequence for encoding "UTF8". The source is SQL_ASCII. Convert the offending table's data before dumping, or dump that table separately with the correct client encoding.

Queries return rows in a different order. A collation difference. The restore rebuilt every index, so the indexes match the new order; what changed is the order your queries return. Add COLLATE "C" to any column that must keep byte order.

A function fails with language "plpython3u" does not exist. Untrusted procedural languages are never offered. Move that logic into your application.