Migrating PostgreSQL Databases to SITE Cloud

This guide covers migration from on-premises or user-managed PostgreSQL workloads to SITE Cloud DBaaS.

Migration Overview

This migration approach supports offline migration with dump and restore, and online-style migration with logical replication. The goal is to move PostgreSQL workloads to SITE Cloud DBaaS while preserving data integrity and planning for downtime, validation, and rollback.

DBaaS shifts infrastructure management, patching, backups, monitoring, and availability to SITE Cloud so application teams can focus on application logic and data modeling.

Assessment and Planning

Before migrating, review:

Area	Guidance
Version compatibility	The target DBaaS version must be greater than or equal to the source PostgreSQL version. Do not migrate from a newer source version to an older target version.
Sizing	Choose the appropriate instance size and storage in DBaaS.
Storage overhead	Plan target storage at 1.5x to 2x the source size to account for index rebuilds, logs, and temporary migration overhead.
Connectivity	Open the required connectivity from source to destination.

Note

PostgreSQL 10.0 or later is supported.

Migration Methods

Dump and Restore

Dump and restore uses pg_dump to export source data and pg_restore to import it into the target database.

Consideration	Detail
Best suited for	Databases smaller than 100 GB.
Downtime	Requires downtime. Updates during the migration window are not captured in the target.
Duration	Can take a few hours to several hours depending on database size.
Complexity	The simplest migration method.

Logical Replication

Logical replication replicates changes from the source instance to the target instance in near real time until cutover.

Consideration	Detail
Best suited for	Migrations that need little or no downtime.
Complexity	Setup and management can be complex.
Schema changes	Certain changes, such as schema modifications, are not published.
Manual intervention	Manual work may be required to carry over changes that replication does not publish.
Version support	Supported from PostgreSQL version 10 onward.

Technical Implementation

Dump and Restore

Export Data

Create a dump file from the source database:

pg_dump -h <hostname> -p <5432> -U <username> -Fc -b -v -f <dumpfilelocation.sql> -d <database_name>

Option	Description
-h	Source server hostname.
-U	Source database user.
-Fc	Custom-format archive suitable for pg_restore.
-b	Include large objects in the dump.
-v	Verbose mode.
-f	Dump file path.

Create the Target Database

Log in to the target database server:

psql -h <hostname> -p 5432 -U <username> -d <database_name>

Create the target database:

CREATE DATABASE <New database name>;

Import Dump Files

Restore the dump into the target database:

pg_restore -v -h <hostname> -U <username> -d <database_name> -j 2 <dumpfilelocation.sql>

Option	Description
-h	Target server hostname.
-U	Target database user.
-d	Target database name.
<dumpfilelocation.sql>	Dump file created with pg_dump.

Migrate Roles and Users

Export global objects such as roles and users from the source database:

pg_dumpall -U <username> -h <hostname> -f <dumpfilelocation.sql> --no-role-passwords -g

Restore roles and users to the target database:

psql -h <hostname> -U <username> -f <dumpfilelocation.sql>

Option	Description
-h	Source or target server hostname, depending on export or restore step.
-U	Database user.
-f	Dump file path.
-g	Dump only global objects such as roles and tablespaces.

Logical Replication

Prerequisites

• Access to the source PostgreSQL database.
• psql, pg_dump, and pg_restore installed locally.
• Connectivity from the destination PostgreSQL database to the source PostgreSQL database.
• Firewall rules or security group settings that allow the required connectivity.

Prepare the Source Database

Logical replication requires the source database to be configured correctly:

• Set wal_level to logical.
• Set max_replication_slots to an appropriate value with at least 1.
• Size max_wal_senders appropriately.
• Restart PostgreSQL after changing the configuration.

Transfer Schema to the Subscriber

On the publisher, export the schema:

pg_dump -d 'postgresql://<user>:<password>@<host>:<port>/<database>' -s --format directory -f dump

Alternative schema export:

pg_dump -s -n myschema -f myschema.sql

On the subscriber, import the schema:

pg_restore -d 'postgresql://<user>:<password>@<host>:<port>/<database>' --verbose dump

Alternative schema import:

psql -U postgres -d yourdb -f myschema.sql

Option	Description
-s	Schema-only dump.
--format directory	Directory format suitable for pg_restore.
-f dump	Output directory for dump files. The directory is created automatically and should not already exist.
--verbose	Detailed output during restore.

Set Up Logical Replication

On the publisher, create a publication for a schema:

CREATE PUBLICATION pub_myschema FOR TABLES IN SCHEMA myschema;

Or create a publication for selected tables:

CREATE PUBLICATION <pub_myschema> FOR TABLE table1, table2;

On the subscriber, create the subscription:

CREATE SUBSCRIPTION sub_myschema
CONNECTION 'host=SOURCE_IP port=5432 dbname=yourdb user=youruser password=yourpassword'
PUBLICATION pub_myschema;

If PostgreSQL returns this warning:

WARNING: out of background worker slots
HINT: You might need to increase max_worker_processes.

Update the configuration and restart PostgreSQL.

Verify Replication Status

On the subscriber, check subscription status:

SELECT * FROM pg_subscription;

When a subscription is created, PostgreSQL may generate temporary replication slots per table. These slots are short-lived and are dropped automatically after data copy completes. The primary replication slot on the publisher must remain active.

After replication completes, lag should be zero or close to zero if the publisher is under high load:

SELECT
  (SELECT slot_name FROM pg_replication_slots s WHERE s.active_pid = r.pid) AS slot_name,
  pid,
  client_addr,
  state,
  sync_state,
  pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag,
  pg_wal_lsn_diff(sent_lsn, flush_lsn) AS flush_lag,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag
FROM pg_stat_replication r;

Remove Publication and Subscription

After successful replication and data verification, remove the subscription and publication.

On the subscriber:

DROP SUBSCRIPTION sub_myschema;

On the publisher:

DROP PUBLICATION pub_myschema;

Transfer Sequences

Logical replication does not copy sequences, so sequence values must be transferred manually.

On the publisher, get the current sequence values from myschema:

CREATE TEMP TABLE IF NOT EXISTS "__tmp_sequences__" (s TEXT);
TRUNCATE TABLE "__tmp_sequences__";

DO $$
DECLARE
  name TEXT;
  value BIGINT;
BEGIN
  FOR name IN (
    SELECT quote_ident(n.nspname) || '.' || quote_ident(c.relname)
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = 'S' AND n.nspname = 'myschema'
  )
  LOOP
    EXECUTE 'SELECT last_value FROM ' || name INTO value;
    INSERT INTO "__tmp_sequences__" (s)
    VALUES ('SELECT setval(''' || name || ''', ' || value::TEXT || ');');
  END LOOP;
END
$$;

SELECT * FROM "__tmp_sequences__";

On the subscriber, execute the generated setval statements.

Delete Schema on Publisher

After cutover and validation, delete the schema from the publisher if required:

DROP SCHEMA myschema CASCADE;

Downtime and Rollback

Downtime depends on the selected migration strategy. Communicate the planned downtime to internal stakeholders and, where applicable, end users.

Prepare a rollback strategy that includes restoring database backups and reinstating the original environment. Test the rollback procedure in a non-production environment before migration.

Testing and Validation

After migration, test thoroughly.

Test area	What to validate
Data integrity	Compare row counts and use checksums to confirm data transferred correctly.
Performance	Run queries and monitor latency, throughput, and resource utilization.
Application behavior	Test dependent services and integrations against the new database.

Conclusion

A PostgreSQL migration depends on planning, preparation, and validation. Before declaring the migration complete:

• Assess the current environment and dependencies.
• Choose a migration strategy based on database size and downtime tolerance.
• Keep reliable backups and protect migration data at rest and in transit.
• Migrate required database and network configuration.
• Validate data integrity, performance, and application compatibility.
• Optimize performance, monitoring, and disaster recovery after migration.