pg_dump / pg_restore

Migration through pg_dump / pg_restore is the clearest and usually the safest way to move data into SPG99.

1. Prepare the target database

Create the tenant and the target database in SPG99. Then make a normal test connection through the DSN in advance to verify:

• that the credentials are correct;
• that TLS works;
• that any cold start happens before the large pg_restore.

2. Create a dump from the source

pg_dump -Fc -h <source-host> -U <source-user> <source-db> > dump.dump

For large databases, estimate the dump size and transfer time in advance.

3. Restore into SPG99 through Gateway

Using a DSN:

export TARGET_DSN="postgres://<pg_user>:<pg_password>@<gateway-host>:5432/<target-db>?sslmode=require"
pg_restore -d "$TARGET_DSN" dump.dump

Using environment variables:

export PGPASSWORD="<pg_password>"
export PGSSLMODE="require"
pg_restore -h <gateway-host> -p 5432 -U <pg_user> -d <target-db> dump.dump

4. Validate the result

After restore, it is useful to verify that:

• the schema and major tables are present;
• the application can connect to the target database;
• extensions and privileges were transferred as expected;
• the dump did not accidentally include unnecessary service secrets and configurations.

Practical recommendations

• for large databases, use --jobs, but control the number of connections;
• if necessary, simplify the migration with flags such as --no-owner and --no-privileges if roles differ in the target environment;
• if you have strict downtime requirements, prepare a cutover checklist in advance instead of switching “by hand.”

When this path is especially good

Choose pg_dump / pg_restore if you want the most predictable and simplest process without a complex long-running synchronization phase.