lib/60-postgres


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

#!/bin/sh

postgres_run(){
  PGSSLMODE=require PGPASSWORD="$boxconf_password" psql \
    --no-align \
    --echo-all \
    --tuples-only \
    --username="$boxconf_username" \
    -v ON_ERROR_STOP=1 \
    "$@"
}
postgres_create_role(){
  # $1 = postgres_host, $2 = username
  cat <<EOF | postgres_run -h "${1}" -d postgres
SELECT 'CREATE ROLE "${2}" WITH LOGIN' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${2}')\\gexec
EOF
}

postgres_create_database(){
  # $1 = postgres_host, $2 = dbname, $3 = owner $4 = encoding, $5 = locale
  cat <<EOF | postgres_run -h "${1}" -d postgres
SELECT 'CREATE DATABASE "${2}" ENCODING "${4:-UTF8}" LOCALE "${5:-en_US.UTF-8}" OWNER "${3:-postgres}" TEMPLATE "template0"' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '${2}')\\gexec
EOF
}