Database configuration
PostgreSQL RDS
Choose the latest version of PostgreSQL RDS here. You can also run:
aws rds describe-db-engine-versions --default-only --engine postgres
Pick a version of postgres you want for the project.
Newer versions of postgres may not be configured in Caktus AWS Web Stacks.
Check here to see if the version you are targeting is listed. If it is not, open a PR to have it added.
You will add your selected version of Postgres to the DatabaseParameterGroupFamily variable found in the WebStacks section of
the deploy/group_vars/all.yaml file.
🗄️ Create PostgreSQL databases
Temporary pod:
Launch a temporary Debian pod within the cluster
inv pod.debian
Install postgres client
The temporary pods don't have much in them, so you will need to install postgresql-client and connect to the RDS PostgreSQL cluster as the admin user.
The Admin
apt update && apt install postgresql-client -y
export DATABASE_URL=...
psql $DATABASE_URL
- Create the environment's role::
CREATE ROLE <<PROJECT_NAME>>_<<ENVIRONMENT>> WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD '<password1>';
- Create environment-specific databases, e.g.::
CREATE DATABASE <<PROJECT_NAME>>_<<ENVIRONMENT>>;
GRANT CONNECT ON DATABASE <<PROJECT_NAME>>_<<ENVIRONMENT>> TO <<PROJECT_NAME>>_<<ENVIRONMENT>>;
GRANT ALL PRIVILEGES ON DATABASE <<PROJECT_NAME>>_<<ENVIRONMENT>> TO <<PROJECT_NAME>>_<<ENVIRONMENT>>;
If your project has postgres extensions that need to be added, you will need to do this at the same time.
For example to add the CITEXT extension you would also run on each database in use for the cluster ('staging', 'production'):
\connect <<PROJECT_NAME>>_<<ENVIRONMENT>>;
CREATE EXTENSION IF NOT EXISTS citext;