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.
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
This would have saved me 10 minutes.