PostgreSQL: Guia de instalação

há 5 dias 2

ANUNCIE AQUI

Saudações. Nesse artigo e tutorial vou apresentar o PostgreSQL (PSQL ou PGSQL) visando ajudar quem precisa instalá-lo de maneira clara e bem documentada.

Pré-requisitos (constam em outros artigos aqui do blog):

Instalação do Linux (Debian);
Obter terminal (shell) como usuário root;

1 – Instalação no HOST (direto no Debian)

Instalar direto no Linux é uma forma de montar servidores dedicados para banco de dados.

Instalando

Bash

# Atualizar sistema: apt-get -y update; apt-get -y upgrade; apt-get -y dist-upgrade; # Ferramentas basicas: apt-get -y install sudo; apt-get -y install curl wget; apt-get -y install ca-certificates; # Instalar base de scripts comuns do PG (não é o PostgreSQL em si) apt-get -y install postgresql-common; # a UNIT /lib/systemd/system/postgresql.service nao faz nada! # Instalar repositorio oficial PostgreSQL: /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh; # tecle ENTER para continuar # Com o repositorio presente, podemos instalar a versão desejada # Versoes: # - 18.0 # - 17.6 # - 16.10 # - 15.14 # - 14.19 # - 13.22 apt-get -y install postgresql-18; apt-get -y install postgresql-client-18; # table-space padrao.: /var/lib/postgresql/18/main # configuracao.......: /etc/postgresql/18/main/postgresql.conf # Controle do servico: # - Verificar status: systemctl status postgresql; # - Parar/Iniciar (reiniciar): systemctl stop postgresql; systemctl start postgresql; systemctl restart postgresql; # - Verificar processos: ps ax | grep postgres; # 194155 ? Ss 0:00 /usr/lib/postgresql/18/bin/postgres -D # /var/lib/postgresql/18/main -c # config_file=/etc/postgresql/18/main/postgresql.conf # 194156 ? Ss 0:00 postgres: 18/main: io worker 0 # 194157 ? Ss 0:00 postgres: 18/main: io worker 1 # 194158 ? Ss 0:00 postgres: 18/main: io worker 2 # 194159 ? Ss 0:00 postgres: 18/main: checkpointer # 194160 ? Ss 0:00 postgres: 18/main: background writer # 194162 ? Ss 0:00 postgres: 18/main: walwriter # 194163 ? Ss 0:00 postgres: 18/main: autovacuum launcher # 194164 ? Ss 0:00 postgres: 18/main: logical replication launcher # 194177 pts/1 S+ 0:00 grep post

Acesso

Formas de entrar via terminal no shell do Postgres:

Bash

# Primeiro acesse como postgres sudo -u postgres psql;

Nota: em linguagem SQL, comentários são definidos com “–“. Comentários com “#” são aceitos mas não recomendados.

Dentro do shell (psql), execute:

PostgreSQL Shell (psql)

-- Alterar senha do usuario padrao "postgres": ALTER USER postgres WITH PASSWORD 'tulipasql'; -- comentario pos-comando -- Crie um novo superusuário "root" (opcional, nao confundir com root do linux): CREATE USER root WITH SUPERUSER PASSWORD 'tulipasql'; -- Sair do psql: \q

Conectando pelo terminal do Linux usando senha e usuários diferentes:

Bash

# Tente conectar com senha psql -U postgres -h localhost -W # Password: tulipasql

2 – Comandos rápidos do terminal PSQL

Comandos do shell do PostgreSQL:

PostgreSQL Shell (psql)

\l -- Listar bancos de dados \c nome_db -- Conectar em um banco de dados \c nome_db usuario -- Conectar em um DB com usuário específico \d* -- Lista todos os objetos \dt -- Listar tabelas do banco conectado \dt+ -- Listar tabelas com detalhes \d nome_da_tabela -- Descreve a estrutura da tabela \d+ nome_da_tabela -- Descreve a estrutura da tabela detalhada \d nome_index_vw -- Descreve índice ou view \dn -- Lista todos os esquemas \du -- Lista usuários/roles \q -- Sair da conexão PGSQL \? -- Ajuda geral dos comandos psql \h -- Lista de todos os comandos \h SELECT -- Ajuda para usar o comando SELECT \h CREATE TABLE -- Ajuda para criar tabela \h comando_aqui -- Ajuda para o comando desejado \l -- Listar bancos de dados \l+ -- Listar bancos de dados com detalhes \dt -- Lista tabelas do schema atual \dt+ -- Lista tabelas com detalhes \dt schema.* -- Lista tabelas de um schema específico \dt *.nome_tabela -- Lista a tabela específica em todos os schemas \dp -- Lista privilégios de tabelas \dp+ -- Lista privilégios de tabelas com detalhes \dn -- Lista esquemas \dn+ -- Lista esquemas com detalhes \di -- Lista índices \di+ -- Lista índices com detalhes \df -- Lista funções \df+ -- Lista funções com detalhes \df nome_funcao -- Lista função específica \dy -- Lista triggers de eventos \dv -- Lista views \dv+ -- Lista views com detalhes \dm -- Apenas materialized views \ds -- Lista sequências \ds -- Lista sequências com detalhes \db -- Lista tablespaces \db+ -- Lista tablespaces com detalhes \dE -- Lista encodings disponíveis \dC -- Lista collations \dD -- Lista domains \dc -- Lista conversões \do -- Lista operadores \do+ -- Lista operadores com detalhes \da -- Lista funções de agregação \dT -- Lista tipos de dados \dT+ -- Lista tipos de dados com detalhes \e -- Abre editor para último comando \e script.sql -- Abre arquivo informado no editor \ef nome_funcao -- Abre o editor para editar a função informada \s -- Mostra histórico de comandos \s output.txt -- Salva histórico no arquivo informado \w output.sql -- Salva o último comando no arquivo informado \x -- Liga/desliga modo expandido (alterna) \x on -- Liga modo expandido (padrão, chato) \x off -- Desliga modo expandido (bom!) \a -- Alternar exibição entre alinhado/não alinhado \t -- Alternar exibição de cabeçalhos das consultas \f -- Mostrar separador atual (ascii de exibição) \f '|' -- Definir caracter pipe "|" como separador \f '\t' -- Define um TAB como separador \H -- Liga/desliga modo de saída HTML (alterna) \T 'Titulo X' -- Define um título para as próximas consultas \T -- Remove título das consultas \C 'Titulo Y' -- Define um título só para a próxima consulta \pset border 2 -- Define bordas das consultas \pset format wrapped -- Formato com quebra de linha das consultas \pset null '(NULL)' -- Define como exibir colunas com valor NULL \pset footer off -- Remove rodapé das tabelas \timing -- Liga/desliga cronômetro de consultas \timing on -- Liga cronômetro de consultas \timing on -- Desliga cronômetro de consultas \! -- Abre shell para o sistema onde o PG está rodando \! COMMAND -- Executa um comando específico no shell do sistema \cd -- Alterna para o diretório HOME no sistema \cd FOLDER_PATH -- Alterna para um diretório específico no sistema \setenv VN VLE -- Define uma variável de ambiente VN com valor VLE BEGIN -- Iniciar transação \begin -- Iniciar transação (apelido de BEGIN) COMMIT -- Finaliza a transação e executa \commit -- Finaliza a transação e executa (apelido de COMMIG) ROLLBACK -- Desfaz transação \rollback -- Desfaz transação (equivale a ROLLBACK) \i arquivo_sql -- Executa todos os comandos do arquivo (/pasta/…) \o arquivo_saida -- Redireciona a saída da tela para o arquivo SELECT 1\g out.txt -- Executa a SELECT e salva no arquivo out.txt -- Importe o arquivo file.csv para a tabela escolhida \copy tabela FROM 'file.csv' CSV HEADER; -- Exportar a tabela escolhida para um arquivo CSV \copy tabela TO 'file.csv' CSV HEADER; SELECT count(*) FROM tabela \watch 5 -- Executa a SELECT a cada 5 segundos \watch -- Para a repetição -- Executa e mostra em formato expandido SELECT * FROM tabela \gx -- Exibir em formato de tabela cruzada SELECT c1, c2 FROM tabela \crosstabview

3 – Instalando em container Docker

Usar o PG em container se tornou um novo padrão para aplicações em stacks.

Nota: não vou publicar o serviço para a Internet mapeando a porta 5432, faça isso por sua conta e risco usando o argumento -p 5432:5432 no docker run.

Usando docker run (simples)

Bash

# Criar rede de containers para banco de dados # - Rede de containers somente ipv4 docker network create -d bridge network_db; # Criando container 'postgres-18': docker run \ -d \ --restart=always \ --name postgres-18 \ --network network_db \ \ -e "POSTGRES_PASSWORD=tulipasql" \ \ -v pg18_data:/var/lib/postgresql/data \ \ postgres:18 \ postgres --max_connections=8192; # Entrar no shell do Postgres: docker exec -it --user=postgres postgres-18 psql; # \q para sair # Remover o container (nao apaga os dados do volume pg18_data) docker rm -f postgres-18;

Usando docker run (versão com todos os atributos de tuning)

Bash

# Criar rede de containers para banco de dados # - Rede de containers somente ipv4 docker network create -d bridge \ -o "com.docker.network.bridge.name"="br-net-db" \ -o "com.docker.network.bridge.enable_icc"="true" \ -o "com.docker.network.bridge.enable_ip_masquerade"="true" \ --subnet 10.251.0.0/16 \ --gateway 10.251.0.254 \ network_db; # Obter imagem da versao 18 (opcional, docker run ja faz isso) docker pull postgres:18; # Criando container 'postgres-18': docker run \ -d \ --restart=always \ --name postgres-18 \ -h postgres-18.intranet.br \ --network network_db \ --ip=10.251.0.201 \ --memory=2g \ --memory-swap=2g \ --shm-size=256m \ \ --tmpfs /tmp:rw,noexec,nosuid,size=512m \ --tmpfs /run/postgresql:rw,noexec,nosuid,size=128m \ \ -e "TZ=America/Sao_Paulo" \ -e "POSTGRES_PASSWORD=tulipasql" \ -e POSTGRES_INITDB_ARGS="--auth-host=scram-sha-256 --data-checksums" \ \ -v pg18_data:/var/lib/postgresql/data \ -v pg18_logs:/var/log/postgresql \ \ postgres:18 \ postgres \ -c max_connections=8192 \ -c shared_buffers=512MB \ -c effective_cache_size=1536MB \ -c maintenance_work_mem=128MB \ -c checkpoint_completion_target=0.9 \ -c wal_buffers=16MB \ -c default_statistics_target=100 \ -c random_page_cost=1.1 \ -c effective_io_concurrency=200 \ -c work_mem=2621kB \ -c huge_pages=off \ -c min_wal_size=1GB \ -c max_wal_size=4GB \ -c max_worker_processes=4 \ -c max_parallel_workers_per_gather=2 \ -c max_parallel_workers=4 \ -c max_parallel_maintenance_workers=2 \ -c log_timezone='America/Sao_Paulo' \ -c timezone='America/Sao_Paulo'; # Entrar no shell do Postgres: docker exec -it --user=postgres postgres-18 psql; # \q para sair # Remover o container (nao apaga os dados do volume pg18_data) docker rm -f postgres-18;

Usando stack (simples)

Compose Stack – YAML

version: '3.8' services: postgres-18: image: postgres:18 container_name: postgres-18 restart: always networks: - network_db environment: - POSTGRES_PASSWORD=tulipasql volumes: - pg18_data:/var/lib/postgresql/data command: postgres --max_connections=8192 networks: network_db: driver: bridge driver_opts: com.docker.network.enable_ipv6: "false" volumes: pg18_data: driver: local

Usando stack (versão com todos os atributos de tuning)

Compose Stack – YAML

version: '3.8' networks: network_db: driver: bridge driver_opts: com.docker.network.bridge.name: "br-net-db" com.docker.network.bridge.enable_icc: "true" com.docker.network.bridge.enable_ip_masquerade: "true" ipam: config: - subnet: 10.251.0.0/16 gateway: 10.251.0.254 volumes: pg18_data: driver: local pg18_logs: driver: local services: postgres-18: image: postgres:18 container_name: postgres-18 hostname: postgres-18.intranet.br restart: always networks: network_db: ipv4_address: 10.251.0.201 deploy: resources: limits: memory: 2G reservations: memory: 2G shm_size: 256m tmpfs: - /tmp:rw,noexec,nosuid,size=512m - /run/postgresql:rw,noexec,nosuid,size=128m environment: - TZ=America/Sao_Paulo - POSTGRES_PASSWORD=tulipasql - POSTGRES_INITDB_ARGS=--auth-host=scram-sha-256 --data-checksums volumes: - pg18_data:/var/lib/postgresql/data - pg18_logs:/var/log/postgresql command: - postgres - -c - max_connections=8192 - -c - shared_buffers=512MB - -c - effective_cache_size=1536MB - -c - maintenance_work_mem=128MB - -c - checkpoint_completion_target=0.9 - -c - wal_buffers=16MB - -c - default_statistics_target=100 - -c - random_page_cost=1.1 - -c - effective_io_concurrency=200 - -c - work_mem=2621kB - -c - huge_pages=off - -c - min_wal_size=1GB - -c - max_wal_size=4GB - -c - max_worker_processes=4 - -c - max_parallel_workers_per_gather=2 - -c - max_parallel_workers=4 - -c - max_parallel_maintenance_workers=2 - -c - log_timezone=America/Sao_Paulo - -c - timezone=America/Sao_Paulo

Para entrar no shell do PG pelo terminal do Portainer, obtenha shell para /bin/bash com o usuário postgresql, em seguida execute o comando psql.

5 – Tuning básico

Considerando uma instância do PostgreSQL limitado a 2G de RAM, os valores recomendados de tuning são:

ParâmetroValorDescrição

shared_buffers	512MB	25% da RAM total
effective_cache_size	1536MB	75% da RAM total
maintenance_work_mem	128MB	Para VACUUM, CREATE INDEX
work_mem	2621kB	RAM / (max_connections * 4)
wal_buffers	16MB	Para write-ahead logs

Para um I/O eficiente, evite discos SATA/SAS e opte por SSD ou NVME.
Para operações rápidas, opte por processadores modernos, clock de 3GHz ou superior com muita memória Cache L3 (128MB ou mais), memória DDR5 com ECC.

6 – Sistemas de gerenciamento visual

Segue alguns sistemas para administrar o PG visualmente:

pgAdmin – https://www.pgadmin.org/download/
DBeaver – https://dbeaver.io/
DbVisualizer (DbVis) – https://www.dbvis.com/
HeidiSQL – https://www.heidisql.com/
NaviCat – https://www.navicat.com/
DataGrip – https://www.jetbrains.com/datagrip/
TablePlus – https://tableplus.com/
Postico – https://eggerapps.at/postico2/
Luna Modeler – https://www.datensen.com/

Recomendo o pgAdmin por ser um sistema que roda em Docker e pode compor sua stack.

Usando docker run

Altere o usuário (env PGADMIN_DEFAULT_EMAIL) e senha (env PGADMIN_DEFAULT_PASSWORD) nas variáveis de ambiente abaixo:

Bash

# Criar rede de containers para banco de dados # - Rede de containers somente ipv4 docker network create -d bridge network_db; # Criando container 'postgres-18': docker run \ -d \ --restart=always \ --name pgadmin4 \ --network network_db \ \ -p 15080:80 \ \ -e PGADMIN_DEFAULT_EMAIL=root@intranet.br \ -e PGADMIN_DEFAULT_PASSWORD=pgadmin_2025 \ \ -v pgadmin:/var/lib/pgadmin \ \ dpage/pgadmin4;

Usando stack

Compose Stack – YAML

version: '3.8' networks: network_db: driver: bridge external: true volumes: pgadmin: driver: local services: pgadmin4: image: dpage/pgadmin4 container_name: pgadmin4 restart: always networks: - network_db ports: - "15080:80" environment: - PGADMIN_DEFAULT_EMAIL=root@intranet.br - PGADMIN_DEFAULT_PASSWORD=pgadmin_2025 volumes: - pgadmin:/var/lib/pgadmin

Acesse o IP do servidor na porta 15080 (http://ip-do-servidor:1580) para acessar o pgAdmin. Ele pode demorar até 2 minutos para carregar totalmente.

7 – Acesso remoto via shell PSQL

Para acessar via linha de comando um servidor Postgres cujo IP e porta TCP sejam alcançáveis, use alguma dessas técnicas:

Via cliente psql de um servidor para o outro

Bash

# Acesso remoto na porta exposta do container PG (postgres-main) psql -h 45.255.128.2 -p 5432 -U postgres; # ou psql -h 45.255.128.2 -p 5432 -U postgres -d DB_NAME_AQUI; # ou URL do schema postgresql: psql postgresql://postgres:tulipa_pgsql@45.255.128.2:5432; psql postgresql://postgres:tulipa_pgsql@45.255.128.2:5432/DB_NAME_AQUI;

Via SSH com comando remoto

Esse método permite com um único comando SSH executar o psql para acesso:

Bash

# SSH para o servidor # - use o Termius, SecureCRT, Putty ou seu software favorito # - troque 45.255.128.2 e 22 pelo ip e porta desejado ssh root@45.255.128.2 -p 22; # Rodar comando psql dentro do container (exit ou \q para sair): docker exec -it --user=postgres psql postgres-18 -U postgres; docker exec -it --user=postgres psql postgres-18 -U postgres -d DB_NAME_AQUI; # Método reunido: # Rodar o SSH ordenando execução de comando remoto: ssh root@45.255.128.2 -p 22 -t \ docker exec -it --user=postgres postgres-18 psql;

Usando SSH-Tuneling para mapeamento de porta local em servidor remoto

Nesse método o primeiro comando abre um port-forward para o IP do servidor, o que resulta na abertura de uma porta local enquanto o comando ssh estiver rodando.

Essa porta local aberta garante um túnel TCP até a porta TCP do PostgreSQL:

Bash

# Criar túnel TCP # - conecta-se ao servidor 45.255.128.2 -p 22 # - abre a porta 9991 no ip de loopback (127.0.0.1) # - qualquer aplicativo pode ser conectar na porta 9991 local que # dará acesso ao IP 10.249.255.121 porta 5432 # Opção 1: Somente criar o túnel (com o -N): ssh root@45.255.128.2 -p 22 -N -L 127.0.0.1:9991:10.249.255.121:5432 # Agora conecte-se ao postegresql (psql) no ip localhost 127.0.0.1 porta 9991 psql -h 127.0.0.1 -p 9991 -U postgres; # ou psql -h 127.0.0.1 -p 9991 -U postgres -d DB_NAME_AQUI;