How to get data from tables in mysql database into a postgres schema?

I’ve had to do a bit of learning and tinkering to figure out how to do this. I now understand why ETL tools and consultants for data migrations and translations are in demand. I’ll even allow use of proprietary GUI based ETL tools.

Anyway the backstory is I created a django project initially for MySQL but needed to change to Postgres later on. I had been using a mysql instance and wanted to migrate the data to the new postgres db.

Converting to Postgres from MySQL

The best tool to use, in my opinion, for a straight conversion of a source db to postgres is pg_loader.

On the source server I had to install postgres for ease of not having to setup a remote connection and then it is a case of:

sudo apt install pg_loader

Then create the target db in postgres and migrate

$ createdb f1db
$ pgloader mysql://root@localhost/f1db pgsql:///f1db

So we now have a postgres DB which would be suitable for most people.

But I already had a new db instance running and needed to import the data.

Dumping the Postgres DB

An important thing to remember here is that the db already running has a publicschema. This db also has a public schema. Another thing is moving data between databases is  difficult because with postgres you can only connect to one database at a time.

So we should rename the schema in the db we have just converted to something else.

alter schema public rename to original_public;
create schema public;

Then we can import / restore it into the same database we have already running and can copy data between the schemas easily.

Dumping the DB

Use pg_dump to  export just the new schema:

pg_dump -F c -b -v -f --schema=original_public mydb.backup mydb

Restoring the DB

We need to transport the dump to the new server with scp (not in this tutorial).

Once it is accessible, restore the table into the existing db:

pg_restore --dbname=instance_db mydb.backup

Now we have both schemas in the same database.

Moving data between tables

If the tables have the same structure we can easily copy data from one schema to another with a query.

psql
insert into public.the_table select * from original_public.the_table;

and that is that.

Serial Sequencing

There was however an issue when it came to the auto increment fields in my case just primary keys. They were not set at the correct value and the pp broke because of this.

So I manually went and check which number it should be on and updated the relevant fields because I did not have the knowledge.

The knowledge I am referring to is a management command that does this for you called sqlsequencereset

This would have saved me 10 minutes.