How do you use Postgres 9.6.X? or 9.X.X?

postgresql
circle.yml

#1

Hello,

I’ve been running my tests on CircleCI and using Postgres. But now I’ve upgraded my database to Postgres 9.6.1 and I’m trying to use it on CircleCI… this seems like such a common use case, but for some reason it’s not covered at all in the docs… which is extremely frustrating.

A while ago, I found a random Gist that was working for a while: https://gist.github.com/jnwheeler44/220a77ce680431a625878e9da0de1b0e

But now that also seems to be failing, because it looks like the commands that previously worked now cause the Postgres cluster to be removed—maybe it is somehow coupled to the 9.5 installation now and wasn’t previously. I don’t know why it’s failing, because I haven’t changed anything regarding Postgres.

Soon CircleCI may upgrade the default version to be 9.6.X, and other people will have problems because they were relying on 9.5.X. The fact that it is such a black box, and that the images themselves are moving targets makes the whole thing a huge clusterfuck.

After spending multiple different days over the past month dealing with this configuration I’m extremely frustrated now.

How does CircleCI recommend using Postgres 9.6.X?

Can someone who works at CircleCI answer this question once and for all, ideally somewhere in the documentation, so that the one-off questions about Postgres don’t have to crop up all the time?


#2

Thanks for your question and sorry this isn’t clearer in the docs - we’ll get that improved as soon as we can.

For our 14.04 build image, the current way to do this is described here: Ubuntu 14.04 Build Image Update 201701-01

In summary: 9.5 is the currently supported Postgres version. If you wish to use 9.6 instead of 9.5, you can add the following to your circle.yml.

dependencies:
  pre:
    - sudo service postgresql stop
    - sudo mv /usr/lib/postgresql-9.6/9.6 /usr/lib/postgresql/9.6
    - sudo mv /etc/postgresql-9.6/9.6 /etc/postgresql/9.6
    - sudo service postgresql start 9.6

In CircleCI 2.0 you’ll be able to specify exactly the version you need. More info here: https://circleci.com/beta-access/


#3

Hey @tom, thank you for the reply!

Being able to explicitly set the version you need in the CircleCI 2.0 beta sounds awesome, that is exactly what I’m looking for. I’ve requested access.

In the meantime, I believe I’ve finally (after another multiple hour stint) gotten everything working properly, but it was a huge pain in the ass to do and left me very frustrated with Circle overall. The way that Postgres (and presumably all other databases) are configured and documented is extremely lacking.

In case you or others at Circle are looking for feedback…

1. Moving Target

The fact that the Postgres version currently installed on the machine is a moving target, as you update the build container, is very frustrating. Since I’m using pg_dump and pg_restore, any version bump is going to trigger a version mismatch and cause all of my tests to fail. I realize that because of the (seemingly poor) choice in the original design of the configuration setup this isn’t easy to solve, hopefully 2.0 addresses this.

The way I would think would be a lot more sensible is to have all of the actual versions installed on the machine, but nothing running unless the user has specifically opted into Postgres with something in their circle.yml like…

machine:
  postgres:
    version: 9.6

Then once they had, you could easily do the mv, service postgresl start *, etc. that is required to make it seamless—as if I had just installed a fresh copy of a single version myself, but without the wait.

But the commands you responded with aren’t all that I needed (at least as far as I could figure out)…

2. pg_* Binaries

The second frustration I ran into was that the commands you provided didn’t also account for use of pg_dump, pg_restore, etc. So I need to add extra configuration to make sure that even though 9.6 was running, that pg_dump invocations would also be using 9.6.

For future people reading this, you need to add the following to your circle.yml file for pg_* utils to work:

machine:
  environment:
    # Add the Postgres 9.6 binaries to the path.
    PATH: /usr/lib/postgresql/9.6/bin/:$PATH

3. Default User, Database, Port

The next frustration was that trying to search for what CircleCI’s default Postgres configuration was not easy. Nothing in the Postgres-specific docs mention what the default user, password, database are. And Circle’s documentation and search are really bad.

It wasn’t until I stumbled across the sample config page that I happened to spot a Postgres URI with the defaults:

postgres://ubuntu:@127.0.0.1:5432/circle_test

But then that didn’t even work, for a few reasons. One was the passwordless login (more below). Another was that since I was using 9.6 and not 9.5, the port was actually 5433 instead of 5432.

The port change meant that not only did my $DATABASE_URL need to change, but all of my invocations of psql or other commands need to specify the new port as well.

If you’d like to see what I think is a much better way of structuring the documentation, check out the Codeship Postgres docs page.

4. Passwordless Login

The next frustration I ran into was that the default Postgres configuration is setup (as far as I can tell) to always ask the user for a password, unless using sudo. But the default user created has no password, so you essentially can’t log in without using sudo.

Which seems unfortunate since that’s not the case in my dev environment, which means I’d need to have separate logic to use sudo depending on the environment, or some other sort of check.

The separate fix I ended up going with is overwriting the existing ubuntu user with a new user with a password, and creating my own database so that I don’t get burned by Circle changing the defaults in the future.

For future readers, that looked like this:

machine:
  pre:
    # Add a password to the `ubuntu` user, since Postgres is configured to
    # always ask for a password without sudo, and fails without one.
    - sudo -u postgres psql -p 5433 -c "create user ubuntu with password 'ubuntu';"
    - sudo -u postgres psql -p 5433 -c "alter user ubuntu with superuser;"

    # Create a new test database.
    - sudo -u postgres psql -p 5433 -c "create database test;"

Conclusion…

In the end it seems to all be working, and I’ve got 9.6 running after adding the following to my circle.yml file:

machine:

  environment:
    # Add the Postgres 9.6 binaries to the path.
    PATH: /usr/lib/postgresql/9.6/bin/:$PATH

  pre:
    # Start Postgres 9.6 since the default is 9.5.
    - sudo service postgresql stop
    - sudo mv /usr/lib/postgresql-9.6/9.6 /usr/lib/postgresql/9.6
    - sudo mv /etc/postgresql-9.6/9.6 /etc/postgresql/9.6
    - sudo service postgresql start 9.6

    # Add a password to the `ubuntu` user, since Postgres is configured to
    # always ask for a password, and without out it will fail.
    - sudo -u postgres psql -p 5433 -c "create user ubuntu with password 'ubuntu';"
    - sudo -u postgres psql -p 5433 -c "alter user ubuntu with superuser;"

    # Create a new test database.
    - sudo -u postgres psql -p 5433 -c "create database test;"

And adding a $DATABASE_URL environment variable via the Circle UI.

But as someone who isn’t super comfortable with this type of configuration, it took me a hell of a long time, and was very frustrating. For someone like me, every little roadblock involves a new GitHub commit and push, waiting for the tests to try to run, seeing what the error is, probably restarting the build with SSH enabled to run a few commands, … and then do that 100 times.

Anyways, thank you for responding with that tip in the first place. It was definitely helpful in getting me on the right path. I hope that my feedback might be helpful in streamlining the process in the future.


#4

Thanks so much for this explanation! I’m struggling with setting up my circle.yml to use PG 9.6, I followed your sample here in the conclusion section, but I keep getting a no password supplied (PG::ConnectionBad).

I’m wondering if you came across this same issue and what you did to fix it. I also set up the following:

Under machine: environment:
DATABASE_URL: postgres://ubuntu:@127.0.0.1:5433/test_database_name

In database.yml.ci :

test:
  host: localhost
  username: ubuntu
  password: ubuntu
  database: test_database_name
  adapter: postgresql
  port: 5433

Thank you!


#5

Hello,

If you’re following the guide from @ianstormtaylor above, please be sure to follow step 4 regarding the passwordless login.

Hope that helps!


#6

Thanks for your help, it looks like I had my pre commands in the wrong place, under dependencies instead of machine. I’m still having issues with trying to use 9.6, I need to use it for the newly added operators in the pg_trgm extension. My tests are failing because of a PG::UndefinedFunction error that doesn’t find the new operator %>. I tried to use SSH to debug and this is what I see:

  • psql test_database => Opens with psql(version 9.6.1, server 9.5.5)
  • \dx => shows pg_trgm is included for the correct schema

Is my issue that it’s using the 9.5.5 server? How can I change it to use 9.6 ?


#7

It could be you SSH’d into the server before it finished setting up the machine.


#8

Almost works for us, but every 5th run or so we would get this:
psql: could not connect to server: Connection refused
Is the server running on host “localhost” (127.0.0.1) and accepting
TCP/IP connections on port 5432?


#9

I eventually managed to get this to work “seamlessly” with the following configuration:

dependencies:
  override:
    - sudo service postgresql stop
    - sudo mv /usr/lib/postgresql-9.6/9.6 /usr/lib/postgresql/9.6
    - sudo mv /etc/postgresql-9.6/9.6 /etc/postgresql/9.6
    - sudo sed -i 's/port = 5433/port = 5432/' /etc/postgresql/9.6/main/postgresql.conf
    - sudo sed -i 's/md5$/trust/' /etc/postgresql/9.6/main/pg_hba.conf
    - sudo service postgresql start 9.6
    - sudo su postgres -c "createuser -s $USER"

Hope this helps somebody else…


#10