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?
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.
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:
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.
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).
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.
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.
Using the Aiven migration instructions for ElephantSQL, I was running into “‘PGPASSWORD’ is not recognized as an internal or external command, operable program or batch file” errors.
I’ve followed your steps and everything has worked up to the third step. When I run psql -h I get a pile of errors:
ERROR: unrecognized configuration parameter "transaction_timeout"
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
ERROR: must be able to SET ROLE "postgres"
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
ERROR: permission denied to create extension "dict_xsyn"
HINT: Must be superuser to create this extension.
ERROR: extension "dict_xsyn" does not exist
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
ERROR: permission denied to create extension "xml2"
HINT: Must be superuser to create this extension.
ERROR: extension "xml2" does not exist
SET
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE TABLE
ERROR: role "pzstvlyo" does not exist
CREATE SEQUENCE
ERROR: role "pzstvlyo" does not exist
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 1942
COPY 67
COPY 96
COPY 0
COPY 220
COPY 1524
COPY 6
COPY 3047
COPY 87
COPY 12
COPY 3084
COPY 2
setval
--------
603
(1 row)
setval
--------
2043
(1 row)
setval
--------
1
(1 row)
setval
--------
1433
(1 row)
setval
--------
1579
(1 row)
setval
--------
6
(1 row)
setval
--------
3107
(1 row)
setval
--------
87
(1 row)
setval
--------
12
(1 row)
setval
--------
4582
(1 row)
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
GRANT
Any ideas how to solve this? The clock is ticking on the ElephantSQL sunset.
Hi there, can we hire you to help us migration from ElephantSQL? We have a small database that we need to port ASAP. It doesn’t look like I can DM you.