Operation

Restricting connections from the internet

By default, Exoscale DBaaS are not accessible from the whole internet. Data does not transmit in clear over the network as it is SSL encrypted by default and authenticated.

To allow incoming connections to your database service, you can add a filter allowing:

  • just one IP address,
  • a network range,
  • or a combination of IP address and network range

To do this, update your service or create it with the IP filter, which is a comma separated list of CIDRs:

exo dbaas update -z de-fra-1 test-pg --pg-ip-filter=a.b.c.d/32,w,x,y,z/24

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.

Prerequisites

  • A target PostgreSQL service: this can be a new or an existing service.
  • The hostname, port and password of the source PostgreSQL service.
  • The source PostgreSQL service can be secured with SSL/TLS.
  • Publicly accessible source PostgreSQL service (for that, you might need to set bind parameter to a publicly reachable network interface in your PostgreSQL configuration). Any firewalls need to be open on port TCP 5432 if using the default to allow traffic from the source to the target servers. Also pay attention to other protections, that can restrict connections: SELinux should be disabled or set in permissive mode for the duration of the migration on the source server.
  • Migration using replication will not work for PostgreSQL databases with capital letters If you can not rename this database please check Aiven set up logical replication to replicate your data
  • Access to a superuser role on the source cluster.
  • wal_level setting to logical on the source cluster: This setting ensures that the necessary information for logical replication, such as individual data modifications and the associated metadata, is recorded in the WAL; it allows an accurate data replication to the target cluster during the migration process.

Disabling all constraints/triggers using aiven_extras

NOTE - If you are migrating data and before deciding on the fully manual approach using aiven_extras consider checking if logical replication suits better your use case. The approach outlined below is done automatically via logical replication.

Access to super-user functionality is restricted in order to have a clear demarcation line for service availability. Disabling triggers is one of them.

Fortunately an alternative is available using aiven_extras plugin.

Step 1: In the database shell install the aiven_extras extension with

CREATE EXTENSION aiven_extras CASCADE;

Step 2: Disable constraints with:

SELECT aiven_extras.session_replication_role('replica');

Step 3: Import your data or another task that requires disabled constraints

Step 4: Re-enable triggers

SELECT aiven_extras.session_replication_role('origin');

Keep in mind that as all constraints get disabled there’s a risk of creating orphaned records or inconsistent data.

Changes made without constraints can affect other transactions in progress, leading to data anomalies, so ideally should be done when users don’t insert data into the database.

Re-enabling constraints might trigger a check of all rows, which can be resource-intensive for large tables.