PostgreSQL

Criar usuário somente leitura no PostgreSQL 9+

Tem vários artigos na internet falando como realizar o procedimento, mas a maioria não funciona nas versões 9+, então, compartilho aqui como consegui fazer funcionar:

Usuário: readonly
Senha: readonly
Banco de Dados: foo


CREATE USER readonly  WITH ENCRYPTED PASSWORD 'readonly';
GRANT USAGE ON SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- repita o codigo abaixo para cada banco de dados

GRANT CONNECT ON DATABASE foo to readonly;
\c foo
-- o codigo abaixo concede o privilegio em novas tabelas geradas no banco "foo"
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO readonly;
GRANT USAGE ON SCHEMA public to readonly; 
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Fonte: StackOverflow


Uber migra do PostgreSQL para o MySQL

O Uber, como você deve saber, é uma empresa que trouxe para o mercado uma forma rápida, eficiente, e consideravelmente mais barata que os taxis conencionais para se locomover em várias cidades do planeta.

O que talvez não saiba é sobre a tecnologia por trás do serviço.

Aqui vou falar um pouquinho sobre a migração de bancos de dados realizada pelo Uber recentemente. O artigo original foi publicado em um blog do próprio Uber 20 dias antes de eu escrever esse post.

Minha intenção aqui não é traduzir nem fazer um comentário extenso sobre o texto, mas apenas pontuar as informações que achei mais interessantes. Vamos lá:

O Postgres tem um funcionamento que faz gerar várias linhas (fisicamente, no disco, chamada ctid) para uma mesma linha no banco de dados, ou seja, se você tem uma tabela que armazena os dados de uma pessoa, e você altera o telefone dela, uma nova linha física (ctid) é gerada.

Para a maioria dos sistemas, isso não chega a ser um problema. O Postgres tem um recurso chamado auto-vacuum que faz a limpeza das linhas antigas. A questão é que, de tempos em tempos, pode ser necessário parar o sistema por algumas horas e executar um vacuum full (limpeza geral), e para sistemas que não podem parar, isso pode ser um problema. Se muito tempo passa sem a execução de um vacuum full, o sistema começa a ficar lento.

Mas a grande questão para o Uber é que eles replicam os dados em diferentes data centers (costa leste e costa oeste dos Estados Unidos) para Recuperação de Desastres e, nesse caso, a replicação dessas linhas torna-se muito lenta e cara. Com frequência uma alteração estava sendo realizada e não sendo sincronizada corretamente.

O problema é agravado pelo fato de que no caso deles, muitas tabelas tinham muitos índices, e updates pequenos e frequentes eram executados.

Repare que é uma situação específica: replicação de dados em data centers diferentes, com updates frequentes e muitos índices.

Nem todo serviço funciona dessa forma, e obviamente, isso não quer dizer que o Postgres é um banco de dados ruim. Mas no caso específico do Uber, eles tiveram problemas.

Segundo o artigo, o MySQL (com engine InnoDB) cria uma camada a mais de abstração, o que normalmente é uma desvantagem pois deixa o sistema todo mais lento, mas é uma solução no caso da replicação, pois o MySQL replica o DML (update, insert, delete) propriamente dito, e não o registro físico da informação.

Outra questão é a atualização de versão. Usando a versão 9.2, eles tiveram problemas para atualizar para a 9.3, porque demorava tempo demais e o serviço não poderia ficar parado durante esse tempo. Para agravar a situação, todas as réplicas têm que usar a mesma versão, ou seja, eles não poderiam fazer a atualização primeiro em uma ráplica e depois no master, por exemplo. Acrescentam que isso foi resolvido na versão 9.4 em diante, usando o recurso pglogical, que adicona uma camada lógica para replicação.

No MySQL esse problema não existe. É possível, na maioria das situações operar com versões diferentes (já que o que é replicado é o DML em si).

Outro grande problema do Postgres é relativo ao número de conexões. Mesmo em sistemas com abundância de recursos, é bem difícil abrir mais que algumas centenas de conexões, porque o número de conexões é diretamente ligado ao número de processos (mais conexões, mais processos). No MySQL, o número de conexões é diretamente ligado ao número de threads.

Isso faz com que no MySQL seja possível abrir 10 mil conexões sem grandes problemas.

Veja o artigo original para obter mais informações:

Outro artigo comentando o artigo do Uber:


Instalando o PostgreSQL no Linux Mint ou Ubuntu

Para instalar o PostgreSQL no Linux Mint ou Ubuntu, primeiramente você precisa criar um arquivo "source" para ser utilizado com o apt-get.

Abra o Terminal (CTRL + ALT + T) e digite:

sudo touch /etc/apt/sources.list.d/pgdg.list

Depois edite o arquivo com o editor de sua preferência. Usarei o pluma (Linux Mint). No Ubuntu você pode usar o gedit.

sudo pluma /etc/apt/sources.list.d/pgdg.list

A configuração abaixo serve para o Linux Mint 17 ou Ubuntu 14.04 (Trusty). Caso esteja utilizando outra versão, será necessário utilizar o repositório adequado. Dentro do arquivo, coloque o seguinte conteúdo:

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

Salve o arquivo e digite as seguintes linhas (uma por uma):

sudo apt-get install wget ca-certificates -y
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update -y
sudo apt-get upgrade -y
sudo apt-get install postgresql-9.4 pgadmin3 -y

Note que a linha "sudo apt-get upgrade -y" pode demorar bastante. O parâmetro -y serve para que não sejam feitas perguntas (sim para tudo).

Pronto! O PostgreSQL está instalado. Para este artigo foi utilizado o PostgreSQL 9.4 e o Linux Mint 17.

Agora você vai precisar definir uma senha para o PostgreSQL e em seguida pode começar a utilizá-lo!

Fonte: http://www.codeproject.com/Articles/898303/Installing-and-Configuring-PostgreSQL-on-Linux-Min

 


Descoberta falha de segurança no PostgreSQL

Foi liberada hoje uma atualização de segurança do PosgreSQL para todas as versões ativas (8.4, 9.0, 9.1 e 9.2). A correção feita trata especificamente um bug de alto risco para a integridade do sistema de arquivos. É extremamente recomendado que as atualizações sejam realizadas o quanto antes.

O bug é o seguinte: se alguém abrir uma conexão com o seu servidor, e utilizar um nome de banco de dados que começa com "-", o sistema de arquivos pode ser danificado. O caso é mais grave para servidores que estão expostos à internet sem a devida proteção de Firewall, o que reforça a importância de se fechar o acesso ao servidor e deixar apenas as portas necessárias, para os IP's necessários.

Segundo o site Database Soup, existem 120 mil servidores no mundo expostas à essa falha e em situação de risco.

Fonte: http://www.postgresql.org/about/news/1456/


Como criar listas sequenciais no PostgreSQL com a função generate_series

Essa é uma dica bem especifica, mas que provavelmente vai ser útil em algum momento na vida de um programador e/ou DBA.

Como criar uma lista sequencial a partir de dados que não existem em uma tabela? Por exemplo: eu quero criar um select que retorne uma lista de 1 a 100 (cada número em uma linha). Como fazer isso? Veja aqui o exemplo:

select generate_series(1,100) as sequencia

Acho que não precisa nem explicar né? O primeiro parâmetro do generate_series é o início da lista, e o segundo o é o fim da lista.

Quer outro exemplo? Imagine que seu gerente pediu pra totalizar as vendas diárias do mes de abril de 2012, ou seja, trazer uma lista com quanto vendeu em cada dia. Mas tem um problema: no dia 3 não vendeu nada. Como você vai exibir um total de um dado que não existe? O generate_series vai te ajudar! Veja o exemplo:

select generate_series(0,29) + date '2012-04-01' as data

O select acima vai trazer uma lista com todos os dias de Abril de 2012. E eu não precisei de tabela nenhuma pra isso.

Saiba mais aqui: http://blog.redfin.com/devblog/2008/05/fun_with_generate_series.html


Retornando aspas simples e duplas no PostgreSQL

Aspas simples são utilizadas na estrutura de um comando SQL, sendo assim, podemos ter problemas se precisarmos exibir as aspas simples como um valor fixo. No meu caso, tinha que exibir latitude e longitude (que estava armazenado no banco em campos inteiros) e depois colocar as aspas simples e duplas fixas, ao lado do valor.

Sem mais lenga lenga, pra fazer isso você poder utilizar a função CHR, que retorna um caracter da tabela ASCII.

select
cast(chr(39) as varchar) as aspas_simples,
cast(chr(34) as varchar) as aspas_duplas

É possível que funcione da mesma forma em outros bancos.


Instalando o PostgreSQL 9 e pgAdmin III no Ubuntu 10.04

Versão atualizada desse artigo

Sem muita conversinha, vamos direto ao assunto. Para instalar o PostgreSQL 9 e o pgAdmin III no Ubuntu 10.04, acesse o Terminal (Menu Principal / Acessórios / Terminal) e digite os seguintes comandos:

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql
sudo apt-get install pgadmin3

O que você acabou de fazer foi instalar o PosgreSQL a partir de um repositório PPA, ou seja, um repositório que não faz parte da distribuição oficial, que traz a versão 8.4.

Depois de instalar, você vai precisar criar a senha para o usuário padrão do posgresql. Veja como fazer isso clicando aqui.

Se você não conseguiu abrir o PgAdmin depois de instalar, veja aqui como resolver.


Não consegue abrir o pgAdmin III no Ubuntu 10.04? Eis a solução!

Esses dias tive esse problema. Instalei o PostgreSQL 9 e o pgAdmin III no meu notebook com o Ubuntu 10.04 e eu simplesmente não conseguia abrir o pgAdmin. Clicava e não acontecia nada. Pesquisando um pouco, encontrei um tópico no Launchpad que me trouxe a solução. É um bug que deveria estar resolvido, mas não foi. Se você está com esse problema, abra o Terminal (Menu Principal / Acessórios / Terminal) e digite o seguinte código:

sudo apt-get remove pgadmin3
sudo apt-get build-dep pgadmin3
sudo apt-get --compile source pgadmin3

O processo demora vários minutos, então vá tomar um café enquanto é executado. O que esses comandos estão fazendo é baixando o código fonte do pgAdmin III e compilando uma versão específica para o seu Ubuntu. Depois de compilar, agora você tem que encontrar o arquivo gerado. Digite o comando:

ls pgadmin*deb

Como resultado você vai ter algo do tipo: pgadmin3_1.10.2_1_i386.deb Então é só instalar o pacote assim:

sudo dpkg -i nome_do_arquivo_gerado.deb

É isso!


Retornando CPF ou CNPJ com pontuação com o PostgreSQL

Os CPFs e CNPJs em seu sistema estão cadastrados todos sem pontos, como é bastante comum. Mas agora você precisa trazê-los do banco já com a pontuação correta. Como fazer isso?

Utilizando a função SUBSTR do PostgreSQL, podemos separar um String (Varchar, por exemplo) em várias partes e depois concatenar com a pontuação desejada. Lembre-se que o campo deve estar como String. Se estiver como inteiro, você vai ter que converter utilizando o CAST. Vamos aos exemplos:

CPF

select substr(cpf, 1, 3) || '.' ||
       substr(cpf, 4, 3) || '.' ||
       substr(cpf, 7, 3) || '-' ||
       substr(cpf, 10) as cpf
       from (select cast('93746041597' as varchar) as cpf)a

CNPJ

select  substr(cnpj, 1, 2) || '.' || SUBSTR(cnpj, 3, 3) || '.' ||
        substr(cnpj, 6, 3) || '/' || SUBSTR(cnpj, 9, 4) || '-' ||
        substr(cnpj, 13) AS cnpj
        from (select cast('99325358000151' as varchar) as cnpj)a


Fonte: http://flaviosilveira.com/2009/tratando-cpf-cnpj-primeiro-nome-e-data-pelo-sql-postgre/


Adicionar zeros à esquerda em um código no PostgreSQL com a função LPAD

Você tem uma tabela nos PostgreSQL quem tem vários códigos, sendo alguns com dois caracteres, outros com três, e por aí vai. Mas como fazer para uniformizar a quantidade de caracteres, ou seja, deixar todos os códigos com a mesma quantidade de números, adicionando zeros à esquerda? Basta utilizar a função LPAD do PostgreSQL. Vamos ao exemplo:

select 
   lpad(cast(cod_curso as varchar),4,'0') as cod_curso 
   from cursos

No exemplo acima, o código do curso que possui 3 caracteres, por exemplo: 125, virou 0125. O código do curso com 2 caracteres, por exemplo: 15, virou 0015. E por aí vai.

Você pode fazer os testes utilizando o banco de testes.

Saiba mais sobre o LPAD aqui e aqui.


  • Publicidade

  • Redes Sociais

    Facebook  Twitter
  • Estatísticas

    Page Views (desde março de 2010):

    Estatísticas detalhadas
  • Novidades por e-mail!

    Digite seu e-mail:


    Fique tranquilo. Seu e-mail não será usado para outros fins, e você poderá se descadastrar quando quizer.

    Eu!

    Tiago Passos
    Todo o conteúdo desse site esta licenciado sob a licença Creative Commons 3.0 (CC BY 3.0). Você pode copiar e modificar o conteúdo desde que cite o autor.
    iDream theme by Templates Next | Powered by WordPress