Aiven PostgreSQL supports all the same tools as a regular PostgreSQL database does. This allows you to use the very same tools for migrating to Aiven PostgreSQL that you'd use with regular PostgreSQL.

Aiven Database

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

Aiven PostgreSQL allows you to easily switch between different plan sizes but at least for the duration of the dumping process it usually makes sense to conservatively pick a plan size that is large enough for the task. This allows you to limit the downtime during the migration process by having a sufficiently powerful PostgreSQL plan.

Users & databases in the target database

If just using the defaultdb that Aiven creates for you automatically is not sufficient you can create more databases and database users. The default user account avnadmin can also directly create databases but for convenience's sake we recommend creating them through our Web console. To create these you can go to the web console and create them through the "User & Databases" tab. 

Loading data to your database

First of all we need to dump the data out of your pre-existing PostgreSQL database. The mechanism we're using to do will take time roughly in proportion to your existing database size. During this time period any new writes that happen during the dumping process will not be included so in order to get all your data you should turn off all the writes to the old database server before starting the dumping process.

Usually it pays to try this out first as a cold run without turning off writes on your previous box and just running the dump concurrently. This gives you an upper bound of how much time will the dumping/reloading process probably take. It also helps you in practicing the actual operation when it's not causing downtime your customers.

To actually dump your database you need the PostgreSQL client tools to be of the same version or newer as on the server your migrating from:

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

Once dumped from the original server, you can simply load it into the database of your choosing with:

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

In case you have multiple databases that you want to migrate you can just simply repeat the process dumping/loading one database after another. The --jobs  option in the previous command means it uses 4 CPUs to dump and restore the database concurrently. If you have more or less CPUs than that you can adjust the performance to be better suited for your server.  Note that you might want to run pg_dump with the --no-owner  flag in case you run into issues with restoring your previous object ownerships to users that do not exist in your Aiven database. You can create the ownership hierarchy also afterwards.

Once done with the data loading you should switch your applications connection settings to use the new database in Aiven. When doing this remember that the user passwords will be different than on the server you migrated from. Remember to check the new values from the "Users & Databases" tab.

Another thing that is good to remember is to run the command ANALYZE  in order to get proper database statistics in place for the newly loaded data.

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

newdb=> ANALYZE;
Did this answer your question?