sebastianwebber

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

sebastianwebber

-- criação da função
CREATE OR REPLACE FUNCTION fnc_roda_selects()
RETURNS SETOF RECORD
AS $$
DECLARE 
	rResult RECORD;
BEGIN

 -- tenta buscar dados na tabela com um filtro especifico
 PERFORM * FROM dw.dim_tempo;

sebastianwebber

Uber facts

Original posts/information

Migrating Uber from MySQL to PostgreSQL

Key points

• ~50GB MySQL Application
• Main motivation: PostGis
• Migration made with a custom tool(xml2pgcopy) and mysqldump on 45min

sebastianwebber

#!/bin/bash

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
LAST_BACKUP=$(ls -lto ${ARCHIVEDIR})

FILE_LIST=$(pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}")

## TEST FIRST
pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" | find -type f -mmin +30 | xargs ls -lh

sebastianwebber

MySQL DOJO -> Constat LM2

Dojo sobre MySQL Enterprise executado na Constat LM2 no dia 05/09/2016.

TODO List

• Instalação no El7
• Avaliar conceitos de tuning
• Backup e Restore
• Disaster Recovery issues

sebastianwebber

CREATE OR REPLACE FUNCTION fnc_intervalo()
RETURNS SETOF interval AS
$$
DECLARE
BEGIN
  RETURN NEXT 1;
  RETURN NEXT 2;
END;
$$ LANGUAGE 'plpgsql';

sebastianwebber

test=# \pset null UNKNOW
Null display is "UNKNOW".
test=# create table employee1(id serial primary key, name text);
CREATE TABLE
test=# create table employee2(id serial primary key, name text);
CREATE TABLE
test=# -- load the fake data on employee1
test=# insert into employee1 (name) SELECT '1: Person '|| generate_series(1,10);
INSERT 0 10
test=# -- load the fake data on employee2

sebastianwebber

Zabbix 3 Install on CEntOS 7 with PostgreSQL 9.5

Repo installation

Zabbix repo

yum install http://repo.zabbix.com/zabbix/3.0/rhel/7/x86_64/zabbix-release-3.0-1.el7.noarch.rpm

sebastianwebber

#!/bin/bash

# Ideia original do @matheusoliveira
# $1 é %f, $2 é %p

## IMPORTANTE: Lembrar de configurar o `archive_cleanup_command` no `recovery.conf` pra
##             chamar o pg_archivecleanup e apontar pro ${CACHE_DIR}
##   exemplo:
###      restore_command = '/opt/resources/wal_prefetch.sh %f %p'
###      archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /slave/wal-cache %r'

sebastianwebber

  - set_fact: 
      events_session: |+
        events {
            worker_connections {{ total_fpm_pools * max_phpfpm_server_per_pool }};

            use epoll;

            multi_accept on;
        }