Relyt One and Neon, fact by fact

Move a Postgres database to Relyt One by hand

A manual dump and restore with the standard PostgreSQL client tools. It is for source databases on PostgreSQL 17 or earlier, such as a local server or a Neon database on one of those versions. Replace the placeholders in the first step with your own connection strings, then run each block in order.

These commands were replayed exactly as shown between two PostgreSQL 17 servers, using PostgreSQL 17 client tools.

1. 01

   Set the two connection strings

   Create a project in Relyt One and copy its connection string. Export it together with the source database's connection string. Relyt One runs PostgreSQL 17; pg_dump must be at least as new as the source server, so check its version. If the source is Neon, use its direct (non-pooled) connection string: Neon's docs advise against running pg_dump over a pooled connection. If the source runs a version newer than PostgreSQL 17 (for example Postgres 18 on Neon), restoring it into Relyt One's PostgreSQL 17 is not supported.

   export SOURCE_DATABASE_URL="postgresql://<user>:<password>@<source-host>/<dbname>"
   export TARGET_DATABASE_URL="postgresql://<user>:<password>@<relyt-one-host>/<dbname>"
   pg_dump --version

2. 02

   Compare extensions

   List the extensions the source database uses, then the extensions available in your Relyt One database. If something the source relies on is not in the second list, objects that depend on it will fail to restore, so adjust the schema first.

   psql "$SOURCE_DATABASE_URL" -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;"
   psql "$TARGET_DATABASE_URL" -c "SELECT name, default_version FROM pg_available_extensions ORDER BY 1;"

3. 03

   Dump the source

   Write a custom-format archive (-Fc) of the source database to a local file. -v prints each object as it is dumped.

   pg_dump -Fc -v -d "$SOURCE_DATABASE_URL" -f source.dump

4. 04

   Restore into Relyt One

   Restore the archive into your Relyt One database. --no-owner and --no-acl skip the source's ownership and privilege statements, whose roles usually do not exist in the target, so the restored objects belong to the role you connect as.

   pg_restore -v --no-owner --no-acl -d "$TARGET_DATABASE_URL" source.dump

5. 05

   Compare row counts

   Count the rows of every table on both sides and compare the two outputs. When they match, point your application at the Relyt One connection string.

   for url in "$SOURCE_DATABASE_URL" "$TARGET_DATABASE_URL"; do
     psql "$url" -c "SELECT table_schema, table_name, (xpath('/row/c/text()', query_to_xml(format('SELECT count(*) AS c FROM %I.%I', table_schema, table_name), false, true, '')))[1]::text::bigint AS row_count FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY 1, 2;"
   done