LAB Hot Standby PostgreSQL 11

O post de hoje é de um lab que fiz montando um grupo de três servidores Hot Standby para PostgreSQL 11.

Duvidas de parâmetros e documentação: https://www.postgresql.org/docs/

  • Arquitetura

====== Informações dos servidores ======

192.168.11.132 master.localdomain master
192.168.11.133 slave1.localdomain slave1
192.168.11.134 slave2.localdomain slave2
192.168.11.135 slave3.localdomain slave3

====== Instalar o PostgreSQL em todos os slaves ======

rpm -Uvh https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.5-x86_64/pgdg-centos11-11-2.noarch.rpm
yum -y install postgresql11 postgresql11-server postgresql11-contrib postgresql11-libs postgresql-client postgresql-common postgresql-devel
systemctl enable postgresql-11

====== SSH entre os servidores sem senha ======

1 - Executar em todos os servidores

cat >> /etc/hosts << EOF
192.168.11.132 master.localdomain master
192.168.11.133 slave1.localdomain slave1
192.168.11.134 slave2.localdomain slave2
192.168.11.135 slave3.localdomain slave3
EOF

su - postgres

ssh-keygen -t rsa

2 -  Executar no master para ter acesso aos slaves sem senha

ssh-copy-id postgres@192.168.11.133
ssh-copy-id postgres@192.168.11.134
ssh-copy-id postgres@192.168.11.135

====== Master ======

1 - Montar o acesso ao storage onde iremos salvar os archivelogs e compartilhar com todos os slaves /wal.

2 - Criar o usuario para ter acesso da replicação via streaming replication

CREATE ROLE replication WITH REPLICATION PASSWORD 'password' LOGIN;
ALTER USER usr_rep WITH SUPERUSER;

3 - Edição do /var/lib/pgsql/11/data/pg_hba.conf

cat >> /var/lib/pgsql/11/data/pg_hba.conf << EOF
########## LIBERANDO ACESSO #########
host all all 0.0.0.0/0 md5
host all all 127.0.0.1/32 md5
##### Liberar acesso Slave1 Streaming Replication ######
host replication usr_rep 192.168.11.133/24 md5
EOF

4 - Edição do /var/lib/pgsql/11/data/postgresql.conf

max_wal_senders = 3
wal_level = replica
fsync = on 
synchronous_commit = on
archive_mode = on
wal_sync_method = fsync
archive_command='test ! -f /wal/%f && cp %p /wal/%f'
archive_timeout = 1800 = 30 minutos switch
#wal_keep_segments = 10
listen_addresses = '*'
port = 5432

5 -  Aplicando as modificações

systemctl reload postgresql-11

6 - Gerando o archivelog/wal

psql -c "select pg_switch_wal();"


7 - Verificando o archivelog gerado no /wal

ls -lat /wal/


8 - Gerar backup e copiar para o slave via rsync

psql -U postgres -c "SELECT pg_start_backup('base');"

rsync -av /var/lib/pgsql/11/data/ --exclude postgresql.conf --exclude postmaster.pid --exclude postmaster.opts --exclude pg_xlog/* /var/lib/pgsql/11/data/ postgres@192.168.11.133:/var/lib/pgsql/11/data/

rsync -av /var/lib/pgsql/11/data/ --exclude postgresql.conf --exclude postmaster.pid --exclude postmaster.opts --exclude pg_xlog/* /var/lib/pgsql/11/data/ postgres@192.168.11.134:/var/lib/pgsql/11/data/

rsync -av /var/lib/pgsql/11/data/ --exclude postgresql.conf --exclude postmaster.pid --exclude postmaster.opts --exclude pg_xlog/* /var/lib/pgsql/11/data/ postgres@192.168.11.135:/var/lib/pgsql/11/data/

psql -U postgres -c "SELECT pg_stop_backup();"

====== Slave1 Streaming Replication ======

su - postgres

1 - Editar /var/lib/pgsql/11/data/postgresql.conf 

listen_addresses = '*'
port = 5432

2 - Editar /var/lib/pgsql/11/data/recovery.conf

standby_mode = on # Libera Acesso das aplicações ou usuários para leitura nos bancos

trigger_file = '/var/lib/pgsql/11/data/failover.trg' # Para promover o standby para master basta criar o arquivo /var/lib/pgsql/11/data/failover.trg

primary_conninfo = 'host=192.168.11.132 port=5432 user=usr_rep password=usr_rep'  # String usada para conexão do Streaming Replication

3 - Iniciar o PostgreSQL

systemctl start postgresql-11

Salve2 Streaming Replication e Archivelog

su - postgres

1 - Editar /var/lib/pgsql/11/data/postgresql.conf 

listen_addresses = '*'
port = 5432

2 - Editar /var/lib/pgsql/11/data/recovery.conf

standby_mode = 'on' # Libera Acesso das aplicações ou usuários para leitura nos bancos
primary_conninfo = 'host=192.168.11.132 port=5432 user=usr_rep password=usr_rep'target_session_attrs=any'

restore_command = 'cp /wal/%f %p'

trigger_file = '/var/lib/pgsql/11/data/failover.trg' # Para promover o standby para master basta criar o arquivo /var/lib/pgsql/11/data/failover.trg
recovery_target_timeline = 'latest'

3 - Iniciar o PostgreSQL

systemctl start postgresql-11

====== Slave3 Archivelog ======

su - postgres

1 - Editar /var/lib/pgsql/11/data/postgresql.conf 
listen_addresses = '*'
port = 5432

2 - Editar /var/lib/pgsql/11/data/recovery.conf
standby_mode = 'on' # Libera Acesso das aplicações ou usuários para leitura nos bancos
restore_command = 'cp /wal/%f %p'
trigger_file = '/var/lib/pgsql/11/data/failover.trg' # Para promover o standby para master basta criar o arquivo /var/lib/pgsql/11/data/failover.trg
recovery_target_timeline = 'latest'

3 - Iniciar o PostgreSQL
systemctl start postgresql-11

====== Logs ======
tail -f /var/log/messages

====== Monitoramento do sincronismo ======

1 - Master
select * from pg_stat_replication;



2 - Atrasos
select now() - pg_last_xact_replay_timestamp() AS replication_delay;

3 - Via sistema operacional

Master 
ps -eaf | grep sender


Slaves
ps -eaf | grep receiver
ps -eaf | grep startup


============== Testando ==============
- Criando banco no master
create database laba;
- Switch Wal/Archivelog


============== Verificando no slave1 ==============


============== Verificando no slave2 ==============

============== Verificando no slave3 ==============

Anúncios

Shrink table and lob

-- LEVANTAR INFORMAÇÕES PARA COMPARAR APOS A MANUTENÇÃO
-- TAMANHO ATUAL DA TABELA
SELECT bytes/1048576 MB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
OWNER = 'TESTE' AND
SEGMENT_NAME = 'TB_LOG' AND
SEGMENT_TYPE = 'TABLE';
/

-- TAMANHO REAL DO LOB
SELECT nvl((sum(dbms_lob.getlength( arquivo ))),0)/1048576 AS mb FROM TESTE.TB_LOG;

-- REALIZAR SHRINK DA TABELA LOB
ALTER TABLE TESTE.TB_LOG MODIFY LOB(arquivo) (SHRINK SPACE CASCADE);
ALTER TABLE TESTE.TB_LOG SHRINK SPACE COMPACT CASCADE;
ALTER TABLE TESTE.TB_LOG DISABLE ROW MOVEMENT;
-- MOVER TABELA PARA OUTRA TABLESPACE
ALTER TABLE TESTE.TB_LOG move TABLESPACE TBS_TESTE;
ALTER TABLE TESTE.TB_LOG move TABLESPACE TBS_PRODUCAO;
-- CASO ALGUM INDEX FIQUE INVALIDO
select 'ALTER INDEX TESTE.' || index_name || ' REBUILD TABLESPACE TBS_PRODUCAO;' 
from dba_indexes where owner='TESTE'
and table_name = 'TB_LOG' 
and status='UNUSABLE';

-- GERAR O SRHINK
select a.file_id,
 a.file_name,
 ceil((nvl(hwm,1)*8192)/1024) tamanho_para_diminuir,
 ceil(blocks*8192/1024) tamanho_atual,
 ceil(blocks*8192/1024) -  ceil((nvl(hwm,1)*8192)/1024) espaco_livre,
 'alter database datafile '||a.file_id||' resize '||ceil((nvl(hwm,1)*8192)/1024)||'k;'
 from dba_data_files a, 
 (select file_id, max(block_id+blocks-1) hwm 
 from dba_extents where owner='TESTE'
 group by file_id) b
 where a.file_id = b.file_id;

Criando bases Oracle ambientes HML e DEV

A dica de hoje é para quem precisar sempre criar ou atualizar um ambiente Oracle de desenvolvimento ou homologação de forma fácil utilizando o duplicate.

1- Criar um init baseado nas configurações do teu ambiente, podem adaptar este init base DEV abaixo e utilizar:

*.audit_trail=’DB’
*.commit_logging=’BATCH’
*.commit_wait=’NOWAIT’
*.compatible=’11.2.0.4.0′
*.control_files=’/u02/dev/oradata/dev/control01.ctl’,’/u02/dev/oradata/dev/control02.ctl’,’/u02/dev/oradata/dev/control03.ctl’#Restore Controlfile
*.core_dump_dest=’/u01/app/oracle/admin/dev/cdump’
*.cursor_sharing=’EXACT’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’dev’
*.db_writer_processes=2
*.diagnostic_dest=’/u01/app/oracle’
*.enable_goldengate_replication=TRUE
*.job_queue_processes=20
*.large_pool_size=0
*.log_archive_dest_1=’LOCATION=/u02/dev/backup/archivelog/’
*.log_archive_format=’%t_%s_%r.arc’
*.memory_max_target=9663676416
*.memory_target=9663676416
*.open_cursors=6000
*.optimizer_index_caching=70
*.optimizer_index_cost_adj=1
*.optimizer_mode=’ALL_ROWS’
*.pga_aggregate_target=0
*.processes=2000
*.recyclebin=’OFF’
*.remote_login_passwordfile=’EXCLUSIVE’
*.session_cached_cursors=40
*.sessions=1105
*.sga_max_size=0
*.sga_target=0
*.undo_management=’AUTO’
*.undo_retention=3600
*.undo_tablespace=’UNDOTBS1′
*.utl_file_dir=’*’
*.db_file_name_convert=’/oradata/DADOS/prd/’,’/u02/dev/oradata/dev/’,’/oradata/INDICES/prd/’,’/u02/dev/oradata/dev/’,’/oradata/INDICES/datafile’,’/u02/dev/oradata/dev/’
*.log_file_name_convert=’/oradata/DADOS/REDOLOG/prd/’,’/u02/dev/oradata/dev/’,’/oradata/INDICES/REDOLOG/prd/’,’/u02/dev/oradata/dev/’

2 – Ajustar o script do duplicate abaixo:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=dev
export BASE=/u01/app/oracle
export DEST=/u02/dev/backup/
export HOJE=`date +%Y%m%d`
export SPOOL=$DEST/logs

export ORACLE_SID=dev
sqlplus / as sysdba << EOF
spool $SPOOL/atualiza_dev_0_shutdown_base_$HOJE.spool
SHUTDOWN ABORT;

# INICIANDO A INSTANCIA DO BANCO UTILIZANDO O PFILE QUE CRIAMOS ACIMA
STARTUP NOMOUNT pfile=’/u02/dev/backup/scripts/initdev.ora’;
spool off
exit
EOF

# CLONE DO BACKUP PARA HML
rman auxiliary / msglog $SPOOL/clonando_base_$HOJE.spool << EOF
RUN
{

#DATA E HORARIO FINAL DO PONTO DO RESTORE A SER EFETUADO
set until time “to_date(’18/10/2018 23:00:00′,’DD/MM/YYYY HH24:MI:SS’)”;
allocate auxiliary channel c1 type disk ;

# LOCAL ONDE ESTA O BACKUP DOS CONTROLFILES E DO BACKUPSET
duplicate target database to hml backup location=’/u02/dev/backup/restore’ nofilenamecheck;
}
EOF

 

# LEMBRAR DE RECRIAR A TEMP

sqlplus / as sysdba << EOF
spool $SPOOL/create_spfile_hml_$HOJE.spool
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE ‘/u02/dev/oradata/dev/temp_01.dbf’ SIZE 20M autoextend on next 100M maxsize 31G;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;
SHUTDOWN ABORT;
STARTUP pfile=’/u02/dev/backup/scripts/initdev.ora’;
CREATE SPFILE FROM pfile=’/u02/dev/backup/scripts/initdev.ora’;
SHUTDOWN ABORT;
STARTUP
spool off
exit
EOF

 

Data no history

Hoje a dica é bem simples, incluir a data no histórico (history do terminal).

Root:

cat >> /root/.bashrc << EOF
export HISTTIMEFORMAT=”%F-%T ”
EOF

Oracle:

cat >> /home/oracle/.bashrc << EOF
export HISTTIMEFORMAT=”%F-%T ”
EOF

Capture

Problema no tamanho do log Listener Windows

Quem tiver o mesmo problema que o meu no windows, sobre o tamanho do arquivo do log do listener que faz o listener parar quando o arquivo chega em 4gb, eu fiz um script para automatizar e evitar esses problemas bastando apenas ser colocado no agendador do windows.

Basta mudar o diretorio onde são gerados os logs do seu ambiente, e criar um arquivo.bat

set d=%date:~-4,4%%date:~-7,2%%date:~0,2%
set d=%d: =_%
set t=%time:~0,2%%time:~3,2%%time:~6,2%
set t=%t: =0%
D:
cd \app\Administrator\diag\tnslsnr\srv03\listener3\trace
lsnrctl set log_status off
RENAME listener.log "listener_%d%_%t%.log"
lsnrctl set log_status on

SSH entre AIX e Linux sem senha

A dica de hoje para criar o acesso via ssh sem senha do AIX para o linux é criando o script abaixo e executando no aix, simplificando a criação do acesso:

#!/bin/sh
 
# Shell script to install your public key on a remote machine
# Takes the remote machine name as an argument.
# Obviously, the remote machine must accept password authentication,
# or one of the other keys in your ssh-agent, for this to work.
 
ID_FILE="${HOME}/.ssh/id_rsa.pub"
 
if [ "-i" = "$1" ]; then
  shift
  # check if we have 2 parameters left, if so the first is the new ID file
  if [ -n "$2" ]; then
    if expr "$1" : ".*\.pub" > /dev/null ; then
      ID_FILE="$1"
    else
      ID_FILE="$1.pub"
    fi
    shift         # and this should leave $1 as the target name
  fi
else
  if [ x$SSH_AUTH_SOCK != x ] && ssh-add -L >/dev/null 2>&1; then
    GET_ID="$GET_ID ssh-add -L"
  fi
fi
 
if [ -z "`eval $GET_ID`" ] && [ -r "${ID_FILE}" ] ; then
  GET_ID="cat \"${ID_FILE}\""
fi
 
if [ -z "`eval $GET_ID`" ]; then
  echo "$0: ERROR: No identities found" >&2
  exit 1
fi
 
if [ "$#" -lt 1 ] || [ "$1" = "-h" ] || [ "$1" = "--help" ]; then
  echo "Usage: $0 [-i [identity_file]] [user@]machine" >&2
  exit 1
fi
 
# strip any trailing colon
host=`echo $1 | sed 's/:$//'`
 
{ eval "$GET_ID" ; } | ssh $host "umask 077; test -d ~/.ssh || mkdir ~/.ssh ; cat >> ~/.ssh/authorized_keys" || exit 1
 
cat <<EOF
Now try logging into the machine, with "ssh '$host'", and check in:
 
  ~/.ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
EOF

-- Permissões necessarias
chmod 755 ssh-copy-id

-- Criando a conexão do AIX para o Linux para acesso via SSH sem senha
./ssh-copy-id oracle@192.168.0.100