DB

Provision and manage replicated postgresql clusters on Linux operating systems.

Setup working environment:

ENV_NAME=test-$(whoami)
cd "$(git rev-parse --show-toplevel)/ansible"

Provision a new environment

./bin/envcp staging $ENV_NAME
./bin/ansible-playbook-$ENV_NAME workflow-linode-dbs.yml

Tear down environment

./bin/ansible-playbook-$ENV_NAME deprovision-linode.yml -e target=provider_linode

Perform a failover

patch -p0 <<EOF
--- inventory/$ENV_NAME
+++ inventory/$ENV_NAME
@@ -1,8 +1,8 @@
 [db_primary]
-iac-$ENV_NAME-psql-primary
+iac-$ENV_NAME-psql-bi

 [db_standbys]
-iac-$ENV_NAME-psql-bi
+iac-$ENV_NAME-psql-primary

 [db_servers:children]
 db_primary
EOF
./bin/ansible-playbook-$ENV_NAME db-rotate.yml

Rebuild standbys from environment primary

./bin/ansible-playbook-$ENV_NAME db-standbys.yml -e postgresql_cluster_recreate=true

Seed data in a new environment from an existing environment

The following example will replicate data from the staging environment into a new environment, $ENV_NAME, and then setup one of the replicas in $ENV_NAME as the new primary.

While this snippet demonstrates that this operation is currently possible, it also clearly shows that the automated aspect of such an operation is not yet streamlined, requiring several by-hand steps to complete.

# 1. declare staging as the primary for ENV_NAME
patch -p0 <<EOF
--- inventory/$ENV_NAME
+++ inventory/$ENV_NAME
@@ -1,7 +1,8 @@
 [db_primary]
-iac-$ENV_NAME-psql-primary
+iac-staging-psql-primary

 [db_standbys]
+iac-$ENV_NAME-psql-primary
 iac-$ENV_NAME-psql-bi

 [db_servers:children]
EOF

# 2. Add connection details by hand for staging primary to ENV_NAME ssh config
sed -E -n '/Host iac-staging-psql-primary/,/\s+HostName/p' inventory/ssh_config.d/staging >> inventory/ssh_config.d/$ENV_NAME

# 3. Add replication accounts and pg_hba entries by hand for ENV_NAME servers to staging primary
# 0.0.0.0/0 should be replaced with the private ip of each server
pg_hba=/etc/postgresql/11/main/pg_hba.conf
ssh -F inventory/ssh_config.d/$ENV_NAME iac-staging-psql-primary sudo -u postgres /bin/bash <<EOF
  set -e
  echo "host replication iac-$ENV_NAME-psql-primary 0.0.0.0/0 trust" >> "$pg_hba"
  echo "host replication iac-$ENV_NAME-psql-bi      0.0.0.0/0 trust" >> "$pg_hba"
  psql -c "CREATE ROLE \"iac-$ENV_NAME-psql-primary\" WITH REPLICATION LOGIN"
  psql -c "CREATE ROLE \"iac-$ENV_NAME-psql-bi\"      WITH REPLICATION LOGIN"
  psql -c "SELECT pg_reload_conf()"
EOF

# 4. rebuild ENV_NAME servers as replicas of staging primary
./bin/ansible-playbook-$ENV_NAME db-standbys.yml -e postgresql_cluster_recreate=true

# 5. declare ENV_NAME as the primary and remove staging
patch -p0 <<EOF
--- inventory/$ENV_NAME
+++ inventory/$ENV_NAME
@@ -1,8 +1,7 @@
 [db_primary]
-iac-staging-psql-primary
+iac-$ENV_NAME-psql-primary

 [db_standbys]
-iac-$ENV_NAME-psql-primary
 iac-$ENV_NAME-psql-bi

 [db_servers:children]
EOF

# 6. effect the primary change
./bin/ansible-playbook-$ENV_NAME workflow-db-rotate.yml

# 7. cleanup staging by hand
ssh -F inventory/ssh_config.d/$ENV_NAME iac-staging-psql-primary sudo -u postgres /bin/bash <<EOF
  sed -i "/iac-$ENV_NAME-psql-primary/d" "$pg_hba"
  sed -i "/iac-$ENV_NAME-psql-bi/d"      "$pg_hba"
  psql -c "DROP ROLE IF EXISTS \"iac-$ENV_NAME-psql-primary\""
  psql -c "DROP ROLE IF EXISTS \"iac-$ENV_NAME-psql-bi\""
  psql -c "SELECT pg_reload_conf()"
EOF

# 8. cleanup ENV_NAME ssh config by hand
sed -E -i '/Host iac-staging-psql-primary/,/\s+HostName/d' inventory/ssh_config.d/$ENV_NAME