Move a local PostgreSQL database to a managed production database

Moving a database is more than copying rows. The target must support the source schema and extensions, the application must reach it securely, and you need a tested way to switch back if validation fails. This guide is for moving one PostgreSQL database from a laptop or a self-managed server to a managed service. It does not cover changing Prisma models or running a schema migration; use the separate Prisma production migration guide for that task.

Decide who should operate PostgreSQL

A managed database is useful when you would rather not install and patch the database server, maintain its backup schedule, or operate the host. A VPS gives you more control over PostgreSQL’s operating system, configuration and extensions, but you also own updates, access control, monitoring, backups and restore drills. Hostbrid offers both managed databases and Cloud VPS; this does not mean every database workload or extension is supported by either product.

Hostbrid’s published database offer lists PostgreSQL, MariaDB and Redis. It describes daily backups and, on its DB-HA plan, three PostgreSQL replicas with automatic failover. The same page says a managed database has no public IP by default and is reachable from eligible services in the account; remote access needs an explicit allowed IP. Check the current product details and plan limits before you create the target. A daily backup is useful, but it does not prove your own restore procedure or guarantee that an application can switch hosts without adjustment.

Choose self-managed PostgreSQL if you depend on superuser access, server extensions or settings the managed plan does not expose, or if you need a deployment model the product does not support. Choose managed only after checking the exact PostgreSQL major version, available extensions, storage, connection limits and permitted network path. The product page is the source for current commercial features; PostgreSQL’s documentation explains the dump format and migration tools.

Inventory before exporting anything

Record the source PostgreSQL major version, database size, encoding, collation, installed extensions and application roles. On the source, inspect extensions with:

SELECT extname, extversion FROM pg_extension ORDER BY extname;

Confirm each required extension exists on the target. Check for database owners, grants, row-level security policies, sequences, large objects and scheduled jobs. A per-database pg_dump does not include every cluster-wide role or server setting. If the application relies on those, plan a deliberate target equivalent instead of restoring the source cluster blindly. Do not copy raw data you are not allowed to transfer; test with a sanitized sample when possible.

The target must be created and reachable before the restore. For Hostbrid, read the endpoint, port, database name, username and TLS instructions from the account panel. The service is not open to arbitrary public connections by default. Run the migration from a workload allowed to reach the database, or request the narrowest temporary access the product supports for your trusted source address. Do not open PostgreSQL to the whole internet. Revoke temporary workstation access after the move.

Use a PostgreSQL client that can read the source server version and that is compatible with the target. The official pg_dump reference describes version compatibility; PostgreSQL expects dumps to load into newer server versions, but a dump created by a newer major client is not a safe way to target an older server. Confirm compatibility instead of assuming a major-version upgrade is transparent.

Make a protected archive and restore to an empty target

Store source and target credentials in a permissions-restricted libpq password file or a secret manager. Do not add passwords to shell history, paste connection strings into logs, or commit them. If using a .pgpass file, restrict it to its owner (0600). Follow the TLS mode and certificate checks shown by the destination service; do not turn off certificate verification to make a connection succeed.

With the source host, port, database and username available to the client, create a custom-format archive:

pg_dump --format=custom --host="$SOURCE_HOST" --port="$SOURCE_PORT" \
  --username="$SOURCE_USER" \
  --dbname="$SOURCE_DB" --file=before-migration.dump
pg_restore --list before-migration.dump

pg_dump connects to one database and creates a consistent logical export while the database remains available. The custom format supports inspection and selective restore. In this example, --no-owner --no-acl on restore intentionally avoids carrying source ownership and grants into the new service; objects are restored under the target login. That choice changes permissions, so test your application role and grants explicitly. If you need to preserve ownership or cluster roles, design that separately with the database administrator.

Create a new, empty target database with the required extension(s), then restore the archive using the target’s connection details:

pg_restore --exit-on-error --no-owner --no-acl \
  --host="$TARGET_HOST" --port="$TARGET_PORT" --username="$TARGET_USER" \
  --dbname="$TARGET_DB" before-migration.dump

Do not add --clean to a production restore just to clear errors: it can drop existing objects. Restore into an empty database first, and stop if pg_restore reports errors. The official pg_restore reference explains archive options and how failures are reported. Keep the encrypted archive in a restricted location until you have verified the target, then apply your retention and deletion policy.

Prove the copy before changing the application

Compare table lists and approximate row counts for important tables; check constraints, indexes, sequences, permissions, time zones and representative values with accents or binary data. Open the application against the target in a test environment and exercise login, writes, background jobs and any file references. A database dump does not move uploaded files, object storage, Redis cache contents, email, environment variables or external integrations.

For a development database that receives no production writes, a single export may be enough. For a live service, schedule a maintenance window: put the application into read-only/maintenance mode, stop every writer and job, take the final dump, restore it, and then switch the single DATABASE_URL or equivalent secret. This is a planned cutover, not a promise of zero downtime. Run a smoke test before reopening writes. Do not let old and new databases both accept writes unless you have a tested replication and conflict strategy.

Keep the old source intact and read-only until the new application has passed its checks. If you roll back after users have written to the new target, those writes will not exist in the old source. Decide in advance whether rollback means accepting a short data loss window, replaying changes, or keeping the service in maintenance while you reconcile data. The only reliable test of a backup is a restore you have actually run.

After cutover, monitor application connection errors and database storage/connection use. Rotate credentials if the migration file or temporary access could have exposed them. Database schema changes after cutover are a separate operation; see the Prisma migration checklist. If a managed database fits your extension and network requirements, compare the Hostbrid database plans; if you need to operate the database server yourself, compare VPS plans. Contact the team if a compatibility question remains before moving production data.