Histórico da Página
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.
Informações | ||
---|---|---|
| ||
|
Informações |
---|
Neste manual, demonstramos a instalação do PostgreSQL 12, última versão homologada até a data de publicação deste documento. |
Aviso | ||
---|---|---|
| ||
Em ambientes de produção, o ideal é ter um DBA para apoiar na instalação e configuração, por conta de configurações específicas de cenário e tuning no banco de dados. |
Nota | ||
---|---|---|
| ||
O sistema operacional utilizado como base para esta página foi o Red Hat Enterprise Linux release 8.3 - x86_64. Os comandos aqui apresentados são referentes ao Red Hat 8. |
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.
Bloco de código |
---|
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 |
Informações | ||
---|---|---|
| ||
Você pode alterar a versão e verificar o comando para instalação em outras versões. |
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.
Bloco de código |
---|
/usr/pgsql-12/bin/postgresql-12-setup initdb |
Posterior à criação do Cluster, vamos colocar na inicialização do Sistema Operacional:
Bloco de código |
---|
systemctl enable postgresql-12 |
E iniciar:
Bloco de código |
---|
systemctl start postgresql-12 |
Pode-se verificar o status da seguinte forma:
Bloco de código |
---|
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.
Informações | ||
---|---|---|
| ||
Foi utilizado, neste documento, a ferramenta PGConfig. Esta ferramenta é desenvolvida e mantida pela comunidade Open Source do PostgreSQL. Outra ferramenta amplamente utilizada é o PGTune. |
Aviso | ||
---|---|---|
| ||
Autovacuum deve esta ligado! autovacuum = on Durante o processo de homologação foram realizados teste com ele em modo desabilitado e modo habilitado, o processo em modo desabilitado apresentou baixa perfomance ao executar determinadas rotinas e por isso recomendamos que esteja ligado. |
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) |
Após alterar todos os valores necessários, as recomendações para a configuração do banco de dados logo abaixo.
Aviso | ||
---|---|---|
| ||
Estes ajustes são referentes ao sizing do servidor de exemplo, e os valores aqui retratados NÃO devem ser utilizados como base para seu ambiente de produção. O único valor que será utilizado para o Protheus é o Application Profile = ERP or Long Transaction Applications. |
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.
Aviso | ||
---|---|---|
| ||
Estes ajustes são referentes ao sizing do servidor de exemplo, e os valores aqui retratados NÃO devem ser utilizados como base para seu ambiente de produção. O único valor que será utilizado para o Protheus é o Application Profile = ERP. |
Nota |
---|
Com um limite de 200 a 300 conexões no Protheus, o Linux default pode atender à demanda, mas é importante que o postgreSQL também esteja em Linux. Em determinados casos, é importante efetuar ajustes no sistema operacional para garantir uma boa performance do cenário. |
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.
Bloco de código | ||||||
---|---|---|---|---|---|---|
| ||||||
$ 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:
T (TOTVS) P (Protheus) PRD (Produção) | TPPRD |
T (TOTVS) P (Protheus) HML (Homologação) | TPHML |
T (TOTVS) P (Protheus) DEV (Desenvolvimento) | TPDEV |
Acesse o linux, e entre com a usuário criado automaticamente na instalação do PostgreSQL:
Bloco de código |
---|
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
Bloco de código |
---|
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:
Bloco de código |
---|
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:
Bloco de código |
---|
SELECT usename FROM pg_user; |
Após a criação do usuário, crie os tablespaces e dê permissão ao usuário criado.
Informações | ||
---|---|---|
| ||
O Path utilizado (/pgdata) foi criado no sistema operacional e é exclusivo para os datafiles Protheus. Você pode especificar um path diferente, caso haja. |
- Permissão para usuário Postgre
chown -R postgres:postgres /pgdata/
- Criação de Path para Datafiles
Bloco de código |
---|
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:
Bloco de código |
---|
su - postgres |
#(Ambiente Produção)
Bloco de código |
---|
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)
Bloco de código |
---|
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)
Bloco de código |
---|
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:
Bloco de código |
---|
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'; |
Informações | ||
---|---|---|
| ||
Para ambientes que utilizam serviços da AWS (RDS) não tem necessidade de criar tablespace. |
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:
Bloco de código |
---|
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:
Bloco de código |
---|
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:
Bloco de código |
---|
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;
Nota | ||
---|---|---|
| ||
Configure o ambiente Produção, Homologação e Desenvolvimento em servidores diferentes para evitar a concorrência de serviços computacionais. |
Métodos de autenticação.
Para se conectar na instância, rode o seguinte comando:
Bloco de código |
---|
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.
Bloco de código |
---|
sudo vim /var/lib/pgsql/12/data/pg_hba.conf |
ou
Bloco de código |
---|
vim /var/lib/pgsql/12/data/pg_hba.conf |
Métodos:
trust | permite conexão a qualquer um incondicionalmente (sem senha) |
reject | rejeita conexão incondicionalmente para o user/host especificado |
crypt | recomendada somente para versões inferiores a 7.2. Atualmente recomenda-se md5 |
krb5 | somente disponível para conexões via TCP/IP |
ident | Obtém o nome do usuário do sistema operacional. Para conexões TCP/IP contacta o servidor ident no cliente. Para conexões locais, recebendo este do sistema operacional. |
pam | usando o serviço PAM (Pluggable Authentication Modules) do sistema operacional. |
password | envia senha em texto claro. |
md5 | deve ser preferido, pois envia senhas criptografadas. |
Wiki Modo de Autenticação
Configurar Acessos pg_hba.conf
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:
Bloco de código |
---|
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:
Bloco de código |
---|
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:
Bloco de código |
---|
# /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.
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
Bloco de código |
---|
#/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
Bloco de código |
---|
#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
http://pgdocptbr.sourceforge.net/pg80/app-pgdump.html
https://pgtune.leopard.in.ua/#/
https://tdn.totvs.com/display/tec/DBAccess
https://www.postgresql.org/docs/12/app-initdb.html