Request: Postgres 9.5


#1

The new minor version bump of pg has had a few point releases now.

Anyone else have dependencies that are requiring this for their build?


Upgrade to Postgres 9.6.1
Postgres 9.5.3 `pg_dump` version mismatch
#2

Yeah i am having the same issue, please upgrade to 9.5


#3

If you’re using CircleCI’s experimental “Ubuntu 14.04 Trusty” container, you may have noticed that both Postgres 9.4 and Postgres 9.5 are already running. It’s not officially supported, so without some tweaks, will only be able to access the 9.4 instance. These changes to your circle.yml will force Rails to use the new version.

machine:
  environment:
    # See note about config.database.yml below.
    DATABASE_URL: postgres://postgres@127.0.0.1:5433/test_database

database:
  override:
    # This next line prevents Circle from automatically overwriting
    # config/database.yml with one pointing at Postgres 9.4.
    - true

dependencies:
  override:
    # Just to be 100% certian you are using 9.5, and save a bit of memory.
    - sudo service postgresql stop 9.4

    # The 9.4 instance has been configured to work with Docker. Just
    # copy that over to 9.5 and restart.
    - sudo cp -v /etc/postgresql/9.{4,5}/main/pg_hba.conf && sudo service postgresql restart 9.5

If your Rails project was created before Rails 4.1, you may not yet have changed over to using DATABASE_URL. Your config/database.yml should look like this:

test:
  url: <%= ENV['DATABASE_URL'] %>

Ubuntu 14.04 Build Image Update 201606-01
#4

This fails with the following output:

My Circle.yml (Project is using GO)

# CircleCI configuration file.
machine:
  environment:
    PORT: 3000
    DATABASE_URL_SSL_MODE: disable
    DATABASE_URL: postgres://postgres@127.0.0.1:5433/test_database

test:
  override:
    - go test -p 1 github.com/kiddom/kiddom-mitm

dependencies:
  override:
    # https://discuss.circleci.com/t/request-postgres-9-5/2611/2
    # Just to be 100% certian you are using 9.5, and save a bit of memory.
    - sudo service postgresql stop 9.4

    # The 9.4 instance has been configured to work with Docker. Just
    # copy that over to 9.5 and restart.
    - sudo cp -v /etc/postgresql/9.{4,5}/main/pg_hba.conf && sudo service postgresql restart 9.5

#5

Are you using the Ubuntu 14.04 Trusty container? You’ll need to switch to that for this to work. It’s at the bottom of “Experimental settings” in your project.


#6

Now I am, and it works. Thanks.


Ubuntu Trusty doesn't have pdo_pgsql
#7

Unfortunately I am getting psql: FATAL: database "test_database" does not exist. With 9.4 I used the database circle_test, which is the default according to the docs. With 9.5 however, this does not work either. Any ideas?


#8

Ok, so apparently an available database is postgres.
So I set
DATABASE_URL: "postgres://postgres@127.0.0.1:5433/postgres"
Alternatively you can manually create user and table as stated in this related article.
Then you’d just set

dependencies:
  override:
    - sudo service postgresql stop 9.4
    - sudo sed -i "s/port = ..../port = 5432/g" /etc/postgresql/9.5/main/postgresql.conf
    - sudo cp -v /etc/postgresql/9.{4,5}/main/pg_hba.conf && sudo service postgresql restart 9.5
    - sudo su - postgres -c "echo \"create user ubuntu with password '';\" | psql" 
    - sudo su - postgres -c "echo \"create database ubuntu;\" | psql"
    - sudo su - postgres -c "echo \"grant all privileges on database ubuntu to ubuntu;\" | psql"

#9

Do you know what version of PostGIS runs on the 9.5 instance?


#10

I do have Trusty selected, but I get an error that says:

Failing command: Configure the build 
Exit code: 
Output:
invalid command

with no other information. I am pretty sure the spaces are correct in my circle.yml file, and I have typed in the actual commands correctly. Here is my YML file in full:

machine:
  environment:
    DATABASE_URL: postgres://postgres@127.0.0.1:5433/postgres
  hosts:
    api.rails-app.dev: 127.0.0.1
  post:
    - ./$CIRCLE_PROJECT_REPONAME/scripts/install_qt5.sh
database:
  override:
    # This next line prevents Circle from automatically overwriting
    - true
dependencies:
  override:
    # Just to be 100% certian you are using 9.5, and save a bit of memory.
    - sudo service postgresql stop 9.4
    # copy that over to 9.5 and restart.
    - sudo cp -v /etc/postgresql/9.{4,5}/main/pg_hba.conf && sudo service postgresql restart 9.5

What am I doing wrong?


#11

This is a bit of a moving target, Circle might make 9.5 the default soon enough. But eventually the recipe that worked for me involved:

  1. Remove the database override section - that may have caused the invalid command error
  2. Use the explicit PG 9.5 configuration commands mentioned earlier in this thread, incl creating the ubuntu user
  3. More importantly, make your ubuntu user a superuser, like so: alter ROLE ubuntu with superuser. I needed this to let that role turn off referential integrity to set up my RoR fixtures. Fun fun.
  4. At the end, I even had to add bundle install - somehow having my own dependencies[pre] section in circle.yml caused that step to be skipped by Circle’s inference engine, I think.

#12

Up until yesterday everything worked fine. Now every build fails with:

sudo cp -v /etc/postgresql/9.{4,5}/main/pg_hba.conf && sudo service postgresql restart 9.5
cp: cannot stat ‘/etc/postgresql/9.4/main/pg_hba.conf’: No such file or directory

sudo cp -v /etc/postgresql/9.{4,5}/main/pg_hba.conf && sudo service postgresql restart 9.5 returned exit code 1

Action failed: sudo cp -v /etc/postgresql/9.{4,5}/main/pg_hba.conf && sudo service postgresql restart 9.5

Didn’t see any changes to the 14.04 image, not sure what causes this…


UPDATE:

Apparently 9.5 is now default on 14.04, so there’s no additional configuration necessary. I scrapped all the 9.4/9.5 config commands and reverted back to port 5432 and everything works fine


#13

Ok, so how do I downgrade to 9.4 from 9.5 now?

All our builds are failing on 9.5.


#14

As of June 14th PostgreSQL 9.5.3 is now the only version preinstalled on our 14.04 ‘Trusty’ image.

There are 3 options if you’re using 9.4:

  1. Change to 12.04 ‘Precise’ which is running 9.4 (You can change the build image via the menu in Project settings > Build environment)
  2. Upgrade your DB to 9.5 (see Postgres documentation here)
  3. Install 9.4 on to the 14.04 image with these steps in circle.yml:
dependencies:
  override:
    - sudo service postgresql stop
    - sudo apt-get install postgresql-9.4
    - sudo sed -i "s/port = ..../port = 5432/g" /etc/postgresql/9.4/main/postgresql.conf
    - sudo cp -v /etc/postgresql/9.{5,4}/main/pg_hba.conf
    - sudo apt-get remove --purge postgresql-9.5
    - sudo service postgresql restart
    - sudo su - postgres -c "echo \"create user ubuntu with password '';\" | psql" 
    - sudo su - postgres -c "echo \"create database ubuntu;\" | psql"
    - sudo su - postgres -c "echo \"grant all privileges on database ubuntu to ubuntu;\" | psql"

Hope that helps.


Simplify selection of specific database version
Postgres 9.5.3 `pg_dump` version mismatch
#15

I had problems with option 3:

sudo cp -v /etc/postgresql/9.{5,4}/main/pg_hba.conf
cp: cannot stat `/etc/postgresql/9.5/main/pg_hba.conf’: No such file or directory

sudo cp -v /etc/postgresql/9.{5,4}/main/pg_hba.conf returned exit code 1

Action failed: sudo cp -v /etc/postgresql/9.{5,4}/main/pg_hba.conf


#16

That’s strange. It works for me. Are you definitely on the 14.04 image?


#17

You’re right I’ve mixed our two projects up. Our failing build must be caused by something else. Sorry about that!


#18