14

I am running PostgreSQL 9.6 on a Windows 7 laptop that is tightly managed by corporate IT. I do not have admin privileges on this laptop, but can do "many" things, including software installation, through elevated rights granted through BeyondTrust.

I use this database to import a number of medium-sized datasets from various sources and perform some in-depth analysis and reporting. These datasets have a crazy variation of encoding and formatting which creates challenges while importing them. For one dataset, I first import it into MySQL, strip offending characters from the data, export it back out to CSV, and finally import this cleaned-up data into PostgreSQL.

As an experiment, I installed PostgreSQL 9.6 and pg_loader on my home iMac running macOS 10.12, and it is able to import all of the datasets with no problem.

Given all this as a backdrop, I have a need to use pg_loader to import various datasets. However, the website does not offer either a Windows installer or instructions for compiling it on Windows.

Can anyone point me in the direction of one of the following, with the list going from most desirable to least desirable?

  1. Windows installer of pg_loader
  2. Windows installer of a comparable alternative to pg_loader
  3. Detailed instructions for compiling pg_loader on Windows
  4. Suggestions for using Python to clean up the data prior to import (caveat: I have very little experience with Python)
Michael Sheaver
  • 2,059
  • 5
  • 25
  • 38
  • When it comes to pgloader for Windows i get the feeling even the author isn't a great fan and comments towards it on his git page. https://github.com/dimitri/pgloader/issues/385 Can you not adjust your postgres enconding to allow the 'characters' you are having to remove in MySQL ? And have you tried just COPY commands to load the data ? https://www.postgresql.org/docs/9.6/static/sql-copy.html For a python approach you can read the files and use replace / strip to remove the offending characters before import. – VynlJunkie May 14 '18 at 16:02

2 Answers2

12

You can install the Windows Subsystem for Linux, install a Linux distro, then install pgloader with apt or other package manager - 

$ sudo apt install pgloader
$ pgloader --version

pgloader version "3.4.1"
compiled with SBCL 1.3.3.debian

$ cd /mnt/c/Users/bburns/Desktop
$ pgloader ....
Brian Burns
  • 20,575
  • 8
  • 83
  • 77
8

This solution may come a little late to the original asker, but for those still searching for a windows solution. I can confirm that I was able to get pgloader working using Docker for Windows.

My environment is a local MySQL install on a Windows 10 PC. I installed Docker for Windows. I then installed PostgreSQL via docker: docker run --name some-postgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres

Finally, I ran pgloader via docker to pull the schema & data from mysql to postgres using the following: docker run --rm --name pgloader dimitri/pgloader:latest pgloader mysql://root:root@host.docker.internal/my_db_name pgsql://postgres:mysecretpassword@host.docker.internal/postgres

This created a schema called "my_db_name" on my postgres instance. The "root:root" and "postgres:mysecretpassword" are my username:password details. This is obviously a production ready environment! The other useful bit "host.docker.internal" is docker's reference to the parent host environment. Also, I ignored the SBCL warning.

I'm still processing the results. Most everything looks like it migrated successfully. I'm missing triggers (unsupported) and some foreign keys (bug?). Anyways, I hope this might be helpful to others.

user3179263
  • 170
  • 1
  • 5