sebawebber

Setup do HOT Standby com Streamming replication

Antes de começar

Remoção dos diretórios de dados (PGDATA)

Antes de iniciar, remova o pare o banco de dados e remova o diretório de dados anterior e recrie-o novamente:

systemctl stop postgresql-9.5
rm -rf /var/lib/pgsql/9.5/data
/usr/pgsql-9.5/bin/postgresql95-setup initdb

sebawebber

Setup do Warm Standby

Antes de começar

Remoção dos diretórios de dados (PGDATA)

Antes de iniciar, remova o pare o banco de dados e remova o diretório de dados anterior e recrie-o novamente:

systemctl stop postgresql-9.5
rm -rf /var/lib/pgsql/9.5/data
/usr/pgsql-9.5/bin/postgresql95-setup initdb

sebawebber

---
## PostgreSQL Installation on EL7 Systems 
- hosts: all
  vars:
    pgdg_repo: "https://download.postgresql.org/pub/repos/yum"
    pg_version: "9.5" 
    repo_file: "{{pgdg_repo}}/{{pg_version}}/redhat/rhel-7-x86_64/pgdg-centos{{pg_version | replace('.','')}}-{{pg_version}}-2.noarch.rpm"
    pg_packages_base: "postgresql{{pg_version | replace('.','')}}"
  tasks:
    - name: Install needed repos

sebawebber

WITH settings AS (
  SELECT setting::NUMERIC as block from pg_settings
  WHERE name = 'block_size'
)
select 
  pg_size_pretty(heap_blks_read * settings.block ) as heap_blks_MB, 
  pg_size_pretty(idx_blks_hit * settings.block) as idx_blks_MB, * 
from pg_statio_user_tables,settings;

sebawebber

#!/bin/bash

export PGUSER='postgres'
dia_backup=$(date -I)
data_dir=$(psql -c "show data_directory" -At)
archive_dir='/dados/archive_log'
backup_dir='/dados/backup'

psql -U postgres -c "SELECT pg_start_backup('backup_"${dia_backup}"');"

sebawebber

# -*- mode: ruby -*-
# vi: set ft=ruby :

Vagrant.configure(2) do |config|


  config.vm.box = "centos/7"

  config.vm.provider "virtualbox" do |vb|
    vb.gui = false

sebawebber

-- Criação da estrutura
CREATE TABLE empresa (nome TEXT PRIMARY KEY, apelido TEXT, cnpj bigint);
CREATE TABLE nota_fiscal (numero INT , empresa TEXT REFERENCES empresa (nome), data_emissao TIMESTAMP DEFAULT NOW(), valor NUMERIC(8,2), PRIMARY KEY (numero, empresa));


-- Inserir alguns dados
--- Empresas
INSERT INTO empresa (nome,apelido, cnpj) VALUES ('Coca cola SA', 'MATRIZ', 1234567890001);
INSERT INTO empresa (nome,apelido, cnpj) VALUES ('Vonpar da Coca cola RS', 'Filial RS', 1234567890002)

sebawebber

PGPool II and PostgreSQL 9.5 on Centos 7

About the virtual machines and network details, see this blog post

Instalation

Run on both servers:

yum install postgresql95-server postgresql95-contrib pgpool-II-95

sebawebber

CREATE multiple vacuums analyze for database

This is a simple hack using shellscript and psql. on 9.5, there's avaliable a parallel vacuumdb. ;)

psql -U postgres -d autosystem -f gera_vacuum.sql -At > vacuum_base.sh
split -l 200 vacuum_base.sh vacuum_job_
chmod +x vacuum_job_*
sed  -i '1i #!/bin/bash' vacuum_job_*
ls vacuum_job_a* | xargs -I job_name nohup job_name > job_name.log 2>&1 &

sebawebber

-- cadastra os produtos
INSERT INTO PRODUTO (id, nome, valor)
SELECT serie, 'Produto ' || serie as nome, serie * 10.0 as valor FROM generate_series(1,20) as serie;

-- Insere uma nota 1
BEGIN;

-- NOW() retorna a data/hora de agora
INSERT INTO nota (id, data_emissao)
	VALUES (1, NOW());