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.Sistemas Operacionais Linux, e pode ser utilizado em um ambiente de produção como referência e apoio ao DBA. 

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:

...

Operating System = GNU/Linux Based

Application Profile= ERP or Long Transaction Applications

Architecture= 64 Bits (x86-64)

PostgreSQL Version= 12

Storage Type= SSD Storage

Number of CPUs= 4  (De acordo com o Servidor do Banco de Dados)

Total Memory (GB)= 16 (De acordo com o Servidor do Banco de Dados)

Max Connections= 100 (Com dicionário no Banco de Dados coloque, no mínimo, o dobro de conexões do ERP)

Data Storage = SSD Storage (De acordo com o Servidor do Banco de Dados)

Nas páginas abaixo, é demonstrada a instalação do PostgreSQL 12 em sistema operacional RedHat Enterprise Linux release 8.3 - x86_64.

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:

Image Removed

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:

Image Removed

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:

Image Removed

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

Image Removed

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:

Image Removed

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

Image Removed

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.

• 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”

Image Removed

Ou com a seguinte query:

SELECT usename FROM pg_user;

Image Removed

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:

Image Removed

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:

Image Removed

Ou com a seguinte query:

SELECT spcname FROM pg_database;

Image Removed

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

...

trust

...

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

Image Removed

Image Removed

Image Removed

Image Removed

Image Removed

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

Image Removed

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

Image Removed

Image Removed

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:

Image Removed

Ou com a seguinte query:

SELECT tablename FROM pg_tables where tableowner='tpprd';

Image Removed

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