Sobre o DBA Brasil 1.0

Considerações do Fábio Telles (SavePoint)

Sobre o DBA Brasil 1.0

Meus dois centavos !!

Kenia Milene

Está aberta a chamada para os interessados em palestrar no DBA BRASIL 1.0 !!!

Pessoal agora é Oficial:

Se você tem interesse em palestrar em um evento falando sobre Banco de Dados, então está será a sua chance!

Chamada para trabalhos: Se você tiver interesse em apresentar uma palestra neste evento, preencha oformulário de chamada para trabalhos, que estará disponível até o final do dia 18/3/16. Os organizadores farão uma seleção e entrarão em contato com os escolhidos.

Está aberta a chamada para os interessados em palestrar no DBA BRASIL 1.0 !!!

Além da chamada, você encontra informações do evento no site.

Acesse http://dbabrasil1.0.dbabr.com.br

Acesse, inscreva suas palestras e divulgue!!

Meus dois centavos !!

Kenia Milene
Seguir @keniamilene

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???
V
eja 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