cz01-psql01 PostgreSQL database installation

cz01-psql01_installation.sh

#PostgreSQL installation
yum localinstall -y http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm
yum install -y postgresql93-server repmgr
yum install -y --enablerepo=centos-base postgresql93-contrib
service postgresql-9.3 initdb
chkconfig postgresql-9.3 on
 
sed -i.orig \
-e "s/^#listen_addresses = 'localhost'/listen_addresses = '*'/" \
-e "s/^#shared_preload_libraries = ''/shared_preload_libraries = 'repmgr_funcs'/" \
-e "s/^#wal_level = minimal/wal_level = hot_standby/" \
-e "s/^#archive_mode = off/archive_mode = on/" \
-e "s@^#archive_command = ''@archive_command = 'cd .'@" \
-e "s/^#max_wal_senders = 0/max_wal_senders = 1/" \
-e "s/^#wal_keep_segments = 0/wal_keep_segments = 5000/" \
-e "s/^#\(wal_sender_timeout =.*\)/\1/" \
-e "s/^#hot_standby = off/hot_standby = on/" \
-e "s/^#log_min_duration_statement = -1/log_min_duration_statement = 0/" \
-e "s/^log_line_prefix = '< %m >'/log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '/" \
-e "s/^#log_checkpoints =.*/log_checkpoints = on/" \
-e "s/^#log_connections =.*/log_connections = on/" \
-e "s/^#log_disconnections =.*/log_disconnections = on/" \
-e "s/^#log_lock_waits = off/log_lock_waits = on/" \
-e "s/^#log_statement = 'none'/log_statement = 'all'/" \
-e "s/^#log_temp_files = -1/log_temp_files = 0/" \
/var/lib/pgsql/9.3/data/postgresql.conf

cat >> /var/lib/pgsql/9.3/data/pg_hba.conf << EOF
host    all             admin           0.0.0.0/0               md5
host    all             all             10.32.243.0/24          md5
# cz01-psql01
host    repmgr          repmgr          10.32.243.147/32        trust
host    replication     repmgr          10.32.243.147/32        trust
# cz01-psql02
host    repmgr          repmgr          10.32.243.148/32        trust
host    replication     repmgr          10.32.243.148/32        trust
EOF
 
for SERVER in cz01-psql01 cz01-psql02 cz01-pgpool-ha cz01-pgpool01 cz01-pgpool02; do
  echo "$SERVER.example.com:5432:postgres:admin:password123" >> ~/.pgpass
  echo "$SERVER.example.com:5432:repmgr:repmgr:repmgr_password" >> ~/.pgpass
done 
chmod 0600 ~/.pgpass
cp ~/.pgpass /var/lib/pgsql/

#Configure repmgr
mkdir /var/lib/pgsql/repmgr
 
cat > /var/lib/pgsql/repmgr/repmgr.conf << EOF
cluster=pgsql_cluster
node=1
node_name=cz01-psql01
conninfo='host=cz01-psql01.example.com user=repmgr dbname=repmgr'
pg_bindir=/usr/pgsql-9.3/bin/
master_response_timeout=5
reconnect_attempts=2
reconnect_interval=2
failover=manual
promote_command='/usr/pgsql-9.3/bin/repmgr standby promote -f /var/lib/pgsql/repmgr/repmgr.conf'
follow_command='/usr/pgsql-9.3/bin/repmgr standby follow -f /var/lib/pgsql/repmgr/repmgr.conf'
EOF
 
cp -r /root/.ssh /var/lib/pgsql/
chown -R postgres:postgres /var/lib/pgsql/.ssh /var/lib/pgsql/.pgpass /var/lib/pgsql/repmgr
 
echo 'PATH=/usr/pgsql-9.3/bin:$PATH' >> /var/lib/pgsql/.bash_profile
service postgresql-9.3 start 

#Add users
sudo -u postgres psql -c "CREATE ROLE admin SUPERUSER CREATEDB CREATEROLE INHERIT REPLICATION LOGIN ENCRYPTED PASSWORD 'password123';"
sudo -u postgres psql -c "CREATE USER repmgr SUPERUSER LOGIN ENCRYPTED PASSWORD 'repmgr_password';"
sudo -u postgres psql -c "CREATE DATABASE repmgr OWNER repmgr;"

#Register DB instance as master
su - postgres -c "repmgr -f /var/lib/pgsql/repmgr/repmgr.conf --verbose master register"

#Configure SSL Layer for PostgresSQL
sed -i.orig \
-e 's@\$dir/cacert.pem@\$dir/example.com-ca.crt @' \
-e 's@\$dir/crl.pem@\$dir/example.com-ca.crl @' \
-e 's@\$dir/private/cakey.pem@\$dir/private/example.com-ca.key @' \
-e 's/^\(crlnumber\)/#\1/' \
-e 's/= XX/= CZ/' \
-e 's/^#\(stateOrProvinceName_default.*\) Default Province/\1 Czech Republic/' \
-e 's/= Default City/= Brno/' \
-e 's/= Default Company Ltd/= Example, Inc\./' \
-e 's/= policy_match/= policy_anything/' \
-e 's/^#\(unique_subject\)/\1/' /etc/pki/tls/openssl.cnf
 
touch /etc/pki/CA/index.txt
echo 01 > /etc/pki/CA/serial
cd /etc/pki/CA
 
# Private key for CA
(
umask 077
openssl genrsa -passout pass:password123 -out private/example.com-ca.key 1024
openssl pkey -text -passout pass:password123 -in private/example.com-ca.key > private/example.com-ca.key.info
)
 
SUBJ="
C=CZ
ST=Czech Republic
O=Example, Inc.
localityName=Brno
commonName=example.com Certificate Authority
"
 
openssl req -passin pass:password123 -subj "$(echo -n "$SUBJ" | tr "\n" "/")" -new -x509 -key private/example.com-ca.key -days 3650 -out example.com-ca.crt
openssl x509 -noout -text -in example.com-ca.crt > example.com-ca.crt.info
 
# cz01-psql01 Certificate
openssl genrsa -passout pass:password123 -des3 -out cz01-psql01.example.com_priv_encrypted.key 2048
openssl rsa -passin pass:password123 -in cz01-psql01.example.com_priv_encrypted.key -out cz01-psql01.example.com_priv.key
 
SUBJ="
C=CZ
ST=Czech Republic
O=Example
OU=Deployment
L=Brno
CN=cz01-psql01.example.com
emailAddress=root@example.com
"
openssl req -passin pass:password123 -new -subj "$(echo -n "$SUBJ" | tr "\n" "/")" -days 3650 -key cz01-psql01.example.com_priv_encrypted.key -out cz01-psql01.example.com.csr
 
openssl ca -passin pass:password123 -batch -in cz01-psql01.example.com.csr -out cz01-psql01.example.com.crt
openssl x509 -noout -text -in cz01-psql01.example.com.crt > cz01-psql01.example.com.crt.info
 
cp /etc/pki/CA/cz01-psql01.example.com.crt /var/lib/pgsql/9.3/server.crt
cp /etc/pki/CA/cz01-psql01.example.com_priv.key /var/lib/pgsql/9.3/server.key
chown postgres:postgres /var/lib/pgsql/9.3/server.*
chmod 0600 /var/lib/pgsql/9.3/server.key
 
sed -i \
-e "s/#ssl = off/ssl = on/" \
-e "s@#ssl_cert_file = 'server.crt'@ssl_cert_file = '../server.crt'@" \
-e "s@#ssl_key_file = 'server.key'@ssl_key_file = '../server.key'@" \
/var/lib/pgsql/9.3/data/postgresql.conf
 
service postgresql-9.3 restart
 
# Quick Test
export PGSSLMODE=require
psql --host cz01-psql01.example.com --username=fuzeme --dbname=fuzers -w -l