SSL Mode

[thebucknerlife]

Hi,

I'm trying to use PGFutter to import Json into a Heroku Postgres instance.

I've got everything setup but I'm getting this error: pq: no pg_hba.conf entry for host "198.11.218.38", user "xxx", database "xxx", SSL off.

I can't find a flag to enable sslmode=require. Is that possible? Or is there another issue I'm facing?

Thanks!

[lukasmartinelli]

The https://github.com/lib/pq library I am using should be able to support SSL, I just have not built it in yet.

I actually even disabled it I see now.

https://github.com/lukasmartinelli/pgfutter/blob/master/postgres.go#L80

I will add a flag to support this. Thanks for reporting.

[thebucknerlife]

@lukasmartinelli Thanks for the quick response. We're actually planning to use PGFutter tonight to migrate from Mongo to PG on Heroku. I'm gonna fork this and see if I can get it to work simply by changing that line.

[lukasmartinelli]

You need the following flags right?

• sslmode - Whether or not to use SSL (default is require, this is not the default for libpq)
• sslcert - Cert file location. The file must contain PEM encoded data.
• sslkey - Key file location. The file must contain PEM encoded data.
• sslrootcert

[thebucknerlife]

I was only going to add the sslmode flag, based on this Heroku doc: https://devcenter.heroku.com/articles/heroku-postgresql#external-connections-ingress

I haven't tried it yet though, so I might need to specify the certs. I'm not sure tbh.

[lukasmartinelli]

@lukasmartinelli Thanks for the quick response. We're actually planning to use PGFutter tonight to migrate from Mongo to PG on Heroku. I'm gonna fork this and see if I can get it to work simply by changing that line.

Wow thats really cool to hear that. Yes that should work well, I wanted to write a guide how to do it with pgfutter. You just import the single document column and then you can start building out a new schema (this part will perhaps be a bit tedious). And PostgreSQL JSON / BSON support rocks!!

[lukasmartinelli]

There is a section about connection parameters at https://godoc.org/github.com/lib/pq

[thebucknerlife]

That's exactly what we're doing.

mongoexport -> pgfutter -> sql queries

[thebucknerlife]

I think this won't require the certs:

* require - Always SSL (skip verification)

[thebucknerlife]

wrt a post about switching from mongo to postgres, we're planning to write one this week. Would love to have your input on it. We've got a bunch of scripts (mostly ruby) and we're using a Node server to async the heavy lifting (mongoexport and pgfutter of ~10 mil records).

[lukasmartinelli]

I think this won't require the certs:

• require - Always SSL (skip verification)

Yes it seems that way from the documentation.
Tell me whether it works :)

[lukasmartinelli]

wrt a post about switching from mongo to postgres, we're planning to write one this week. Would love to have your input on it. We've got a bunch of scripts (mostly ruby) and we're using a Node server to async the heavy lifting (mongoexport and pgfutter of ~10 mil records).

So cool. I would love too. 10 million records should work fine. The maximum where I used pgfutter was about 1.5 billion JSON records.

[lukasmartinelli]

The import table will contain JSON but if you want to query the table directly (because you can make quite cool JSON queries with PostgreSQL) it makes sense to transform it to JSONB. http://www.postgresql.org/docs/9.4/static/datatype-json.html

[lukasmartinelli]

If that works it probably works for any PostgreSQL database and I can hardcode it directly as well.

thebucknerlife@3845774#diff-dce2e070de6dd445c8a81742c8fc7d06R80

Easiest way to compile is using a docker container.
Takes some time to download the build image though.

docker run --rm -v "$(pwd)":/usr/src/pgfutter -w /usr/src/pgfutter tcnksm/gox:1.4.2-light

[thebucknerlife]

I'll start on it now.

[thebucknerlife]

I'm getting this issue (just installed docker via homebrew):

$ docker run --rm -v "$(pwd)":/usr/src/pgfutter -w /usr/src/pgfutter tcnksm/gox:1.4.2-light
Post http://%2Fvar%2Frun%2Fdocker.sock/v1.20/containers/create: dial unix /var/run/docker.sock: connect: no such file or directory.
* Are you trying to connect to a TLS-enabled daemon without TLS?
* Is your docker daemon up and running?

[lukasmartinelli]

If you install Docker toolbox you also need to source the env vars.

docker-machine start default
eval(docker-machine env default)

But if you're on OSX you might as well install Golang. Since Docker is a new concept as well :)
I'll try to create an OSX binary for you.

[thebucknerlife]

I did install golang just now (I was going to try and do this go get github.com/thebucknerlife/pgfutter hoping go get would compile the binary) Obviously that didn't work :) haha

Installing docker-machine now. If you can create an OSX binary for me, though, that would be great.

[lukasmartinelli]

That's quite tough to get started on Golang like that :) I would be completly lost.

[lukasmartinelli]

If you did go get with github.com/thebucknerlife/pgfutter you can actually just type: go install when you are inside the $GOPATH and $GOPATH/src/github.com/thebucknerlife/pgfutter and then you should see a pbfutter binary.

[lukasmartinelli]

No sorry you need to do go build inside $GOPATH/src/github.com/thebucknerlife/pgfutter.
Golang has kind of a weird directory structure.

Does that work: https://github.com/lukasmartinelli/pgfutter/releases/download/v0.3.2/pgfutter_ssl
This is not really static binary - still rerquires the Golang runtime but you have installed that on your machine.

[thebucknerlife]

Trying the binary it now. Yeah, the gopath setup is a little weird. Was just fumbling with it.

[thebucknerlife]

It worked! I just tested pgfutter against pgfutter_ssl and the ssl worked with Heroku Postgres.

So the difference between the static binary and what I've got is the static binary has golang embedded in it?

[bentona]

@lukasmartinelli thank you so much for helping us with this!

[lukasmartinelli]

To be honest it also took some time for me to get the project compiling again - was on a new OSX machine after Linux and had to setup Golang as well - it is indeed a bit fiddly.

It worked! I just tested pgfutter against pgfutter_ssl and the ssl worked with Heroku Postgres.

Yeeeah! I will include it into master then.
So you are already importing?
But you were able to build the project yourself with a proper $GOPATH setup?

So the difference between the static binary and what I've got is the static binary has golang embedded in it?

Yes from what I know you can build a binary that has the golang runtime linked statically.

@lukasmartinelli thank you so much for helping us with this!

Np. I am still psyched someone can actually use pgfutter :)

[thebucknerlife]

But you were able to build the project yourself with a proper $GOPATH setup?

I wasn't able to build the project, not yet. I only tried to run the binary you sent us and it worked.

Yeeeah! I will include it into master then.

@lukasmartinelli Just made a PR for the change.

[thebucknerlife]

Now I'm trying to build the project myself. Here are the steps I've been following:

I set my $GOPATH to $HOME/go

$ cd ~/go
$ go get github.com/thebucknerlife/pgfutter
$ ls
bin         pkg          src
$ go install
can't load package: package .: no buildable Go source files in /Users/gbuck/go

So that's where I am on my machine. The binary you sent still works though.

[lukasmartinelli]

cd $GOPATH/src/github.com/thebucknerlife/pgfutter and then you are inside the actual project (the git repo itself and then go build

[thebucknerlife]

That worked. Now I understand it. Thanks a lot @lukasmartinelli you made this much easier for us tonight. Will let you know how it goes! 👍

[lukasmartinelli]

Migration worked?

[lukasmartinelli]

Bump.

Migration worked?

[thebucknerlife]

Hey @lukasmartinelli the migration went very well - actually better than expected! We've seen a dramatic drop in memory issues on Rails since switching to PG from Mongo. Took us all night, until about 6am, but we got it done. PGFutter was one of the crucial tools for the migration.

Thank you again for being so diligent and responsive. A nice way to learn some Golang to 😄

Are you going to be adding an SSL flag to the binary?

[lukasmartinelli]

Implemented the flag in e982dd1.
Took a while 😁