Como Instalar e Usar o PostgreSQL no Ubuntu

Introdução

Sistemas de gerenciamento de banco de dados são um componente central de muitos sites e aplicativos, fornecendo uma maneira estruturada de armazenar, organizar e acessar informações. PostgreSQL, comumente conhecido como Postgres, é um dos principais sistemas de gerenciamento de banco de dados relacional de código aberto. Ele oferece uma implementação completa da linguagem de consulta SQL e é bem considerado por sua conformidade com padrões e recursos avançados, como transações confiáveis e concorrência sem bloqueios de leitura.

Este guia demonstra o processo completo para configurar o PostgreSQL em um servidor Ubuntu, começando com a instalação dos pacotes de software necessários. Em seguida, você aprenderá como interagir com o sistema de autenticação do Postgres, que é gerenciado por meio de “roles” (papéis), incluindo a criação de novos usuários e bancos de dados. Depois disso, abordaremos os fundamentos da gestão de bancos de dados: conectando-se ao seu novo banco de dados, definindo uma estrutura de dados por meio da criação de tabelas e manipulando dados usando comandos SQL básicos. Também exploraremos tópicos administrativos chave, incluindo a configuração essencial para desempenho, estratégias de backup e restauração, uma comparação do PostgreSQL com outros motores de banco de dados e um guia para solução de problemas comuns.

Pré-requisitos

Para acompanhar este tutorial, você precisará de um servidor Ubuntu que tenha sido configurado seguindo nosso guia de Configuração Inicial do Servidor para Ubuntu. Após completar este tutorial pré-requisito, seu servidor deverá ter um usuário não-root com permissões sudo e um firewall básico.

Passo 1 — Instalando o PostgreSQL

Para instalar o PostgreSQL, primeiro atualize o índice de pacotes local do seu servidor:

sudo apt update

Em seguida, instale o pacote postgres junto com um pacote -contrib, que adiciona algumas utilidades e funcionalidades adicionais:

sudo apt install postgresql postgresql-contrib

Você pode verificar a versão executando o seguinte comando:

psql --version

Certifique-se de que o serviço está iniciado:

sudo systemctl start postgresql.service

Etapa 2 — Usando Roles e Bancos de Dados do PostgreSQL

Por padrão, o Postgres usa um conceito chamado "roles" para lidar com autenticação e autorização. Estes são, de certa forma, semelhantes aos usuários e grupos no estilo Unix.

Após a instalação, o Postgres é configurado para usar autenticação peer, o que significa que ele associa funções do Postgres a uma conta do sistema Unix/Linux correspondente. Se uma função existir no Postgres, um nome de usuário do Unix/Linux com o mesmo nome poderá entrar como essa função.

O procedimento de instalação criou uma conta de usuário chamada postgres que está associada ao papel padrão do Postgres. Existem algumas maneiras de usar essa conta para acessar o Postgres. Uma delas é alternar para a conta postgres em seu servidor executando o seguinte comando:

sudo -i -u postgres

Então você pode acessar o prompt do Postgres executando:

psql

Isso irá conectá-lo ao prompt do PostgreSQL e, a partir daí, você pode interagir com o sistema de gerenciamento de banco de dados imediatamente.

Para sair do prompt do PostgreSQL, execute o seguinte:

\q

Isso irá levá-lo de volta ao prompt de comando do Linux postgres. Para retornar ao seu usuário regular do sistema, execute o comando exit:

exit

Outra forma de se conectar ao prompt do Postgres é executar o comando psql como a conta postgres diretamente com sudo:

sudo -u postgres psql

Isso fará com que você entre diretamente no Postgres sem o shell intermediário bash no meio.

Novamente, você pode sair da sessão interativa do Postgres executando o seguinte:

\q

Passo 3 — Criando um Novo Papel

Se você estiver logado com a conta postgres, pode criar um novo papel executando o seguinte comando:

createuser --interactive

Se, em vez disso, você preferir usar sudo para cada comando sem mudar da sua conta normal, execute:

sudo -u postgres createuser --interactive

De qualquer forma, o script irá apresentar algumas opções e, com base nas suas respostas, executar os comandos corretos do Postgres para criar um usuário de acordo com suas especificações.

Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Passo 4 — Criando um Novo Banco de Dados

Outra suposição que o sistema de autenticação do Postgres faz por padrão é que qualquer função usada para fazer login terá um banco de dados com o mesmo nome que ela pode acessar.

Isso significa que, se o usuário que você criou na seção anterior se chamar sammy, essa função tentará se conectar a um banco de dados que também é chamado “sammy” por padrão. Você pode criar o banco de dados apropriado com o comando createdb.

Se você estiver logado como a conta postgres, você digitariam algo como o seguinte:

createdb sammy

Se, em vez disso, você preferir usar sudo para cada comando sem sair da sua conta normal, você deverá executar:

sudo -u postgres createdb sammy

Passo 5 — O Método SQL para Criar um Novo Usuário e Banco de Dados (Opcional)

Os comandos de shell createuser e createdb são auxiliares convenientes. No entanto, para ter mais controle, você pode realizar essas mesmas ações diretamente no PostgreSQL usando comandos SQL. Essa abordagem costuma ser mais clara ao definir senhas ou conceder permissões específicas desde o início.

1. Primeiro, conecte-se como o usuário administrativo postgres:

   sudo -u postgres psql

   Uma vez no prompt do PostgreSQL, você pode usar CREATE ROLE e CREATE DATABASE para configurar seu novo usuário e banco de dados.

2. Criar uma Nova Função (Usuário): Enquanto o comando de shell createuser é interativo, o comando CREATE ROLE permite definir tudo em uma única instrução. Para criar um usuário chamado sammy que pode fazer login (LOGIN) e possui uma senha, execute:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password';

   Se esse usuário também precisar criar bancos de dados, você pode conceder essa permissão ao mesmo tempo:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password' CREATEDB;

   Isso fornece um subconjunto de permissões de superusuário, permitindo especificamente que o usuário crie novos bancos de dados. Embora responder ‘sim’ à pergunta sobre superusuário no assistente interativo também conceda essa permissão, o próprio SUPERUSER é um papel muito mais amplo e perigoso.

3. Criar um Novo Banco de Dados: Em seguida, crie o banco de dados. É uma boa prática atribuir a propriedade do novo banco de dados ao novo papel que você acabou de criar.

   CREATE DATABASE sammydb OWNER sammy;

   Para conceder ao usuário sammy permissão para se conectar ao novo banco de dados (um próximo passo comum), você pode executar:

   GRANT ALL PRIVILEGES ON DATABASE sammydb TO sammy;

   Isso concede privilégios em nível de banco de dados, como CONNECT, mas não permissões para tabelas específicas. Observe que isso não concede automaticamente permissões para visualizar ou modificar tabelas dentro do banco de dados; comandos adicionais de GRANT no esquema (por exemplo, public) e nas próprias tabelas são necessários para isso.

4. Saia da sessão postgres: Você pode agora sair do prompt psql do usuário postgres.

   \q

Como você definiu uma senha, este novo usuário pode se autenticar usando métodos diferentes de ident, o que é necessário para conexões remotas.

Passo 6 — Abrindo um Prompt do Postgres com o Novo Papel

Para fazer login com autenticação baseada em ident, você precisará de um usuário Linux com o mesmo nome da sua função e banco de dados do Postgres.

Se você não tiver um usuário Linux correspondente disponível, pode criar um com o comando adduser. Você terá que fazer isso a partir da sua conta não-root com privilégios de sudo (ou seja, não logado como o usuário postgres):

sudo adduser sammy

Assim que esta nova conta estiver disponível, você pode alternar e se conectar ao banco de dados executando o seguinte:

sudo -i -u sammy

psql

Ou, você pode fazer isso diretamente:

sudo -u sammy psql

Este comando fará o login automaticamente, assumindo que todos os componentes tenham sido configurados corretamente.

Se você quiser que seu usuário se conecte a um banco de dados diferente, pode fazê-lo especificando o banco de dados da seguinte maneira:

psql -d postgres

Uma vez conectado, você pode verificar suas informações de conexão atuais executando:

\conninfo

You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Isso é útil se você estiver se conectando a bancos de dados não padrão ou com usuários não padrão.

Etapa 7 — Criando e Excluindo Tabelas

Agora que você sabe como se conectar ao sistema de banco de dados PostgreSQL, pode aprender algumas tarefas básicas de gerenciamento do Postgres.

A sintaxe básica para criar tabelas é a seguinte:

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

Como você pode ver, esses comandos dão um nome à tabela e, em seguida, definem as colunas, bem como o tipo de coluna e o comprimento máximo dos dados do campo. Você também pode, opcionalmente, adicionar restrições de tabela para cada coluna.

Para saber mais sobre como criar e excluir tabelas, confira nosso artigo sobre como criar e gerenciar tabelas no Postgres.

Para fins de demonstração, crie a seguinte tabela:

CREATE TABLE playground (

    equip_id serial PRIMARY KEY,

    type varchar (50) NOT NULL,

    color varchar (25) NOT NULL,

    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),

    install_date date

);

Este comando criará uma tabela que inventaria o equipamento de playground. A primeira coluna da tabela conterá os números de ID do equipamento do tipo serial, que é um número inteiro com incremento automático. Essa coluna também possui a restrição PRIMARY KEY, o que significa que os valores nela devem ser únicos e não nulos.

As próximas duas linhas criam colunas para o equipamento type e color, respectivamente, nenhuma das quais pode ficar vazia. A linha seguinte cria uma coluna location assim como uma restrição que exige que o valor seja um dos oito possíveis. A última linha cria uma coluna date que registra a data em que você instalou o equipamento.

Para duas das colunas (equip_id e install_date), o comando não especifica um comprimento de campo. A razão para isso é que alguns tipos de dados não exigem um comprimento definido porque o tamanho ou formato é implícito.

Você pode ver sua nova tabela digitando:

\d

                  List of relations
 Schema |          Name           |   Type   | Owner 
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Sua tabela playground está aqui, mas também existe algo chamado playground_equip_id_seq que é do tipo sequence. Isso é uma representação do tipo serial que você deu à sua coluna equip_id. Isso acompanha o próximo número na sequência e é criado automaticamente para colunas desse tipo.

Se você quiser ver apenas a tabela sem a sequência, você pode digitar:

\dt

          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

Com uma tabela pronta, vamos usá-la para praticar o gerenciamento de dados.

Passo 8 — Adicionando, Consultando e Excluindo Dados em uma Tabela

Agora que você tem uma tabela, você pode inserir alguns dados nela. Como exemplo, adicione um slide e um balanço chamando a tabela na qual você quer adicionar, nomeando as colunas e depois fornecendo dados para cada coluna, assim:

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');

INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

Você deve ter cuidado ao inserir os dados para evitar alguns erros comuns. Por exemplo, não coloque os nomes das colunas entre aspas, mas os valores das colunas que você inserir precisam de aspas.

Outra coisa a ter em mente é que você não deve inserir um valor na coluna equip_id. Isso ocorre porque ela é gerada automaticamente sempre que você adiciona uma nova linha à tabela.

Recupere as informações que você adicionou digitando:

SELECT * FROM playground;

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

Aqui, você pode ver que seu equip_id foi preenchido com sucesso e que todos os seus outros dados foram organizados corretamente.

Se o escorregador do parquinho quebrar e você precisar removê-lo, você também pode remover a linha da sua tabela digitando:

DELETE FROM playground WHERE type = 'slide';

Consulte a tabela novamente:

SELECT * FROM playground;

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Observe que a linha slide não faz mais parte da tabela.

Passo 9 — Adicionando e Excluindo Colunas de uma Tabela

Depois de criar uma tabela, você pode modificá-la adicionando ou removendo colunas. Adicione uma coluna para mostrar a última visita de manutenção de cada equipamento digitando:

ALTER TABLE playground ADD last_maint date;

Se você visualizar as informações da sua tabela novamente, verá que a nova coluna foi adicionada, mas nenhum dado foi inserido:

SELECT * FROM playground;

 equip_id | type  | color  | location  | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   | 
(1 row)

Se você descobrir que sua equipe de trabalho usa uma ferramenta separada para acompanhar o histórico de manutenção, você pode excluir a coluna digitando:

ALTER TABLE playground DROP last_maint;

Isso exclui a coluna last_maint e quaisquer valores encontrados nela, mas mantém todos os outros dados intactos.

Passo 10 — Atualizando Dados em uma Tabela

Até agora, você aprendeu como adicionar registros a uma tabela e como excluí-los, mas este tutorial ainda não abordou como modificar entradas existentes.

Você pode atualizar os valores de uma entrada existente consultando o registro desejado e definindo a coluna para o valor que deseja usar. Você pode consultar o registro swing (isso corresponderá a todas as balanças na sua tabela) e alterar sua cor para vermelho. Isso pode ser útil se você deu uma demão de tinta no conjunto de balanços:

UPDATE playground SET color = 'red' WHERE type = 'swing';

Você pode verificar se a operação foi bem-sucedida consultando os dados novamente:

SELECT * FROM playground;

 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2018-08-16
(1 row)

Como você pode ver, o slide agora está registrado como sendo vermelho.

Configuração e Dicas de Desempenho do PostgreSQL

Configurar o PostgreSQL corretamente é essencial para um banco de dados estável e rápido. As configurações padrão são projetadas para serem seguras para qualquer hardware, o que significa que quase nunca são adequadas para uma carga de trabalho de produção. Ajustar o desempenho envolve a modificação da alocação de recursos—principalmente memória, I/O e gerenciamento de conexões—para corresponder ao hardware do seu servidor e aos padrões específicos de consulta da sua aplicação.

Vamos discutir alguns parâmetros de configuração principais e estratégias gerais para melhorar o desempenho do banco de dados.

Arquivos de Configuração Principais

Antes de fazer ajustes, você deve saber onde os arquivos de configuração estão. No Ubuntu, eles estão localizados em /etc/postgresql/[versão]/main/:

• postgresql.conf: O principal arquivo de configuração do servidor. É aqui que você ajusta todos os parâmetros relacionados ao desempenho.
• pg_hba.conf: Este arquivo controla a autenticação de clientes (quais usuários podem se conectar de quais endereços). Ele não afeta diretamente o desempenho das consultas, mas é fundamental para a segurança.

Após editar o postgresql.conf, você pode aplicar as alterações recarregando o serviço PostgreSQL:

sudo systemctl reload postgresql

No entanto, alguns parâmetros (marcados como contexto postmaster na documentação) exigem uma reinicialização completa:

sudo systemctl restart postgresql

Você pode executar o seguinte comando para verificar se precisa reiniciar ou recarregar após uma alteração:

SELECT context FROM pg_settings WHERE name = 'parameter_name';

Em contraste, alterações no pg_hba.conf exigem apenas um reload.

sudo systemctl reload postgresql

Configuração de Memória

A memória é o fator mais significativo no desempenho do banco de dados. O PostgreSQL usa memória compartilhada para armazenar em cache os dados e memória separada por conexão para operações como ordenação.

buffers_compartilhados

Esta é a configuração de memória mais importante. Ela define a quantidade de memória que o PostgreSQL dedica ao seu cache de dados. Um cache maior significa que mais do seu conjunto de dados em uso pode ser lido da memória rápida em vez do disco lento.

• Diretriz: Comece configurando shared_buffers para 25% da memória RAM total do seu sistema.

• Exemplo: Em um servidor com 16 GB de RAM, um bom ponto de partida é 4GB.

• Configuração (postgresql.conf):

  # Default is 128MB, which is too low
  shared_buffers = 4GB

work_mem

Esta configuração define a quantidade de memória que cada operação individual do banco de dados (como uma ordenação, junção por hash ou bitmap) pode usar antes de gravar em arquivos temporários no disco. Consultas complexas podem ter várias operações, cada uma usando essa quantidade de memória.

Definir isso muito alto pode levar à exaustão de memória se muitas consultas complexas forem executadas ao mesmo tempo. Definir muito baixo causa ordenação frequente e lenta baseada no disco.

• Diretriz: Comece com o padrão (4MB). Analise suas consultas lentas usando EXPLAIN ANALYZE. Se você vir “Sort Method: external merge Disk” ou “HashAgg Disk”, isso significa que work_mem é muito pequeno. Aumente gradualmente (por exemplo, 16MB, 32MB) para sua sessão para testar, e só defina globalmente se tiver certeza.

memória_para_trabalho_de_manutenação

Esta configuração reserva memória para operações internas de manutenção, como VACUUM, CREATE INDEX e ALTER TABLE ADD FOREIGN KEY.

• Diretriz: Defina isso maior do que work_mem (por exemplo, 128MB ou 256MB). Como essas operações não são executadas com a mesma frequência que as consultas dos usuários, você pode ser mais generoso para acelerar a manutenção.

Configuração de Checkpoint e WAL

Checkpoints são processos nos quais o PostgreSQL grava todos os dados "sujos" (alterações mantidas na memória) nos arquivos de dados permanentes no disco. Esta é uma operação muito intensiva de E/S. O Write-Ahead Log (WAL) registra cada transação. Ajustar esses parâmetros ajuda a equilibrar a carga de E/S e o tempo de recuperação.

16MB

Gerenciamento de Conexão

Toda conexão com o PostgreSQL utiliza memória. Um número alto de conexões pode esgotar os recursos do servidor.

• max_connections: O número máximo de conexões simultâneas. O padrão é 100. Embora seja tentador aumentar esse valor, cada conexão consome memória.
• Use um Pool de Conexões: Para aplicações que abrem e fecham muitas conexões (como a maioria das aplicações web), não aumente o max_connections. Em vez disso, use um pool de conexões externo como o PgBouncer ou o Pgpool-II. Um pool mantém um pequeno conjunto de conexões persistentes com o banco de dados e atende a muitas conexões concorrentes da aplicação, o que é muito mais eficiente.

Planejamento e Análise de Consultas

Nenhuma quantidade de ajuste no servidor pode corrigir uma consulta ineficiente.

• EXPLAIN ANALYZE: Esta é sua ferramenta principal para otimização de consultas. Execute este comando nas suas consultas lentas para ver o plano de execução real e o tempo gasto.

  EXPLAIN ANALYZE SELECT * FROM users WHERE last_name = 'Smith';

  Procure por “Varredura Sequencial” (Seq Scan) em tabelas grandes e indexadas. Isso geralmente significa que você está sem um índice ou que a consulta não é seletiva o suficiente.

• Criar Índices: Se você filtra ou faz junções com frequência em colunas específicas (por exemplo, user_id, email, created_at), elas são bons candidatos para um índice.

  CREATE INDEX idx_users_on_email ON users (email);

• Execute VACUUM ANALYZE: O PostgreSQL utiliza um planejador de consultas que depende de estatísticas sobre os dados da sua tabela. Essas estatísticas são atualizadas pelo ANALYZE. Embora o autovacuum faça isso automaticamente, é uma boa prática executar manualmente VACUUM ANALYZE; após grandes carregamentos de dados ou se você notar que os planos de consulta estão ruins.

Backup e Restauração de Bancos de Dados PostgreSQL

Proteger seus dados é uma responsabilidade primária da administração de bancos de dados. Backups regulares e verificados são a defesa mais eficaz contra corrupção de dados, falhas de hardware ou erro humano. O PostgreSQL fornece um conjunto de ferramentas robustas de linha de comando para criar backups lógicos e restaurá-los.

Existem duas categorias principais de backups:

1. Backups Lógicos (SQL Dumps): Esses backups consistem em comandos SQL que, quando executados, recriam os objetos do banco de dados (como tabelas e índices) e os dados. Eles são criados com as ferramentas pg_dump e pg_dumpall. Esse método é flexível e portátil entre diferentes versões do PostgreSQL.
2. Backups Físicos (nível de Sistema de Arquivos): Este método envolve copiar os arquivos de dados físicos do diretório de dados do PostgreSQL. Isso é normalmente usado para Recuperação Pontual (PITR), uma configuração avançada discutida posteriormente nesta seção.

Criando Backups Lógicos com pg_dump

O utilitário pg_dump faz backup de um único banco de dados. Ele não fará backup de objetos globais como funções ou tablespaces. É altamente flexível e pode ser executado em um banco de dados ativo sem bloquear leitores ou escritores.

Opções Comuns do pg_dump

Você pode controlar a saída de pg_dump com vários parâmetros de linha de comando:

• -F c (Formato: personalizado): Este é o formato recomendado para a maioria dos backups. Ele produz um arquivo binário comprimido que não é legível por humanos, mas permite restaurações em paralelo e a reorganização ou exclusão de objetos durante a restauração.
• -F p (Formato: plain): Este é o padrão. Ele gera um grande arquivo .sql. Sua principal desvantagem é que deve ser restaurado de uma só vez e não suporta restaurações paralelas.
• -s (Apenas esquema): Faz backup apenas da estrutura do banco de dados (tabelas, views, índices) sem nenhum dado.
• -a (Somente dados): Faz backup apenas dos dados, assumindo que o esquema já existe no destino.
• --exclude-table=TABLE_NAME: Exclui uma tabela específica do backup. Isso é útil para pular tabelas grandes, temporárias ou não críticas.
• -f FILENAME: Especifica o arquivo de saída.
• -U USER: Especifica o usuário do PostgreSQL para conexão.

Este é o comando recomendado para a maioria das tarefas de backup. Ele se conecta ao banco de dados mydatabase como o usuário postgres e cria um arquivo compactado chamado mydatabase.dump.

pg_dump -U postgres -F c -f mydatabase.dump mydatabase

Restaurando Backups Lógicos

A ferramenta que você usa para restaurar depende do formato do seu arquivo de backup.

Restaurando de um Arquivo Personalizado (.dump)

Use a ferramenta pg_restore. Esta ferramenta oferece flexibilidade significativa, incluindo a capacidade de restaurar em paralelo e executar verificações preliminares.

Em vez de criar manualmente o banco de dados primeiro, a melhor prática é usar o parâmetro --create. Esse parâmetro indica ao pg_restore para emitir o comando CREATE DATABASE a partir do arquivo de backup. Para fazer isso, você deve se conectar a um banco de dados diferente já existente, como o banco de dados padrão postgres.

O comando a seguir conecta ao banco de dados postgres, exclui e recria mydatabase (por causa de --clean e --create), e usa 4 trabalhos em paralelo para restaurá-lo.

pg_restore -U postgres -d postgres --create --clean -j 4 mydatabase.dump

• -d postgres: Conecta-se ao banco de dados postgres para emitir o comando de criação.
• --create: Instrui o pg_restore a criar o banco de dados de destino (por exemplo, mydatabase) antes de restaurar nele.
• --clean: Quando usado com --create, este parâmetro irá apagar e recriar todo o banco de dados de destino. Quando usado sozinho (restaurando em um banco de dados existente), ele apaga os objetos do banco de dados existentes antes de recriá-los.
• -j 4: Usa 4 trabalhos em paralelo para a restauração.

Conectamos ao banco de dados postgres porque o pg_restore precisa de uma conexão com um banco de dados existente no servidor para emitir o comando CREATE DATABASE para o nosso novo destino.

Restaurando de um arquivo de texto simples (.sql)

Use o cliente padrão psql. Este método requer que o banco de dados de destino já exista.

1. Crie o banco de dados:

   createdb -U postgres mydatabase_new

2. Encaminhe o arquivo .sql para o psql:

   psql -U postgres -d mydatabase_new < mydatabase.sql

Fazendo Backup de Todos os Bancos de Dados (pg_dumpall)

Para fazer backup de todos os bancos de dados no servidor, além de todos os objetos globais (roles, usuários e tablespaces), use a ferramenta pg_dumpall. Essa ferramenta produz apenas um arquivo .sql em texto simples.

# Run as the postgres superuser

pg_dumpall -U postgres > all_databases.sql

Para restaurar a partir de um arquivo pg_dumpall, você deve direcioná-lo para o psql. É necessário conectar-se como um superusuário (como postgres) a um banco de dados padrão (como o próprio postgres). O arquivo de script contém todos os comandos CREATE ROLE e CREATE DATABASE necessários e irá executá-los a partir daí.

# Run as the postgres superuser

psql -U postgres -d postgres -f all_databases.sql

Fazendo backup apenas de funções e variáveis globais

Um padrão comum e útil é fazer backup apenas de objetos globais. Isso é feito com a flag --globals-only e é uma maneira eficaz de replicar permissões de usuário entre servidores.

pg_dumpall -U postgres --globals-only > roles.sql

Resumo da Estratégia de Backup e Restauração

A tabela a seguir resume a estratégia de backup e restauração:

.sql or .dump

.sql

Melhores Práticas de Automação e Backup

Um plano de backup não está completo até que seja automatizado, verificado e seguro.

• Automatize seus backups: Use uma ferramenta padrão do Linux como o cron para agendar seus comandos pg_dump ou pg_dumpall para rodarem à noite.
• Gerencie Senhas com Segurança: Para executar backups em um script não interativo, não codifique senhas diretamente. Use o arquivo .pgpass. Crie um arquivo em ~/.pgpass no diretório home do usuário (por exemplo, /var/lib/postgresql/.pgpass). O arquivo deve ter permissões estritas 0600, caso contrário será ignorado pelo PostgreSQL. O formato é hostname:port:database:user:password. Você pode usar * como curinga para qualquer campo, por exemplo, localhost:5432:*:postgres:sua_senha.
• Teste Seus Restaurações: Um backup só é útil se puder ser restaurado. Pratique regularmente a restauração de seus backups em um servidor separado, que não seja de produção, para verificar sua integridade.
• Verifique seus backups: Após criar um backup, adicione uma etapa ao script para verificar se o tamanho do arquivo não é zero. Para formatos personalizados, você pode executar pg_restore -l mydatabase.dump &> /dev/null (que lista o conteúdo em /dev/null) para confirmar que o arquivo não está corrompido, verificando o código de saída do comando.
• Armazene Backups Remotamente: Não armazene seus backups no mesmo servidor que seu banco de dados. Uma falha completa do servidor resultaria na perda de ambos. Copie os backups para um local remoto, como o Linux-Console.net Spaces ou outro servidor, como a etapa final do seu script.

Comparação do PostgreSQL com Outros Motores de Banco de Dados no Ubuntu

Os repositórios de pacotes do Ubuntu oferecem acesso a uma grande variedade de motores de banco de dados. A melhor escolha depende inteiramente dos requisitos da sua aplicação, como seu modelo de dados, necessidades de escalabilidade e garantias de consistência.

• PostgreSQL: Este banco de dados objeto-relacional é conhecido pelo seu alto nível de conformidade com padrões, integridade dos dados e conjunto robusto de recursos. É totalmente compatível com ACID e oferece bom suporte a tipos de dados avançados e consultas complexas, tornando-se uma escolha comum para um banco de dados principal de “sistema de registro”.
• MySQL: Como parte do stack LAMP original, o MySQL é um dos bancos de dados open-source mais populares. É conhecido por sua facilidade de uso, desempenho de leitura historicamente forte e comunidade ativa. Também é totalmente compatível com ACID (com o mecanismo InnoDB padrão) e é favorecido para aplicações web simples.
• MariaDB: Um fork do MySQL mantido pela comunidade. Embora mantenha alta compatibilidade (especialmente com versões mais antigas), ele se diferenciou e introduz seus próprios recursos, portanto, pode não ser um substituto completo para aplicativos desenvolvidos com recursos do MySQL 8.0+ modernos.
• SQLite: Este não é um banco de dados cliente-servidor. É uma biblioteca em C que incorpora um motor de banco de dados completo em uma aplicação, armazenando o banco de dados inteiro em um único arquivo. Ele lida bem com múltiplos leitores simultâneos, mas não é ideal para aplicações com gravações concorrentes frequentes ou cenários multiusuário em rede.
• MongoDB: Um banco de dados NoSQL líder, o MongoDB armazena dados em documentos flexíveis, semelhantes a JSON. Isso permite um esquema flexível e é projetado para alta disponibilidade e escalabilidade horizontal. Embora seja conhecido pela flexibilidade, o MongoDB também oferece suporte a transações ACID envolvendo múltiplos documentos desde a versão 4.0, tornando-o uma escolha viável para aplicações que exigem consistência forte.
• Redis: Este é um armazenamento de estruturas de dados em memória, o que significa que ele mantém todos os seus dados na RAM para uma velocidade excepcional. Não é um substituto de uso geral para um banco de dados baseado em disco, mas quase sempre é usado junto com um (como PostgreSQL) para agir como um cache de alta velocidade ou armazenamento de sessões.

Aqui está uma comparação do PostgreSQL com outros motores de banco de dados populares disponíveis no Ubuntu.

Comparação de Motores de Banco de Dados

Problemas Comuns do PostgreSQL e Soluções no Ubuntu

Executar o PostgreSQL no Ubuntu pode apresentar desafios específicos, especialmente para usuários novos em sua arquitetura em sistemas baseados no Debian. A maioria dos erros se enquadra em categorias previsíveis: conflitos de instalação, regras de autenticação, permissões de arquivos e gerenciamento de serviços.

Antes de solucionar problemas, sua ferramenta mais importante é o arquivo de log do PostgreSQL. Em instalações padrão do Ubuntu, você pode encontrá-lo em /var/log/postgresql/postgresql-[versão]-main.log. Sempre verifique este log primeiro, pois ele fornece mensagens de erro específicas que irão orientar seu diagnóstico.

Problemas de Instalação

Esses problemas geralmente ocorrem durante o processo inicial de apt install ou quando o initdb tenta criar um novo cluster de banco de dados pela primeira vez.

Conflitos de Pacotes ou Incompatibilidade de Versões

Esse problema ocorre quando seu sistema tenta instalar pacotes do PostgreSQL de duas fontes diferentes, como o repositório padrão do Ubuntu e o repositório oficial do PostgreSQL Global Development Group (PGDG). Os repositórios do Ubuntu frequentemente contêm versões mais antigas e estáveis, enquanto o repositório PGDG fornece os lançamentos mais recentes. Misturá-los pode levar a dependências quebradas.

Resolução de problemas:

• Verifique suas fontes: Use apt-cache policy postgresql para ver quais versões estão disponíveis e de qual repositório.
• Escolha uma fonte: É altamente recomendável usar o repositório PGDG para obter a versão mais atualizada. Se você adicionar o repositório PGDG, pode ser necessário usar a técnica de apt pinning para priorizar seus pacotes em relação aos pacotes padrão do Ubuntu.
• Corrigir instalações quebradas: Se você já está em um estado conflituoso, sudo apt-get -f install pode resolvê-lo. Caso contrário, pode ser necessário purgar completamente todos os pacotes do PostgreSQL (sudo apt-get purge "postgresql-*") e reinstalar a partir de uma única fonte limpa.

Porta 5432 Já Está em Uso

Este erro significa que outro programa já está usando a porta padrão do PostgreSQL (5432). Isso é comum se uma instância antiga do PostgreSQL que não foi desinstalada corretamente não conseguiu desligar ou se você tem múltiplos clusters do PostgreSQL em execução (um recurso do sistema pg_ctlcluster do Ubuntu).

Resolução de problemas:

• Identifique o processo: Use netstat para descobrir o que está usando a porta.

  sudo netstat -tulpn | grep 5432

• Verificar outros clusters: Se o processo for postgres, use o comando pg_lsclusters para ver todos os clusters configurados e seus status.

  pg_lsclusters

  A saída pode mostrar um 'online' e um 'offline'.

• Pare o cluster indesejado: Se você encontrar um cluster antigo ou não intencional em execução, pare-o usando sua versão e nome.

  sudo pg_ctlcluster 13 main stop

• Altere a porta: Se outro serviço estiver usando a porta 5432 corretamente, você deve configurar seu novo cluster PostgreSQL para usar uma porta diferente editando seu arquivo postgresql.conf.

Erros de Localidade e Codificação Durante o initdb

Erros como “nome de localidade inválido” ou “não foi possível determinar a localidade padrão” durante a configuração inicial do cluster (initdb) significam que as configurações de localidade do seu sistema Ubuntu estão ausentes ou configuradas incorretamente. O PostgreSQL herda essas configurações do sistema operacional para determinar a ordem de classificação (collation), a classificação de caracteres e a formatação.

Resolução de problemas:

• Verifique os locais do seu sistema: Execute locale -a para ver todos os locais disponíveis.

• Gerar locais ausentes: Se o local desejado (por exemplo, en_US.UTF-8) não estiver listado, você deve gerá-lo.

  sudo locale-gen en_US.UTF-8
  
  sudo update-locale

• Reinicialize o cluster: Após configurar os locais do sistema, pode ser necessário remover e recriar o cluster PostgreSQL. O comando pg_createcluster é o método padrão do Ubuntu para isso.

  Aviso: Isso destrói todos os dados no cluster

  sudo pg_dropcluster [version] main
  
  sudo pg_createcluster [version] main

Problemas de autenticação

Estes são os problemas mais frequentes que os usuários enfrentam após uma instalação bem-sucedida.

Falha na autenticação entre pares

Este é o erro mais comum. Significa que você está tentando se conectar localmente e o nome de usuário do Ubuntu com o qual você está logado atualmente não corresponde ao nome do papel do banco de dados PostgreSQL que você está tentando usar.

Por padrão, o PostgreSQL no Ubuntu usa autenticação peer para conexões locais. Esse método pergunta ao sistema operacional: "Qual é o nome de usuário do processo que está se conectando a mim?" e permite a conexão se esse nome de usuário corresponder ao papel do banco de dados solicitado.

Por exemplo, se você estiver logado como myuser e executar psql -U postgres, isso falha porque o sistema operacional informa seu nome de usuário como myuser, não postgres.

Solução:

A forma correta de se conectar como superusuário postgres é primeiro assumir a identidade do usuário do sistema postgres usando sudo.

sudo -u postgres psql

Se você deseja alterar esse comportamento, deve definir uma senha para o papel postgres e, em seguida, editar o arquivo pg_hba.conf para usar md5 ou scram-sha-256 em vez de peer.

Mau funcionamento do pg_hba.conf

Erros no arquivo pg_hba.conf (Autenticação Baseada em Host) são a principal causa de falhas de conexão. Este arquivo, localizado em /etc/postgresql/[version]/main/pg_hba.conf, é um conjunto de regras que controla quem pode se conectar de onde para qual banco de dados usando qual método. Um único erro de sintaxe ou a ordem incorreta das regras pode impedir o acesso.

Resolução de problemas:

• A ordem das regras importa: O PostgreSQL lê este arquivo de cima para baixo e usa a primeira regra que corresponder. Regras mais específicas devem sempre ser colocadas antes das mais gerais.

• Recarregar após alterações: Após editar pg_hba.conf, você deve recarregar o serviço PostgreSQL para que as alterações tenham efeito:

  sudo systemctl reload postgresql

Aqui estão erros comuns de configuração:

host all all 127.0.0.1/32 scram-sha-256

host all all 0.0.0.0/0 reject, host all all 192.168.1.100/32 md5

local all postgres md5

Problemas de Permissão

Esses erros frequentemente aparecem como “Permissão negada” nos logs ou ao tentar se conectar.

Não é possível acessar o arquivo de socket

Erros como “não foi possível conectar ao servidor: Arquivo ou diretório não encontrado” ou “Permissão negada” ao referir-se a /var/run/postgresql/.s.PGSQL.5432 geralmente significam uma de duas coisas:

1. O serviço não está em execução: O arquivo de socket é removido quando o serviço é parado. Verifique o status do serviço com sudo systemctl status postgresql.
2. Permissões de usuário: O diretório do socket é de propriedade de postgres e acessível pelo grupo postgres. Se o seu usuário do sistema não estiver no grupo postgres, você não poderá usar o socket.

Solução:

Adicione seu usuário ao grupo postgres. Você deve sair e entrar novamente para que essa mudança tenha efeito.

sudo usermod -a -G postgres $USER

Permissões do Diretório de Dados

Se o serviço PostgreSQL não conseguir iniciar e os registros mencionarem “Permissão negada” relacionado ao diretório de dados (por exemplo, /var/lib/postgresql/[version]/main), a propriedade do diretório ou as permissões de arquivo estão incorretas.

O PostgreSQL exige que seu diretório de dados seja de propriedade exclusiva do usuário do sistema postgres e possua permissões 0700 (apenas o proprietário pode ler, escrever e executar). Isso é uma medida de segurança. Esse problema geralmente acontece após restaurar um backup ou mover o diretório de dados manualmente.

Solução:

Redefina recursivamente a propriedade e as permissões.

# Correct the ownership (replace 18 with your version)

sudo chown -R postgres:postgres /var/lib/postgresql/18/main



# Correct the permissions

sudo chmod 0700 /var/lib/postgresql/18/main

Gestão de Serviços

Esses problemas estão relacionados ao iniciar, parar e gerenciar o serviço postgresql em si.

Falha ao Iniciar o Serviço Após Alterações na Configuração

Se o serviço não iniciar imediatamente após você editar postgresql.conf ou pg_hba.conf, provavelmente você introduziu um erro de sintaxe.

Resolução de problemas:

1. Verifique os logs: Esta é a maneira mais rápida de encontrar o erro. sudo journalctl -u postgresql ou o log específico em /var/log/postgresql/postgresql-[version]-main.log frequentemente mostrará o número exato da linha da configuração inválida.

2. Erros Comuns em postgresql.conf:

   • Valores de string não entre aspas (por exemplo, log_destination=stderr está correto; log_destination=/var/log/my.log está errado, deveria ser 'var/log/my.log').
   • Valores inválidos para as configurações (por exemplo, shared_buffers=10GB quando você tem apenas 8GB de RAM).

Confusão Sobre Múltiplos Clusters

O pacote postgresql-common do Ubuntu inclui um conjunto de scripts wrapper (pg_lsclusters, pg_ctlcluster, pg_createcluster) que permitem executar várias instâncias separadas do PostgreSQL (clusters) na mesma máquina, muitas vezes de diferentes versões.

Este é um recurso poderoso, mas pode ser confuso. Você pode editar o arquivo de configuração para a versão 18, mas estar se conectando à versão 13, levando você a acreditar que suas "alterações não estão sendo aplicadas."

Resolução de problemas:

• Listar todos os clusters: Sempre comece por aqui para ver o que está rodando, em qual porta e a localização de seus arquivos de configuração.

  pg_lsclusters
  # Ver Cluster Port Status Owner    Data directory                       Log file
  # 13  main    5432 down   postgres /var/lib/postgresql/13/main         ...
  # 18  main    5433 online postgres /var/lib/postgresql/18/main         ...

• Gerencie clusters específicos: Ao iniciar, parar ou recarregar, sempre especifique o cluster que você pretende alterar:

  sudo pg_ctlcluster 18 main reload

Limites de Recursos

Esses erros ocorrem quando o PostgreSQL solicita mais recursos (como memória) do que o sistema operacional está configurado para fornecer.

Erros de Memória Compartilhada

Erros na inicialização como “não foi possível criar o segmento de memória compartilhada: Argumento inválido” ou “Sem espaço disponível no dispositivo” geralmente significam que a configuração shared_buffers do PostgreSQL é maior do que o limite do kernel do seu sistema operacional para memória compartilhada (SHMMAX).

O PostgreSQL usa um grande bloco de memória compartilhada para cache e comunicação entre processos. Se ele solicitar mais do que o sistema operacional permite, o sistema operacional nega a solicitação e o PostgreSQL não consegue iniciar. Isso é comum em ambientes com memória limitada ou em contêineres.

Resolução de problemas:

1. Verifique os limites do SO:

   cat /proc/sys/kernel/shmmax

2. Verifique o postgresql.conf: Olhe a sua configuração shared_buffers.

3. Solução 1 (Recomendada): Reduza o valor de shared_buffers no postgresql.conf para ficar com segurança abaixo do limite de shmmax.

4. Solução 2 (Avançada): Aumente o limite shmmax do kernel. Edite /etc/sysctl.conf e adicione uma linha como kernel.shmmax=134217728 (para 128MB). Aplique com sudo sysctl -p. Geralmente, isso só é necessário para ajustes de alto desempenho.

Perguntas Frequentes

1. O que é PostgreSQL e por que eu deveria usá-lo no Ubuntu?

PostgreSQL, frequentemente chamado de Postgres, é um sistema de banco de dados objeto-relacional poderoso e de código aberto. Ele tem uma forte reputação por confiabilidade, robustez de recursos e integridade dos dados, estando em desenvolvimento ativo há mais de 30 anos.

Você deveria considerar usá-lo em suas aplicações porque ele oferece suporte total à conformidade com ACID (Atomicidade, Consistência, Isolamento, Durabilidade), lida bem com cargas de trabalho de alta concorrência e suporta uma ampla gama de tipos de dados avançados e extensões, como a extensão PostGIS para dados geoespaciais.

Usar PostgreSQL no Ubuntu é uma escolha comum porque o Ubuntu é uma distribuição Linux popular e estável para servidores. O PostgreSQL está incluído nos repositórios padrão de pacotes do Ubuntu, o que torna a instalação e o recebimento de atualizações de segurança em tempo hábil um processo simples, gerenciado pelo gerenciador de pacotes apt do sistema.

2. Como eu instalo o PostgreSQL no Ubuntu?

Você pode instalar o PostgreSQL diretamente nos repositórios padrão do Ubuntu usando o comando apt.

Primeiro, atualize o índice de pacotes local do seu servidor para garantir que você tenha as listas de pacotes mais recentes:

sudo apt update

Em seguida, instale o pacote principal do PostgreSQL e o pacote contrib, que inclui utilitários e extensões adicionais:

sudo apt install postgresql postgresql-contrib

3. Como faço para iniciar e habilitar o serviço do PostgreSQL?

Após uma instalação bem-sucedida, o serviço PostgreSQL deve iniciar automaticamente. Você pode verificar seu status usando systemctl.

sudo systemctl status postgresql

Você pode ver uma saída assim:

Output
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2025-10-29 10:30:01 IST; 1min 12s ago
   Main PID: 1234 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4662)
...

Se o serviço não estiver em execução, você pode iniciá-lo manualmente:

sudo systemctl start postgresql

Para garantir que o banco de dados inicie automaticamente toda vez que o servidor for ligado, você deve ativar o serviço:

sudo systemctl enable postgresql

4. Como posso criar um novo banco de dados no PostgreSQL?

O método mais simples é usar a ferramenta de linha de comando createdb. Este comando deve ser executado por um papel do PostgreSQL que tenha permissão para criar bancos de dados.

Por padrão, a conta de usuário postgres no Linux é configurada para se conectar como o superusuário do banco de dados postgres. Você pode mudar para esse usuário e executar o comando:

sudo -i -u postgres

createdb my_database

Alternativamente, você pode fazer login no prompt interativo do PostgreSQL (psql) e usar o comando SQL CREATE DATABASE.

Primeiro, acesse o prompt como o usuário postgres:

sudo -i -u postgres

psql

Então, a partir do prompt psql (que aparece como postgres=# porque postgres é um superusuário), execute o comando SQL. Lembre-se de terminar os comandos SQL com um ponto e vírgula.

CREATE DATABASE my_database;

Você pode sair do prompt psql digitando \q e depois digitar exit para retornar ao seu usuário comum.

5. Como criar e gerenciar usuários e funções do PostgreSQL?

No PostgreSQL, o gerenciamento de usuários é feito através de roles. Uma role pode ser um usuário de banco de dados ou um grupo de usuários, mas uma role com o privilégio LOGIN é considerada um usuário.

1. Criando um Usuário

Você pode usar o utilitário de linha de comando createuser. É melhor executá-lo como o usuário postgres.

sudo -i -u postgres

Um método recomendado é usar a flag --interactive para receber solicitações de configuração e a flag --pwprompt para definir uma senha imediatamente.

createuser --interactive --pwprompt

Isso solicitará o nome da função, uma senha e se a função deve ser superusuário ou ter outras permissões de alto nível.

Output
Enter name of role to add: sammy
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Você também pode criar um usuário com um único comando SQL a partir do prompt psql. Isso cria um usuário mínimo que só pode fazer login.

CREATE ROLE sammy WITH LOGIN PASSWORD 'secure_password';

2. Concedendo Privilégios

Criar um usuário não concede automaticamente acesso a um banco de dados. Você deve conceder privilégios explicitamente. Esse processo possui várias etapas:

1. Conceder Acesso ao Connect: Primeiro, conceda ao novo cargo permissão para se conectar ao seu banco de dados.

   GRANT CONNECT ON DATABASE my_database TO sammy;

   Erro Comum: Usar GRANT ALL PRIVILEGES ON DATABASE my_database TO sammy; é enganoso. Este comando apenas concede permissões de CONNECT, CREATE e TEMP no próprio banco de dados, não nas tabelas dentro dele.

2. Uso do Esquema de Concessão: Em seguida, você deve conceder permissão para acessar o esquema dentro do banco de dados. Sem isso, o usuário pode se conectar, mas não consegue ver nenhuma tabela.

   GRANT USAGE ON SCHEMA public TO sammy;

3. Conceder Permissões de Tabela: Agora, você pode conceder as permissões específicas que o usuário precisa nos objetos dentro do esquema. Conecte-se ao banco de dados como superusuário para fazer isso:

   \c my_database

   Você está agora conectado a my_database.

4. Conceder permissões em objetos existentes: Para dar à função sammy acesso de leitura/escrita em todas as tabelas existentes, execute:

   GRANT ALL ON ALL TABLES IN SCHEMA public TO sammy;
   
   GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO sammy;

   Se você quiser conceder apenas acesso de leitura, você deve usar GRANT SELECT em vez de GRANT ALL.

5. (Opcional) Conceder Permissões para Objetos Futuros: Os comandos acima afetam apenas tabelas existentes. Para conceder permissões automaticamente para qualquer tabela nova criada no futuro (por exemplo, por um administrador), você deve alterar os privilégios padrão:

   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sammy;
   
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sammy;

6. Quais são os comandos básicos do PostgreSQL para iniciantes?

A ferramenta mais importante para aprender é o terminal interativo psql. Uma vez que você esteja logado (por exemplo, executando sudo -i -u postgres e depois psql), você pode usar meta-comandos (que começam com uma barra invertida \) para navegar e inspecionar seu banco de dados.

Aqui estão alguns dos comandos psql mais comuns:

\l or \list

\c database_name

\conninfo

\dt

\du

\d table_name

\?

\q

7. Como faço para conectar a um banco de dados PostgreSQL pelo terminal?

Como você se conecta depende do método de autenticação configurado no arquivo pg_hba.conf. (Este arquivo normalmente está localizado em /etc/postgresql/<versão>/main/pg_hba.conf). Por padrão, o PostgreSQL no Ubuntu usa peer authentication para conexões locais.

A autenticação peer funciona obtendo o nome de usuário do sistema operacional do cliente a partir do kernel e usando-o como o nome de usuário permitido no banco de dados. Isso funciona apenas para conexões locais feitas através de um socket Unix.

1. Conectando como o administrador postgres (autenticação peer local)

Esta é a maneira mais simples de se conectar localmente. Você usa sudo para executar comandos como o usuário Linux postgres, o que a autenticação peer automaticamente mapeia para o superusuário do banco de dados postgres.

sudo -i -u postgres

psql

Isso conecta você através de um socket Unix local ao banco de dados padrão chamado postgres.

2. Conectando como um usuário específico (autenticação Local peer vs. TCP/IP password)

Digamos que você criou um papel do PostgreSQL chamado sammy, mas você está logado no seu servidor como um usuário Linux diferente (por exemplo, ubuntu).

• Tentativa 1: Falha (Autenticação de Peer não corresponde): Se você executar psql -U sammy -d my_database, provavelmente falhará. Este comando tenta usar o socket Unix local, então a autenticação peer tenta corresponder seu usuário do sistema operacional (ubuntu) com o usuário do banco de dados (sammy). Como eles não correspondem, a conexão é recusada.

• Tentativa 2: Sucesso (Autenticação por Senha): Para contornar a autenticação peer, você pode forçar uma conexão TCP/IP (rede) usando -h localhost. Por padrão, o arquivo pg_hba.conf do Ubuntu está configurado para usar autenticação md5 ou scram-sha-256 (senha) para conexões TCP/IP locais.

  psql -U sammy -d my_database -h localhost

  O terminal então solicitará a senha que você definiu para a função sammy.

  Dica: Para evitar digitar uma senha a cada conexão, você pode criar um arquivo .pgpass no seu diretório pessoal. Esse arquivo armazena suas senhas de conexão de forma segura. Certifique-se de definir as permissões do arquivo para 0600 (leitura/gravação apenas para o seu usuário).

• Tentativa 3: Sucesso (Correspondência de Autenticação Peer): Se você também tiver um usuário de sistema Linux chamado sammy, você poderia usar sudo para executar psql como esse usuário. A autenticação peer então seria bem-sucedida.

  sudo -i -u sammy
  
  psql -d my_database

Conclusão

Este guia forneceu um passo a passo completo para instalar e gerenciar o PostgreSQL em um servidor Ubuntu. Você começou com a instalação inicial do software, configurou a autenticação de usuários criando funções e criou novos bancos de dados. Em seguida, abordamos os comandos SQL fundamentais para manipulação de dados e exploramos tarefas administrativas essenciais, incluindo configuração de desempenho, procedimentos de backup e restauração, e resolução de problemas comuns.

Agora você tem um servidor de banco de dados funcional e as habilidades fundamentais necessárias para mantê-lo de forma segura e eficiente. A capacidade de gerenciar dados, controlar o acesso dos usuários e garantir a integridade do seu banco de dados prepara você para dar suporte a aplicações complexas.

Se você quiser aprender mais sobre o Postgres e como usá-lo, recomendamos que confira os seguintes guias:

• SQLite vs MySQL vs PostgreSQL: Uma Comparação de Sistemas de Gerenciamento de Banco de Dados Relacional
• Pratique executar consultas com SQL

Obrigado por aprender com a comunidade Linux-Console.net.