Este documento tem como objetivo auxiliar analistas na instalação e configuração básica do banco de dados PostgreSQL em ambientes locais e em sistemas operacionais Linux. A instalação e configuração em ambientes de produção não é o foco deste documento, embora alguns tópicos referentes para apoio sejam abordados.

Instalando o PostgreSQL

Para baixar e instalar o PostgreSQL, execute os comandos abaixo. Atenção: estes comandos são referentes ao sistema operacional Red Hat 8.

dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

dnf module disable postgresql

sudo dnf install -y postgresql12-server postgresql-contrib 

Criação do Cluster PostgreSQL

Após realizar o download do pacote e a instalação, execute o seguinte comando para a criação do novo Cluster de banco de dados. O Cluster é uma coleção de banco dados gerenciados em uma única instância. 

/usr/pgsql-12/bin/postgresql-12-setup initdb

Posterior à criação do Cluster, vamos colocar na inicialização do Sistema Operacional:

systemctl enable postgresql-12

E iniciar:

systemctl start postgresql-12

Pode-se verificar o status da seguinte forma:

systemctl status postgresql-12

Configuração para boa performance

Com o Cluster instalado e iniciado, é possível realizar a configuração mínima para boa performance do banco de dados com o ERP Protheus.

Neste exemplo, os seguintes parâmetros foram alterados:

Após alterar todos os valores necessários, as recomendações para a configuração do banco de dados logo abaixo.

Do lado direito (Profile Comparison), será demonstrado os valores recomendados na coluna OLTP:

Caso você prefira entender cada parâmetro de forma mais detalhada, você pode clicar no mesmo que irá expandir um explicação básica e o link da documentação PostgreSQL com maiores detalhes:

A segunda possibilidade é “Export Config” do lado direito onde você em duas opções de formato para configuração:

• “UNIX-Like config file”, onde você possui a configuração indicada para o arquivo postgresql.conf de acordo com o hardware indicado acima:

• “ALTER SYSTEM commands”, onde podemos coletar os comandos para alteração diretamente na instância:

Ambas as maneiras trarão o mesmo resultado.

É possível executar os comandos ALTER SYSTEM pelo PGAdmin ou por linha de comando, pelo psql. 

No pgAdmin, clique com o botão direito sobre sua database, clique em Query Tool e cole o conteúdo. Após isto, clique em Executar ou aperte F5:

Por linha de comando, no psql, cole as linhas de configuração. 

Ao aplicar as alterações, seja pelo pgAdmin ou psql, você pode verificar as alterações que foram realizadas no arquivo:

/var/lib/pgsql/12/data/postgresql.auto.conf

Esse arquivo não pode ser alterado. Mas caso queira alterar manualmente o arquivo de configuração, realize a alteração do arquivo: /var/lib/pgsql/12/data/postgresql.conf. 

Lembre-se de reiniciar a instância após a alteração para que todos os parâmetros sejam aplicados.

Vacuum

Dados extraído documentação official PostgreSQL:

O comando VACUUM recupera a área de armazenamento ocupada pelas tuplas excluídas. Na operação normal do PostgreSQL as tuplas excluídas, ou tornadas obsoletas por causa de uma atualização, não são fisicamente removidas da tabela; permanecem presentes até o comando VACUUM ser executado. Portanto, é necessário executar o comando VACUUM periodicamente, especialmente em tabelas freqüentemente atualizadas.

Sem nenhum parâmetro, o comando VACUUM processa todas as tabelas do banco de dados corrente. Com um parâmetro, o comando VACUUM processa somente esta tabela.

O comando VACUUM ANALYZE executa o VACUUM e depois o ANALYZE para cada tabela selecionada. Esta é uma forma de combinação útil para scripts de rotinas de manutenção. Para obter mais detalhes sobre o seu processamento deve ser consultado o comando ANALYZE.

O comando VACUUM simples (sem o FULL) apenas recupera o espaço, tornando-o disponível para ser reutilizado. Esta forma do comando pode operar em paralelo com a leitura e escrita normal da tabela, porque não é obtido um bloqueio exclusivo. O VACUUM FULL executa um processamento mais extenso, incluindo a movimentação das tuplas entre blocos para tentar compactar a tabela no menor número de blocos de disco possível. Esta forma é muito mais lenta, e requer o bloqueio exclusivo de cada tabela enquanto está sendo processada.

• -a : passa em todos bancos de dados;
• -d base : para escolher o banco de dados;
• -f : vacuum full;
• -z : vacuum analyze;
• -v : verbose;
• -h : servidor remoto postgres;
• -U : login do usuário do banco.

$ vacuumdb -d tpprd -z  -f -v 2>/tmp/tpprd.log

$ vacuumdb -d tphml -z  -f -v 2>/tmp/tphml.log

$ vacuumdb -d tpdev -z  -f -v 2>/tmp/tpdev.log

• Criação de Usuário e Base de Dados para o ERP Protheus;

Após realizar a alteração ou no arquivo postgresql.conf ou com linha de comando para melhor performance, podemos prosseguir com a criação do banco de dados.

Neste exemplo, vamos criar os usuários ‘tpprd’, ‘tphml’ e ‘tpdev’ com a senha igual ao usuário no Cluster PostgreSQL. Mas atenção: por questões de segurança, não adote esta prática em seu ambiente de produção.

Você pode escolher outro nome e senha para a criação, esse padrão que utilizamos segue a seguinte definição e indicações de nomenclatura:

Acesse o linux, e entre com a usuário criado automaticamente na instalação do PostgreSQL:

su - postgresql

Certifique-se que este usuário tem acesso a pasta para criação dos datafiles do banco de dados.

Grants mínimos para funcionamento do DBAccess.

Diretamente no Sistema Operacional com a ferramenta psql:

•  Create User

psql -c "CREATE USER tpprd WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'tpprd'"
psql -c "CREATE USER tphml WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'tphml'"
psql -c "CREATE USER tpdev WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'tpdev'"

Ou com a ferramenta pgAdmin:

CREATE USER tpprd WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'tpprd'
CREATE USER tphml WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'tphml'
CREATE USER tpdev WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'tpdev'

Pode-se verificar a criação do usuário no PgAdmin no “Login/Group Roles”

Ou com a seguinte query:

SELECT usename FROM pg_user;

Após a criação do usuário, crie os tablespaces e dê permissão ao usuário criado.

•  Permissão para usuário Postgre

chown -R postgres:postgres /pgdata/ 

•  Criação de Path para Datafiles

su - postgres
cd /pgdata
mkdir tpprd/data
mkdir tpprd/index
mkdir tphml/data
mkdir tphml/index
mkdir tpdev/data
mkdir tpdev/index

•  Tablespace para Dados

Diretamente no Sistema Operacional com a ferramenta psql:

su - postgres

#(Ambiente Produção)

psql -c "CREATE TABLESPACE tpprd_data OWNER tpprd LOCATION '/pgdata/tpprd/data'";
psql -c "CREATE TABLESPACE tpprd_index OWNER tpprd LOCATION '/pgdata/tpprd/index'";

#(Ambiente Homologação)

psql -c "CREATE TABLESPACE tphml_data OWNER tphml LOCATION '/pgdata/tphml/data'";
psql -c "CREATE TABLESPACE tphml_index OWNER tphml LOCATION '/pgdata/tphml/index'";

#(Ambiente Desenvolvimento)

psql -c "CREATE TABLESPACE tpdev_data OWNER tpdev LOCATION '/pgdata/tpdev/data'";
psql -c "CREATE TABLESPACE tpdev_index OWNER tpdev LOCATION '/pgdata/tpdev/index'";

Ou com a ferramenta pgAdmin:

CREATE TABLESPACE tpprd_data OWNER tpprd LOCATION '/pgdata/tpprd/data';
CREATE TABLESPACE tpprd_index OWNER tpprd LOCATION '/pgdata/tpprd/index';
CREATE TABLESPACE tphml_data OWNER tphml LOCATION '/pgdata/tphml/data';
CREATE TABLESPACE tphml_index OWNER tphml LOCATION '/pgdata/tphml/index';
CREATE TABLESPACE tpdev_data OWNER tpdev LOCATION '/pgdata/tpdev/data';
CREATE TABLESPACE tpdev_index OWNER tpdev LOCATION '/pgdata/tpdev/index';

No pgAdmin podemos visualizar os tablespaces criados:

Ou com a seguinte query:

SELECT spcname FROM pg_tablespace;

Agora vamos para criação dos bancos de dados vinculando aos schemas "tpprd", "tphml" e "tpdev" criados anteriormente:

No pgAdmin podemos visualizar os tablespaces criados:

Ou com a query:

SELECT spcname FROM pg_tablespace;

Agora vamos para criação dos bancos de dados vinculando aos Schema “tpprd”, “tphml” e “tpdev” criados anteriormentes:

• Create Database

Diretamente no Sistema Operacional com a ferramenta psql:

su - postgres
psql -c "CREATE DATABASE tpprd with OWNER = tpprd TEMPLATE = template0 ENCODING = 'WIN1252' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = tpprd_data CONNECTION LIMIT = -1;";
psql -c "CREATE DATABASE tphml with OWNER = tphml TEMPLATE = template0 ENCODING = 'WIN1252' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = tphml_data CONNECTION LIMIT = -1;";
psql -c "CREATE DATABASE tpdev with OWNER = tpdev TEMPLATE = template0 ENCODING = 'WIN1252' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = tpdev_data CONNECTION LIMIT = -1;";

Ou com a ferramenta pgAdmin:

CREATE DATABASE tpprd with OWNER = tpprd TEMPLATE = template0 ENCODING = 'WIN1252' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = tpprd_data CONNECTION LIMIT = -1;
CREATE DATABASE tphml with OWNER = tphml TEMPLATE = template0 ENCODING = 'WIN1252' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = tphml_data CONNECTION LIMIT = -1;
CREATE DATABASE tpdev with OWNER = tpdev TEMPLATE = template0 ENCODING = 'WIN1252' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = tpdev_data CONNECTION LIMIT = -1;

No pgAdmin, visualize no item Databases, os bancos de dados que foram criados:

Ou com a seguinte query:

SELECT spcname FROM pg_database;

Métodos de autenticação.

Para se conectar na instância, rode o seguinte comando:

su - postgres 
psql

Pronto, você está conectado no postgres. Para quem está acostumado com Oracle, essa ferramenta é similar ao sqlplus. Você deve ter notado que não precisou colocar nenhum usuário e senha para esse acesso.

Nesse método se entende que o usuário conectado ao sistema operacional deve ter acesso ao Banco de Dados, sendo assim a restrição fica a cargo do acesso ao sistema operacional.

Para ajustar a configuração para utilizar autenticação no acesso via psql, acesse o arquivo pg_hba.conf e realize a modificação de acordo com a regra de segurança da empresa.

sudo vim /var/lib/pgsql/12/data/pg_hba.conf

ou

vim /var/lib/pgsql/12/data/pg_hba.conf

Métodos:

Wiki Modo de Autenticação

Configurar Acessos pg_hba.conf

Métodos de Autenticação

Backup e Restore

• Backup.

Guia prático do PostgreSQL para backup e restore.

Guia prático do PostgreSQL para importar e exportar.

Como diria um grande professor no passado, a coisa mais importante de um banco de dados é backup, backup, backup e por último backup.

Segue o comando de backup no PostgreSQL:

su - postgres
pg_dump -Fc  tmprd -v -U postgres > tmprd_backup.dump

Descrição

O pg_dump é um utilitário para fazer cópias de segurança de um banco de dados do PostgreSQL. São feitas cópias de segurança consistentes, mesmo que o banco de dados esteja sendo utilizado ao mesmo tempo. O pg_dump não bloqueia os outros usuários que estão acessando o banco de dados (leitura ou escrita).

O comando acima irá realizar o backup na unidade que você estiver logado no Sistema Operacional, indicamos a criação de uma unidade separada do banco de dados e sistema operacional, e que possua backup externo configurado. Garantindo assim um backup fora do servidor de banco de dados.

Opções

-F formato

--format=formato

c

Gera um arquivo personalizado adequado para servir de entrada para o pg_restore. Este é o formato mais flexível, porque permite a reordenação da restauração dos dados, assim como das definições dos objetos. Além disso, este formato é comprimido por padrão.

-v

--verbose

Especifica o modo verboso, fazendo o pg_dump colocar comentários detalhados sobre os objetos e os tempos de início/fim no arquivo de cópia de segurança, e mensagens de progresso na saída de erro padrão.

-U nome_do_usuário

Conectar como o usuário especificado.

• Restore.

Tão importante quanto realizar o backup é realizar o restore de forma satisfatória.

Segue o comando de restore no PostgreeSQL:

su - postgres

pg_restore -d p12hml -v -c -U p12hml < tmprd_backup.dump

Onde temos pg_restore que é o comando solicitando o restore do arquivo:  “tmprd_backup.dump”.

-d = dbname

Esse é o banco de dados onde deve ser restaurado o backup.

-v = verbose

Especifica o modo verbose

- c = clean

Limpar os objetos do banco de dados antes de recriar ele.

-U = Username

Usuário para a conexão no banco de dados.

O comando acima irá realizar o restore se você estiver na unidade do Sistema Operacional em que o arquivo se encontra, indicamos a criação de uma unidade separada do banco de dados e sistema operacional, e que possua backup externo configurado. Garantindo assim um backup fora do servidor de banco de dados.

• Configuração ODBC

A configuração ODBC é necessária para conexão do DBAccess com o Banco de Dados, vamos aos exemplos de configurações

# LINUX

Conecte-se no Sistema Operacional e rode o seguinte comando:

sudo su
yum update
yum install unixODBC.x86_64 postgresql-odbc.x86_64
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf update
yum install postgresql12-odbc.x86_64

Exemplo:

Configurar o arquivo odbc criado, apontando cada base de dados:

# /etc/odbc.ini

[tpprd]
Servername=10.2.0.8 (configurar o ip do servidor de banco de dados tpprd)
Username=tpprd (nome do user)
Password=tpprd (senha do user)
Database=tpprd (base de dados)
Driver=PostgreSQL
Port=5432
ReadOnly=0
MaxLongVarcharSize=2000
UnknownSizes=2
UseServerSidePrepare=1

[tphml]
Servername=10.2.0.8 (configurar o ip do servidor de banco de dados tphml)
Username=tphml
Password=tphml
Database=tphml
Driver=PostgreSQL
Port=5432
ReadOnly=0
MaxLongVarcharSize=2000
UnknownSizes=2
UseServerSidePrepare=1

[tpdev]
Servername=10.2.0.8 (configurar o ip do servidor de banco de dados tpdev)
Username=tpdev
Password=tpdev
Database=tpdev
Driver=PostgreSQL
Port=5432
ReadOnly=0
MaxLongVarcharSize=2000
UnknownSizes=2
UseServerSidePrepare=1

# Windows

PostgreSQL ODBC driver Versão 12 usado neste procedimento.

Portal ODBC driver PostgreSQL

Abrir a conexão ODBC - 64 bits: C:\windows\system32\odbcad32.exe, adicionar uma nova conexão:

Configurar conforme os dados de criação de base de dados: tpprd, tphml e tpdev.

Exemplo de dbaccess.ini para ambientes em Linux usando PostgreSQL:

#Linux

#/totvs/microsiga/dbaccess/multi/dbaccess.ini

[General]
ByYouProc=0
ODBC30=1
LicenseServer=10.2.0.9
LicensePort=5555
;;;;;------- Quando se utiliza o DBAccess em Linux é importante apontar o clientlibrary do odbc após ter realizado a instalação conforme o exemplo abaixo:

[POSTGRES]
environments=tpprd,tphml,tpdev
clientlibrary=/usr/pgsql-12/lib/psqlodbca.so
CodePage=WIN1252

[POSTGRES/tpprd]
user=tpprd
password=
TableSpace=tpprd_data
IndexSpace=tpprd_index

[POSTGRES/tphml]
user=tphml
password=
TableSpace=tphml_data
IndexSpace=tphml_index

[POSTGRES/tpdev]
user=tpdev
password=
TableSpace=tpdev_data
IndexSpace=tpdev_index

Exemplo de dbaccess.ini para ambientes em Windows usando PostgreSQL:

#Windows

#D:\totvs\microsiga\dbaccess\dbaccess.ini

[General]
LicenseServer=10.2.0.9
LicensePort=5555
ODBC30=1

[POSTGRES]
environments=TPPRD,TPHML,TPDEV

[POSTGRES/TPPRD]
user=tpprd
password=œðö¼
TableSpace=tpprd_data
IndexSpace=tpprd_index

[POSTGRES/TPHML]
user=tphml
password=œðèó´
TableSpace=tphml_data
IndexSpace=tphml_index

[POSTGRES/TPDEV]
user=tpdev
password=œðäëŠ
TableSpace=tpdev_data
IndexSpace=tpdev_index

Após o teste de conexão podemos verificar a criação das tabelas do DBAccess no banco de dados, sendo assim o banco de dados está liberado para ser utilizado para o ERP.

No pgAdmin podemos visualizar no “Databases” + “tpprd” + “Schemas” + “Tables”, as tabelas que foram criadas após o teste de conexão do DBAccess:

Ou com a seguinte query:

SELECT tablename FROM pg_tables where tableowner='tpprd';

Links Relacionados:

Wiki Guia para Report de Problemas

Grupo Telegram com mais de 2mil membros mantido pelo time da timbiras 

Ferramentas para o PostgreSQL

http://pgdocptbr.sourceforge.net/pg80/app-pgdump.html

https://pgtune.leopard.in.ua/#/

https://tdn.totvs.com/display/tec/DBAccess

https://www.postgresql.org/

https://www.postgresql.org/docs/12/app-initdb.html