PostgreSQL: migrando de LATIN1 pra UTF-8

Para migração de bases, de LATIN1 pra UTF8 no postgresql, não existe uma ferramenta pronta para tal tarefa. Para tal, foi desenvolvido um roteiro (testado em um servidor RedHat5 e Debian 5 com PostgreSQL 8.1 em uma base de em torno de 2 Gb).
Recomendado que teste em um ambiente separado antes de aplicar o procedimento produção.

Primeiro, pare todos os serviços que utilizam o banco (ex. apache/php, tomcat, jboss) para não ter o risco de nenhum usuários alterar o banco durante o processo.
Nos comandos abaixo, substitua:
usuario_banco = usuário dono do banco, com permissões de apagar e criar bancos
meu_banco = o banco que vc quer fazer a migração

fazer um backup em texto plano

/usr/bin/pg_dump -i -h localhost -U usuario_banco -F p -D -v -f bancoL.sql meu_banco

Aqui está o segredo. Use o comando iconv para conversão:

iconv -f iso-8859-1 -t utf-8 bancoL.sql > bancoU.sql

Abra o arquivo bancoU.sql e no inicio, por volta da linha 7, onde tem “SET client_encoding = ‘LATIN1’;” troque LATIN1 por UTF8.

Com banco fazer:

Faça um backup, por garantia.
pg_dump -U usuario_banco -i -F c -b -f banco.backup meu_banco

Agora apague o banco inteiro
dropdb -h localhost -U usuario_banco meu_banco

crie-o em UTF8
createdb -h localhost -E UTF8 -U usuario_banco meu_banco

e agora restaure o banco convertido.
psql -h localhost -U usuario_banco -d meu_banco < bancoU.sql

caso use em seus SQL a função TO_ASCII, com UTF-8 ela não irã funcionar. Veja aqui a alternativa em um artigo passado.

Anúncios

MySql em rede

O banco MySql, por padrão, é configurado para permitir acesso somente da máquina local (onde está instalado). Caso acesse de outra máquina, ele retorna um erro de acesso não autorizado (mesmo estando com usuário/senha correto). Para liberar acesso para  todas as máquinas da rede, edite o arquivo /etc/mysql/my.cnf  e procure a seguinte linha

bind-address = 127.0.0.1

agora a comente ou a apague.
reinicie o mysql

/etc/init.d/mysql restart

Agora o mysql vai aceitar conexão pela rede.
Mas tem um problema. Ele vai aceitar conexão de qualquer máquina, e isso pode gerar problemas de segurança (caso tenha várias interfaces de rede), então no caso o recomendável seria liberar acesso somente para a rede interna (ex: 192.168.1.*). Para isso, foi usado o portmap, ou seja, adicione as seguintes linhas no fim do arquivo /etc/host.allow

mysqld : 192.168.1.0/255.255.255.0 : allow
mysqld : ALL : deny

e reinicie o portmap

/etc/init.d/portmap restart

Pronto.

PostgreSQL: função TO_ASCII em banco UTF-8

A função TO_ASCII no postgresql é muito útil principalmente para buscas onde se quer ignorar acentos em string. Ela funciona bem quando o banco de dados estiver em LATIN1 por exemplo.

SELECT TO_ASCII('tchê'); -- retorna 'tche'

mas quando usamos ela em um banco UTF8 retorna o erro “conversão de condificação de UTF8 para ASCII não é suportada”.

Para contornar esse problema, cria-se, uma função de banco onde podemos chamar de SEM_ACENTO, para usar no lugar da TO_ASCII.

CREATE OR REPLACE FUNCTION SEM_ACENTO(text)
RETURNS text AS
$BODY$
select
translate($1,'áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇ',
        'aaaaaeeeeiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcC');
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;

Tamanho das tabelas no PostgreSQL

Em muitos casos é preciso saber o espaço em que as tabelas do banco de dados estão utilizando em disco, para fins de monitoramento e decisão de estruturação de banco. Para tal tarefa no PostgreSQL o comando abaixo resolve o problema.

SELECT esquema, tabela,
       pg_size_pretty(pg_relation_size(esq_tab)) AS tamanho,
       pg_size_pretty(pg_total_relation_size(esq_tab)) AS tamanho_total
  FROM (SELECT tablename AS tabela,
               schemaname AS esquema,
               schemaname||'.'||tablename AS esq_tab
          FROM pg_catalog.pg_tables
         WHERE schemaname NOT
            IN ('pg_catalog', 'information_schema', 'pg_toast') ) AS x
 ORDER BY pg_total_relation_size(esq_tab) DESC;

a coluna tamanho mostra o tamanho que os registros (tuplas) da tabela está ocupando no disco, e a coluna tamanho_total inclui também os TOASTs e os índices associados à tabela.

Com essa consulta, é possível inclusive montar scripts (em shellscript, perl, python, groovy, etc..) de monitoramento do banco, por exemplo: caso a tabela tal, ou a soma de todas as tabelas chegar a X Mb, envie um email de alerta. Outra utilização seria de geração de indicadores para análise de tendência da expansão do banco.