Como Instalar e Usar o PostgreSQL no Ubuntu

Introdução

Sistemas de gestão de bases de dados são um componente central de muitos sites e aplicações, fornecendo uma forma estruturada de armazenar, organizar e aceder à informação. O PostgreSQL, conhecido geralmente como Postgres, é um sistema de gestão de bases de dados relacionais de código aberto líder. Fornece uma implementação completa da linguagem de consulta SQL e é bem considerado pela sua conformidade com os padrões e funcionalidades avançadas, como transações fiáveis e concorrência sem bloqueios de leitura.

Este guia demonstra o processo completo para configurar o PostgreSQL num servidor Ubuntu, começando pela instalação dos pacotes de software necessários. Em seguida, aprenderá a interagir com o sistema de autenticação do Postgres, que é gerido através de "roles", incluindo a criação de novos utilizadores e bases de dados. Depois disso, abordaremos os fundamentos da gestão de bases de dados: ligar-se à sua nova base de dados, definir uma estrutura de dados criando tabelas e manipular dados utilizando comandos SQL básicos. Também exploraremos tópicos administrativos importantes, incluindo configuração essencial para desempenho, estratégias de backup e restauração, uma comparação do PostgreSQL com outros motores de base de dados e um guia para resolver problemas comuns.

Pré-requisitos

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

Passo 1 — Instalar o PostgreSQL

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

sudo apt update

Depois, instale o pacote postgres juntamente com um pacote -contrib que adiciona algumas utilidades e funcionalidades adicionais:

sudo apt install postgresql postgresql-contrib

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

Passo 2 — Utilização de Funções e Bases de Dados PostgreSQL

Por defeito, o Postgres utiliza um conceito chamado «roles» para gerir a autenticação e autorização. Estas são, de certa forma, semelhantes aos utilizadores e grupos tradicionais ao estilo Unix.

Após a instalação, o Postgres é configurado para usar autenticação peer, o que significa que associa os papéis do Postgres a uma conta correspondente do sistema Unix/Linux. Se um papel existir dentro do Postgres, um nome de utilizador do Unix/Linux com o mesmo nome pode iniciar sessão como esse papel.

O procedimento de instalação criou uma conta de utilizador chamada postgres que está associada à função padrão do Postgres. Existem algumas formas de utilizar esta conta para aceder ao Postgres. Uma delas é mudar para a conta postgres no seu servidor executando o seguinte comando:

sudo -i -u postgres

Depois pode aceder ao prompt do Postgres executando:

psql

Isto irá iniciar sessão no prompt do PostgreSQL e, a partir daqui, pode interagir com o sistema de gestão de bases de dados imediatamente.

Para sair do prompt do PostgreSQL, execute o seguinte:

\q

Isto irá levá-lo de volta ao prompt de comandos do Linux postgres. Para voltar ao seu utilizador normal do sistema, execute o comando exit:

exit

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

sudo -u postgres psql

Isto irá conectá-lo diretamente ao Postgres sem o shell intermediário bash pelo meio.

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

\q

Passo 3 — Criar uma Nova Função

Se estiver autenticado com a conta postgres, pode criar um novo utilizador executando o seguinte comando:

createuser --interactive

Se, em vez disso, 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-lhe algumas opções e, com base nas suas respostas, executará os comandos corretos do Postgres para criar um utilizador de acordo com as suas especificações.

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

Passo 4 — Criar uma Nova Base de Dados

Outra suposição que o sistema de autenticação do Postgres faz por defeito é que, para qualquer função utilizada para iniciar sessão, essa função terá uma base de dados com o mesmo nome à qual pode aceder.

Isto significa que, se o utilizador que criou na última secção se chamar sammy, essa função irá tentar ligar-se a uma base de dados que também é chamada “sammy” por defeito. Pode criar a base de dados apropriada com o comando createdb.

Se estiver ligado com a conta postgres, deverá digitar algo como o seguinte:

createdb sammy

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

sudo -u postgres createdb sammy

Passo 5 — O Método SQL para Criar um Novo Utilizador e Base de Dados (Opcional)

Os comandos de shell createuser e createdb são auxiliares convenientes. No entanto, para maior controlo, pode realizar estas mesmas acções directamente no PostgreSQL usando comandos SQL. Esta abordagem é frequentemente mais clara quando se definem palavras-passe ou se concedem permissões específicas desde o início.

1. Primeiro, ligue-se como o utilizador administrativo postgres:

   sudo -u postgres psql

   Uma vez no prompt do PostgreSQL, pode usar CREATE ROLE e CREATE DATABASE para configurar o seu novo utilizador e base de dados.

2. Criar uma Nova Função (Utilizador): Enquanto o comando createuser na linha de comando é interativo, o comando CREATE ROLE permite definir tudo numa única instrução. Para criar um utilizador chamado sammy que possa iniciar sessão (LOGIN) e tenha uma palavra-passe, execute:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password';

   Se este utilizador também precisar de criar bases de dados, pode conceder essa permissão ao mesmo tempo:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password' CREATEDB;

   Isto fornece um subconjunto de permissões de superutilizador, permitindo especificamente que o utilizador crie novas bases de dados. Embora responder ‘sim’ à questão de superutilizador no assistente interativo também conceda esta permissão, o próprio SUPERUSER é um papel muito mais amplo e perigoso.

3. Criar uma Nova Base de Dados: De seguida, crie a base de dados. É boa prática atribuir a propriedade da nova base de dados ao novo papel que acabou de criar.

   CREATE DATABASE sammydb OWNER sammy;

   Para conceder ao utilizador sammy permissão para se ligar à nova base de dados (um passo seguinte comum), pode executar:

   GRANT ALL PRIVILEGES ON DATABASE sammydb TO sammy;

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

4. Sair da sessão postgres: Pode agora sair do prompt psql para o utilizador postgres.

   \q

Porque definiu uma palavra-passe, este novo utilizador pode autenticar-se utilizando métodos diferentes de ident, o que é necessário para ligações remotas.

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

Para iniciar sessão com autenticação baseada em ident, precisará de um utilizador Linux com o mesmo nome que a sua função e base de dados Postgres.

Se não tiver um utilizador Linux correspondente disponível, pode criar um com o comando adduser. Terá de fazer isto a partir da sua conta não-root com privilégios de sudo (ou seja, não estando ligado como o utilizador postgres):

sudo adduser sammy

Assim que esta nova conta estiver disponível, pode alternar e ligar-se à base de dados executando o seguinte:

sudo -i -u sammy

psql

Ou, podes fazer isto diretamente:

sudo -u sammy psql

Este comando irá iniciar sessão automaticamente, assumindo que todos os componentes tenham sido configurados corretamente.

Se quiser que o seu utilizador se ligue a uma base de dados diferente, pode fazê-lo especificando a base de dados da seguinte forma:

psql -d postgres

Uma vez iniciado sessão, pode verificar a sua informação de ligação atual ao executar:

\conninfo

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

Isto é útil se estiver a ligar-se a bases de dados não predefinidas ou com utilizadores não predefinidos.

Passo 7 — Criar e Eliminar Tabelas

Agora que sabes como te ligar ao sistema de base de dados PostgreSQL, podes aprender algumas tarefas básicas de gestão 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 pode ver, estes comandos dão um nome à tabela e depois definem as colunas, assim como o tipo de coluna e o comprimento máximo dos dados do campo. Opcionalmente, também pode adicionar restrições à tabela para cada coluna.

Para saber mais sobre como criar e eliminar tabelas, consulte o nosso artigo sobre como criar e gerir 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 irá criar uma tabela que inventaria o equipamento de parque infantil. A primeira coluna da tabela irá conter números de identificação do equipamento do tipo serial, que é um inteiro que se incrementa automaticamente. Esta coluna também tem a restrição de PRIMARY KEY, o que significa que os valores nela devem ser únicos e não nulos.

As duas linhas seguintes criam colunas para o equipamento type e color, respetivamente, nenhuma das quais pode estar 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 regista a data em que 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 isto é que alguns tipos de dados não exigem um comprimento definido porque o comprimento ou formato é implícito.

Pode ver a 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)

A sua tabela playground está aqui, mas existe também algo chamado playground_equip_id_seq que é do tipo sequence. Esta é uma representação do tipo serial que atribuiu à sua coluna equip_id. Isto mantém o controlo do próximo número da sequência e é criado automaticamente para colunas deste tipo.

Se quiser ver apenas a tabela sem a sequência, 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 a gestão de dados.

Passo 8 — Adicionar, Consultar e Eliminar Dados numa Tabela

Agora que tem uma tabela, pode inserir alguns dados nela. Como exemplo, adicione um slide e um baloiço chamando a tabela à qual 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');

Deve ter cuidado ao introduzir os dados para evitar alguns problemas comuns. Por exemplo, não coloque aspas nos nomes das colunas, mas os valores das colunas que introduzir precisam de aspas.

Outra coisa a ter em mente é que não deve inserir um valor na coluna equip_id. Isto acontece porque este é gerado automaticamente sempre que adiciona uma nova linha à tabela.

Recupere a informação que 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, pode ver que o seu equip_id foi preenchido com sucesso e que todos os seus outros dados foram organizados corretamente.

Se o escorrega no parque infantil se quebrar e você tiver de o remover, também pode remover a linha da sua tabela digitando:

DELETE FROM playground WHERE type = 'slide';

Consulte novamente a tabela:

SELECT * FROM playground;

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

Repare que a linha slide já não faz parte da tabela.

Passo 9 — Adicionar e Eliminar Colunas de uma Tabela

Depois de criar uma tabela, 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 voltar a consultar a informação da sua tabela, verá que a nova coluna foi adicionada, mas nenhum dado foi introduzido:

SELECT * FROM playground;

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

Se descobrir que a sua equipa de trabalho utiliza uma ferramenta separada para acompanhar o histórico de manutenção, pode eliminar a coluna digitando:

ALTER TABLE playground DROP last_maint;

Isto elimina a coluna last_maint e quaisquer valores presentes nela, mas mantém intactos todos os outros dados.

Passo 10 — Atualizar Dados numa Tabela

Até agora, aprendeu como adicionar registos a uma tabela e como os eliminar, mas este tutorial ainda não abordou como modificar entradas existentes.

Pode atualizar os valores de uma entrada existente consultando o registo que deseja e definindo a coluna para o valor que pretende utilizar. Pode consultar o registo swing (isto irá corresponder a tudo o que seja swing na sua tabela) e mudar a sua cor para vermelho. Isto pode ser útil se tiver dado uma pintura ao conjunto de baloiços:

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

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 pode ver, a lâmina está agora registada como sendo vermelha.

Configuração e Dicas de Desempenho do PostgreSQL

Configurar o PostgreSQL corretamente é essencial para uma base de dados estável e rápida. As definições por defeito são concebidas para serem seguras para qualquer hardware, o que significa que quase nunca são adequadas para uma carga de trabalho de produção. A otimização do desempenho envolve ajustar a alocação de recursos—principalmente memória, I/O e gestão de ligaçõ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 chave e estratégias gerais para melhorar o desempenho da base de dados.

Ficheiros de Configuração Principais

Antes de fazer ajustes, deve saber onde estão os ficheiros de configuração. No Ubuntu, eles encontram-se em /etc/postgresql/[version]/main/:

• postgresql.conf: O ficheiro principal de configuração do servidor. É aqui que se ajustam todos os parâmetros relacionados com o desempenho.
• pg_hba.conf: Este ficheiro controla a autenticação dos clientes (quais os utilizadores que podem ligar-se a partir de quais endereços). Não afeta diretamente o desempenho das consultas, mas é fundamental para a segurança.

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

sudo systemctl reload postgresql

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

sudo systemctl restart postgresql

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 ao pg_hba.conf apenas requerem um reload.

sudo systemctl reload postgresql

Configuração de Memória

A memória é o fator mais significativo no desempenho de bases de dados. O PostgreSQL utiliza memória partilhada para armazenar dados em cache e memória separada por ligação para operações como a ordenação.

buffers_partilhados

Esta é a definição de memória mais importante. Define a quantidade de memória que o PostgreSQL dedica à sua cache de dados. Uma cache maior significa que mais do seu conjunto de dados de trabalho pode ser lido a partir da memória rápida em vez do disco lento.

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

• Exemplo: Num 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 de base de dados (como uma ordenação, junção por hash ou bitmap) pode utilizar antes de recorrer a ficheiros temporários no disco. Consultas complexas podem ter várias operações, cada uma a utilizar esta quantidade de memória.

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

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

manutenção_work_mem

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 isto mais alto do que work_mem (por exemplo, 128MB ou 256MB). Como estas operações não são executadas com a mesma frequência que as consultas dos utilizadores, pode dar-se ao luxo de ser mais generoso para acelerar a manutenção.

Configuração de Checkpoint e WAL

Os pontos de verificação são processos onde o PostgreSQL escreve todos os dados "sujos" (alterações mantidas na memória) para os ficheiros de dados permanentes no disco. Esta é uma operação muito intensiva em I/O. O Log de Escrita Antecipada (WAL) regista todas as transações. Ajustar estes parâmetros ajuda a equilibrar a carga de I/O e o tempo de recuperação.

16MB

Gestão de Ligações

Cada ligação ao PostgreSQL utiliza memória. Um número elevado de ligações pode esgotar os recursos do servidor.

• max_connections: O número máximo de conexões simultâneas. O valor predefinido é 100. Embora seja tentador aumentá-lo, cada conexão consome memória.
• Utilize 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, utilize um pool de conexões externo como o PgBouncer ou o Pgpool-II. Um pool mantém um pequeno conjunto de conexões persistentes à base de dados e serve muitas conexões concorrentes da aplicação, o que é muito mais eficiente.

Planeamento e Análise de Consultas

Nenhuma quantidade de otimização do servidor pode corrigir uma consulta ineficiente.

• EXPLAIN ANALYZE: Esta é a 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 “Digitalização Sequencial” (Seq Scan) em tabelas grandes e indexadas. Isto normalmente significa que está em falta um índice ou que a consulta não é suficientemente seletiva.

• Criar Índices: Se filtra ou junta frequentemente em colunas específicas (por exemplo, user_id, email, created_at), elas são bons candidatos a um índice.

  CREATE INDEX idx_users_on_email ON users (email);

• Execute VACUUM ANALYZE: O PostgreSQL utiliza um planeador de consultas que depende de estatísticas sobre os dados das suas tabelas. Estas estatísticas são actualizadas pelo ANALYZE. Embora o autovacuum trate disto automaticamente, é boa prática executar manualmente um VACUUM ANALYZE; após carregamentos de dados grandes ou se notar que os planos de consulta estão fracos.

Backup e Restauração de Bases de Dados PostgreSQL

Proteger os seus dados é uma responsabilidade principal da administração de bases de dados. Cópias de segurança regulares e verificadas são a defesa mais eficaz contra a 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 (Dumps SQL): Estes backups consistem em comandos SQL que, quando executados, recriam os objetos da base de dados (como tabelas e índices) e os dados. São criados com as ferramentas pg_dump e pg_dumpall. Este método é flexível e portátil entre diferentes versões do PostgreSQL.
2. Cópias de Segurança Físicas (nível de sistema de ficheiros): Este método envolve copiar os ficheiros de dados físicos do diretório de dados do PostgreSQL. Isto é normalmente usado para Recuperação Pontual no Tempo (PITR), uma configuração avançada discutida mais adiante nesta secção.

Criar Backups Lógicos com pg_dump

A utilidade pg_dump faz o backup de uma única base de dados. Não faz backup de objetos globais como funções ou tablespaces. É altamente flexível e pode ser executada numa base de dados ativa sem bloquear leitores ou escritores.

Opções Comuns do pg_dump

Pode controlar a saída de pg_dump com várias opções de linha de comando:

• -F c (Formato: personalizado): Este é o formato recomendado para a maioria dos backups. Produz um arquivo binário comprimido que não é legível por humanos, mas permite restauros em paralelo e a reorganização ou exclusão de objetos durante a restauração.
• -F p (Formato: simples): Este é o padrão. Gera um grande ficheiro .sql. A sua principal desvantagem é que deve ser restaurado de uma só vez e não suporta restauros em paralelo.
• -s (Apenas esquema): Faz backup apenas da estrutura da base de dados (tabelas, vistas, índices) sem quaisquer dados.
• -a (Só 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. Isto é útil para ignorar tabelas grandes, temporárias ou não essenciais.
• -f FILENAME: Especifica o ficheiro de saída.
• -U USER: Especifica o utilizador do PostgreSQL com o qual se pretende ligar.

Este é o comando recomendado para a maioria das tarefas de backup. Conecta-se à base de dados mydatabase como o utilizador postgres e cria um ficheiro comprimido chamado mydatabase.dump.

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

Restaurar Cópias de Segurança Lógicas

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

A restaurar a partir de um Arquivo Personalizado (.dump)

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

Em vez de criar manualmente a base de dados primeiro, a melhor prática é usar o parâmetro --create. Este parâmetro indica ao pg_restore para emitir o comando CREATE DATABASE a partir do arquivo. Para isso, deve-se ligar a uma base de dados diferente já existente, como a base de dados padrão postgres.

O comando seguinte liga-se à base de dados postgres, elimina e recria mydatabase (por causa de --clean e --create), e utiliza 4 tarefas em paralelo para a restaurar.

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

• -d postgres: Conecta-se à base de dados postgres para emitir o comando de criação.
• --create: Indica ao pg_restore que crie a base de dados de destino (por exemplo, mydatabase) antes de restaurar para ela.
• --clean: Quando usado com --create, este parâmetro irá eliminar e recriar toda a base de dados de destino. Quando usado sozinho (restaurando numa base de dados existente), elimina os objetos existentes da base de dados antes de os recriar.
• -j 4: Utiliza 4 tarefas em paralelo para a restauração.

Conectamo-nos à base de dados postgres porque o pg_restore precisa de uma ligação a uma base de dados existente no servidor para emitir o comando CREATE DATABASE para o nosso novo destino.

Restaurar a partir de um Ficheiro de Texto Simples (.sql)

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

1. Criar a base de dados:

   createdb -U postgres mydatabase_new

2. Enviar o ficheiro .sql para o psql:

   psql -U postgres -d mydatabase_new < mydatabase.sql

Fazer Backup de Todas as Bases de Dados (pg_dumpall)

Para fazer backup de todas as bases de dados no servidor, assim como de todos os objetos globais (funções, utilizadores e tablespaces), utilize a ferramenta pg_dumpall. Esta ferramenta apenas gera um ficheiro .sql em formato de texto simples.

# Run as the postgres superuser

pg_dumpall -U postgres > all_databases.sql

Para restaurar a partir de um ficheiro pg_dumpall, deve enviá-lo para psql. Deve ligar-se como superutilizador (como postgres) a uma base de dados por defeito (como a própria postgres). O ficheiro 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

Fazer Backup Apenas de Funções e Variáveis Globais

Um padrão comum e útil é fazer backup apenas dos objetos globais. Isto é feito com a opção --globals-only e é uma forma eficaz de replicar permissões de utilizador entre servidores.

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

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

A tabela seguinte resume a estratégia de backup e restauro:

.sql or .dump

.sql

Melhores Práticas de Automação e Backup

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

• Automatize os Seus Backups: Utilize uma ferramenta padrão do Linux como o cron para agendar os seus comandos pg_dump ou pg_dumpall para serem executados todas as noites.
• Gerir palavras-passe de forma segura: Para executar backups num script não interativo, não codifique as palavras-passe diretamente. Use o ficheiro .pgpass. Crie um ficheiro em ~/.pgpass no diretório pessoal do utilizador (por exemplo, /var/lib/postgresql/.pgpass). O ficheiro deve ter permissões rigorosas 0600, caso contrário será ignorado pelo PostgreSQL. O formato é hostname:port:database:user:password. Pode usar * como coringa para qualquer campo, por exemplo, localhost:5432:*:postgres:sua_palavra_passe.
• Teste as suas restauros: Um backup só é útil se puder ser restaurado. Pratique regularmente a restauração dos seus backups num servidor separado, que não seja de produção, para verificar a sua integridade.
• Verifique os Seus Backups: Depois de criar um backup, adicione um passo no script para verificar se o tamanho do ficheiro não é zero. Para formatos personalizados, pode executar pg_restore -l mydatabase.dump &> /dev/null (que lista o conteúdo para /dev/null) para confirmar que o arquivo não está corrompido verificando o código de saída do comando.
• Armazenar Cópias de Segurança Remotamente: Não guarde as suas cópias de segurança no mesmo servidor que a sua base de dados. Uma falha completa do servidor faria perder ambos. Copie as cópias de segurança para um local remoto, como os Spaces do Linux-Console.net ou outro servidor, como passo final do seu script.

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

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

• PostgreSQL: Esta base de dados objeto-relacional é conhecida pelo seu elevado nível de conformidade com os standards, integridade dos dados e conjunto robusto de funcionalidades. É totalmente compatível com ACID e suporta bem tipos de dados avançados e consultas complexas, tornando-a uma escolha comum para uma base de dados principal «sistema de registo».
• MySQL: Como parte do stack LAMP original, o MySQL é uma das bases de dados open-source mais populares. É conhecido pela sua facilidade de utilização, pelo desempenho historicamente forte em leitura e pela comunidade ativa. É também totalmente compatível com ACID (com o motor InnoDB por defeito) e é preferido para aplicações web simples.
• MariaDB: Um fork do MySQL orientado pela comunidade. Embora mantenha uma elevada compatibilidade (especialmente com versões mais antigas), divergiu e introduz as suas próprias funcionalidades, pelo que pode não ser um substituto completo para aplicações construídas com as funcionalidades mais recentes do MySQL 8.0+.
• SQLite: Esta não é uma base de dados cliente-servidor. É uma biblioteca em C que incorpora um motor de base de dados completo numa aplicação, armazenando toda a base de dados num único ficheiro. Lida bem com múltiplos leitores simultâneos, mas não é ideal para aplicações com escritas concorrentes frequentes ou cenários multiutilizador em rede.
• MongoDB: Uma base de dados NoSQL líder, o MongoDB armazena dados em documentos flexíveis, semelhantes a JSON. Isto permite um esquema flexível e é concebido para alta disponibilidade e escalabilidade horizontal. Embora seja conhecido pela flexibilidade, o MongoDB também suporta transações ACID com múltiplos documentos desde a versão 4.0, tornando-o uma escolha viável para aplicações que requerem consistência forte.
• Redis: Este é um armazenamento de estruturas de dados em memória, o que significa que mantém todos os dados na RAM para uma velocidade excecional. Não é um substituto de uso geral para uma base de dados em disco, mas é quase sempre usado em conjunto com uma (como o PostgreSQL) para agir como cache de alta velocidade ou armazenamento de sessões.

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

Comparação de Motores de Base de Dados

Problemas Comuns do PostgreSQL e Solução de Problemas no Ubuntu

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

Antes de resolver problemas, a sua ferramenta mais importante é o ficheiro de registo do PostgreSQL. Em instalações padrão do Ubuntu, pode encontrá-lo em /var/log/postgresql/postgresql-[versão]-main.log. Verifique sempre primeiro este registo, pois fornece mensagens de erro específicas que irão orientar o seu diagnóstico.

Problemas de Instalação

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

Conflitos de Pacotes ou Incompatibilidades de Versão

Este problema ocorre quando o seu sistema tenta instalar pacotes 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 as versões mais recentes. Misturá-los pode levar a dependências quebradas.

Resolução de problemas:

• Verifique as suas fontes: Utilize apt-cache policy postgresql para ver quais versões estão disponíveis e de que repositório.
• Escolha uma fonte: É altamente recomendável utilizar o repositório PGDG para obter a versão mais atualizada. Se adicionar o repositório PGDG, pode ser necessário usar o apt pinning para dar prioridade aos seus pacotes em relação aos pacotes padrão do Ubuntu.
• Corrigir instalações danificadas: Se já se encontra num estado conflituoso, sudo apt-get -f install pode resolvê-lo. Caso contrário, poderá 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á a ouvir na porta padrão do PostgreSQL (5432). Isto é comum se uma instância antiga do PostgreSQL, não desinstalada corretamente, não tiver sido encerrada ou se tiver múltiplos clusters do PostgreSQL a correr (uma funcionalidade do sistema pg_ctlcluster do Ubuntu).

Resolução de problemas:

• Identificar o processo: Use netstat para descobrir o que está a usar 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 os seus estados.

  pg_lsclusters

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

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

  sudo pg_ctlcluster 13 main stop

• Alterar a porta: Se outro serviço estiver a usar corretamente a porta 5432, deve configurar o seu novo cluster PostgreSQL para usar uma porta diferente editando o seu ficheiro 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 definições de localidade do seu sistema Ubuntu estão em falta ou mal configuradas. O PostgreSQL herda estas definições do sistema operativo 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 em falta: Se o local desejado (por exemplo, en_US.UTF-8) não estiver listado, terá de o gerar.

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

• Reinicializar o cluster: Após configurar as localidades do sistema, poderá ser necessário eliminar e recriar o cluster PostgreSQL. O comando pg_createcluster é o método padrão do Ubuntu para isso.

  Aviso: Isto 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 utilizadores enfrentam após uma instalação bem-sucedida.

Falha na autenticação entre pares

Este é o erro mais comum. Significa que está a tentar ligar localmente e o nome de utilizador do Ubuntu com o qual está atualmente ligado não corresponde ao nome da função da base de dados PostgreSQL que está a tentar usar.

Por padrão, o PostgreSQL no Ubuntu usa a autenticação peer para ligações locais. Este método pergunta ao sistema operativo: «Qual é o nome do utilizador do processo que se está a ligar a mim?» e permite a ligação se esse nome de utilizador coincidir com a função de base de dados solicitada.

Por exemplo, se estiver ligado como myuser e executar psql -U postgres, irá falhar porque o sistema operativo reporta o seu nome de utilizador como myuser, não postgres.

Solução:

A forma correcta de se ligar como superutilizador postgres é primeiro assumir a identidade do utilizador do sistema postgres utilizando sudo.

sudo -u postgres psql

Se desejar alterar este comportamento, deve definir uma palavra-passe para o papel postgres e, em seguida, editar o ficheiro pg_hba.conf para utilizar md5 ou scram-sha-256 em vez de peer.

Mau funcionamento do pg_hba.conf

Erros no ficheiro pg_hba.conf (Autenticação Baseada em Hosts) são a principal causa de falhas de ligação. Este ficheiro, localizado em /etc/postgresql/[versão]/main/pg_hba.conf, é um conjunto de regras que controla quem pode ligar-se de onde a qual base de dados usando qual método. Um único erro de sintaxe ou uma ordem de regras incorreta pode bloqueá-lo.

Resolução de problemas:

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

• Recarregar após alterações: Após editar pg_hba.conf, 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

Estes erros aparecem frequentemente como «Permissão negada» nos logs ou ao tentar conectar.

Não é possível aceder ao ficheiro de socket

Erros como “não foi possível ligar ao servidor: Ficheiro ou diretório inexistente” ou “Permissão negada” ao referenciar /var/run/postgresql/.s.PGSQL.5432 normalmente significam uma de duas coisas:

1. O serviço não está a ser executado: O ficheiro de socket é removido quando o serviço é interrompido. Verifique o estado do serviço com sudo systemctl status postgresql.
2. Permissões de utilizador: O diretório do socket é propriedade de postgres e acessível pelo grupo postgres. Se o seu utilizador do sistema não pertencer ao grupo postgres, não poderá utilizar o socket.

Solução:

Adicione o seu utilizador ao grupo postgres. Deve sair e voltar a entrar na sessão para que esta alteração 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 registos mencionarem “Permissão negada” relacionado com o diretório de dados (por exemplo, /var/lib/postgresql/[version]/main), a propriedade do diretório ou as permissões de ficheiro estão incorrectas.

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

Solução:

Redefinir 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

Estas questões estão relacionadas com iniciar, parar e gerir o serviço postgresql.

O serviço não consegue iniciar após alterações de configuração

Se o serviço não iniciar imediatamente após editar o postgresql.conf ou o pg_hba.conf, é provável que tenha introduzido um erro de sintaxe.

Resolução de problemas:

1. Verifique os logs: Esta é a forma 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 mostram a linha exata da configuração inválida.

2. Erros Comuns em postgresql.conf:

   • Valores de cadeias de texto 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 definições (por exemplo, shared_buffers=10GB quando só tem 8GB de RAM).

Confusão Sobre Múltiplos Clusters

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

Esta é uma funcionalidade poderosa, mas pode ser confusa. Pode editar o ficheiro de configuração para a versão 18, mas estar a ligar-se à versão 13, levando-o a acreditar que as suas "alterações não estão a ser aplicadas."

Resolução de problemas:

• Listar todos os clusters: Comece sempre por aqui para ver o que está a correr, em que porta e a localização dos seus ficheiros 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         ...

• Gerir clusters específicos: Ao iniciar, parar ou reiniciar, indique sempre o cluster que pretende alterar:

  sudo pg_ctlcluster 18 main reload

Limites de Recursos

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

Erros de Memória Partilhada

Erros na inicialização como “não foi possível criar o segmento de memória partilhada: 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 operativo para memória partilhada (SHMMAX).

O PostgreSQL utiliza um grande bloco de memória partilhada para cache e comunicação entre processos. Se solicitar mais do que o sistema operativo permite, o sistema operativo nega o pedido e o PostgreSQL não consegue iniciar. Isto é comum em ambientes com recursos de memória limitados ou em contêineres.

Resolução de problemas:

1. Verificar limites do SO:

   cat /proc/sys/kernel/shmmax

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

3. Solução 1 (Recomendada): Reduza o valor de shared_buffers no postgresql.conf para ficar seguramente 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. Isto é geralmente necessário apenas para otimização de alto desempenho.

Perguntas Frequentes

1. O que é o PostgreSQL e por que devo usá-lo no Ubuntu?

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

Deveria considerar usá-lo nas suas aplicações porque suporta totalmente a conformidade ACID (Atomicidade, Consistência, Isolamento, Durabilidade), lida bem com cargas de trabalho de elevada concorrência e suporta uma ampla gama de tipos de dados e extensões avançadas, 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 de pacotes padrão do Ubuntu, o que torna a instalação e a recepção de atualizações de segurança em tempo útil um processo simples gerido pelo gestor de pacotes apt do sistema.

2. Como instalo o PostgreSQL no Ubuntu?

Pode instalar o PostgreSQL diretamente a partir dos repositórios predefinidos do Ubuntu utilizando o comando apt.

Primeiro, actualize o índice de pacotes local do seu servidor para garantir que tem as listas de pacotes mais recentes:

sudo apt update

De 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 inicio e ativo o serviço PostgreSQL?

Após uma instalação bem-sucedida, o serviço PostgreSQL deve iniciar automaticamente. Pode verificar o seu estado usando systemctl.

sudo systemctl status postgresql

Pode ver uma saída como esta:

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 a funcionar, pode iniciá-lo manualmente:

sudo systemctl start postgresql

Para garantir que a base de dados arranque automaticamente sempre que o servidor é iniciado, deve ativar o serviço:

sudo systemctl enable postgresql

4. Como posso criar uma nova base de dados no PostgreSQL?

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

Por defeito, a conta de utilizador postgres no Linux está configurada para se conectar como superutilizador da base de dados postgres. Pode mudar para este utilizador e executar o comando:

sudo -i -u postgres

createdb my_database

Alternativamente, pode entrar no prompt interativo do PostgreSQL (psql) e usar o comando SQL CREATE DATABASE.

Primeiro, aceda ao prompt como utilizador postgres:

sudo -i -u postgres

psql

Em seguida, a partir do prompt do psql (que parece postgres=# porque postgres é um superutilizador), execute o comando SQL. Lembre-se de terminar os comandos SQL com um ponto e vírgula.

CREATE DATABASE my_database;

Pode sair do prompt psql digitando \q e depois digitar exit para regressar ao seu utilizador normal.

5. Como posso criar e gerir utilizadores e funções no PostgreSQL?

No PostgreSQL, a gestão de utilizadores é feita através de roles. Uma role pode ser um utilizador de base de dados ou um grupo de utilizadores, mas uma role com o privilégio LOGIN é considerada um utilizador.

1. Criar um Utilizador

Pode usar a utilidade de linha de comandos createuser. É melhor executar isto como utilizador postgres.

sudo -i -u postgres

Um método recomendado é usar a flag --interactive para ser solicitado a definir as configurações e a flag --pwprompt para definir uma palavra-passe imediatamente.

createuser --interactive --pwprompt

Isto irá pedir o nome da função, uma password, e se a função deve ser de superutilizador 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

Também pode criar um utilizador com um único comando SQL a partir do prompt psql. Isto cria um utilizador mínimo que só pode iniciar sessão.

CREATE ROLE sammy WITH LOGIN PASSWORD 'secure_password';

2. Conceder Privilégios

Criar um utilizador não lhe dá automaticamente acesso a uma base de dados. Deve conceder privilégios explicitamente. Este processo tem várias etapas:

1. Conceder Acesso ao Connect: Primeiro, conceda ao novo papel permissão para se ligar à sua base de dados.

   GRANT CONNECT ON DATABASE my_database TO sammy;

   Erro Comum: Usar GRANT ALL PRIVILEGES ON DATABASE my_database TO sammy; é enganador. Este comando apenas concede permissões de CONNECT, CREATE e TEMP na própria base de dados, não nas tabelas dentro dela.

2. Utilização do Esquema de Permissões: De seguida, deve conceder permissão para aceder ao esquema dentro da base de dados. Sem isto, o utilizador pode ligar-se, mas não consegue ver quaisquer tabelas.

   GRANT USAGE ON SCHEMA public TO sammy;

3. Conceder Permissões à Tabela: Agora, pode conceder as permissões específicas que o utilizador precisa nos objetos dentro do esquema. Ligue-se à base de dados como superutilizador para o fazer:

   \c my_database

   Está agora ligado a my_database.

4. Conceder permissões em objetos existentes: Para dar à função sammy acesso de leitura/escrita a 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 apenas quiser conceder acesso de leitura, deve usar GRANT SELECT em vez de GRANT ALL.

5. (Opcional) Conceder Permissões para Objetos Futuros: Os comandos acima apenas afetam tabelas existentes. Para conceder automaticamente permissões para quaisquer tabelas novas criadas no futuro (por exemplo, por um administrador), 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 a aprender é o terminal interativo psql. Uma vez que esteja ligado (por exemplo, executando sudo -i -u postgres e depois psql), pode usar meta-comandos (que começam com uma barra invertida \) para navegar e inspecionar a sua base 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 posso ligar-me a uma base de dados PostgreSQL a partir do terminal?

A forma como se liga depende do método de autenticação configurado no ficheiro pg_hba.conf. (Este ficheiro está normalmente localizado em /etc/postgresql/<version>/main/pg_hba.conf). Por defeito, o PostgreSQL no Ubuntu utiliza peer authentication para ligações locais.

A autenticação peer funciona obtendo o nome de utilizador do sistema operativo do cliente a partir do kernel e usando-o como o nome de utilizador permitido na base de dados. Isto funciona apenas para ligações locais feitas através de um socket Unix.

1. Ligando-se como o administrador postgres (autenticação local peer)

Esta é a forma mais simples de se conectar localmente. Usa-se sudo para executar comandos como o utilizador Linux postgres, que a autenticação peer mapeia automaticamente para o superutilizador da base de dados postgres.

sudo -i -u postgres

psql

Isto liga-o através de um socket Unix local à base de dados predefinida chamada postgres.

2. Ligar-se como um utilizador específico (autenticação Local peer vs. TCP/IP password)

Digamos que você criou um papel do PostgreSQL chamado sammy, mas iniciou sessão no seu servidor como um utilizador Linux diferente (por exemplo, ubuntu).

• Tentativa 1: Falha (Autenticação do Peer não coincide): Se executar psql -U sammy -d my_database, provavelmente irá falhar. Este comando tenta usar o socket Unix local, pelo que a autenticação peer tenta corresponder o seu utilizador do SO (ubuntu) com o utilizador da base de dados (sammy). Como não coincidem, a ligação é recusada.

• Tentativa 2: Sucesso (Autenticação por Palavra-passe): Para contornar a autenticação peer, pode forçar uma ligação TCP/IP (rede) usando -h localhost. Por predefinição, o ficheiro pg_hba.conf do Ubuntu está configurado para usar autenticação md5 ou scram-sha-256 (por palavra-passe) para ligações TCP/IP locais.

  psql -U sammy -d my_database -h localhost

  O terminal irá então pedir-lhe a palavra-passe que definiu para a função sammy.

  Dica: Para evitar inserir a palavra-passe em cada ligação, pode criar um ficheiro .pgpass no seu diretório pessoal. Este ficheiro armazena de forma segura as suas palavras-passe de ligação. Certifique-se de definir as permissões do ficheiro para 0600 (leitura/escrita apenas para o seu utilizador).

• Tentativa 3: Sucesso (Correspondência de Autenticação Peer): Se você também tiver um utilizador do sistema Linux chamado sammy, poderá usar sudo para executar psql como esse utilizador. A autenticação peer então terá sucesso.

  sudo -i -u sammy
  
  psql -d my_database

Conclusão

Este guia forneceu um passo a passo completo para instalar e gerir o PostgreSQL num servidor Ubuntu. Começou com a instalação inicial do software, configurou a autenticação de utilizadores ao criar funções e criou novas bases de dados. Em seguida, abordámos os comandos SQL fundamentais para manipulação de dados e explorámos tarefas administrativas essenciais, incluindo configuração de desempenho, procedimentos de cópia de segurança e restauração, e resolução de problemas comuns.

Agora tem um servidor de base de dados funcional e as competências essenciais necessárias para o manter de forma segura e eficiente. A capacidade de gerir dados, administrar o acesso dos utilizadores e garantir a integridade da sua base de dados prepara-o para dar suporte a aplicações complexas.

Se quiser saber mais sobre o Postgres e como utilizá-lo, incentivamo-lo a consultar os seguintes guias:

• SQLite vs MySQL vs PostgreSQL: Uma Comparação de Sistemas de Gestão de Bases de Dados Relacionais
• Praticar a execução de consultas com SQL

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