Convert SQL from MySQL to PostGreSQL pgloader?

Parthian

I've a very old database. Probably mysql.
Hoping to import it into Postgresql for Django.
It starts.
`-- phpMyAdmin SQL Dump
-- version 4.4.15.10
-- https://www.phpmyadmin.net

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8mb4 */;

CREATE TABLE IF NOT EXISTS "archives" (
"archive_id" int(11) NOT NULL,
"name" varchar(255) DEFAULT NULL,
"url" varchar(255) DEFAULT NULL,
"notes" text
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8;`

It did use backticks for table names etc but I globally replaced them while blundering around.
Tried Adminer Import (you never know) but loads of errors - no surprise.

Rumour has it that pgloader does the trick.
Any thoughts?
Is it available or a better way?

Ta.

sean

Parthian I've never used pgloader but if you want to give it a try then you can install it with the following steps:

mkdir -p ~/tmp ~/bin
export PATH=$HOME/bin:$PATH
cd ~/tmp
wget https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-7-x86_64/pgloader-3.6.2-1.rhel7.x86_64.rpm
rpm2cpio pgloader-3.6.2-1.rhel7.x86_64.rpm | cpio -idmv
mv usr/bin/pgloader ~/bin/

Parthian

Super. Installed.
Now have to figure it out. Oddly, it doesn't appear to read sql files but can read csv. Can't think why.
Perhaps I'll make myself a mysql database (mariadb). Import the sql into that. And then use pgloader to convert to postgresql (want to use PostGIS otherwise I'd just use the mariadb).

One for tomorrow.
Ta.

nick

Right, these may be of help in that regard,

https://github.com/dimitri/pgloader#usage
https://pgloader.readthedocs.io/en/latest/intro.html

Parthian

That actually worked!

Bit daft but I made a new mariadb. Imported using Adminer.

Then used pgloader thus

pgloader mysql://mariadbaseusername:password@opal5.opalstack.com/mariadbasename postgresql://postgresdbaseusername:password@opal5.opalstack.com/postgresdbname

Failed first time as there was a enum field in a table that somehow was saved with a null value. Fixed that in Adminer and rerun of pgloader worked.

It did create a new Schema in postgres - which wasn't immediately obvious (I suspect one of the many options could have stopped that). So my tables weren't in the place I expected them but Adminer has a Move process that worked well.

Importing into Django ongoing. https://docs.djangoproject.com/en/4.0/howto/legacy-databases/
(env) $ python manage.py inspectdb > mergedmodels.py

But watch out as django tables tend to get the appname_tablename - I renamed all my new tables to that structure and not sure I should have. But got everything into Django Admin.

Still to worry about AutoIncrements (nextval) type stuff. That always goes wrong with database imports I find.

Cheers.