27 Articles tagged “Pgloader”

pgloader loads data into PostgreSQL. The new version is stable enough nowadays that it’s soon to be released, the last piece of the 3.1.0 puzzle being full debian packaging of the tool. The pgloader logo is a loader truck, just because. As you might have noticed if you’ve read my blog before, I decided that pgloader needed a full rewrite in order for it to be able to enter the current decade as a relevant tool.

A long time ago we talked about how to Import fixed width data with pgloader, following up on other stories still online at Postgres OnLine Journal and on David Fetter’s blog. Back then, I showed that using pgloader made it easier to import the data, but also showed quite poor performances characteristics due to using the debug mode in the timings. Let’s update that article with current pgloader wonders!

As presented at the PostgreSQL Conference Europe the new version of pgloader is now able to fully migrate a MySQL database, including discovering the schema, casting data types, transforming data and default values. Sakila is the traditional MySQL example database, in this article we’re going to fully migrate it over to PostgreSQL. What about switching to PostgreSQL, it’s easier than ever. Without further ado, here’s what happens when you ask pgloader to please migrate the whole thing over to PostgreSQL:

Last week I had the pleasure to present two talks at the awesome PostgreSQL Conference Europe. The first one was actually a tutorial about Writing & using Postgres Extensions where we spent 3 hours on what are PostgreSQL Extensions, what you can expect from them, and how to develop a new one. Then I also had the opportunity to present the new version of pgloader in a talk about Migrating from MySQL to PostgreSQL.

In our previous article about Loading Geolocation Data, we did load some data into PostgreSQL and saw the quite noticable impact of a user transformation. As it happens, the function that did the integer to IP representation was so naive as to scratch the micro optimisation itch of some Common Lisp hackers: thanks a lot guys, in particular stassats who came up with the solution we’re seeing now. The previous code was a straight rewrite of the provided documentation in Common Lisp.

As I’ve been mentionning in the past already, I’m currently rewriting pgloader from scratch in Common Lisp. In terms of technical debt that’s akin to declaring bankrupcy, which is both sad news and good news as there’s suddenly new hope of doing it right this time. Let’s dive into the python to common lisp rewrite Why rewriting pgloader? Several problems hinted me into doing something other than maintaining the code I had for pgloader.

About the only time when I will accept to work with MySQL is when you need help to migrate away from it because you decided to move to PostgreSQL instead. And it’s already been too much of a pain really, so after all this time I began consolidating what I know about that topic and am writing a software to help me here. Consider it the MySQL Migration Toolkit. A real classic that I couldn’t resist using here…

While making progress with both Event Triggers and Extension Templates, I needed to make a little break. My current keeping sane mental exercise seems to mainly involve using Common Lisp, a programming language that ships with about all the building blocks you need. Yes, that old language brings so much on the table When using Common Lisp, you have an awesome interactive development environment where you can redefine function and objects while testing them.

Dimitri Fontaine

PostgreSQL Major Contributor

Open Source Software Engineer

France