Multiple Postgres databases in CircleCI 2.0

Build Environment Databases
,
1

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
2

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.

3

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

4

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.

8

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
9

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