# Multi Database PostgreSQL with PGBouncer

This Compose project will let you run multiple database within one PostgreSQL container. It's based on [Cristian Angulo](https://dev.to/nietzscheson/multiples-postgres-databases-in-one-service-with-docker-compose-4fdf)'s post on the subject.

The `multiple-databases.sh` script does the heavy lifting, creating the databases and the associated users.

The original version creates users per database, with the same name as the database.  This version sets the password for the users created to that of the postgres user.

It also starts up a PGBouncer.

## ENV file
Create a .env file with the following keys: 

* HOST_IP
* HOST_KIND
* POSTGRES_PASSWORD

### Host IP
Use this to set the IP for Postgres to bind to.

### Host Kind
In the compose.yaml, you'll see `postgresql.${HOST_KIND}.conf`. This lets you have custom parameters for PostgreSQL per host type.  Use https://pgtune.leopard.in.ua to build your own.

* CPX11 is a Hetzner host - 2 vCPUS, 2GB RAM
* Nano is a Linode host - 1 vCPU, 1GB RAM

### POSTGRES_PASSWORD
Used to set the `postgres` user's password

### POSTGRES_MULTIPLE_DATABASES
Set the `POSTGRES_MULTIPLE_DATABASES` value in `compose.yaml` to a series of comma separated database names. 

## Data directory
If you're using this for cache, say [SolidCache](https://github.com/rails/solid_cache), you may be happy to use named bind mountings and let Docker manage storage.

Alternatively, bind mount a local directory for PostgreSQL to use.

## Logging
This configuration will log to a mounted volume, using PostgreSQL logging, and not to Stderr / Docker logging as per the default image.

## PGBouncer
Add PGBouncer accounts with: 

```bash
./generate-userlist pgb_admin >> userlist.txt 
```

Update `pgbouncer.ini` adding the databases defined in POSTGRES_MULTIPLE_DATABASES to the `[databases]` section.