sebawebber

Guia pra facilitar da vida de quem lê as suas consultas

Consulta original

SELECT e.ID_USUARIO, u.NOME_COMPLETO USUARIO, e.ID_USUARIO_SOLICITANTE , us.NOME_COMPLETO USUARIO_SOLICITANTE, e.DATA_EMPRESTIMO DATA_EMPRESTIMO, e.DATA_DEVOLUCAO DATA_DEVOLUCAO,
         e.STATUS STATUS, l.TITULO
         FROM emprestimo e
         INNER JOIN usuario u ON e.ID_USUARIO = u.ID
         INNER JOIN usuario us ON e.ID_USUARIO_SOLICITANTE = us.ID

sebawebber

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

            use epoll;

            multi_accept on;
        }
    

sebawebber

#!/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'

sebawebber

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

sebawebber

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

sebawebber

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

sebawebber

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

sebawebber

#!/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

sebawebber

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

sebawebber

-- 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;