Jonis WebSite

Hoje irei postar o procedimento para criação de uma base de dados replicada em postgresql usando o pgpool-II. No ambiente de demonstração, existem três computadores virtuais rodando na plataforma XenServer:

• Servidor Banco de Dados 1 – 192.168.5.2
• Servidor Banco de Dados 2 – 192.168.5.3
• Servidor de Conexões (PGPool) – 192.168.5.1
• Usuário ‘postgres’
• Senha ‘ABCD1234’

Todos os servidores possuem o CentOS 6.7 instalado em sua versão mínima, nada mais. A configuração de cada VM vai variar de acordo com a sua necessidade. Vamos começar pelas máquinas de banco de dados. Os comandos abaixo devem ser executados em ambos os servidores de banco de dados.

# Instalar XenTools
mount /dev/cdrom /mnt 2>&1 && /mnt/Linux/install.sh -n && umount /mnt


# Desabilita SELinux
sed -i 's/^SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config


# Instala o repositorio do PGPool-II
yum install http://www.pgpool.net/yum/rpms/3.4/redhat/rhel-6-x86_64/pgpool-II-release-3.4-1.noarch.rpm -y


# Instala o repositorio do PostgreSQL 9.4
yum install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm -y


# Instalar utilitarios basicos
yum install postgresql94-server postgresql94-contrib pgpool-II-pg94-extensions sudo wget vim yum-presto yum-utils openssh-clients rsync -y


# Libera o acesso ao pg via rede
sed -i '/-A INPUT -i lo -j ACCEPT/ a -A INPUT -p tcp --dport 5432 -j ACCEPT' /etc/sysconfig/iptables


# Configurar o postgresql para inicializar automaticamente com o sistema
chkconfig postgresql-9.4 on


# Inicializa o banco de dados pela primeira vez
/etc/init.d/postgresql-9.4 initdb


# Inicia o Banco de dados
/etc/init.d/postgresql-9.4 start


# Altera a senha do usuario postgres
passwd postgres


# Reinicia o servidor
reboot


# Acessa a conta do usuário postgres e altera a senha dele no banco de dados
su - postgres
psql
alter user postgres with password 'ABCD1234';
\q


# Ainda na conta do usuário postgres, gerar a chave SSH em cada
# um dos servidores e exportar para o(s) outro(s) servidor(es)
ssh-keygen
ssh-copy-id -i ~/.ssh/id_rsa.pub <IP do outro servidor>


# Libera o acesso entre os servidores e a maquina cliente
cat >>/var/lib/pgsql/9.4/data/pg_hba.conf <<'EOT'
host    all             postgres        192.168.5.1/32          md5
host    all             postgres        192.168.5.2/32          md5
host    replication     postgres        192.168.5.2/32          md5
host    all             postgres        192.168.5.3/32          md5
host    replication     postgres        192.168.5.3/32          md5
EOT


# Configurações do PostgreSQL
cat >>/var/lib/pgsql/9.4/data/postgresql.conf <<'EOT'
listen_addresses = '*'
max_wal_senders = 1
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/9.4/data/archive_log/backup_in_progress || (test -f /var/lib/pgsql/9.4/data/archive_log/%f || cp %p /var/lib/pgsql/9.4/data/archive_log/%f)'
EOT


# Criar e configura a pasta de backup dos logs de transações do PG
install -o postgres -g postgres -m 700 -d /var/lib/pgsql/9.4/data/archive_log


# Reinicia o banco de dados
/etc/init.d/postgresql-9.4 restart


# Cria as extensões necessárias para o recovery dos bancos
echo 'CREATE EXTENSION "pgpool_recovery"' | \
  su - postgres -c 'psql template1'


# Habilita pg_basebackup não-interativo
su - postgres
echo '*:*:*:postgres:ABCD1234' > .pgpass
chmod 0600 .pgpass
exit


# Habilita usuário postgres para iniciar e parar o serviço
# de banco de dados no sistema
cat >/etc/sudoers.d/pgpool-postgres <<'EOT'
postgres ALL=(ALL:ALL) NOPASSWD:/etc/init.d/postgresql-9.4 start
postgres ALL=(ALL:ALL) NOPASSWD:/etc/init.d/postgresql-9.4 stop
EOT
sed -i 's/^Defaults    requiretty/Defaults    !requiretty/g' /etc/sudoers


# Recovery scripts
# Primeiro estagio
cat >/var/lib/pgsql/9.4/data/1st_stage.sh <<'EOF'
#!/bin/sh
TS=$(date +%Y-%m-%d_%H-%M-%S)
MASTER_HOST=$(hostname -f)
MASTER_DATA=$1
RECOVERY_TARGET=$2
RECOVERY_DATA=$3

# Faz um backup removendo a pasta de dados atual
ssh -T $RECOVERY_TARGET \
    "[ -d $RECOVERY_DATA ] && mv $RECOVERY_DATA $RECOVERY_DATA.$TS"

# Remove os arquivos de log antigos
rm $MASTER_DATA/archive_log/*

# Cria um arquivo de semaforo
touch $MASTER_DATA/archive_log/backup_in_progress

# Executa um backup do banco de dados
ssh -T $RECOVERY_TARGET \
    "pg_basebackup -h $MASTER_HOST -D $RECOVERY_DATA --xlog"

# Configura o arquivo recovery.conf para efetuar o restore
echo "restore_command = 'cp $RECOVERY_DATA/archive_log/%f %p'" | \
    ssh -T $RECOVERY_TARGET "cat > $RECOVERY_DATA/recovery.conf"
EOF


# Segundo Estágio
cat >/var/lib/pgsql/9.4/data/2nd_stage.sh <<'EOF'
#! /bin/sh
MASTER_DATA=$1
RECOVERY_TARGET=$2
RECOVERY_DATA=$3
port=5432

# Força o salvamento em disco de sequences nos arquivos de logs
psql -p $port -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn' template1|
while read i
do
  if [ "$i" != "" ];then
    psql -p $port -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S'" $i
  fi
done

# Força o salvamento em disco das transações atuais
psql -p $port -c "SELECT pgpool_switch_xlog('$MASTER_DATA/archive_log')" template1

# Copia os arquivos de log
rsync -avx --delete $MASTER_DATA/archive_log/ \
    $RECOVERY_TARGET:$RECOVERY_DATA/archive_log/

# Remove o arquivo de semaforo
rm $MASTER_DATA/archive_log/backup_in_progress
EOF


# Script de reinicio remoto
cat >/var/lib/pgsql/9.4/data/pgpool_remote_start <<'EOF'
#!/bin/sh
ssh $1 sudo /etc/init.d/postgresql-9.4 start
EOF


# Seta as permissões dos arquivos criados
chmod +x /var/lib/pgsql/9.4/data/1st_stage.sh
chmod +x /var/lib/pgsql/9.4/data/2nd_stage.sh
chmod +x /var/lib/pgsql/9.4/data/pgpool_remote_start


# Reinicia o servidor
reboot

# Fim

Em seguida, o script para configuração do servidor de conexões (PGPool).

# Instalar XenTools
mount /dev/cdrom /mnt 2>&1 && /mnt/Linux/install.sh -n && umount /mnt


# Desabilita SELinux
sed -i 's/^SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config


# Instala o repositorio do PGPool-II
yum install http://www.pgpool.net/yum/rpms/3.4/redhat/rhel-6-x86_64/pgpool-II-release-3.4-1.noarch.rpm -y


# Instala o repositorio do PostgreSQL 9.4
yum install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm -y


# Instala utilitarios basico, pgclient e pgpool
yum install postgresql94 pgpoolAdmin pgpool-II-94 sudo wget vim yum-presto yum-utils openssh-clients rsync -y


# Habilita apache na inicialização
chkconfig httpd on


# Habilita pgpool na inicialização
chkconfig pgpool-II-94 on


# Libera porta 80 e 5432 no iptables
sed -i '/-A INPUT -i lo -j ACCEPT/ a -A INPUT -p tcp --dport 80 -j ACCEPT' /etc/sysconfig/iptables
sed -i '/-A INPUT -i lo -j ACCEPT/ a -A INPUT -p tcp --dport 5432 -j ACCEPT' /etc/sysconfig/iptables


# Adiciona o usuário postgres no sistema e configura
# as permissões no pgpool-II para este usuário
adduser postgres
chgrp -R postgres /etc/pgpool-II-94/


# Criar os arquivos de configurações básicos
cd /etc/pgpool-II-94/
mv pcp.conf.sample pcp.conf
echo "postgres:$(pg_md5 ABCD1234)" >> pcp.conf
mv pgpool.conf.sample pgpool.conf
mv pool_hba.conf.sample pool_hba.conf
sed -i 's/trust$/md5/g' pool_hba.conf
sed -i 's/\(enable_pool_hba =\) off/\1 on/g' pgpool.conf
sed -i "s/^listen_addresses = 'localhost'/listen_addresses = '*'/g" pgpool.conf
sed -i "s/^port = 9999/port = 5432/g" pgpool.conf
touch pool_passwd
pg_md5 -m -u postgres ABCD1234
sed -i 's/^load_balance_mode = off/load_balance_mode = on/g' pgpool.conf
sed -i 's/^replication_mode = off/replication_mode = on/g' pgpool.conf
	
	
# Agora, configurando os backends
sed -i 's/^\(backend_\)/# \1/g' pgpool.conf
cat >>pgpool.conf <<'EOT'
backend_hostname0 = '192.168.5.2'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.4/data'
backend_hostname1 = '192.168.5.3'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.4/data'
EOT


# Configurando recovery
sed -i 's/^\(recovery_\|client_idle_limit_in_recovery\)/# \1/g' pgpool.conf	
cat >>pgpool.conf <<'EOT'
recovery_user = 'postgres'
recovery_password = 'ABCD1234'
recovery_1st_stage_command = '1st_stage.sh'
recovery_2nd_stage_command = '2nd_stage.sh'
client_idle_limit_in_recovery = -1
EOT	


# Permissões dos arquivos do pgpool-II
mkdir /var/run/pgpool-II-94
chmod 777 /var/run/pgpool-II-94
mkdir /var/log/pgpool-II-94
chmod -R 777 /var/log/pgpool-II-94
touch /var/log/pgpool-II-94/pgpool_status
chmod 777 /var/log/pgpool-II-94/pgpool_status
chgrp -R postgres /etc/pgpool-II-94


# Libera acesso aos computadores da rede local
cat >>/etc/pgpool-II-94/pool_hba.conf <<'EOT'
host    all         all    192.168.5.0/24          md5
EOT


# Configura o gerenciador Web (pgpoolAdmin)
sed -i 's/\/usr\/local\/etc\/pgpool.conf/\/etc\/pgpool-II-94\/pgpool.conf/g' /var/www/html/pgpoolAdmin/conf/pgmgt.conf.php
sed -i 's/\/usr\/local\/etc\/pcp.conf/\/etc\/pgpool-II-94\/pcp.conf/g' /var/www/html/pgpoolAdmin/conf/pgmgt.conf.php
sed -i 's/\/usr\/local\/bin\/pgpool/\/usr\/bin\/pgpool/g' /var/www/html/pgpoolAdmin/conf/pgmgt.conf.php
sed -i "s/define('_PGPOOL2_PCP_DIR', '\/usr\/local\/bin');/define('_PGPOOL2_PCP_DIR', '\/usr\/bin');/g" /var/www/html/pgpoolAdmin/conf/pgmgt.conf.php
sed -i "s/define('_PGPOOL2_LANG', 'ja');/define('_PGPOOL2_LANG', 'en');/g" /var/www/html/pgpoolAdmin/conf/pgmgt.conf.php
sed -i "s/define('_PGPOOL2_VERSION', '3.2');/define('_PGPOOL2_VERSION', '3.4');/g" /var/www/html/pgpoolAdmin/conf/pgmgt.conf.php
chgrp -R apache /etc/pgpool-II-94
chmod -R g+rwx /etc/pgpool-II-94

# Fim

Caso tenha encontrado algum erro ou queira sugerir uma melhoria, não deixe de entrar em contato.