Multiple Postgres databases in CircleCI 2.0

How can I have my Postgres image create more than one database? For example, I want to do something like this:

- image: circleci/postgres:9.6.7-alpine
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db_test
      POSTGRES_DB: db_special

Is there a way to do this? And if not, how can I go about creating the second database with the same roles as the first?

1 Like

Yep! Just install a PostgreSQL console client in a run step, and then for each non-root user, call the client with appropriate CREATE USER statements.

So here’s my run step:

- run:
      name: Setup PSQL Databases
      command: |
        sudo apt install -y postgresql-client
        createdb db_test -O user
        createdb db_special -O user

(I removed both POSTGRES_DB fields from the environment of my image).

When I run this, I get createdb: could not connect to database template1: could not connect to server: No such file or directory

Is there any more to the error message? This post suggests the file it is looking for is the PID file.

What you could do is to add a custom command line for this image, I think the key is command. Using that you can launch the server in a custom way. Perhaps it is trying to use a socket, and needs to use a TCP port instead?

Alternatively, if you are not wedded to using a secondary container for your database, you could see this post on installing the database server in the primary/build container.

Hey!

I ran into the same issue and was able to come up with a solution. It looks like by default postgresql-client will try to connect to a socket rather than an IP address.

By specifying -h localhost I was able to run commands against a secondary postgres container.

So in your example, something like this should work:

- run:
      name: Setup PSQL Databases
      command: |
        sudo apt install -y postgresql-client
        createdb -h localhost db_test -O user
        createdb -h localhost db_special -O user

This assumes that user exists as a role on the database that is able to create new schemas. You can configure this as an environment argument on your postgresql container.

3 Likes

This topic was automatically closed 10 days after the last reply. New replies are no longer allowed.