Migrate to Aiven for PostgreSQL® with
We recommend to migrate your PostgreSQL® database to Aiven by using aiven-db-migrate.
Aiven for PostgreSQL® supports the same tools as a regular PostgreSQL database, so you can migrate using the standard
The pg_dump tool can be used to extract the data from your existing PostgreSQL database and pg_restore can then insert that data into your Aiven for PostgreSQL database. The duration of the process depends on the size of your existing database.
During the migration no new data written to the database is included. You should turn off all write operations to your source database server before you run the
You can keep the write operations enabled, and use the steps to try out the migration process first before the actual migration. This way, you will find out about the duration, and check everything works without downtime.
You can use the following variables in the code samples provided:
Service URI for the source PostgreSQL connection
Local Folder used to store the source database dump files
Name of the destination Aiven for PostgreSQL service
Aiven plan for the destination Aiven for PostgreSQL service
Service URI for the destination PostgreSQL connection, available from the Aiven Console
Perform the migration#
If you don’t have an Aiven for PostgreSQL database yet, run the following command to create a couple of PostgreSQL services via Aiven CLI substituting the parameters accordingly:
avn service create -t pg -p DEST_PG_PLAN DEST_PG_NAME
Aiven for PostgreSQL allows you to easily switch between different service plans, but for the duration of the initial migration process 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. Once migrated, you can scale the plan size up or down as needed.
Aiven automatically creates a
defaultdb database and
avnadmin user account, which are used by default.
pg_dumpcommand substituting the
SRC_SERVICE_URIwith the service URI of your source PostgreSQL service, and
DUMP_FOLDERwith the folder where you want to store the dump in:
pg_dump -d 'SRC_SERVICE_URI' --jobs 4 --format directory -f DUMP_FOLDER
--jobs option in this command instructs the operation to use 4 CPUs to dump the database. Depending on the number of CPUs you have available, you can use this option to adjust the performance to better suit your server.
If you encounter problems 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 create the ownership hierarchy after the data is migrated.
pg_restoreto load the data into the new database:
pg_restore -d 'DEST_SERVICE_URI' --jobs 4 DUMP_FOLDER
If you have more than one database to migrate, repeat the
pg_restore steps for each database.
Switch the connection settings in your applications to use the new Aiven database once you have migrated all of your data.
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.
Connect to the target database via
ANALYZEcommand to apply proper database statistics for the newly loaded data:
If you got this far, then all went well and your Aiven for PostgreSQL database is now ready to use.
When migrating PostgreSQL databases to Aiven via
pg_restore you could encounter errors like:
could not execute query: ERROR: must be owner of extension <extension>
For example, the following
pg_restore error appears quite commonly:
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension <some_extension>
This type of error is often related to the lack of superuser-level privileges blocking non-essential queries.
A typical example is due to failing
COMMENT ON EXTENSION queries trying to replace the documented comment string for an extension. In such cases, the errors are harmless and can be ignored. Alternatively, use the
--no-comments parameter in
pg_restore to skip these queries.
pg_restore offers similar
--no-XXX options to switch off other, often unnecessary restore queries. More information is available in the PostgreSQL documentation.
Poor performance after migration#
Whenever you load data with the
pg_restore or similar tools, it is recommended to run
VACUUM ANALYZE on your entire database to collect new statistics. The database will not have up-to-date statistics on the tables and indexes without these operations. In turn, this may lead to poor query plans and poor database performance.
Generally, the Aiven platform automatically runs
ANALYZE on your service after performing a major version upgrade in order to ensure the statistics are up-to-date.
For more information about
ANALYZE, you may see the official SQL analyze documentation.