Migrate from PostgreSQL Single to PostgreSQL Cluster

This guide walks you through exporting data from a PostgreSQL Single Node instance and restoring it to a PostgreSQL Cluster on vDB. Two scenarios are covered: Section A for instances with a single database, and Section B for instances with multiple databases.

Note: vDB does not support changing the Deployment Type of an existing database. Migration requires creating a new PostgreSQL Cluster and transferring data manually using the steps below.

---

Prerequisites

Before you begin, make sure:

• psql, pg_dump or pg_dumpall, and pg_restore are installed on the machine running the migration (version must match the PostgreSQL version on the source instance).

• You have credentials (host, username, password) for both the PostgreSQL Single Node and the PostgreSQL Cluster.

---

A. Single-Database Migration (pg_dump)

Use this section when your PostgreSQL Single Node instance contains one database that you want to migrate.

Step 1 — Verify Connectivity

Before starting, confirm that both your PostgreSQL Single Node instance and your PostgreSQL Cluster are running and reachable.

# Test PostgreSQL Single Node
psql -h <IP_single> -U <username_single> -d <database_single> -c "SELECT 1;"

# Test PostgreSQL Cluster
psql -h <IP_cluster> -U <username_cluster> -d <database_cluster> -c "SELECT 1;"

---

Step 2 — Stop Write Operations

Before exporting, prevent any write operations to the source database to ensure a consistent snapshot. Some approaches:

Option 1 — Stop from the application: Shut down or pause the application writing to the source database.

Option 2 — Set the source database to read-only:

ALTER DATABASE <database_name> SET default_transaction_read_only = on;

Remember to revert this after the restore completes:

ALTER DATABASE <database_name> SET default_transaction_read_only = off;

---

Step 3 — Export the Database

Use pg_dump to export the source database. Choose the format that matches your preferred restore method.

Option A — Custom format (recommended; for use with pg_restore):

pg_dump -h <IP_single> -U <username_single> -d <database_single> \
        -Fc -f backup.dump

Option B — Plain SQL format (for use with psql):

pg_dump -h <IP_single> -U <username_single> -d <database_single> \
        -Fp > backup.sql

Tip — Choosing a format: The custom format (-Fc) produces a compressed binary file and supports multi-job restore with pg_restore. The plain SQL format (-Fp) produces a human-readable file that is easier to inspect or edit before restoring. Use custom format for large databases.

---

Step 4 — Restore on PostgreSQL Cluster

Restore the dump to the target cluster using the method that matches your export format.

If you exported in custom format, use pg_restore:

pg_restore -h <IP_cluster> -U <username_cluster> -d <database_cluster> \
           --no-owner --no-privileges backup.dump

If you exported in plain SQL format, use psql:

psql -h <IP_cluster> -U <username_cluster> -d <database_cluster> \
     -f backup.sql

Superuser errors during restore: PostgreSQL Cluster does not grant superuser access. You may see errors such as ERROR: must be superuser or warnings about ALTER TABLE ... OWNER TO. These are expected and safe to ignore.

The flags --no-owner and --no-privileges (shown above for pg_restore) suppress the most common ones. Any error related to table creation or actual data should be investigated before proceeding.

---

Step 5 — Verify the Restored Data

After the restore completes, validate the data on PostgreSQL Cluster before cutting over traffic. Recommended checks:

• Compare table counts between source and target.

• Compare row counts for critical tables.

• Spot-check a sample of rows in key tables.

• Test application queries against the restored database.

---

B. Multi-Database Migration (pg_dumpall / pg_dump)

Use this section when your PostgreSQL Single Node instance contains more than one database.

---

Step 1 — Verify Connectivity

Confirm that both instances are running and accessible before proceeding.

# Test PostgreSQL Single Node
psql -h <IP_single> -U <username_single> -c "\l"

# Test PostgreSQL Cluster
psql -h <IP_cluster> -U <username_cluster> -c "\l"

---

Step 2 — Stop Write Operations

Before exporting, prevent any write operations to the source database to ensure a consistent snapshot. Some approaches:

Option 1 — Stop from the application: Shut down or pause the application writing to the source database.

Option 2 — Set the source database to read-only:

ALTER DATABASE <database_name> SET default_transaction_read_only = on;

Remember to revert this after the restore completes:

ALTER DATABASE <database_name> SET default_transaction_read_only = off;

---

Step 3 — Export All Databases

Choose one of the two export approaches below.

Option A — pg_dumpall (exports all databases in a single file):

pg_dumpall -h <IP_single> -U <username_single> \
           --no-role-passwords > alldb.sql

pg_dumpall limitations: pg_dumpall only produces plain SQL output, so you must restore with psql. It also attempts to export global objects (roles, tablespaces) that require superuser access. Errors like ERROR: must be superuser during export are expected and safe to skip. The flag --no-role-passwords omits hashed passwords, which cannot be restored without superuser access anyway.

Option B — pg_dump per database (recommended for most cases):

Run pg_dump separately for each database. This avoids global-permission errors entirely and gives you more control over the restore process.

# Repeat for each database
pg_dump -h <IP_single> -U <username_single> -d <db_name> \
        -Fc -f <db_name>.dump

pg_dump does not export global objects such as roles and tablespaces. You must recreate these manually on PostgreSQL Cluster before restoring. See Step 3 for details.

---

Step 4 — Recreate Roles and Tablespaces (if needed)

If your databases rely on specific roles or custom tablespaces, recreate them on PostgreSQL Cluster before restoring. Roles created here will need passwords set manually.

-- Recreate a role
CREATE ROLE <role_name> WITH LOGIN;
ALTER ROLE <role_name> WITH PASSWORD '<new_password>';

If you used pg_dumpall, the SQL file contains role definitions, but some statements may fail due to missing superuser access. Review the file and apply role statements manually as needed.

---

Step 5 — Restore on PostgreSQL Cluster

If you used pg_dumpall (Option A), restore with psql connecting to the default postgres database:

psql -h <IP_cluster> -U <username_cluster> -d postgres \
     -f alldb.sql

If you used pg_dump per database (Option B), restore each dump individually:

# Repeat for each database
pg_restore -h <IP_cluster> -U <username_cluster> -d <db_name> \
           --no-owner --no-privileges <db_name>.dump

Role passwords after restore: pg_dumpall exports role definitions but not their passwords (the --no-role-passwords flag omits them, and encrypted passwords cannot be restored without superuser access). After the restore, you must manually set passwords for all roles that require authentication:

ALTER ROLE <role_name> WITH PASSWORD '<new_password>';

---

Step 6 — Verify Each Database

After the restore, verify every database on PostgreSQL Cluster:

• Confirm all expected databases are present.

• Check table counts and row counts in each database.

• Verify that application users can connect and query successfully.

---

Quick Reference

Scenario	Export tool	Restore tool	When to use
Single database	pg_dump -Fc	pg_restore	Large databases; supports multi-job restore
Single database	pg_dump -Fp	psql	Need to inspect or edit the SQL file before restoring
Multiple databases	pg_dumpall	psql	Want to export all databases into a single file
Multiple databases	pg_dump (per DB)	pg_restore / psql	Need per-database control; avoids global permission errors

---

Troubleshooting

ERROR: must be superuser

Errors mentioning superuser access are expected on both PostgreSQL Single Node and PostgreSQL Cluster. Skip them. Use --no-owner and --no-privileges flags with pg_restore to suppress the most common cases.

Roles or ownership errors after restore

pg_dump and pg_dumpall do not carry over role passwords. After restoring, manually set passwords for all roles that need to authenticate.

Missing tablespaces

If the source database uses custom tablespaces, the restore may fail because those tablespaces do not exist on the target cluster. Recreate any required tablespaces on PostgreSQL Cluster before running the restore.

Table creation errors

Errors unrelated to superuser access (e.g., duplicate tables, type mismatches) should be investigated before proceeding. Do not skip these.

Large databases

For very large databases, use pg_restore --jobs <n> to enable multi-job restore with the custom format and reduce the total restore time.

---

Next Steps

After verifying the data successfully:

• Update the connection string in your application to point to the PostgreSQL Cluster (new host, port, and credentials).

• Confirm the application works correctly against the migrated database.

• Delete the old PostgreSQL Single Node on vDB once you have confirmed it is no longer needed.