Multiple Posgres databases in CircleCI 2.0

Question

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?

Answer 1

Disclaimer: Developer Advocate at CircleCI

I see three options for you.

1. Extend the PostgreSQL Docker image yourself and have it created the additional databases you need by default.
2. Create the additional databases on the fly through your app (should just be one SQL line) or by using the PostgreSQL CLI (which you'd need to install in the container).
3. Use two or more PostgreSQL containers at the same time, one for each database. You'd also then need to change the default hostname for one of them. The first would be located at localhost and the second by a name you set.

Here's an example for #3:

- image: circleci/my-main-image:latest
- image: circleci/postgres:9.6.7-alpine
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db_test
- image: circleci/postgres:9.6.7-alpine
    name: pg2
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db_special