Sebastian Webber sebastianwebber

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 &gt; job_name.log 2&gt;&amp;1 &amp;

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

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

	# gera o arquivo com as instruções
	psql -c "SELECT * FROM fnc_apaga_auditoria('2015-01-01','auditoria')" -At > meu_arquivo.sql

	## $ cat meu_arquivo.sql
	## -- Table auditoria.foo
	## DELETE FROM auditoria.foo WHERE dtinc <= '2015-01-01';
	## VACUUM FULL auditoria.foo;
	##
	## -- Table auditoria.bar
	## DELETE FROM auditoria.bar WHERE dtinc <= '2015-01-01';

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

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

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

	#!/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}"');"

	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;

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