Postgresql – Migrating PostgreSQL DB from AWS to Alibaba Cloud

amazon-rdsmigrationpostgresql

I'm migrating PostgreSQL RDS instance from AWS located in Frankfurt to the RDS instance in Alibaba Cloud also located in Frankfurt.
I use Data Transmission tool from Alibaba Cloud. It connects both DB fine, there are no errors on the connections.
The only problem is with versioning:

I've tried downgrading the source DB, but it seems to be impossible. Does anybody have ideas how it could be done?

Best Answer

You can downgrade postgresql database from the command line.

Simply extract the database with a

pg_dump --host=... --port=... --dbname=.... --user=.... >dump

command. Then you will have your database in the file dump in your local directory.

Then insert the dump into your new database with the command

psql --host=... --port=... --dbname=.... --user=.... <dump

It won't work at first. Typically, you will have syntax errors or similar. But get a good text editor, and edit it all out, until it won't be clean.

Related Solutions

Postgresql – AWS RDS PostgreSQL dump/restore – syntax error

A pg_dump output with the -Fc (custom) is not a SQL script, it's a compressed archive that only pg_restore is able to handle.

You may either pass the dump file to pg_restore and pipe its output to psql (often done that way and close to what you tried):

pg_restore /home/jboss/proc_dump_jan15.dump | psql  --host=blahrds [other options]...

...or use the -d,-h,... and other options of pg_restore to have it connect to the database and skip the psql invocation.

See pg_restore manpage for 9.3 for details.

Nb. the EC2 database is PG 9.0 and the RDS database is PG 9.3.5 but I assume this is ok as the dump/restore will deal with the migration?

The recommended method is to use pg_dump 9.3 against the 9.0 database to create the dump. The reasoning is that newer versions always know how to extract older databases such that they will import flawlessly into their version. The opposite is not true, since obviously at the time when pg_dump 9.0 is released, the authors don't know what compatibility problems future versions might bring.

Postgresql – Migrating PostgreSQL files to new server

Install the latest minor release of the same PostgreSQL major version - e.g. if you were using 9.3.1 before, install 9.3.9. Make sure it's the same architecture - i.e. if you were using a 32-bit PostgreSQL before you must install 32-bit PostgreSQL now.

Stop the service using the services control panel (services.msc).
Delete %PROGRAMFILES%\PostgreSQL\9.3\data.
Copy the old data directory to that location.
Get properties on the data directory and in the security tab recursively grant full control to NETWORKSERVICE.
Start the service using services.msc
immediately set up backups.

Best Answer

Related Solutions

Postgresql – AWS RDS PostgreSQL dump/restore – syntax error

Postgresql – Migrating PostgreSQL files to new server

Related Question