Postgis: Geolocalização open-source

Quem trabalha com geolocalização já deve ter ouvido falar do Postgis.

Ele é um framework muito robusto e usado por diversas empresas de classe global ao redor do mundo.

Para realizar usa instalação, porém, o caminho pode ser tortuoso. Principalmente se você quiser instalar via código-fonte.

Abaixo segue uma receita de como fazer isso em um CentOS 7.

Receita:

INSTALANDO O POSTGRES

cd /usr/local/src

wget https://ftp.postgresql.org/pub/source/v12.3/postgresql-12.3.tar.gz

tar zxf postgresql-12.3.tar.gz

cd postgresql-12.3/

yum groupinstall “Development Tools”

 yum install readline-devel libxml2-devel openssl-devel

./configure –prefix=/usr/local/pgsql-12.3 –with-openssl –with-libxml –without-ldap

make -j16

make install

cd /usr/local

ln -s pgsql-12.3/ pgsql

vi /etc/profile.d/postgresql.sh

export PATH=/usr/local/pgsql/bin:$PATH

export LD_LIBRARY_PATH=/usr/local/lib:/usr/local/pgsql/lib:$LD_LIBRARY_PATH

export PGDATA=/pasta/pgdata

source /etc/profile.d/postgresql.sh

cd /usr/local/src/postgresql-12.3/contrib/

make -j10

make install

cd start-scripts/

cp linux /etc/init.d/postgresql

chmod +x /etc/init.d/postgresql

chkconfig postgresql on

vi /etc/init.d/postgresql

PGDATA=/pasta/pgdata

useradd –system –user-group –create-home –comment “PostgreSQL Admin User” –shell /bin/bash postgres

mkdir -p  $PGDATA

chown -R postgres. /dados/

chmod -R 700 /dados/pgdata

su – postgres

chmod 700 $PGDATA

cd $PGDATA

initdb -E utf8 –locale=pt_BR.utf-8 -D $PGDATA

ROOT:

/etc/init.d/postgresql start

INICIANDO INSTALAÇÃO DO POSTGIS

cd /usr/local/src

wget https://download.osgeo.org/postgis/source/postgis-3.0.1.tar.gzyum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

sudo yum install epel-release-latest-7.noarch.rpm

INSTALANDO BIBLIOTECAS POSTGIS

yum –showduplicates list geos | expand

yum install geos38.x86_64 geos38-devel.x86_64

yum install proj proj-devel

yum install gdal gdal-devel

yum install cmake

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/json-c-devel-0.11-4.el7_0.x86_64.rpm

sudo yum install json-c-devel-0.11-4.el7_0.x86_64.rpm

wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.1.1/protobuf-c-1.1.1.tar.gz

tar zxf protobuf-c-1.1.1.tar.gz

wget https://github.com/protocolbuffers/protobuf/releases/download/v2.6.0/protobuf-2.6.0.tar.gz

tar zxf protobuf-2.6.0.tar.gz

cd protobuf-2.6.0/

./autogen.sh

./configure

make

make install

export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig

cd ../protobuf-c-1.1.1/

./configure

make 

make install

FINALMENTE INSTALANDO O POSTGIS

cd ../

tar zxf postgis-3.0.1.tar.gz

cd postgis-3.0.1/

make clean  SE FOR DA SEGUNDA VEZ EM DIANTE

./configure –with-geosconfig=/usr/geos38/bin/geos-config

make

make install

reiniciar pg

/etc/init.d/postgresql stop

/etc/init.d/postgresql start

TESTANDO O POSTGIS

psql -U postgres

create extension postgis;

SELECT encode(ST_AsGeobuf(q, ‘geom’), ‘base64’) FROM (SELECT ST_GeomFromText(‘POLYGON((0 0,0 1,1 1,1 0,0 0))’) AS geom) AS q;

OUTRAS BIBLIOTECAS:

PG_CRON

wget https://github.com/citusdata/pg_cron/archive/master.zip

mv master.zip pg_cron-master.zip

unzip pg_cron-master.zip

cd pg_cron-master/

make

make install

postgresql.conf

shared_preload_libraries = ‘pg_cron’        

cron.database_name = ‘postgres’

reiniciar pg

/etc/init.d/postgresql stop

/etc/init.d/postgresql start

ORACLE_FDW

wget https://download.oracle.com/otn_software/linux/instantclient/195000/oracle-instantclient19.5-basic-19.5.0.0.0-1.x86_64.rpm

wget https://download.oracle.com/otn_software/linux/instantclient/195000/oracle-instantclient19.5-devel-19.5.0.0.0-1.x86_64.rpm

wget https://download.oracle.com/otn_software/linux/instantclient/195000/oracle-instantclient19.5-tools-19.5.0.0.0-1.x86_64.rpm

rpm -i oracle-instantclient19.5-basic-19.5.0.0.0-1.x86_64.rpm

rpm -i oracle-instantclient19.5-devel-19.5.0.0.0-1.x86_64.rpm

rpm -i oracle-instantclient19.5-tools-19.5.0.0.0-1.x86_64.rpm

wget https://github.com/laurenz/oracle_fdw/archive/master.zip

mv master.zip oracle_fdw.zip

unzip oracle_fdw.zip

cd oracle_fdw-master/

ls -l /usr/include/oracle/

OPCAO 1

cp Makefile orig_makefile

sed -i ‘s/19.6/19.5/g’ Makefile

OPCAO 2:

Add the following to Makefile yourself:

To PG_CPPFLAGS add -I/usr/include/oracle/XX.X/client64, and to SHLIB_LINK add -L/usr/lib/oracle/XX.X/client64/lib.

make

make install

TDS-FDW (SQL SERVER)

sudo yum install epel-release

sudo yum install freetds-devel

sudo yum install centos-release-scl

sudo yum install llvm-toolset-7-clang llvm5.0

yum install git

cd /usr/local/src

git clone https://github.com/tds-fdw/tds_fdw.git

cd tds_fdw

make USE_PGXS=1 PG_CONFIG=/usr/local/pgsql/bin/pg_config

sudo make USE_PGXS=1 PG_CONFIG=/usr/local/pgsql/bin/pg_config install

PgBouncer: Pool de Conexões

O PgBouncer se define como um “pool de conexões leve para PostgreSQL” (site do projeto aqui).

Seu primeiro release púbico, a versão 1.0, ocorreu em Março de 2007. Atualmente ele possui versões para Windows e Linux (código-fonte e instruções para instalação aqui).

A principal função do PgBouncer é ser um pool de conexões.

O conceito geral é bastante simples: os clientes (usuários e sistemas) apontam suas connection strings para o IP/Porta do PgBouncer, usando um usuário especificamente criado para esta conexão (que pode ou não ser o mesmo do banco de dados), eles são introduzidos em uma fila e esta fila é executada pelo banco de dados através de uma conexão entre o pool e o banco de dados.

Até aqui nenhuma novidade, afinal, esta é a forma como o sistema se conectaria ao banco de dados de maneira geral. Os benefícios desta arquitetura começam com os parâmetros de gerenciamento de conexões oferecidos pelo PgBouncer, dentre eles:

Pool_Mode: Define o modo pelo qual as instruções enviadas pelos clientes são processadas pelo banco de dados. Existem 3 modos:

Session: A conexão entre o pool e o banco de dados só é encerrada quando a sessão do usuário desconecta, é o valor padrão e é o comportamento normal em um banco de dados sem pool de conexões.

Transaction: A conexão é encerrada com o fim de uma transação. É importante lembrar que a conexão entre o cliente final e o pool se mantém, dando a ilusão de conexão ativa, mas o pool, para este cliente em específico, não estará mais conectado ao banco de dados até que uma nova transação se inicie e comece a ser executada. Este comportamento nos ajuda a não manter no banco de dados conexões abertas sem necessidade, visto que cada conexão utiliza recursos que, se não estiverem em uso, poderiam ser melhor utilizados por outras sessões ativas.

Statement: A conexão entre o pool e o banco de dados se encerra após a execução de um statement, ou seja, de um comando qualquer. Este tipo de configuração acaba impedindo a execução de transações, esta opção é recomendada com cautela.

Max_Client_Conn: Define o número máximo de pessoas que podem se conectar ao pool de conexões. Não confundir com a quantidade de conexões efetivamente abertas entre o pool e o banco de dados.

Default_Pool_Size: O tamanho padrão do pool para cada combinação usuário/banco de dados. Pode ser sobrescrita na configuração individual do banco de dados. Este é o valor máximo de conexões que serão abertas pelo pool para executar queries no banco de dados, dentro do modo de pool escolhido em Pool_Mode.

Min_Pool_Size: Este é um parâmetro interessante. Como abrir uma conexão com o banco de dados pode ser algo custoso, você pode definir um mínimo de conexões abertas entre o pool e o banco de dados, e estas ficaram abertas, mesmo que em idle, para que quando uma nova solicitação de execução de query chegar, o pool não tenha o overhead de ter que abrir a conexão.

Server_Check_Delay: O tempo em que uma conexão entre o pool e o banco de dados permanecerá aberta após o fim da execução das queries. Assim como a configuração anterior, esta configuração tenta ajudar a evitar o overhead de criação/destruição de conexões com o banco de dados.

Server_Lifetime: Tempo máximo de uma conexão entre o pool e o banco de dados. Cuidado com esta configuração, este time-out funciona até para conexões ativas. Para matar apenas conexões idle, veja a configuração abaixo.

Server_Idle_Timeout: Tempo máximo em que uma conexão idle será mantida entre o pool e o banco de dados.

Client_Idle_Timeout: Desta vez estamos falando da conexão entre o cliente/usuário e o pool de conexões. As conexões com mais do que este tempo em estado idle serão mortas.

Idle_Transcation_Timeout: O mesmo que o parâmetro anterior, porém para conexões no estado “Idle in Transaction”.

Estas são algumas das configurações disponíveis no PgBouncer e que tornam a conexão através do pool melhor gerenciável do que as feitas diretamente entre o cliente e o banco de dados.


Outro ponto importante é a Segurança. Você pode criar um usuário específico para que todos os clientes possam se autenticar no banco de dados, porém, este usuário não precisa ter nenhum privilégio no banco de dados. Desta forma, você pode distribuir este usuário/senha para seus clientes internos e externos e estes apenas poderão se conectar ao banco de dados através do pool de conexões, com todas as regras aplicadas. Outro ponto importante é que a conexão entre o pool e o banco de dados é feita por uma connection string interna, escondendo do usuário final o usuário que realmente está se conectando à base de dados. Desta forma ele  não terá acesso a esta informação.


Este recurso é particularmente importante pois, infelizmente, muitos clientes distribuem a senha do super usuário Postgres para todos os sistemas e clientes internos, dando acessos desnecessários à todo o cluster, ao inveś de fechar os privilégios por banco de dados. Através desta técnica do PgBouncer, você pode alterar os usuários e privilégios da conexão com o banco de dados sem ter que informar seus clientes finais, que continuarão usando o usuário de conexão ao pool.


Por fim, mas não menos importante, é a habilidade do pool em realizar o failover de um cluster de banco de dados. O PostgreSQL nos dá, nativamente, a capacidade de replicar o banco de dados principal para um ou mais bancos de dados secundários, que podem ficar disponíveis apenas para leitura. No caso de falha do servidor primário, o servidor secundário pode ser promovido a primário e continuar a servir os sistemas e clientes.
Em uma conexão direta ao banco de dados, os sistemas e clientes teriam que refazer a connection string, ou a equipe de infraestrutura teria que ter a capacidade de alterar o DNS/IP das máquinas, fazendo com que os clientes acessem o secundário através do IP que antes pertencia ao primário. Esta técnica é muito utilizado e possui um downtime relativamente baixo, provavelmente de alguns minutos dependendo das condições e equipes envolvidas.


Com o PgBouncer, basta alterar a configuração interna para que a connection string  do antigo secundário seja agora a do primário. Esta alteração requer um reload da configuração e o processo todo pode ser realizado em alguns segundos, sem que os usuários tenham que se reconectar à base de dados. Obviamente, aqueles usuários que estavam executando uma transação no momento da queda, perceberão a queda, porém os que estavam apenas conectados, estavam apenas conectados ao pool e desta forma, ao enviar uma nova query, ela será direcionada para o novo primário, sem que o cliente final perceba o failover.
Todo este processo diminui drasticamente o downtime do serviço, aumentando o SLA como um todo.


Com isso, indicamos o uso do PgBouncer em seu ambiente para que você possa usufruir de todos estes benefícios!

Veja uma palestra completa sobre o assunto em: https://www.infoq.com/br/presentations/pgbouncer-pool-seguranca-e-disaster

Para onde vai a carreira do DBA?

Ser um DBA nos dias de hoje é se perguntar: diante dos novos caminhos que surgem, para onde devo ir?

Ao contrário da foto, porém, o caminho que devemos seguir ainda não existe, ele está sendo criado por todos nós, neste exato momento.

Esta indagação surge por conta das diversas mudanças que a área de TI tem sofrido ao longo dos últimos anos. De maneira simplificada, podemos dizer que o mundo de TI sofreu uma revolução em sua maneira de trabalhar quando as equipes de desenvolvimento pararam de acreditar em modelos fechados e não iterativos para adotar um modo ágil de trabalhar, focando nas entregas contínuas, iterativas e na interação entre as pessoas.

A mudança do modelo Waterfall para o Ágil protagonizada pelos desenvolvedores levou os Gerentes de Projeto a mudarem sua forma de trabalho também. Antes focados em processos e estimativas rígidas, estes passaram a adotar o Scrum como framework de trabalho para gerenciar seus projetos.

Em seguida, as equipes de infraestrutura começaram a ficar por fora da revolução ágil, visto que os desenvolvedores estavam cada vez mais rápidos na entrega de software aos clientes, levando o antigo processo burocrático de gerenciamento de ambiente a se tornar mais ágil e fluído, dando origem ao movimento Devops.

Paralelo a isso tudo, vimos a passagem do tempo no próprio modo de gerenciar nosso hardware. Se antes tínhamos servidores, estes passaram a ser virtualizados (ainda que dentro dos próprios datacenters) e por fim passaram a sequer ficar fisicamente dentro da própria empresa, indo para a nuvem.

Somados todos estes pontos, não é de se admirar que as coisas tenham mudado também para os DBAs. Tarefas que antes dependiam de conhecimento técnico e tempo de um expert na área, foram disponibilizadas aos clientes finais através de cliques do mouse nas consoles das principais nuvens do mercado.

Neste momento nós (DBAs) estamos justamente dentro desta névoa, saindo do nosso antigo mundo e indo para este novo mundo mais alinhado com os modos de trabalho de todos os nossos colegas de trabalho (desenvolvedores, gerentes de projeto, infraestrutura, etc) e por isso talvez não seja tão simples identificar claramente os novos caminhos, mas pelo que já podemos perceber, estes caminhos passam por:

Mudança de Mindset

Esta talvez seja a mais importante. DBAs são famosos por serem sempre a favor do “status quo”, bloqueando mudanças no ambiente. E não sem razão. Todo DBA, provavelmente, tem uma história para contar sobre um desenvolvedor que derrubou um ambiente com um patch emergencial mal feito. Mas esta é a questão. Erros acontecem, com ou sem planejamento. O que nós temos que ter em mente é que devemos dispor de processos e ferramentas que nos permitam validar automaticamente as mudanças antes de as aplicarmos e voltar a um estado anterior de estabilidade rapidamente, caso algum bug ocorra.

Cloud Advisor

Você já parou para contar quantas features cada player de nuvem oferece para que possamos utilizar bancos de dados na nuvem? Eu nunca contei, mas são muitas variações. Temos variações no modo de oferta do hardware, do sistema de armazenamento, do tipo de serviço (IaaS e Paas), do tipo de tecnologia (SQL, NoSQL, etc), da forma de cobrança (algumas cobram por espaço utilizado, outras por dados transferidos), da curva de aprendizado, etc.

Isso sem contar que as ofertas de nuvem, embora relativamente parecidas, são bem diferentes em seus detalhes, detalhes estes que influenciam diretamente na escolha e na precificação dos itens.

Por isso, os DBAs devem conhecer as diferentes possibilidades e ajudar às empresas a realizarem as melhores escolhas diante de cada cenário. Por exemplo: você sabia que ao invés de subir um serviço PaaS de SQL Server (relativamente caro) você pode instalar o mesmo em uma máquina Linux de custo mais baixo e que, dependendo da versão do SQL Server, você sequer precisará pagar por licenciamento?

Devops

As práticas de Devops como infraestrutura como código e builds contínuas podem e devem ser adaptadas aos bancos de dados, aumentando a produtividade do DBA e a qualidade dos projetos que necessitam commitar códigos SQL e manter a versão dos mesmos alinhada com a versão da build do software.

Database Development

Bancos de dados são compostos por tabelas, dados, queries, procedures e, opcionalmente, por features extensíveis como geolocalização, suporte a xml e json, suporte a dados binários etc.

O DBA pode tanto realizar quanto orientar as equipes em obterem uma melhor modelagem, melhor performance em queries e procedures e aconselhando quanto aos recursos “built-in” que podem facilitar as necessidades dos desenvolvedores, visando sempre a melhor performance e escalabilidade da aplicação.

Disaster Recovery e Alta Disponibilidade

Embora seja mais fácil criar ambientes de Disaster Recovery e Alta Disponibilidade (em algumas nuvens isto pode ser feito com um simples clique) estas arquiteturas tem que ser planejadas e, principalmente, testadas, visando que a equipe saiba qual procedimento executar em caso de problemas no ambiente, tornando a recuperação mais rápida e diminuindo o downtime da aplicação em casos de desastre (lembrando que no mundo digital, em muitos casos, downtime na aplicação significa parada total na entrada de receitas para a empresa).

Performance Benchmarking

Neste quesito eu ainda preciso realizar testes mais “científicos”, porém já notei em experiências pessoais que existem diferenças de performance entre nuvens e entre máquinas on-premisse com a mesma configuração.

Nos casos em que trabalhei, em alguns cenários as máquinas em nuvem (de mesma configuração que a on-premise) possuíam uma performance pior, o que nos obrigava a comprar máquinas maiores e, consequentemente, mais caras.

Este tipo de trabalho é especialmente interessante quando a empresa está avaliando a troca do seu fornecedor de nuvem.

Squads

Popularizada pelo Spotify, esta metodologia de formação de times tem influenciado diversas startups ao redor do mundo. Ao quebrar os sistemas da empresa em micro-serviços e definir squads como responsáveis por cada micro-serviço, a empresa está de fato se dividindo em “mini-empresas”, onde cada squad tem um relativamente elevado nível de autonomia.

Porém, se cada squad realmente trabalhar de maneira totalmente autônoma e sem guidance, o resultado podem ser diversos tipos de tecnologia sendo usados, o que dificulta a contratação de novas pessoas, tanto para o desenvolvimento quanto para o suporte, sem contar na curva de aprendizado maior caso haja troca de integrantes entre squads.

Neste cenário, os DBAs podem ajudar selecionando um range de tecnologias a serem utilizadas, montando ferramentas de monitoramento dos ambientes e também ajudando os times nos quesitos levantados nos tópicos anteriores.

Novas Tecnologias

Redshift, Spanner e Aurora são só alguns bancos de dados que não existiam há poucos anos atrás. As diferentes nuvens tem se diferenciado ao oferecer diversos tipos de novas tecnologias (não apenas bancos de dados) e estas novas tecnologias possuem características próprias para resolver determinados problemas.

Estar antenado com estas mudanças para sugerir às equipes a utilização das mesmas também é uma tarefa desenvolvida pelo DBA neste novo cenário.

Conclusão

Este artigo é a minha opinião sobre o tema e, embora longo, com certeza não abrange todas as variáveis. E você, qual é a sua opinião? Por quais caminhos você tem visto o DBA trilhar ou acredita que o DBA irá trilhar nos próximos anos?