How-To

PostgreSQL Read Replicas

PostgreSQL read replicas provide improved performance by distributing read queries, thus reducing the load on the primary database. They also enhance high availability, scalability, and disaster recovery. Additionally, replicas can be placed in different geographical zones to further improve availability and reduce latency.

Promoting the Replica to Master

Step 1: Get details of the replica service to find the integration ID

exo api get-dbaas-service-pg -z at-vie-1 rocket

Step 2: Check Integration Status

exo api get-dbaas-integration <integration_id>

Step 3: Promote Replica to a master Database (Remove Replication)

exo api delete-dbaas-integration <integration_id>

NOTE -

  • Customers can create a read replica on a startup plan, regardless of the main cluster’s plan.
  • The read replica node is vertically scalable.
  • It can be horizontally scaled by promoting it to the primary, becoming a standalone service detached from the main cluster.

Performing data migration

To perform the migration, you can either use the portal or the Exoscale CLI, depends on when the action it’s performed and if the service receiving the migration already exists or not.

Run the following command to create a new service and start the migration immediately:

exo dbaas create pg business-4 target-postgresql-service-name --pg-ip-filter 0.0.0.0/0 --pg-migration-host x.x.x.x --pg-migration-port 5432 --pg-migration-username xxxxx --pg-migration-password xxxxx --pg-migration-method replication --pg-migration-ssl false --pg-migration-dbname xxxxx
  • target-PostgreSQL-service-name : Target DBaaS Service Name
  • –pg-migration-host : the IP address of your source PostgreSQL server
  • –pg-migration-port : the port on which your source PostgreSQL server is reachable
  • –pg-migration-username : username on your source PostgreSQL server
  • –pg-migration-password : password on your source PostgreSQL server
  • –pg-migration-method : the chosen method for migration, which could be replication or dump method.

In case the PostgreSQL service is already created, you can run the command below:

exo dbaas update --pg-migration-host x.x.x.x --pg-migration-port 5432 --pg-migration-username xxxxx --pg-migration-password xxxxx --pg-migration-method dump target-postgresql-service-name

To check status of our migration we can run command:

exo dbaas migration status target-postgresql-service-name

And if we want to stop the running migration:

exo dbaas migration stop target-postgresql-service-name

NOTE - Ensure you are using at least version 1.53.0 of the Exoscale CLI.

Migrating to Managed PostgreSQL

NOTE - Due to the fact that migrations always involve interaction with third party services running outside of Exoscale, only best effort support is provided on all migrations.

Migrate your PostgreSQL database to our Managed PostgreSQL service. Two methods are supported: Replication and pg_dump.

The migration first attempts to use the replication method, and if it fails, it uses pg_dump.

The replication method is preferred for production databases because it allows migrating data with minimal downtime. The pg_dump method is slower and will migrate one database at a time.