I had a unique problem recently. I was refactoring a web app, and I wanted to try PostgreSQL. The previous developer had written the database in MariaDB.

Let me say that if you haven't used PostgreSQL before, check it out; it's pretty cool. If you're using PostgreSQL for the first time, you probably don't want to do what I did and convert an existing database over. It isn't a lot of fun.

Well, it isn't a lot of fun in my instance, anyway. My unique circumstance was that my new database PostgreSQL server didn't have direct access to the existing MariaDB server, which meant I had to do a SQL dump and then import the data using Pgloader.

Getting the Data Out of MariaDB

The best way that I found was to export each table to a TSV. This wasn't too onerous for me because it's a small database with only two tables.

mysql -u user -p secret -e "SELECT \* FROM plants" > plants.tsv

mysql -u user -p secret -e "SELECT \* FROM svc_events" > svc_events.tsv

Like my super-secure username and password?

Installing Pgloader

The next rabbit hole I went down is trying to install Pgloader from source. Save yourself the headache. In MacOS:

brew install pgloader

Importing Tables to PostgreSQL

Next you'll want to create a new database from the PostgreSQL command line:

CREATE DATABASE servicelog;

Then collect your SQL dumps into a folder and create a config file for each table you need to import. I had plantscsv.load:

LOAD CSV
    FROM 'plants.tsv' WITH ENCODING us-ascii
    HAVING FIELDS
    (
        plant_id, plant_name
    )
    INTO postgresql://username:password@localhost:5432/servicelog
    TARGET TABLE plants
    TARGET COLUMNS
    (
        plant_id, plant_name
    )
    WITH truncate,
        skip header = 1,
        fields optionally enclosed by '"',
        fields escaped by backslash-quote,
        fields terminated by '\t'

    SET work_mem to '32 MB', maintenance_work_mem to '64 MB'

    BEFORE LOAD DO
     $$ drop table if exists plants; $$,
     $$ create table plants (
        plant_id integer primary key,
        plant_name varchar(50)
        );
$$;

And svc_eventscsv.load:

LOAD CSV
    FROM 'svc_events.tsv' WITH ENCODING us-ascii
    HAVING FIELDS
    (
        service_id, plant, svc_date, contractor, description, proj_repair, proj_no, report_compl, log_upload, req_no, compl_date
    )
    INTO postgresql://username:password@localhost:5432/servicelog
    TARGET TABLE svc_events
    TARGET COLUMNS
    (
        service_id, plant, svc_date, contractor, description, proj_repair, proj_no, report_compl, log_upload, req_no, compl_date
    )
    WITH truncate,
        skip header = 1,
        fields optionally enclosed by '"',
        fields escaped by backslash-quote,
        fields terminated by '\t'

    SET work_mem to '32 MB', maintenance_work_mem to '64 MB'

    BEFORE LOAD DO
     $$ drop table if exists svc_events; $$,
     $$ create table svc_events (
        service_id integer primary key GENERATED BY DEFAULT AS IDENTITY,
        plant integer,
        svc_date date,
        contractor varchar(100),
        description varchar(500),
        proj_repair varchar(50),
        proj_no varchar(25),
        report_compl varchar(5),
        log_upload varchar(200),
        req_no varchar(25),
        compl_date date
        );
$$;

Wrapup

That should be most of it. Constraints like foreign keys isn't supported in the CSV import by Pgloader that I could find. I was able to add that constraint with:

\c servicelog;
ALTER TABLE svc_events ADD CONSTRAINT plants_fk FOREIGN KEY (plant) REFERENCES plants(plant_id);

It does support those if you're doing a direct transfer from MariaDB to PostgreSQL. Not really an issue for me since the database is so small. I think if it were bigger, I would have found a way to connect directly to the legacy database.