Migrate PostgreSQL® databases to Aiven using the console
Migrate PostgreSQL databases to the Aiven platform using the Aiven Console.
For the CLI method using aiven-db-migrate
, see
Migrate to Aiven for PostgreSQL® with aiven-db-migrate.
About migrating via console
The console migration tool enables you to migrate PostgreSQL databases to managed PostgreSQL clusters in your Aiven organization. You can migrate the following:
- Existing on-premise PostgreSQL databases
- Cloud-hosted PostgreSQL databases
- Managed PostgreSQL database clusters on Aiven.
With the console migration tool, you can migrate your data using one of these methods:
- Continuous migration method (default and recommended)
- One-time snapshot method (
pg_dump
).
Continuous migration
The continuous migration method is used by default in the console. The continuous migration keeps the source database operational during the migration. This method uses logical replication, which enables data transfer not only for the data that has already been there in the source database when triggering the migration but also for any data written to the source database during the migration.
Before you use the logical replication, make sure you know and understand all the restrictions it has. For details, see Logical replication restrictions.
Using the continuous migration requires either superuser permissions or
the aiven_extras
extension installed on the source database.
How to verify that you have superuser permissions.
Use psql
to run the \du
command:
\du
Role name | Attributes | Member of
----------+------------------------------------------------------------+-----------------------------------------
_source_db | Superuser, Replication | {}
example | Create role, Create DB, Replication, Bypass RLS | {pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Identify your role name in the Role name
column and check if it has
the Superuser
attribute assigned in the Attributes
column. If not,
request it from your system administrator.
aiven_extras
.If you don't have superuser permissions, but you still want to use the
continuous migration, you can install the aiven_extras
extension on
the source database using the following command:
CREATE EXTENSION `aiven_extras` CASCADE;
Dump migration
pg_dump
is a point-in-time snapshot. The data written to the source
database during the migration process (after initiating the dump) is not
migrated to the target database. When you start a dump migration, make
sure no data is written to the source database by the time the dumping
process is over.
When you trigger the migration setup in the console and initial checks
detect that your source database does not support the logical
replication, you are notified about it via wizard. To continue with the
migration, you can select the alternative pg_dump
migration method in
the wizard.
aiven_extras
? Migrate using the dump method.Without superuser permissions or aiven_extras
installed, you cannot
use the logical replication and migrate in a continuous manner. In that
case, you can migrate your database using the dump method if you have
the following permissions:
- Connect
- Select on all tables in the database
- Select on all the sequences in the database
For the instruction on how to perform a dump, skip a few sections that follow and go straight to Migrate a database.
Prerequisites
To use the default continuous migration method in the console:
- Have the logical replication enabled on your source database
either with superuser permissions or the
aiven_extras
extension. - The source database's hostname or IP address must be accessible from the public Internet.
- Collect the following source database's credentials and
reference data:
- Public hostname or connection string, or IP address used to connect to the database
- Port used to connect to the database
- Username (for a user with superuser permissions)
- Password.
- Firewalls protecting the source database and the target databases need to be open to allow the traffic and connection between the databases (update or disable the firewalls temporarily if needed).
Pre-configure the source
-
Allow remote connections on the source database.
Ensure your database allows all remote connections by using
psql
to run the following query:SHOW listen_addresses;
If enabled, you can expect the following output (with
listen_addresses
set to*
):listen_addresses
-----------
*
(1 row)If the command line returns something different, enable remote connections for your database with the following query:
ALTER SYSTEM SET listen_addresses = '*';
-
Change your IPv4 local connection to
0.0.0.0/0
to allow all incoming IP addresses.Find the
pg_hba.conf
configuration file using the following query:SHOW hba_file;
Open
pg_hba.conf
in a text editor of your choice, for example, Visual Studio Code.code pg_hba.conf
Under
IPv4 local connections
, find and replace the IP address with0.0.0.0/0
.# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::/0 md5For more details on the configuration file's syntax, see The pg_hba.conf File.
-
Enable the logical replication.
For cloud-hosted databases, the logical replication is usually enabled by default, while databases hosted on-premises can have the logical replication not enabled.
Check that the logical replication is enabled using
psql
to run the following query:SHOW wal_level;
Expected output if enabledwal_level
-----------
logical
(1 row)If the command prompt returns something different, enable the logical replication in your database by setting
wal_level
tological
:ALTER SYSTEM SET wal_level = logical;
-
Set the maximum number of replication slots to a value that is equal to or greater than the number of databases in the PostgreSQL server.
Check the current status using the following query:
SHOW max_replication_slots;
You can expect the following output:
max_replication_slots
-----------
<number of slots, for example, 8>
(1 row)If
number of slots
is smaller than the number of databases in your PostgreSQL server, modify it using the following query:ALTER SYSTEM SET max_replication_slots = use_your_number;
where
use_your_number
stands for the number of databases in your server. -
Restart your PostgreSQL server using the following command:
sudo service postgresql restart
Migrate a database
- Log in to the Aiven Console.
- On the Services page, select the service where your target database is located.
- From the sidebar on your service's page, select Service settings.
- On the Service settings page, go to the Service management section, and select Import database.
- Guided by the migration wizard, go through all the migration steps.
Step 1: Configure
Get familiar with the guidelines provided in the PostgreSQL migration configuration guide window, make sure your configuration is in line with them, and select Get started.
Step 2: Validation
- To establish a connection to your source database, enter required
database details in the Database connection and validation
window:
- Hostname
- Port
- Database name
- Username
- Password
- Select the SSL encryption (recommended) checkbox.
- Optionally, exclude specific databases from the migration by entering their names (separated with spaces) into the Exclude databases field.
- Select Run check.
If your connection test returns information that you cannot migrate the
database using the logical replication due to the missing superuser
permissions or aiven_extras
extension, you can still migrate your data
using the dump method.
To start a dump, select checkbox Start the migration using a one-time snapshot (dump method).
Step 3: Migration
If all the checks pass with no error messages, you are ready to start the migration. Before you do that, be aware of its limitations and consequences.
It's recommended to migrate into an empty database. If you migrate into a populated database, colliding tables with primary keys are not affected, but tables without primary keys are appended. Check other limitations in Logical replication restrictions.
Trigger the migration by selecting Start migration in the Database migration window.
While the migration is in progress, you can take the following actions:
- Let it proceed until completed by selecting Close window, which closes the wizard. You can come back to check the status at any time on the Service settings page > the Service management section > Import database.
- Write to the target database.
- Discontinue the migration by selecting Stop migration. Although the data already migrated is retained, you cannot restart the stopped process. To continue with the migration, start a new migration process from scratch.
To avoid conflicts and replication issues while the migration is ongoing, take the following precautions:
- Do not write to any tables in the target database that are being processed by the migration tool.
- Do not change the replication configuration of the source database
manually. Do not modify
wal_level
or reducemax_replication_slots
. - Do not make database changes that can disrupt or prevent the connection between the source database and the target database. Do not change the listen address of the source database and do not modify or enable firewalls on the databases.
If you happen to get such a notification, investigate potential causes of the failure and try to fix the issues. When you are ready, trigger the migration again by selecting Start over.
Step 4: Close
As soon as the wizard communicates the completion of the migration, check if there's also information about the replication mode being active.
This information in the wizard means that your data has been transferred to Aiven, but some new data is still continuously being synced between the connected databases.
- If there is no replication in progress, select Close connection in the migration wizard to finalize the migration process. As a result, on the Service settings page > the Service management section > Import database, you'll see the Ready tag.
- If the replication mode is active, you can select Keep replicating. As a result, on the Service settings page > the Service management section > Import database, you'll see the Syncing tag, and you'll be able to see the status of the migration process by selecting Status update.
You have successfully migrated your PostgreSQL database into you Aiven for PostgreSQL service.