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.