Migrate an ElephantSQL PostgreSQL® database to Aiven for PostgreSQL®

Use this guide to migrate your ElephantSQL database to an Aiven for PostgreSQL® database using the pgdump utility

Subscribe to RSS

This article addresses how to migrate an ElephantSQL (PostgreSQL®) database to an Aiven for PostgreSQL® database using pg_dump.

To migrate databases from your local computer or another cloud service, see Aiven for PostgreSQL® Migration.

Prerequisites

The migration process

To migrate an ElephantSQL database to Aiven for PostgreSQL, all we need to do is use pg_dump on the ElephantSQL database and pg_restore on the Aiven for PostgreSQL database.

About 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

Confirm your psql and pg_dump version numbers

We'll use our local machine's command line to perform the migration. It's important that the version of psql and pg_dump we have installed are at version 16 or later, or else the migration will fail.

To check the version of psql you have installed, use the following command in a Terminal:

psql --version

If you need to update to a newer version of psql, uninstall your current version and re-install a newer version. If you're using a package manager like Homebrew on Mac, be sure to specify the version number in the formulae:

brew install postgresql@16

To check the version of pg_dump, use the following command in a Terminal:

pg_dump --version

Upgrading psql should automatically upgrade pg_dump as well. If for some reason it doesn't, you can install libpq:

brew install libpq

Create an Aiven for PostgreSQL database and install extensions

Next, go to the Aiven Console and create a new Aiven for PostgreSQL service.

When the service is created, install any extensions you might need. Some ElephantSQL databases use the dict_xsyn and xml2 extensions by default.

Read how to install new extensions to your Aiven for PostgreSQL database.

Get connection data from source and target databases

Log into the ElephantSQL console.

Select the database to migrate and note the following information:

  • Server
  • Port (By default ElephantSQL uses port 5432 for all connections.)
  • User & Default database
  • Password

Next, log into the Aiven console and note the following information:

  • Host
  • Port
  • Username
  • Password

Navigate to the Databases section of the Aiven console, and either:

  • Note the name of the default database, defaultdb, or
  • Create a new database that you want to migrate to and note that

Perform the migration using the command line

Open a Terminal window or command line, and use the following command to migrate your database:

PGPASSWORD=<ELEPHANTSQL_PASSWORD> pg_dump <ELEPHANTSQL_SERVER> -p <ELEPHANTSQL_PORT> -U <ELEPHANTSQL_USER> -d <ELEPHANTSQL_DATABASE> | psql -h <AIVEN_HOST> -p <AIVEN_PORT> -U <AIVEN_USER>  <AIVEN_DATABASE>

Where:

  • <ELEPHANTSQL_SERVER> is the ElephantSQL server we noted avove
  • <ELEPHANTSQL_PORT> is the ElephantSQL database's port, or 5432.
  • <ELEPHANTSQL_USER> is the user name for the ElephantSQL database.
  • <ELEPHANTSQL_DATABASE> is the database name for the ElephantSQL database. In most cases, this is the same as the user name.
  • <ELEPHANTSQL_PASSWORD> is the password for the ElephantSQL database.
  • <AIVEN_HOST> is the Host for the Aiven for PostgreSQL database.
  • <AIVEN_PORT> is the port for the Aiven for PostgreSQL service.
  • <AIVEN_USER> is the user name for the Aiven for PostgreSQL database. The default user is avnadmin.
  • <AIVEN_DATABASE> Is the database name to migrate to the Aiven for PostgreSQL service. The default is defaultdb.

You should be prompted for the password to your Aiven service. Copy and paste it into the command line and hit Enter.

Leave the process to complete. Depending on the size of your database, this might take some time.

User `USERNAME` does not exist

You might receive a warning in the command line "role "USERNAME" does not exist`. This is because we did not recreate roles in Aiven for PostgreSQL before migrating, as Aiven has a default user that we can use. If you need to, create any users you might need in Aiven for PostgreSQL before migrating.

Verify the results using PgAdmin

  1. Open PgAdmin and select Add New Server. Enter the following:

    • In General, give the new server a name such as Aiven for PostgreSQL server.
    • In Connection:
      • Host name/address: The Host of your Aiven for PostgreSQL service
      • Port: The port number your Aiven for PostgreSQL service uses. Note that Aiven services do not use the default 5432.
      • Maitenence database: The Database you migrated to (the default is defaultdb)
      • Username: The Username you used to connect to your database (the default is avnadmin)
      • Password: The Password assosciated with your username.

  2. Click Save to save your connection information and connect to the database.

  3. Navigate to your database and use the Query tool to run a query against your data. The query that makes sense for you to run depends on the data in your database.

Next steps

As far as the database migration goes, that's it! The next steps are to ensure that all your services are now pointing at your new Aiven for PostgreSQL database. Read our connect to a PostgreSQL database docs for more information.

Aiven for PostgreSQL also includes lots of one-click integrations with popular monitoring services, metrics services and more. See What you can integrate with.

Aiven also offers a number of different data services, from streaming to storage to analysis. Learn more about the rest of the Aiven Platform and see what you can do with your data!