Histórico da Página
Instalando o PostgreSQL
Para "baixar" e instalar o PostgreSQL, execute os comandos abaixo.a seguir:
Nota | ||
---|---|---|
| ||
Estes comandos são referentes ao sistema operacional 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 que é desenvolvida e mantida pela comunidade Open Source do PostgreSQL. Outra ferramenta amplamente utilizada é o PGTune. Clique aqui para visualizar dicas sobre desempenho e otimizações para o PostgreSQL. |
Aviso | ||
---|---|---|
| ||
autovacuum = on Durante o processo de homologação foram realizados testes com o autovacuum em desabilitado e habilitado. O , o processo em modo desabilitado apresentou baixa perfomance ao executar determinadas rotinas. Por isso, recomendamos que o autovacuum 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 estarão 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 similarmente, em qualquer ambiente, para o Protheus, é o Application Profile = ERP or Long Transaction Applications. |
Do lado direito, em Profile Comparison, são apresentados os valores recomendados, na coluna OLTP:
Caso queira entender cada parâmetro de forma mais detalhada, clique no mesmo. Uma explicação básica e o link da documentação do PostgreSQL serão apresentados com maiores mais detalhes:
Em Export Config, do lado direito, você receberá duas opções de formato para realizar a configuração:
- “UNIX-Like config file” exibe a configuração indicada para o arquivo postgresql.conf de acordo com o hardware informado acima:
- “ALTER SYSTEM commands”, permite coletar os comandos para realizar a alteração diretamente na instância:
Nota | ||
---|---|---|
| ||
Ambas as maneiras de alteração trarão o mesmo resultado, configurando o sistema. |
Os comandos ALTER SYSTEM podem ser executados 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 similarmente, em qualquer ambiente, para o Protheus, é o Application Profile = ERP or Long Transaction Applications. |
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
Informações | ||
---|---|---|
| ||
Reinicie a instância após a alteração para assegurar que todos os parâmetros serão aplicados. |
Vacuum
Dados extraídos da documentação oficial do 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 frequentemente 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 no arquivo postgresql.conf ou com linha de comando para melhor performance, podemos prosseguir com a criação do banco de dados.
Neste exemplo, foram criados os usuários ‘tpprd’, ‘tphml’ e ‘tpdev’ com a senha igual ao nome de usuário no Cluster PostgreSQL.
Nota | ||
---|---|---|
| ||
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, o padrão utilizado neste documento segue esta 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 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.
Nota | ||
---|---|---|
| ||
Confira os grants mínimos para o funcionamento do DBAccess. |
Diretamente no Sistema Operacional com a ferramenta psql:
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 Postgres no diretório dos datafiles
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 |
- Criando as Tablespaces 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 há a necessidade de criar tablespace. |
No pgAdmin podemos visualizar os tablespaces criados:
Ou com a query:
Bloco de código |
---|
SELECT spcname FROM pg_tablespace; |
Criação dos bancos de dados vinculados aos schemas “tpprd”, “tphml” e “tpdev” criados anteriormente
- 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:
Bloco de código | ||
---|---|---|
| ||
SELECT datname 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, execute o seguinte comando:
Bloco de código |
---|
su - postgres psql |
Desta maneira você está conectado no postgres. Esta ferramenta é similar ao sqlplus aos que estão acostumados com Oracle. Note que não foi necessário informar 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 e 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 Permite conexão a qualquer um incondicionalmente (sem senha) |
reject | rejeita Rejeita conexão incondicionalmente para o user/host especificado |
crypt | recomendada Recomendada somente para versões inferiores a 7.2. Atualmente recomenda-se md5 |
krb5 | somente 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 Usando o serviço PAM (Pluggable Authentication Modules) do sistema operacional. |
password | envia Envia senha em texto claro. |
md5 | deve Deve ser preferido, pois envia senhas criptografadas. |
Documentos de referência para a criação dos acessos em PostgreSQL
Wiki: configurar acessos pg_hba.conf
Docs PostgreSQL: Métodos de Autenticação (Authentication Methods)
Backup e Restore
Backup
Wiki: Guia prático do PostgreSQL para backup e restore.
Wiki: Guia prático do PostgreSQL para importar e exportar.
O backup é uma das coisas mais importantes que deve ser realizada de forma periódica, assegurando que, em caso de falha, o risco de perda de dados seja minimizado.
O comando de backup no PostgreSQL é:
Bloco de código |
---|
su - postgres pg_dump -Fc tmprd -v -U postgres > tmprd_backup.dump |
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 utilizadas:
-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", que faz 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, além de mensagens de progresso na saída de erro padrão.
-U nome_do_usuário
Conectar-se 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:
Bloco de código |
---|
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 elerecriá-lo.
-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
Download do driver do PostgreSQL utilizado neste documento.
Portal ODBC - driver PostgreSQL
Os prints a seguir detalham a instalação do driver para conexão com o PostgreSQL.
As legendas descrevem as imagens acima.
Na tela inicial, clique em Next para prosseguir com a instalação.
Leia os termos da licença.
Após, marque a opção de aceite caso esteja de acordo.
Isto é necessário para prosseguir com a instalação do ODBC. Clique em Next.
Na tela a seguir, são apresentados os itens que serão instalados, neste caso, o Driver ODBC e a documentação.
Na tela seguinte, clique em Install para prosseguir com a instalação.
Ou clique em Back para rever ou mudar qualquer configuração.
Ao clicar em Install, a ODBC será instalada.
Clique em Finish para finalizar.
Abra a conexão ODBC - 64 bits: C:\windows\system32\odbcad32.exe, e adicione uma nova conexão:
Selecione PostgreSQL ANSI(x64).
Configure conforme os dados de criação de base de dados: tpprd, tphml e tpdev.
Insira em Data Source o nome do ambiente que você criou.
Em Database, insira o nome da database.
Em Server, insira o endereço do servidor do banco de dados.
Em User Name, insira o nome de usuário para acesso ao banco de dados, e em Password, a senha do usuário para acesso.
Em Port, insira a porta utilizada para instalação do banco de dados.
A descrição (Description) não é obrigatória, mas auxilia na identificação do ambiente.
Realize o teste de conexão clicando em Test.
O pop-up que aparecer deve informar que a conexão teve sucesso.
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 |
Aviso | ||
---|---|---|
| ||
A chave ODBC30 deve estar habilitada em ambientes PostgreSQL, conforme documentação do DBAccess. |
Nota |
---|
É importante apontar a chave clientlibrary em ambientes PostgreSQL em Sistemas operacionais Operacionais Linux. |
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 |
Aviso | ||
---|---|---|
| ||
A chave ODBC30 deve estar habilitada em ambientes PostgreSQL, conforme documentação do DBAccess. |
Após o teste de conexão, verifique a criação das tabelas do DBAccess no banco de dados. Com estas tabelas, o banco de dados pode 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:
Bloco de código | ||
---|---|---|
| ||
SELECT tablename FROM pg_tables where tableowner='tpprd'; |