Centos 7 PostgresSQL – Instale o banco de dados e use uma localização diferente da padrão

Este post descreve os passos de como instalar o postgresSQL 9.6 no Centos7 alterando o local padrão de armazenamento dos dados, que por padrão é no diretório /var/lib/pgsql/<versão>/data.

Entre as possíveis razões que podem requisitar a instalação em um local diferenciados envolvem:

• Segurança
• Gerenciamento centralizado
• Performance

O processo basicamente consiste em alterar a variável PGDATA utilizada pelo gerenciador de banco de dados Postgres.

Passo 1) Instalação do repositório do Postgres 9.6

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Passo 2) Instalação dos pacotes servidor e cliente

yum install postgresql96 postgresql96-server

Passo 3) Criação dos diretórios onde os dados do PostgreSQL serão armazenados, no meu caso será no diretório /data/postgresql

mkdir -p /data/postgresql

Passo 4) Conceder as permissões adequadas para o usuário postgres que é criado automaticamente durante o processo de instalação.

chown -R postgres:postgres /data/postgresql/

Passo 5) Inicialização da base de dados

su - postgres -c '/usr/pgsql-9.6/bin/initdb -D /data/postgresql'

Passo 6) Edite o arquivo /usr/lib/systemd/system/postgresql-9.6.service para alterar a variável PGDATA

vi /usr/lib/systemd/system/postgresql-9.6.service

# Location of database directory
# Environment=PGDATA=/var/lib/pgsql/9.6/data/
Environment=PGDATA=/data/postgresql/data/

Para salvar o arquivo pressione ESC + ZZ

Passo 7) Reconfigure as bases

/usr/pgsql-9.6/bin/postgresql96-setup initdb

Passo 8) Inicie o serviço

systemctl start postgresql-9.6

Opcionalmente adicione o serviço para iniciar após o boot do sistema

systemctl enable postgresql-9.6

Até a próxima

Verificando o tamanho da base de dados no PostgreSQL

Olá, neste post irei mostrar como verificar o tamanho da base de dados no PostgreSQL.

SELECT oid,datname FROM pg_database;

Esta consulta irá retornar o OID e o respectivo nome da base de dados. Com base nestas informações iremos descobrir o tamanho ocupado pela base de dados.

Acesse o diretório onde está instalado o PostgreSQL e entre no subdiretório data/base/ (ex: /var/lib/pgsql/9.3/data/base/ no Linux)

Agora com base no OID do banco de dados obtido anteriormente verificamos o tamanho da base.

Nos sistemas Linux podemos informar o seguinte comando: du -sh <OID>/ em sistemas Windows podemos verificar com o botâo direito > Propriedades para visualizar o tamanho do diretório.

 

Até a próxima

Exibindo informações das colunas, tabelas e banco de dados no PostgreSQL, MySQL e SQL Server

Alternando entre banco de dados corrente:

MySQL/SQL Server:

USE database_name;

PostgreSQL (psql):

\c database_name;

Exibindo as tabelas do banco de dados:

MySQL:

SHOW TABLES;

SQL Server:

SELECT * FROM sys.tables;

PostgreSQL (psql):  

\d;

PostgreSQL: 

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Listagem dos banco de dados:

MySQL:

SHOW DATABASES;

PostgreSQL(psql):

\l;

PostgreSQL:

SELECT datname FROM pg_database;

Exibindo as colunas de uma tabela:

MySQL: 

SHOW COLUMNS;

PostgreSQL(psql):

\d table_name;

PostgreSQL:  

SELECT column_name FROM information_schema.columns WHERE table_name ='table_name';

MySQL: 

DESCRIBE TABLE;

PostgreSQL (psql):

d+ table_name;

PostgreSQL:

SELECT column_name FROM information_schema.columns WHERE table_name ='table_name';

SQL Server;

exec sp_help 'table_name';

Até a próxima.

Queries dinâmicas no PostgreSQL

Em certos casos é necessário criar e executar consultas SQL dinâmicas para mostrar resultados diferentes ou até mesmo melhorar a performance das consultas.

No PostgreSQL a instrução EXECUTE é específica para esta finalidade, executando a string que contém o comando SQL.

Resultados de instruções SELECT são desprezados pelo EXECUTE. Portanto, não há como extrair o resultado de um SELECT criado dinamicamente com uma instrução EXECUTE pura. Porém existem duas formas de obter o resultado do SELECT, com o laço FOR-IN-EXECUTE ou um cursor OPEN-FOR-EXECUTE, que futuramente abordaremos.

Exemplo:

CREATE OR REPLACE FUNCTION instrucaoExecute(table TEXT, col TEXT, newValue TEXT, oldValue TEXT)
RETURNS TEXT AS $$
BEGIN
    -- Equivalente a UPDATE table SET col='newValue' WHERE col='oldValue'
    EXECUTE 'UPDATE '||table||' SET '||col||'='||''''||newValue||''''||' WHERE '||col||'='||''''||oldValue||'''';

    RETURN 'OK';
END;
$$ LANGUAGE 'plpgsql';

-- Chamando a função
SELECT instrucaoExecute('clientes', 'endereco', 'Rua ABC, 123', 'Rua XV de Novembro, 5432');

Até a próxima

Liberando acessos remotos PostgreSQL

Neste post mostrarei como configurar o SGDB (Sistema Gerenciador de Banco de Dados) PostgreSQL para receber conexões remotas, pois por padrão o PostgreSQL somente recebe conexões locais (localhost ou 127.0.0.1) para acesso às suas bases de dados.

Inicialmente você deverá encontrar os arquivos postgresql.conf  e pg_hba.conf.

Após os encontrar, abra cada um deles em seu editor de textos preferido e localize as sequintes linhas:

No postgresql.conf vamos editar a seguinte linha

#listen_addresses = ‘localhost’ # what IP address(es) to listen on

Como em todo arquivo de configuração em programas linux, o # na frente da linha significa um comentário e com isso desabilita o parâmetro seguinte, tomando seu valor default, que no caso é localhost.  Descomente essa linha e substitua seu valor por *.  Assim seu PostgreSQL estará apto a ouvir conexões de qualquer IP. Então essa linha ficará da seguinte forma:

#listen_addresses = ‘localhost’ # what IP address(es) to listen on

Isso não quer dizer que ele estará aberto para qualquer IP da rede.  É possível criar políticas de acesso através de outro arquivo que falaremos a seguir.

Liberando conexões no arquivo pg_hba.conf

Este é o arquivo do Postgresql responsável pela liberação de usuários, hosts e bancos de dados.  Nele é possível dizer qual usuário de qual IP (ou rede) conectará a qual banco.  É possível liberar qualquer usuário para coenctar a qualquer banco, mas esta é uma regra cautelosa a ser seguida pois pode comprometer a segurança do seu servidor de bases de dados.

Por padrão o arquivo vem com a seguinte configuração:

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

Pode haver alguma variação das configurações padrões de acordo com a versão utilizada, mas geralmente é algo que represente essas informações. Basicamente elas querem dizer que todas as conexões locais podem ser realizadas por qualquer usuário do banco a qualquer banco de dados.Para liberar o acesso remoto basta criar uma nova regra seguindo o seguinte padrão:

host all all 0.0.0.0/0 trust

Com essa regra qualquer usuário de qualquer IP poderá se conectar a qualquer database.
Importante: O parâmetro trust quer dizer que não será solicitada qualquer senha para conectar ao banco de dados. Então, qualquer pessoa com acesso à sua rede poderá se conectar ao servidor PostgreSQL sem nenhuma restrição. Se a sua rede for restrita como uma turma de um curso ou um ambiente de desenvolvimento interno de empresa não há riscos, porém, é necessário possuir um ambiente de rede interno isolado.

Este parâmetro é útil quando você ainda não se conectou ao banco para criar uma senha de acesso ao usuário postgres (do banco e não do sistema operacional). Então você se conecta e reseta a senha do usuário. Logo em seguida, altere o parâmetro para md5. Assim, uma senha será solicitada ao tentar conectar-se ao servidor Postgresql.

A liberação fica da seguinte maneira:

host all all 0.0.0.0/0 md5

É possível também liberar o acesso apenas a uma rede específica:

host all all 192.168.0.0/32 md5

Ou também, informar qual usuário poderá conectar:

host all usuariodobanco 192.168.0.0/32 md5

Também informar  o banco de dados a ser conectaro por este usuário:

host bancodedados usuariodobanco 192.168.0.0/32 md5

Na regra acima, o usuário usuariodobanco poderá conectar-se apenas ao banco bancodedados apenas se estiver dentro da rede 192.168.0.0.

Bom, por hoje é só, espero que seja útil.

T+