PostgreSQL pg_repack

O pg_repack é uma extensão do PostgreSQL que permite remover o inchaço de tabelas e índices e, opcionalmente, restaurar a ordem física dos índices agrupados. Ao contrário de CLUSTER e VACUUM FULL, ele funciona on-line, sem manter um bloqueio exclusivo nas tabelas processadas durante o processamento. O pg_repack é eficiente para inicializar, com desempenho comparável ao uso direto do CLUSTER.

Abaixo temos um exemplo da sua utilização:

1 - Instalação
-- Centos 8 com PostgreSQL 12
dnf install pg_repack12

2 - Alterar o parâmetro shared_preload_libraries, essa variável especifica uma ou mais bibliotecas compartilhadas a serem pré-carregadas no início do servidor.
psql
ALTER SYSTEM SET shared_preload_libraries TO 'pg_repack';
SELECT pg_reload_conf();
psql -c "SHOW shared_preload_libraries;";

Screenshot_1

3 - Restart PostgreSQL
systemctl restart postgresql-12

4 - Vamos criar um banco e dar carga em uma tabela de exemplo.

psql -c "create database producao;"
psql
\c producao
CREATE TABLE tb_aluno(
id_aluno SERIAL,
nome VARCHAR(80),
curso VARCHAR(80),
descricao text,
data timestamp,
PRIMARY KEY(id_aluno)
);

-- Carga de dados
INSERT INTO tb_aluno (nome,curso,descricao,data)
SELECT 'Aluno '||generate_series nome,'Curso '||generate_series curso,'Teste de carga Laboratorio '||generate_series||'.' texto ,NOW()
FROM generate_series(1,200000);

Screenshot_2

Screenshot_3

5 - Verificar o tamanho atual do banco producao e da tabela tb_aluno.
psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;";
psql
\c producao
SELECT pg_size_pretty( pg_total_relation_size('tb_aluno') );

Screenshot_4

Screenshot_6

6 - Conectar no PostgreSQL e criar a extensão.
psql
\c producao -- criar no banco
CREATE EXTENSION pg_repack;

Screenshot_5

7 - Apagar informações
\c producao
delete from tb_aluno;

8 - Verificar tamanho apos o delete da tabela tb_aluno.
psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;";
psql
\c producao
SELECT pg_size_pretty( pg_total_relation_size('tb_aluno') );

Screenshot_6

9 - Executar o pg_repack com paralelismo de 3 jobs
PATH=/usr/pgsql-12/bin; export PATH
/usr/pgsql-12/bin/pg_repack --table=public.tb_aluno --jobs=3 producao

10 - Verificar tamanho apos o delete da tabela tb_aluno.
psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;";
psql
\dt

Screenshot_7

Screenshot_8

 

PITR PostgreSQL 12 Recovery

Dica do dia é de como efetuar um recovery do ponto mais próximo do seu ultimo archivelog gerado, antes da versão 12, tínhamos o arquivo recovery.conf com alguns parâmetros como recovery_target_time, restore_command e recovery_target_action, segue abaixo como efetuar o recovery na versão 12.

1 - Parando o PostgreSQL
systemctl stop postgresql-12
2 - Criar o arquivo recovery.signal
> /var/lib/pgsql/12/data/recovery.signal
3 - Alterar o arquivo /var/lib/pgsql/12/data/postgresql.conf
restore_command = 'cp /backups/archivelog/%f %p'
recovery_target_name = 'immediate' # Recupera ate o seu ultimo archivelog gerado
#recovery_target_time = '2020-02-14 21:19:47.777714+00:' # Recupera ate um ponto especifico.

4 - Iniciar o PostgreSQL
systemctl start postgresql-12

Paralelismo Dump PostgreSQL

A dica de hoje é acelerando seus backups lógicos do seu PostgreSQL com paralelismo, segue abaixo exemplo.

1 – Tamanho do banco usado para os testes:

Screenshot_1

2 – Dump sem paralelismo levou 7 minutos.

time pg_dump db_teste > db_teste.sql

Screenshot_2

3 –  Dump com paralelismo de 8 threads levou menos de dois minutos.

time pg_dump -Fd -f /backups/export -j 8 db_teste

Screenshot_5

Threads executando o dumpScreenshot_4

Particionamento de Tabelas SQL Server

Pessoal o tema hoje é sobre particionamento de tabelas, a partir da versão 2016 SP1 do SQL Server, essa funcionalidade já é nativa na edição Standard Edition, mas para quem ainda tem ambientes com SQL Server abaixo da 2016, temos uma alternativa que é criar o particionamento manualmente, desta forma segue abaixo exemplo.

-- CRIANDO O BANCO E TABELA PARA O PARTICIONAMENTO MANUAL
CREATE DATABASE TESTE;
GO
USE TESTE
GO

-- CRIANDO FILEGROUP POR ESTADO
ALTER DATABASE TESTE  ADD FILEGROUP PARTICAO_SE;  
ALTER DATABASE TESTE  ADD FILEGROUP PARTICAO_PB;  
ALTER DATABASE TESTE  ADD FILEGROUP PARTICAO_AL;  
GO 
-- SE
ALTER DATABASE TESTE   
ADD FILE   
(  
    NAME = PARTICAO_SE,  
    FILENAME = 'E:\temp\PARTICAO_SE.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 5120MB,  
    FILEGROWTH = 5%  
)
TO FILEGROUP PARTICAO_SE;
-- PB
ALTER DATABASE TESTE
ADD FILE
(  
    NAME = PARTICAO_PB,  
    FILENAME = 'E:\temp\PARTICAO_PB.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 5120MB,  
    FILEGROWTH = 5% 
)
TO FILEGROUP PARTICAO_PB;
-- AL
ALTER DATABASE TESTE
ADD FILE
(  
    NAME = PARTICAO_AL,  
    FILENAME = 'E:\temp\PARTICAO_AL.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 5120MB,  
    FILEGROWTH = 5% 
)
TO FILEGROUP PARTICAO_AL;

-- VERIFICANDO FILEGROUPS CRIADOS E SEUS DATAFILES POR ESTADO
SELECT
dbfile.name AS DatabaseFileName,
dbfile.size/128 AS FileSizeInMB,
sysFG.name AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id


-- A JOGADA DE MESTRE AQUI É, CRIAÇÃO DAS TABELAS SEGMENTADAS POR ESTADO E USANDO UMA CHECK CHECK ESTADO ='SE',ESTADO ='PB',ESTADO ='AL'.
CREATE TABLE TB_FUNCIONARIO_SE
 (
MATRICULA INT NOT NULL,
ESTADO CHAR(2) NOT NULL,
CHECK (ESTADO ='SE'), --CHECK
PRIMARY KEY (MATRICULA,ESTADO)
) ON PARTICAO_SE; -- FILEGROUP SE


CREATE TABLE TB_FUNCIONARIO_PB
 (
MATRICULA INT NOT NULL,
ESTADO CHAR(2) NOT NULL,
CHECK (ESTADO ='PB'),--CHECK
PRIMARY KEY (MATRICULA,ESTADO)
) ON PARTICAO_PB; -- FILEGROUP PB


CREATE TABLE TB_FUNCIONARIO_AL
 (
MATRICULA INT NOT NULL,
ESTADO CHAR(2) NOT NULL,
CHECK (ESTADO ='AL'),--CHECK
PRIMARY KEY (MATRICULA,ESTADO)
) ON PARTICAO_AL; -- FILEGROUP AL


-- VERIFICANDO TABELAS POR FILEGROUP
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables


-- AQUI É A JOGADA DE MESTRE, CRIAMOS UMA VIEW APONTANDO PARA AS TABELAS SEGMENTADAS ESTADO, DESTA FORMA AS OPERAÇÕES VÃO SER NA VIEW E NÃO COM ACESSO DIRETO AS TABELA.
CREATE VIEW VW_FUNCIONARIOS 
AS
SELECT * FROM TB_FUNCIONARIO_SE
UNION ALL
SELECT * FROM TB_FUNCIONARIO_PB
UNION ALL
SELECT * FROM TB_FUNCIONARIO_AL


-- CARGA DE DADOS 50 LINHAS ESTADO AL INSERINDO DIRETO NA VIEW VW_FUNCIONARIOS
DECLARE @i int = 0
WHILE @i < 50 
BEGIN
    SET @i = @i + 1
	INSERT INTO  VW_FUNCIONARIOS (MATRICULA,ESTADO) VALUES (@i,'SE');
END

-- CARGA DE DADOS 50 LINHAS ESTADO PB INSERINDO DIRETO NA VIEW VW_FUNCIONARIOS
DECLARE @i int = 0
WHILE @i < 50 
BEGIN
    SET @i = @i + 1
	INSERT INTO  VW_FUNCIONARIOS (MATRICULA,ESTADO) VALUES (@i,'PB');
END

-- CARGA DE DADOS 50 LINHAS ESTADO AL INSERINDO DIRETO NA VIEW VW_FUNCIONARIOS
DECLARE @i int = 0
WHILE @i < 50 
BEGIN
    SET @i = @i + 1
	INSERT INTO  VW_FUNCIONARIOS (MATRICULA,ESTADO) VALUES (@i,'AL');
END

-- VERIFICANDO LINHAS INSERIDAS POR TABELA DO ESTADO
SELECT * FROM TB_FUNCIONARIO_SE;
SELECT * FROM TB_FUNCIONARIO_PB;
SELECT * FROM TB_FUNCIONARIO_AL;

-- SELECT NA VIEW QUE SERA USADA PARA TODAS AS OPERAÇÕES DE SELECT,INSERT,UPDATE,DELETE
SELECT * FROM VW_FUNCIONARIOS 

1 – Filegroups do banco teste

SELECT
dbfile.name AS DatabaseFileName,
dbfile.size/128 AS FileSizeInMB,
sysFG.name AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id

Screenshot_1

2 – Tabelas por filegroup

SELECT o.[name] tabela, o.[type], i.[name], i.[index_id], f.[name] filegroup
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables

Screenshot_2

3 – Informações por tabela

SELECT top 5 * FROM TB_FUNCIONARIO_SE;
SELECT top 5 * FROM TB_FUNCIONARIO_PB;
SELECT top 5 * FROM TB_FUNCIONARIO_AL;

Screenshot_3

4 – Visão VW_FUNCIONARIOS contendo todas as tabelas

SELECT TOP 5 * FROM VW_FUNCIONARIOS WHERE ESTADO ='SE';
SELECT TOP 5 * FROM VW_FUNCIONARIOS WHERE ESTADO ='PB';
SELECT TOP 5 * FROM VW_FUNCIONARIOS WHERE ESTADO ='AL';

Screenshot_4

 

Habilitar Wal PostgreSQL

Dica de hoje é bem simples de como habilitar o archivelog e gerar um backup físico do seu PostgreSQL.

1 - Verificar parametros necessarios
postgres=# SHOW archive_mode;
postgres=# SHOW archive_command;

2- Criar diretorios dos backups e archivelog
mkdir -p /backups/archivelog/
mkdir -p /backups/logs
mkdir -p /backups/scripts

3- Alterar parametros:
-- HABILITANDO WAL
postgres=# ALTER SYSTEM SET archive_mode TO 'on';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET archive_command TO 'test ! -f /backups/archivelog/%f && cp %p /backups/archivelog/%f';
ALTER SYSTEM

-- FORÇAR A GERAÇÃO DO ARCHIVELOG A CADA 30 MINUTOS = 1800
postgres=# ALTER SYSTEM SET archive_timeout TO '1800';
ALTER SYSTEM

-- Reiniciar PostgreSQL
[root@vm-postgresql ~]# systemctl restart postgresql-12

4- Forçar geração do archivelog
[postgres@vm-postgresql ~]$ psql -c "select pg_switch_wal();"
 pg_switch_wal
---------------
 0/16BCDA8
(1 row)

-- VERIFICANDO ARCHIVELG GERADO
[postgres@vm-postgresql log]$ ls -lat /backups/archivelog/
total 16384
-rw------- 1 postgres postgres 16777216 Mar  5 10:31 000000010000000000000001
drwxr-xr-x 2 postgres postgres       38 Mar  5 10:31 .
drwxr-xr-x 4 postgres postgres       36 Mar  5 10:13 ..

5- Gerando backup com compressão 
[postgres@vm-postgresql ~]$ pg_basebackup -Ft -X none -D /backups/full/`date +%Y%m%d` --verbose --progress --compress=9 2>> /backups/logs/backup_full_`date +%Y%m%d`.log 1>>/backups/logs/backup_full.err

-- Backup gerado
[postgres@vm-postgresql backups]$ ls -lat /backups/full/20200305/
total 2956
-rw------- 1 postgres postgres 3025922 Mar  5 10:35 base.tar.gz
drwx------ 2 postgres postgres      25 Mar  5 10:34 .
drwxr-xr-x 3 postgres postgres      22 Mar  5 10:34 ..

6- Limpeza de archivelogs com mais de 7 dias
/usr/bin/find /backups/archivelog -name "*.*" -type f -mtime +7 -exec rm -f {} \;
find /backup/backup_full -empty -type d -delete


Rodar scripts ou comandos em background

Pessoal sei que a dica de hoje é básica, mas muita gente que esta iniciando no mundo do linux não tem conhecimento de como executar um script ou comando em background via ssh em um servidor, abaixo segue exemplo de execução com a saída no comando de texto com logs nohup.out.

1 - Executando script para rodar em background ate seja finalizado sua execução:
nohup ./export_full.sh &
2 - Executar comando em background com um simples ping:
nohup ping -i1 www.google.com &
3 - Acompanhar em tempo real execução lendo os logs da saida do nohup:
tail -f nohup.out
4 -  Verificar as ultimas 100 linhas do log nohup.out
tail -100 nohup.out

Criando PDB Manualmente Oracle 19

Dica de hoje é de como criar um PDB (Pluggable Database) para separar os ambientes de produção, homologação e teste. O CDB e PDB nada mais é que fazendo uma analogia ao SQL Server, é você ter uma instancia CDB (Container) com vários bancos (PDB Pluggable Databases) onde o CDB possui os metadados do Oracle e o PDB os metadados dos clientes/sistemas, facilitando assim transportar o PDB  para outra instancia Oracle lembrando o velho e famoso attach e detach do SQL Server.

1 – Verificando o container database conectado (CDB);

SQL> show con_name

Screenshot_1

2 – Criando o PDB HML para homologação passando o diretório base criando outro com o PDB HML.

SQL> show con_name

CON_NAME
——————————
CDB$ROOT
SQL> create pluggable database HML admin user pdb_admin identified by manager file_name_convert = (‘/u02/oradata/ORA19C/pdbseed’, ‘/u02/oradata/ORA19C/HML’);

Screenshot_2

 

 

Screenshot_3

3 – Status do PDB HML apos a criação:

select con_id, name, open_mode from v$pdbs;

Screenshot_4

4 – Disponibilizar o PDB HML para uso pois o mesmo esta apenas montado:

SQL> alter session set container = CDB$ROOT;

SQL> alter pluggable database HML open;

Screenshot_5

Screenshot_6

Screenshot_7

4 – Criando usuário para acessar apenas o PDB HML e testar o acesso via TNSNAMES:

SQL> show con_name

CON_NAME
——————————
HML
SQL> create user HML_APP identified by HML_APP;

User created.

SQL> grant create session to HML_APP;

Grant succeeded.

Screenshot_8

sqlplus HML_APP/HML_APP@localhost:1521/HML

Screenshot_9