Migrating PostgreSQL from ElephantSQL

PostgreSQL
1

Hi all. I am an ElephantSQL user and now trying to migrate my database from ElephantSQL to Aiven since their services are being discontinued.

I have been following the guide to migrate to Aiven: https://aiven.io/developer/elephantsql-aiven-migration?utm_source=website&utm_medium=referral&utm_campaign=84codes-elephantsql

I have set up an Aiven for postgres instance and tried to run the command in the guide:

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>

However, this does not work for windows command prompt.

I tried running: set PGPASSWORD=<xyz> and then running the pg_dump | psql command but then I got the following error:

psql: error: connection to server at “” (209.38.167.133), port 10306 failed: FATAL: password authentication failed for user “avnadmin”
connection to server at “” (209.38.167.133), port 10306 failed: FATAL: no pg_hba.conf entry for host “92.11.70.1”, user “avnadmin”, database “defaultdb”, no encryption

Does anyone know why this happened? And does anyone know how to successfully run this migration when using windows?

2

I notice SSL is not mentioned, but it is required by the server, and the lack of it is mentioned in the error response. Perhaps try a connection string, like: psql 'postgres://avnadmin:YOUR_PASSWORD@YOUR_HOST:10306/defaultdb?sslmode=require'

It’s possible that the documentation requires updating if that works.

3

Hi, I just recently finished migrating from ElephantSQL on Windows myself. After some trial and error, I found more success separating the two pg_dump and psql commands.

So instead of running the one singular command as listed in the documentation, I did this instead inside the command prompt:

  1. Set source database password and dump the database from ElephantSQL. This will create an .sql file in the directory where you are running the terminal
    (In my case C:\Users\[USER]>.)
    Set [FILENAME] to whatever you want.
  • set PGPASSWORD=<ELEPHANTSQL_PASSWORD>
  • pg_dump -h <ELEPHANTSQL_SERVER> -p <ELEPHANTSQL_PORT> -U <ELEPHANTSQL_USER> -d <ELEPHANTSQL_DATABASE> > [FILENAME].sql

  1. Inside the .sql file, through an IDE, replace all instances of the <ELEPHANTSQL_USER> with the <AIVEN_USER>. This just makes sure that the tables in the database are referencing the right user once migrated to Aiven.
    (There may be a better way to do this, but this is what I did).

  2. In the command prompt, set target database password and restore dump with the following commands.
    Note: I haven’t figured out a solution for the dict_xsyn and xml2 extensions, and these commands don’t address that. However, if you don’t need the extensions, their exclusion shouldn’t interfere with any operations.

  • set PGPASSWORD=<AIVEN_PASSWORD>
  • psql -h <AIVEN_HOST> -p <AIVEN_PORT> -U <AIVEN_USER> -d <AIVEN_DATABASE> < [FILENAME].sql
  1. To test the source database connection, you can run these commands in the terminal.
  • Connect to the database (if prompted, enter your <AIVEN_PASSWORD>)
    psql -h <AIVEN_HOST> -p <AIVEN_PORT> -U <AIVEN_USER> -d <AIVEN_DATABASE>
  • List tables with \dt
  • Generate some entries form any of the tables to see if they carried over.
    SELECT * FROM your_table LIMIT 10;

If the connection was successful, and the data did carry over, then all you have to do from here is to update the connection string on whatever platform you’re hosting your site (Azure, render.com, etc.) so that they use the Aiven credentials instead of ElephantSQL.