A long time ago we talked about how to
Import fixed width data with pgloader, following up on other stories still
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
in the timings. Let's update that article with current
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.
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.
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.
pgloader is a tool to help loading data into
PostgreSQL, adding some error
management to the
COPY is the fast way of loading data into
PostgreSQL and is transaction safe. That means that if a single error
appears within your bulk of data, you will have loaded none of it.
will submit the data again in smaller chunks until it's able to isolate the
bad from the good, and then the good is loaded in.
Another day, another migration from MySQL to PostgreSQL... or at least that's how it feels sometimes. This time again I've been using some quite old scripts to help me do the migration.
Back to our series about pgloader. The previous articles detailed How To Use PgLoader then How to Setup pgloader, then what to expect from a parallel pgloader setup. This article will detail how to reformat input columns so that what PostgreSQL sees is not what's in the data file, but the result of a transformation from this data into something acceptable as an input for the target data type.
Je viens de publier un billet en anglais intitulé
How to Setup pgloader, qui
complète l'écriture en cours d'un
tutoriel pgloader plus complet. Une fois
de plus, je n'ai pas pris le temps de traduire cet article en français avant
de savoir si cela vous intéresse, ô lecteurs. Si c'est le cas il suffit de
me l'indiquer par mail (ou
courriel, après tout) pour que j'ajoute cela dans
C'est une question qui revient régulièrement, et à laquelle je pensais avoir apporté une réponse satisfaisante avec les exemples pgloader. Ce document ressemble un peu à un tutoriel, en anglais, et je l'ai détaillé dans l'article how to use pgloader sur ce même site, en anglais. Si la demande est suffisante, je le traduirai en français.
Yes. This pgloader project is still maintained and somewhat active. Development happens when I receive a complaint, either about a bug in existing code or a feature in yet-to-write code. If you have a bug to report, just send me an email!