Step 1

Create a network so that the PostgreSQL and pgBouncer containers can communicate with each other:

docker network create --driver bridge pgnetwork

Step 2

Set up key environmental variables for working with both of the containers. This creates a user named hippo with a password of datalake that has access to a database named hippo:

cat << EOF > postgres-env.list
PG_MODE=primary
PG_PRIMARY_USER=postgres
PG_PRIMARY_PASSWORD=datalake
PG_DATABASE=hippo
PG_USER=hippo
PG_PASSWORD=datalake
PG_ROOT_PASSWORD=datalake
PG_PRIMARY_PORT=5432
PGBOUNCER_PASSWORD=bouncearound
EOF

cat << EOF > pgbouncer-env.list
PGBOUNCER_PASSWORD=bouncearound
PG_SERVICE=postgres
EOF

Step 3

Run the containers container:

docker run --publish 5432:5432 \
  --volume=postgres:/pgdata \
  --env-file=postgres-env.list \
  --name="postgres" \
  --hostname="postgres" \
  --network="pgnetwork" \
  --detach \
registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-16.1-0

docker run --publish 6432:6432 \
  --env-file=pgbouncer-env.list \
  --name="pgbouncer" \
  --hostname="pgbouncer" \
  --network="pgnetwork" \
  --detach \
registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.21-0

Step 4

Log into the PostgreSQL cluster as the postgres superuser:

PGPASSWORD=datalake psql -h localhost -p 5432 -U postgres hippo

Once logged into PostgreSQL, run the following commands:

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'pgbouncer') THEN
        CREATE ROLE pgbouncer LOGIN;
    END IF;
END
$$;

CREATE SCHEMA IF NOT EXISTS pgbouncer AUTHORIZATION pgbouncer;

CREATE OR REPLACE FUNCTION pgbouncer.get_auth(p_username TEXT)
RETURNS TABLE(username TEXT, password TEXT) AS
$$
BEGIN
    RAISE WARNING 'PgBouncer auth request: %', p_username;

    RETURN QUERY
    SELECT rolname::TEXT, rolpassword::TEXT
      FROM pg_authid
      WHERE NOT rolsuper
        AND rolname = p_username;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE ALL ON FUNCTION pgbouncer.get_auth(p_username TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(p_username TEXT) TO pgbouncer;

This enables for pgBouncer to connect to the hippo database.

When you are done executing these commands, set the password for the pgbouncer user to bouncearound using the \password directive:

\password pgbouncer

Follow the prompts to enter in the correct password.

Step 5

Log into PostgreSQL as the hippo user to the hippo database using pgBouncer:

PGPASSWORD=datalake psql -h localhost -p 6432 -U hippo hippo

docker pull registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.21-0