Instalando e Implementando DBI-LINK no PostgreSQL

Existem ocasiões onde temos 2 servidores distintos e precisamos de um merge dessas informações, como obter o retorno de um select se tenho dados em 2 servidores separados ???????

Seus problemas acabaram!!!! … Nesse caso temos 2 soluções DBLINK e DBI-LINK, sendo que se os 2 servidores forem postgres, use dblink mas se um for postgres e outro for Oracle, Mysql ou seja la qual banco … usamos o dbi-link.

Vendo o site da pgcon 2008 Internacional achei uma palestra muito bacana do David Fetter sobre o DBI-LINK 3.0 resolvi testar esse bicho … e querem saber ??? é muito bacana !!!!!!.

Antes de começar só vamoslembrar que quando usamos esse tipo de recurso temos que levar em conta que ficamos “refens” da rede, seja interna ou externa, uma vez que as informações estão em servidores distintos

Instalando o postgres

Baixando pacotes

# aptitude install ssh
# aptitude install gcc
# aptitude install make
# aptitude install libreadline-dev
# aptitude install zlib1gdev
# aptitude install zlibc
# aptitude install zlib1g-dev
# aptitude install libio-zlib-perl
# aptitude install perl
# aptitude install libyaml-perl
# aptitude install libconfig-yaml-perl
# aptitude install libyaml-syck-perl
# aptitude install libtest-yaml-meta-perl
# aptitude install libtest-yaml-valid-perl

Compilando
$ tar -xvzf postgresql-8.3.1.tar.gz

$ mv postgresql-8.3.1 postgresql-8.3
$ cd postgresql-8.3
$ cd src/include/
$ vi pg_config_manual.h

#define BLCKSZ 8192 – Usado em BI por isso o bloco de gravação é maior
#define BLCKSZ 4096 – Usado em Transacionais por isso o bloco de gravação é menor

$ cd ../../
$ ./configure –prefix=/home/postgres/postgresql-8.3 –with-python –with-perl
$ make $$ make install

Criando o cluster

O diretório pg83 deve ser criado para alocar o novo cluster

$ mkdir -p /postgres/pg83/dados/
$ /home/postgres/postgresql-8.3/bin/initdb -D /postgres/pg83/dados/ –encoding=latin1

Subindo o banco

$ cd /postgres/pg83/dados/
$ /home/postgres/postgresql-8.3/bin/pg_ctl -D . start

Baixando os pacotes para para DBI-LINK

Para esse procedimento são necessários 2 pacotes: DBI-LINK e o DBD-Pg

Instalando os pacotes

Esse recurso para ser instalado necessita de alguns pré requisitos, ou seja, alguns pacotes instalados:

DBI-LINK

build, test, and install Perl 5 (at least 5.6.1)

BDB-PG

build, test, and install Perl 5 (at least 5.6.1)
build, test, and install the DBI module (at least 1.52)
build, test, and install PostgreSQL (at least 7.4)
build, test, and install Test::Simple (at least 0.47)

Descompacte os pacotes e execute o Makefile.pl para que a instalação seja feita:

$ tar – xvjf dbi-link-2.0.0.tar.bz2
$ cd dbi-link-2.0.0
$ perl Makefile.PL
Writing Makefile for dbi-link

$ tar -xvzf DBD-Pg-2.7.2.tar.gz

O Makefile padrão usa algumas variáveis que devem ser definidas antes da instalação:
POSTGRES_HOME – Instalação do PostgreSQL
POSTGRES_LIB – bilbiotecas do PostgreSQL
POSTGRES_INCLUDE – Diretório de include do PostgreSQL

Para fazer o export das variáveis:

$ export POSTGRES_LIB=”/home/postgres/postgresql-8.3/lib/”
$ export POSTGRES_HOME=”/home/postgres/postgresql-8.3/”
$ export POSTGRES_INCLUDE=”/home/postgres/postgresql-8.3/include/”

Os primeiros passos da instalação devem ser feitos com o usuário comum postgres:

$ cd DBD-Pg-2.7.2
$ perl Makefile.PL
Configuring DBD::Pg 2.7.2
PostgreSQL version: 80301 (default port: 5432)
POSTGRES_HOME: /home/postgres/postgresql-8.3/
POSTGRES_INCLUDE: /home/postgres/postgresql-8.3/include/
POSTGRES_LIB: /home/postgres/postgresql-8.3/lib/
OS: linux
Checking if your kit is complete…
Looks good
Using DBI 1.604 (for perl 5.010000 on i486-linux-gnu-thread-multi) installed in /usr/lib/perl5/auto/DBI/
Writing Makefile for DBD::Pg

$ make

O make install criará alguns diretórios em locais de sistema que não é permitido a usuário comum, sendo assim execute-o com o usuário root

# make install

Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/local/lib/perl/5.10.0/auto/DBD/Pg/Pg.so
Installing /usr/local/lib/perl/5.10.0/auto/DBD/Pg/Pg.bs
Installing /usr/local/lib/perl/5.10.0/Bundle/DBD/Pg.pm
Installing /usr/local/lib/perl/5.10.0/DBD/Pg.pm
Installing /usr/local/man/man3/Bundle::DBD::Pg.3pm
Installing /usr/local/man/man3/DBD::Pg.3pmWriting /usr/local/lib/perl/5.10.0/auto/DBD/Pg/.packlist
Appending installation info to /usr/local/lib/perl/5.10.0/perllocal.pod

Adicionando um DBI-LINK

Antes de tudo é necessário criar a linguagem plperl no banco em questão, pois o dbi-link foi desenvolvido em PL/PERL.

$ createlang plperlu portgres

Agora rode o script que criará toda estrutura dbi-link

$ psql -p 5432 < /home/postgres/dbi-link-2.0.0/dbi_link.sql

Note que no banco foi criado o schema dbi_link, com 3 tabela, 26 funções e 1 trigger

Adicionando uma conexão Remota

MYSQL

Criando nova conexão

SELECT dbi_link.make_accessor_functions(
‘dbi:mysql:database=teste;host=localhost’,–Driver:banco:host‘root’, – usuário
”, – senha
‘— AutoCommit: 1 RaiseError: 1 ‘, – atributos do banco remoto
NULL, – ambiente de conexão
NULL, – schema remoto
NULL, — catalogo remoto
‘teste’ — schema local

Usando a conexão

SELECT * FROM teste.tabela;

Transferindo dados de um banco para outro

É possivel transferir dados direto de um banco para outro, porém com algumas restrições (que serão resolvidas futuramente, segundo o desenvolvedor). Todos os dados retornados do banco externo são formato text, sendo assim ou a tabela destino tem os campos text ou trate as informações antes de fazer a inserção.

CREATE SCHEMA testepg
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA testepg TO postgres;

CREATE TABLE testepg.tabela
(
campo1 character varying,
campo2 character varying,
campo3 character varying,
campo4 date,
campo5 character varying,
campo6 text
CONSTRAINT tabela_pk PRIMARY KEY (campo1)
)
WITH (OIDS=FALSE);
ALTER TABLE testepg.tabela OWNER TO postgres;

INSERT INTO testepg.tabela
(SELECT campo1:: character varying,
campo1:: character varying,
campo2:: character varying,
campo3:: date,
campo4:: character varying,
campo5:: text
FROM teste.tabela
WHERE campo1 = ‘XXX’);

Bom .. por hoje é só pessoal !!!!!!
PS: vou fazer mais algumas brincadeiras com o DBI-LINK e posto aqui.
PS2: Conversei com o David e vou traduzir o pacote dele !!!

Kenia Milene

Particionamento de Tabelas no PostgreSQL

Quando temos uma tabela muito grande, ou seja com milhões de linhas a melhor opção é particiona-la para uma melhor performance do banco. Para isso, é necessário algumas regras na tabela principal e criar as tabelas auxiliares:

Veja abaixo.

1 – Crie a tabela principal

CREATE TABLE salario(
funcionario numeric(10) NOT NULL,
centro_custo character varying(10) NOT NULL,
valor_resultado numeric(13,2),
data_evento date,evento numeric(3),
conta numeric(10),cargo numeric(5)
)
WITH (OIDS=TRUE);

2 – Crie as tabelas particionadas para inserção dos dados, sendo que as mesmas devem herdar as caracteristicas da tabela principal, para isso usamos o () INHERITS

–JANEIRO

CREATE TABLE salario_jan () INHERITS (salario);

–FEVEREIRO

CREATE TABLE salario_fev () INHERITS (salario);

……………………..

–DEZEMBRO

CREATE TABLE salario_dez () INHERITS (salario);

3 – Crie a regra que no caso de uma tabela de pagamentos de funcionarios, Nesse caso, o campo referencia é a data de pagamento tendo base o mês. Sendo assim crie uma rule para cada mês, onde a cada insert feito na tabela mãe … os dados são filtrados e inseridos em cada tabela filha correspondende ao mês.

– JANEIRO

CREATE OR REPLACE RULE insert_jan AS
ON INSERT TO salario
WHERE date_part (‘month’::text, new.data_evento)=1
DO INSTEAD INSERT INTO salario_jan (funcionario, centro_custo, valor_resultado, data_evento, evento, conta, cargo)
VALUES (new.funcionario, new.centro_custo, new.valor_resultado, new.data_evento, new.evento, new.conta, new.cargo);

– FEVEREIRO

CREATE OR REPLACE RULE insert_fev AS
ON INSERT TO salario
WHERE date_part(‘month’::text, new.data_evento)=2
DO INSTEAD INSERT INTO salario_fev (funcionario, centro_custo, valor_resultado, data_evento, evento, conta, cargo)
VALUES (new.funcionario, new.centro_custo, new.valor_resultado, new.data_evento, new.evento, new.conta, new.cargo);

……………………………

– DEZEMBRO

CREATE OR REPLACE RULE insert_dez AS
ON INSERT TO salario
WHERE date_part(‘month’::text, new.data_evento)=12
DO INSTEAD INSERT INTO salario_dez (funcionario, centro_custo, valor_resultado, data_evento, evento, conta, cargo)
VALUES (new.funcionario, new.centro_custo, new.valor_resultado, new.data_evento, new.evento, new.conta, new.cargo);

Explicando …

A função data_part, vai extrair uma determinada parte da data a ser imposta pela regra.

No caso desse particionamento a referencia é o mês. Sendo assim no momento do insert, será verificado o mês em questão e redirecionado para tabela particionada correspondente.

É importante dizer que se um registro não atender a uma determinada regra, ele será inserido na tabela principal.

E que a tabela principal se comportará como uma tabela MERGE, sendo assim ela mostrará todos os registros !!

4 – Criando os Indices ..

Para a busca ser mais rápida é interessante a criação de indice nas tabelas particionadas.

– JANEIRO

CREATE INDEX salario_jan_idx
ON salario_jan
USING btree
(data_evento);

– FEVEREIRO

CREATE INDEX salario_fev_idx
ON salario_fev
USING btree
(data_evento);

………………………..

– DEZEMBRO

CREATE INDEX salario_dez_idx
ON salario_dez
USING btree
(data_evento);

4 – Criando as views …..

As views vão auxiliar na vizualização dos dados particionados por mês, sendo assim, algumas views são interessantes ….

View para cada mês CORRENTE.

CREATE OR REPLACE VIEW vw_salario_jan AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario_jan a
WHERE date_part(‘month’::text, data_evento) = 1
AND date_part(‘year’::text, data_evento) = now()
ORDER BY a.data_evento;

CREATE OR REPLACE VIEW vw_salario_fev AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario_fev a
WHERE date_part(‘month’::text, data_evento) = 2
AND date_part(‘year’::text, data_evento) = now()
ORDER BY a.data_evento;

—————–

CREATE OR REPLACE VIEW vw_salario_dez AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario_dez a
WHERE date_part(‘month’::text, data_evento) = 12
AND date_part(‘year’::text, data_evento) = now()
ORDER BY a.data_evento;

View de anos anteriores.

Para vizualização dos anos anteriores as seguintes views

CREATE OR REPLACE VIEW vw_salario_2006 AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario a
WHERE date_part(‘year’::text, a.data_evento) = 2006
ORDER BY a.data_evento;

CREATE OR REPLACE VIEW vw_salario_2007 AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario a
WHERE date_part(‘year’::text, a.data_evento) = 2007
ORDER BY a.data_evento;

CREATE OR REPLACE VIEW vw_salario_2008 AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario a
WHERE date_part(‘year’::text, a.data_evento) = 2008
ORDER BY a.data_evento;

View dos ultimos 6 meses apartir do 1o dia util

CREATE OR REPLACE VIEW vw_6meses_anteriores AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta,a.cargo
FROM salario a
WHERE a.data_evento >= (current_date – (date_part( ‘day’ , current_date)::integer -1)) – interval ‘6 months’
ORDER BY a.data_evento;

Bom … apartir dai .. é possivel a criação de varias views para ver os dados …
Espero que seja util para vcs como foi pra mim !!!!

Kenia Milene

Alterando Tablespace de Tabelas e Indices no PostgreSQL

Bem, bem, bem ….
O uso de tablespace pode e muito ajudar na administração do banco. Com esse recurso conseguimos um valor mais preciso de volumetria, podemos também ( e é o mais recomendável) separar indices e dados.

Mundo Ideal ??? um disco só para indices e um só para dados assim não temos concorrência e ajudamos nosso amigo banco de dados.

O Postgres ao criar um objeto ele manda tudo para a tablespace padrão que é a pg_default, o que fiz foi criar 2 tablespaces a banco_data e a banco_idx e separar os objetos.

Com isso teremos dados de analise mais precisos e mais performance no banco.

Segue abaixo os scripts usados para essa façanha.

ALTERANDO AS TABELAS

– Cria TableSpace

CREATE TABLESPACE “banco_data” OWNER postgres LOCATION ‘/postgres/pg825/dados/pg_tblspc/banco_data’;

– verifica se as tablespaces foram criadas

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

– Gera Script para alterar tabelas

SELECT ‘ALTER TABLE’ ,n.nspname AS schemaname,’.', c.relname AS tablename, ‘SET TABLESPACE banco_data;’
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

– Confere alteracao das tabelas

SELECT n.nspname AS schemaname, c.relname AS tablename, t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname, c.relname

– Verifica tabelas sem tablespace

SELECT n.nspname AS schemaname, c.relname AS tablename, t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
AND t.spcname IS NULL
ORDER BY t.spcname DESC
– Verifica tamanho da tablespace

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

ALTERANDO OS INDICES

– Cria TableSpace

CREATE TABLESPACE “banco_idx” OWNER postgres LOCATION ‘/postgres/pg825/dados/pg_tblspc/banco_idx’;

– verifica se as tablespaces foram criadas

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

– Verifica quais sao os indices ( Nao primarios) e o tamanho

SELECT n.nspname AS schemaname,c.relname AS tablename,
c.relpages::numeric * 4.096 / 1024::numeric AS espaco_mb
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i’::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

– Gera Script para alterar indices

SELECT ‘ALTER INDEX’, n.nspname AS schemaname , ‘.’ ,c.relname AS tablename, ‘SET TABLESPACE banco_idx;’
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i’::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

– Confere alteracao dos indices

SELECT n.nspname AS schemaname ,c.relname AS tablename,t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i’::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

– Verifica indice sem tablespace

SELECT n.nspname AS schemaname ,c.relname AS tablename,t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i’::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
AND t.spcname IS NULL
ORDER BY t.spcname DESC
– Verifica tamanho da tablespace

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

Espero que tenha ajudado
Kenia Milene

Criando Um Servidor de Banco de Dados PostgreSQL Bem Bacana Usando DEBIAN

Bem bem bem ….
Tudo muito bom tudo muito bem …. maaaaaaaas …
Existe algum padrão ou regra para servidores Debian de Banco de Dados???
Veja bem … !!!!!!

Não existe uma regra … mas algumas coisas são de bom tom ….

Desenhei um padrão que se que tornou bastante útil nos projetos … (sugestões são bem vindas)

• Sistema Operacional

Debian 4 (ETCH) System Base (última versão estável ).

Por se tratar de um servidor de dados não é necessário a instalação do modo gráfico, precisamos do servidor mais enxuto possível, apenas com o system base.

elegance:~# cat /etc/debian_version
4.0

• Partições

Instalar ao sistema operacional em uma particao suficiente e deixar o resto do espaço para que possamos arquitetar as partições para o Banco.

/pgbackup – Partição de backup
/postgres – Partição de dados (produção)
/pghomo – Partição de dados (Homologação/teste)
/pgdev – Partição de dados (Desenvolvimento)
/pg_log – Partição para log

Só lembrando que o ideal é que:

1 – Servidor de produção é um, desenvolvimento é outro e homologação é outro … Evite colocar todas as bases no mesmo servidor … isso afeta o desempenho.

2 – Para ter um servidor eficiente é indicado que cada partição esteja em um disco para não ter concorrência de IO

• Kernel

Usar sempre a ultima versão estável do Kernel

elegance:~# uname -a
Linux HOST 2.6.18-5-686 #1 SMP Fri Jun 1 00:47:00 UTC 2007 i686 GNU/Linux

• Source list

É imprescindível usar o source list estável padrão indicado na documentação.

elegance:~# cat /etc/apt/sources.list
# See sources.list(5) for more information, especialy
# Remember that you can only use http, ftp or file URIs
# CDROMs are managed through the apt-cdrom tool.
deb http://http.us.debian.org/debian stable main contrib non-free

deb http://security.debian.org stable/updates main contrib non-free

# Uncomment if you want the apt-get source function to work
#deb-src http://http.us.debian.org/debian stable main contrib non-free
#deb-src http://non-us.debian.org/debian-non-US stable/non-US main contrib non-free

• Pacotes Adicionais

Sempre instalar o pacote linux-image-2.6-<arquitetura>, onde <arquitetura> é a família de processadores, como 686, por exemplo: esse pacote é virtual e sempre depende da última versão de kernel estável com todos os patches de segurança aplicados. Instalar o linux-image é saber quando é necessário atualizar o kernel. Isso acontece ao usar o aptitude upgrade fazendo com que o o pacote mais novo do kernel (quando existir) apareça como um upgrade.

elegance:~# aptitude install linux-image-2.6-686
elegance:~# aptitude install vim
elegance:~# vim /etc/vim/vimrc
“syntax on
syntax on

elegance:~# aptitude install ssh
elegance:~# aptitude install gcc
elegance:~# aptitude install apticron

O apticron avisa que tem atualizações disponíveis, baixa as atualizações e deixa em cache localmente no servidor onde elas forem baixadas. Então aplique com aptitude upgrade.

elegance:~# aptitude install libreadline-dev
elegance:~# aptitude install zlib1gdev

Esses pacotes server para usar o TAB como complemento qdo usado o psql no shell

• Usuários

postgres – Usuário do postgresql

elegance:~# adduser postgres

Esse usuário deve ser dono e grupo das partições postres e pg*

• Rede (dominio / nome)

Identificar o servidor faz bem né !!!!

elegance:~# cat/etc/hosts
127.0.0.1 localhost
IP HOST.DOMINIO HOST

• Variáveis de Ambiente

Configurar as variáveis de ambiente ajuda na administração do servidor.
As configurações abaixo foram feitas no usuário postgres que é adminitrador do PostgreSQL

elegance:~$ vi .bashrc
alias vi=’vim’

elegance:~$ vi /home/postgres/.bashrc
PG=$HOME/postgresql-8.2.5/bin
PATH=$PATH:/$PG
PAGER=/usr/bin/less
export PATH PG PAGER
LESS=”-S-N”

E Era isso !!!!

O André Lopes fez um post muito bacana sobre servidores Debian … Segue abaixo o link do post

http://www.andrelop.org/blog/2007/11/16/gerenciamento-de-atualizacoes-uma-solucao-simples-e-eficaz/

Kenia Milene

Projeto de Migração do PostgreSQL 8.1 pra 8.2.5 – FASE 4 : O Pavoroso Dia D !!

Pois é minha gente vamos ao pavoroso dia D …
O dia que ninguém almoça, ninguém respira e ninguém dorme !!!

Bem .. posso dizer que essa migração foi bem tranqüila.

Para não esquecer algum passo eu fizemos uma especie de check list de migração. Segue abaixo os procedimentos

1 – Compila
Esse é um processo que já vimos anteriormente … sendo assim não tem mistério.

PS: Só lembrando que para compilação ocorrer se erros é necessário 2 pacotes … Caso ele não esteja instalado …. instale !!!

# aptitude install libreadline-dev
# aptitude install zlib-dev

$ tar -xvzf postgresql-8.2.5.tar.gz
$ cd postgresql-8.2.5
$ cd /src/include
$ vi pg_config_manual.h

$ cd ../../
$ ./configure –prefix=/home/postgres/postgresql-8.2.5 –with-python –with-perl
$ make
$ make install

2 – Cria o cluster
Esse passo também é tranqüilo

/home/postgres/postgresql-8.2.5/bin/initdb -D /pgteste/pg825/dados/ –encoding=latin1

3 – Cria o diretório pg_log
Bem .. isso é um passo que tem que se levado em consideração, senão todas as mensagens de log são exibidas na tela. Uma outra opção é mudar o diretório de log no postgresql.conf

$mkdir pg_log

4 – Copiar e imprimir o postgresql.conf da produção e compara com o novo (8.2.5). Basicamente nada muda, mas temos que nos atentar ao parâmetro DATESTYLE, pois nas versões anteriores a o formato da data é MDY e nessa nova versão é exibido como DMY. Isso pode causar sérios problemas la na frente.

scp -rv postgres@sd1cco:/postgres/pg812/dados/postgresql.conf .
lsten_addresses = ‘*’
port = 5432
max_connections = 25
shared_buffers = 300MB
work_mem = 100MB
search_path = ‘public’
datestyle = ‘iso, mdy‘

$cat /proc/sys/kernel/shmmax
841572800

5 – Muda as portas. Para não ter nenhum tipo de intervenção vamos mudar a porta padrão do atual banco de produção e no novo banco.

Antiga produção de 5432 para 5434 e novo de 5432 para 5436

6 – Altera o pg_hba.conf.

Colocar as configurações que estão em produção

7 – Copia o _database e o _deny. Arquivos esse que define quais usuários são permitidos acesso no banco.

8 – Mudar o /proc/sys/kernel/shmmax. Entende-se que esse parâmetro já foi modificado, pois nesse caso a nova versão vai rodar no mesmo servidor.

vi /etc/sysctl.conf
kernel.shmmax = <VALOR>
cat > /proc/sys/kernel/shmmax
<VALOR>
ctrl + d

9 – Sobe os bancos. Lembrando que estão subindo em portas diferentes do padrão.

/home/postgres/postgresql-8.2.5/bin/pg_ctl -D . start
/home/postgres/postgresql-8.1/bin/pg_ctl -D . start

10 – Altera a senha do postgres da nova versão

/home/postgres/postgresql-8.2.5/bin/psql -p 5436
postgres# ALTER Role postgres PASSWORD ‘SENHA’;
postgres# CREATE DATABASE BANCO
WITH OWNER = postgres
ENCODING = ‘LATIN1′;

11 – Altera o timezone do banco (isso somente no horário de verão)

ALTER DATABASE BANCO SET TimeZone=”Brazil/DeNoronha”;

12 – Para poder fazer a conexão é necessário configurar o pg_hba.conf

# “local” is for Unix domain socket connections only
local all all trust
# # IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
# # IPv6 local connections:
host all all ::1/128 trust

13 – DUMPALL direto do banco de produção para o banco novo

/home/postgres/postgresql-8.2.5/bin/pg_dumpall -p 5434 -i | /home/postgres/postgresql-8.2.5/bin/psql -p 5436 -d pg03

Conferência pós migração

14 – Verifica se todos os objetos foram migrados. Esse passo é de extrema importância, pois todos os objetos devem estar no novo banco. Para isso basta apenas rodar a query abaixo nas 2 bases. (O Leo Cezar salvou o dia me ajudando a montar esse script … Obrigado Leo !!!!):

SELECT o.esquema,o.objecto,COUNT(o.nm_objecto) FROM
(
SELECT n.nspname AS “esquema”,
CASE c.relkind
WHEN ‘r’ THEN ‘TABELAS’
WHEN ‘v’ THEN ‘VISÃO’
WHEN ‘S’ THEN ‘SEQUENCE’
WHEN ‘i’ THEN ‘INDICE’
END as “objecto”,
c.relname as “nm_objecto”
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN (‘S’,'r’,'v’,'i’)
AND n.nspname NOT IN (‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,
‘pg_toast’,'xmg’,'postgres’,'publico’,'public’)

UNION
SELECT trigger_schema AS “esquema”,
‘TRIGGER’ AS “objecto”,
trigger_name as “nm_objecto”
FROM information_schema.triggers

UNION
SELECT specific_schema AS “esquema”,
‘FUNÇÃO’ AS “objecto”,
specific_name as “nm_objecto”
FROM information_schema.routines
WHERE data_type <> ‘”trigger”‘

UNION

SELECT specific_schema AS “esquema”,
‘FUNÇÃO DE TRIGGER’ AS “objecto”,
specific_name as “nm_objecto”
FROM information_schema.routines
WHERE data_type = ‘”trigger”‘

) AS o
GROUP BY esquema,objecto
ORDER BY 1,2
;

15 – Muda o caminho do .bashrc. Precisamos mudar o path do postgres nessa etapa.

$ vi /home/postgres/.bashrc
PG=$HOME/postgresql-8.2.5/bin
PATH=$PATH:/$PG
PAGER=/usr/bin/less
export PATH PG PAGER

16 – É importante não esquece que o serviço não sobe automático, sendo assim é de extrema importância ter um script no init pra fazer esse trabalho. No caso desse projeto esses arquivos já existem, só alteramos o caminho para 8.2.5.

17 – É importante também não esquece dos backups, os comandos de dump devem apontar para o home da nova versão. No caso desse projeto alteramos os caminhos de onde os dumps são alocado para um diretório com o nome da nova versão.

18 – E por final, baixe o banco da versao 8.1, altere a porta da versão 8.2 e suba novamente.

19 – Por final, podemos acompanhar o andamento do banco através de algumas views que podemos criar para monitorar memória, disco, consulta corrente e muito mais. (O Marquinho criou algumas que facilitam a vida do pẽao !!!). Vou postar somente uma para dar água na boca.

View para verificar % de utilização de memória

CREATE OR REPLACE VIEW disco_mem AS

SELECT (sum(pg_stat_database.blks_hit) / sum(pg_stat_database.blks_read + pg_stat_database.blks_hit) * 100::numeric)::integer AS “% de Utilização de Mem”
FROM pg_stat_database;

COMMENT ON VIEW disco_mem IS ‘
Calcula através das estatisticas o percentual de utilização disco / memória.
Valores acima de 70% significa que o banco esta realizado mais tarefas em memória do que i/o em disco’;

E no final entre mortos e feridos sobrevivemos todos!!
Kenia Milene

PGCON 2007 Bombou !!!!!

É isso ai minha gente no ultimo sábado rolou o 1o PGCON aqui no Brasil.

Contamos com cases super interessantes como o do METRO de São Paulo e da FAB. Colaboradores do mundo livre também estavam la, como Telles, Diogo, Leo, Isis, David Fetter, Fike, Euler, Dutra e muito mais …

O evento bombou mesmo tinha muita gente !!!! as palestras foram muito bem recebidas e o auditório ficou cheio até a ultima (que foi de tunning do Fike).

No final rolou a chopada fornecida pela Lev Chopp e todos os organizadores terminaram a noite jantando no Rocks.

Se o primeiro foi surpreendente imagina só os próximos como serão!!!!!

Alguem já brincou de onde esta o Wally ????? Vamos brincar de onde esta  a Kenia?????. Acredite se quiser .. mas eu estou ai no meio !!!!!!

Kenia Milene

Porque usar Debian ao invés de Fedora para Servidores PostgreSQL ????

Me fizeram essa pergunta …

Eu teria milhões de justificativas, mas nada como as palavras de um dos desenvolvedores não é mesmo minha gente !!!!

Da-lhe Andrelop !!!!!

http://www.andrelop.org/blog/2006/09/08/por-que-debian/

Kenia Milene

Projeto de Migração do PostgreSQL 8.1 pra 8.2.5 – FASE 3 : Os Testes do Desenvolvimento

Bem …. já logo de cara sabíamos que enfrentaríamos um problema.

A maioria dos desenvolvedores aqui não indicam o schema dos objetos em suas rotinas, o schema era definido através do search_path de cada usuário (ALTER ROLE usuario SET search_path=schema).

Porém a versão 8.2.5 não reconhece o código se não indicar <schema.objeto>. Até existe um parâmetro de compatibilidade no postgres.conf da nova versão (add_missing_from = off), ou poderíamos manter o search_path do usuário, mas isso faz com que o banco faça mais uma verificação, fazendo o mesmo trabalhar mais um pouquinho !!!!

Pois bem … notificamos o desenvolvedor sobre essa mudança, e o mesmo alterou uma rotina da aplicação para teste. Resultado????

O desenvolvedor achou que ficou mais rápido e concordou em modificar as outras rotinas e as futuras já vir com essa alteração.

Estamos no aguardo do desenvolvedor terminar seus testes e nos dar o OK para o dia D

É isso ai ….
Contagem regressiva para o Dia D !!!

Kenia Milene

Projeto de Migração do PostgreSQL 8.1 pra 8.2.5 – FASE 2 : Preparando o Ambiente PostgreSQL

Permissões

Bem como dito la no começo o ponto de montagem para o banco é o /postgres, esse ponto deve ter o dono e o grupo postgres para que tudo funcione perfeitamente, sendo assim, antes de tudo crie o usuário postgres !!!!
Assim que o server foi instalado fizemos as alterações acima.

A Compilação

A versão para que vamos migrar é a 8.2.5, baixamos a versão, descompactamos e compilamos no diretório /home/postgres/postgresql-8.2.5 como mostrado a seguir:

$ tar -xvzf postgresql-8.2.5.tar.gz
$ cd postgresql-8.2.5
$ cd src/include
$ vi pg_config_manual.h

#define BLCKSZ 8192

Nesse parâmetro definimos o tamanho de cada bloco, que nesse caso será compilado com blocos de 8KB.

Porque a compilação no home do postgres?????

A compilação no home do postgres nos da a possibilidade de ter mais de uma versão do postgreSQL na mesma máquina, ou seja, posso ter no servidor de produção por exemplo a versão 8.1 (Operacional no momento) e a versão 8.2.5. Com isso posso caso alguma catástrofe aconteça (Esperamos que não!!!) , é possível voltar tudo para a versão anterior. Veja a seguir os passos para a compilação:

$ cd ../../
$ ./configure –prefix=/home/postgres/postgresql-8.2.5 –with-python –with-perl
$ make
$ make install

Path do usuário

É importante alterar o path do usuário postgres, pois como compilamos no home, precisamos indicar onde buscar os binários.

$ vi /home/postgres/.bashrc
PG=$HOME/postgresql-8.2.5/bin
PATH=$PATH:/$PG
PAGER=/usr/bin/less
export $PATH $PG $PAGER
LESS=”-S-N”

$ vi /home/postgres/.bash_profile
PATH=$PATH:$HOME/postgresql-8.2.5/bin:$HOME/bin
PAGER=less
export $PATH $LESS
unset USERNAME

Criando o Cluster

Para isso criamos a seguinte estrutura de arquivos: /postgres/pg825/dados
onde criaremos o cluster através do comando initdb e com o parâmetro latin1 para indicar o idioma a ser usado:

$ cd /home/postgres/postgresql-8.2.5/bin/
$ initdb -D /postgres/pg825/dados/ –encoding=latin1

Ajustando Parâmetros do PostgreSQL

Para configurar os parâmetros antes de subir o banco vamos alterar o arquivo postgresql.conf que esta no /postgres/pg825/dados/ :

$ cd /postgres/pg825/dados/
$ vi postgresql.conf

Parâmetro que define quais endereços clientes poderão fazer conexão no banco. Nesse caso todo e qualquer host é permitido, essa restrição faremos em outro arquivo.

listen_addresses = ‘*’

Parâmetro que define qual porta o serviço irá responder

port = 5432

Número máximo de conexões simultâneas permitidas. Essa é uma informação importante, que deve ser levada em consideração.

max_connections = 25

Memória compartilhada, é indicado no caso de servidores dedicado que seja disponibilizado para o postgresql 1/3 da memória disponível. Como temos 1GB de memória vamos então colocar 300MB.
shared_buffers = 300MB

Parâmetro que define a área de ordenação, área essa usada pelo ORDER BY por exemplo. É a área da memoria disponível no disco usada para ordenação das transações.
É importante saber que se essa área não for o suficiente, vai pra disco fazendo que com tudo fique mais lento. A área de ordenação é definida por usuário, e a quantidade total de conexões permitidas * a área de ordenação não pode ser maior do que a memória disponível ( No nosso caso os 600MB restantes). Lembrando que a ordenação não usa a memória compartilhada e sim a memória disponível.
Nesse caso o valor esta maior do que o normal, pois 25 conexões * 100MB cada uma seria equivalente a 2,5GB (Contamos com que os 25 usuários não façam ordenações simultâneas). Como é apenas ambiente de testes para as aplicações o número de conexões é bem reduzido, logo, não teremos problemas nesse ambiente quanto a área de ordenação.

work_mem = 100MB

Parâmetro que define a ordem da procura no schemas quando um objeto é referenciado apenas pelo nome, sem o schema.
Com esse parametro setado sem a variável $user, todo e qualquer objeto, seja ele tabela, view … etc , que não for apontado o schema será retornado um erro de objeto inexistente.

search_path = ‘public’

Alterando Parâmetros do Kernel

Os parâmetros variam de acordo com o Hardware. O ideal é que a memória compartilhada (Shared_buffers) do servidor seja equivalente a 1/3 da memória total e o shmmax seja equivalente a shared+S.O, ou seja, o parâmetro SHMMAX deve ser o valor da shared_buffers + uma folga para o Sistema Operacional. No nosso caso:

$cat /proc/sys/kernel/shmmax
841572800

Levantando o banco

Depois dos parâmetros configurados vamos levantar o banco:

$ /home/postgres/postgresql-8.2.5/bin/pg_ctl -D . start

Para sabermos se tudo correu bem primeiro vamos ver se o serviço esta de pé:

$ ps -aux | grep postgres
postgres 14666 0.3 0.2 29524 2364 pts/0 S 16:29 0:00 /home/postgres/postgresql-8.2.5/bin/postgres -D .

E então fazer uma conexão no banco

$ psql
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#

Importação do Banco de Produção

Bom .. agora só falta os dados … para isso vamos fazer um dumpall da produção e redirecionar para nosso servidor de testes.
Usamos dumpall porque dessa forma ele leva tudo mesmo … inclusive usuários e grupos.
No servidor de produção:

$ pg_dumpall -p 5432 -i | psql -h host -p 5432 -d banco

Ambiente Disponível e Era isso !!!
Até a fase 3

Kenia Milene

Projeto de Migração do PostgreSQL 8.1 pra 8.2.5 – FASE 1 : O HARDWARE

Pois é minha gente la vou eu para mais uma aventura no mundo dos dados…
O desafio dessa vez é migrar a versão de vários bancos postgreSQL, inclusive os bancos de BI. olha só que aventura …
Bom, vou postando aqui as fases desse projeto, e a primeira fase é preparação de um ambiente de testes e homologação, para isso contamos com um servidor de testes dedicado ao projeto:

A migração

Na verdade essa migração esta sendo feita porque estamos com uma versão muito antiga, o que pode causar sérios problemas com possiveis bugs e assim podemos usufruir das melhorias.

Mas a nossa maior preocupação é: As aplicações vão funcionar?????? A principio sim, mas isso vamos ver ao longo dos testes.

Hardware

Bom .. a primeira luta é para ter um servidor de testes dedicado. Conseguimos!!!!
Não é uma super máquina mas para os testes com o desenvolvimento vai suprir as necessidades.
Infelizmente o projeto não contempla upgrade de hardware, ficamos com os servers antigos.
model name : Intel(R) Pentium(R) 4 CPU 1.80GHz
cpu MHz : 1800.354
cache size : 512 KB
Disk1 : 10 GB
Disk2 : 40 GB

Particionamento

O disco menor deixamos para o Sistema Operacional e o segundo disco para o banco, assim evitamos concorrência na gravação.

/dev/hda2 /
/dev/hda1 /boot
/dev/hdb1 /postgres

Sistema Operacional

Mesmo Sistema Operacional e kernel do servidor de teste será o mesmo que esta em produção.

S.O : Fedora Core 4
Kernel : 2.6.11-1

É isso ai ….
Até a fase 2 !!!!
Kenia Milene