The preferred approach to migrating a database to Aiven for PostgreSQL is to use the built-in migration tool. However, Aiven for PostgreSQL supports the same tools as a regular PostgreSQL database, which allows you to use those same tools for migrating to Aiven for PostgreSQL.

The method described here uses the pg_dump tool to extract the data from your existing PostgreSQL database and then add that data to your Aiven for PostgreSQL database with the pg_restore tool. The duration of this process depends on the size of your existing database. While the migration is in progress, no new data written to the database is included, so we recommend turning off all write operations to your existing database server before you run pg_dump.

You can also use these steps to do a trial run without turning off write operations for your current database server before you perform the actual migration. This gives you a better idea of how much time to reserve for the migration, and to make sure that the process works without causing any downtime.

To start, you need an Aiven PostgreSQL database. To create one, you can follow the instructions in our Aiven PostgreSQL Getting Started guide.

Aiven for PostgreSQL allows you to switch between different service plans, but at least for the duration of the initial migration process when using pg_dump, we recommend that you choose a service plan that is large enough for the task. This allows you to limit downtime during the migration process.

Aiven automatically creates a defaultdb database and avnadmin user account, which are used by default. If necessary, you can create additional databases and database users in the Aiven web console, on the Databases and Users tabs of your Aiven for PostgreSQL service. 

Note: Make sure that the PostgreSQL client tools that you use are of the same version or newer as on your current database server.

To migrate your database:

  1. Run the pg_dump command using the details for your Aiven for PostgreSQL service. For example:

    pg_dump -d 'postgres://avnadmin:secret@demopg.demoprj.aivencloud.com:22094/defaultdb?sslmode=require' --jobs 4 --format directory -f mydefaultdbdumpdir

    The --jobs option in this command instructs the operation to use 4 CPUs to dump the database. Depending on the number of CPUs that you have available, you can use this option to adjust the performance to better suit your server.

    Note: If you have issues with restoring your previous object ownerships to users that do not exist in your Aiven database, use the --no-owner option in the pg_dump command. You can then create the ownership hierarchy after the data is migrated.

  2. Run pg_restore to load the data into the new database. For example:

    pg_restore -d 'postgres://avnadmin:secret@demopg.demoprj.aivencloud.com:22094/newdb?sslmode=require' --jobs 4 mydefaultdbdumpdir

  3. If you have more than one database to migrate, repeat the pg_dump and pg_restore steps for each database.

  4. Switch the connection settings in your applications to use the new Aiven database once you have migrated all of your data.
    Note: The user passwords are different from those on the server that you migrated from. Go to the Users tab for your service in the Aiven web console to check the new passwords.

  5. Run the ANALYZE command to apply proper database statistics for the newly loaded data:

    psql 'postgres://avnadmin:secret@demopg.demoprj.aivencloud.com:22094/newdb?sslmode=require'

    newdb=> ANALYZE;

Did this answer your question?