PostgreSQL: Introdução

Como começar com PostgreSQL

👋 Bem-vindo à documentação da Stackhero!

A Stackhero oferece uma solução cloud PostgreSQL pronta a usar que proporciona uma série de benefícios, incluindo:

• Conexões e transferências de dados ilimitadas.
• Interface web PgAdmin incluída.
• Muitos módulos incluídos como PostGIS, TimescaleDB e PgVector.
• Atualizações sem esforço com apenas um clique.
• Desempenho ótimo e segurança robusta alimentados por uma VM privada e dedicada.

Poupe tempo e simplifique a sua vida: leva apenas 5 minutos para experimentar a solução de cloud hosting PostgreSQL da Stackhero!

Utilizar a CLI psql do PostgreSQL

Pode gerir o PostgreSQL com a sua CLI oficial psql. Está disponível para instalação no seu computador, permitindo-lhe gerir o seu serviço PostgreSQL remotamente. Alternativamente, pode executá-la via Docker. Este método é frequentemente preferido porque evita a instalação direta do psql no seu computador e facilita a mudança entre versões.

Para aceder ao psql usando Docker, execute o seguinte comando (certifique-se de substituir 18 pelo número da versão principal do seu serviço PostgreSQL):

docker run -v $(pwd):/mnt -it postgres:18-alpine /bin/bash

Depois, conecte-se ao seu serviço PostgreSQL usando:

cd /mnt
psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=admin

Criar um utilizador e uma base de dados no PostgreSQL

Por padrão, um utilizador admin é criado com direitos administrativos. É uma boa prática criar um utilizador e uma base de dados dedicados para cada projeto que planeia hospedar.

Utilizar a interface web PgAdmin

Para usar a interface web PgAdmin, abra o seu domínio PostgreSQL com HTTPS (por exemplo, https://<XXXXXX>.stackhero-network.com). Inicie sessão com admin como nome de utilizador e a palavra-passe que definiu na configuração do seu serviço (visível no seu painel Stackhero).

Criar um utilizador

1. Vá para Servers / PostgreSQL, clique com o botão direito em Login/Group Roles e selecione Create / Login/Group Role:

   Criar um utilizador no PostgreSQL usando PgAdmin

2. Defina o nome de login:

   Definir login do utilizador

3. Defina uma palavra-passe segura para evitar ataques de força bruta:

   Definir palavra-passe do utilizador

4. Finalmente, certifique-se de que apenas o privilégio "Can login" está selecionado:

   Definir direitos do utilizador

Clique no botão "Save" para criar o seu utilizador.

Criar uma base de dados

1. Vá para Servers / postgresql, clique com o botão direito em Databases e selecione Create / Database...:

   Criar uma base de dados usando PgAdmin

É uma boa prática usar o mesmo nome para a base de dados e o utilizador. Por exemplo, se o seu projeto se chama "superWebsite", considere criar um utilizador chamado "superWebsite" e uma base de dados chamada "superWebsite".

1. Defina o nome da base de dados e escolha o proprietário (o utilizador que acabou de criar):

   Definir nome e proprietário da base de dados

A sua base de dados está agora criada.

Utilizar a CLI psql

Criar um utilizador

Para criar um utilizador no PostgreSQL usando a CLI psql, execute a seguinte consulta SQL:

CREATE ROLE "myProject" WITH
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT
  NOREPLICATION
  CONNECTION LIMIT -1
  PASSWORD 'secretPassword';

Não se esqueça de substituir myProject pelo nome do seu projeto e secretPassword por uma palavra-passe segura. Também é uma boa prática usar o nome do seu projeto como nome de login e de base de dados. Se o seu projeto se chama "superWebsite", considere criar um utilizador chamado "superWebsite" e uma base de dados chamada "superWebsite".

Pode gerar uma palavra-passe segura com esta linha de comando: openssl rand -base64 24 | tr -d '\n' | cut -c1-32

Criar uma base de dados

Para criar uma base de dados no PostgreSQL usando a CLI psql, execute a seguinte consulta SQL:

CREATE DATABASE "myProject"
  WITH
  OWNER = "myProject"
  ENCODING = 'UTF8'
  CONNECTION LIMIT = -1
  IS_TEMPLATE = false;

É uma boa prática usar o mesmo nome para a base de dados e o utilizador. Por exemplo, se o seu projeto se chama "superWebsite", considere criar um utilizador chamado "superWebsite" e uma base de dados chamada "superWebsite".

Importar dados do seu computador para o PostgreSQL

Uma das formas mais simples de importar dados do seu computador para a sua instância PostgreSQL é usar a CLI PostgreSQL. No seu computador, execute o seguinte comando (substitua <DB_NAME> pelo nome da sua base de dados e data.pgsql pelo nome do seu ficheiro SQL):

psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  < data.pgsql

Exportar dados do PostgreSQL para o seu computador

Exportar dados da sua instância PostgreSQL para o seu computador é igualmente simples usando a CLI PostgreSQL.

1. Para exportar toda a base de dados (substitua <DB_NAME> pelo nome da sua base de dados):

   pg_dump \
     --host=<XXXXXX>.stackhero-network.com \
     --username=admin \
     --port=<PORT> \
     --dbname=<DB_NAME> \
     > data.pgsql
   

2. Para exportar uma única tabela (substitua <DB_NAME> pelo nome da sua base de dados e <TABLE_NAME> pelo nome da sua tabela):

   pg_dump \
     --host=<XXXXXX>.stackhero-network.com \
     --username=admin \
     --port=<PORT> \
     --dbname=<DB_NAME> \
     --table=<TABLE_NAME> \
     > data.pgsql
   

Utilizar PostgreSQL com Node.js

Para conectar o PostgreSQL usando Node.js e o pacote pg (também conhecido como node-postgres), pode considerar o seguinte exemplo. Note o argumento ssl, que é importante para garantir a encriptação TLS:

const { Client } = require('pg');

(async () => {
  const pg = new Client({
    host: '<XXXXXX>.stackhero-network.com',
    port: <PORT>,
    user: '<USERNAME>',
    password: '<PASSWORD>',
    database: '<DATABASE>',
    ssl: {}
  });

  await pg.connect();

  const result = await pg.query('SELECT 1');

  await pg.end();
})().catch(error => {
  console.error('');
  console.error('🐞 Ocorreu um erro!');
  console.error(error);
  process.exit(1);
});

Um exemplo completo usando PostgreSQL com Node.js e a biblioteca pg (e async/await) está disponível neste repositório Git: https://github.com/stackhero-io/postgresqlGettingStarted.

Utilizar PostgreSQL com Node.js/TypeORM

Para se conectar ao PostgreSQL a partir de Node.js usando TypeORM, defina o parâmetro ssl como true, conforme mostrado no exemplo abaixo:

createConnection({
  type: 'postgres',
  host: '<XXXXXX>.stackhero-network.com',
  port: <PORT>,
  username: '<USERNAME>',
  password: '<PASSWORD>',
  database: '<DATABASE>',
  extra: {
    ssl: true
  }
});

Utilizar PostgreSQL com Django

Se ainda não estiver instalado, instale o módulo psycopg, que será usado para se conectar ao PostgreSQL:

pip install psycopg

Nesta etapa inicial, irá armazenar a palavra-passe diretamente no ficheiro settings.py. Este método é apenas para testes, pois não é seguro. Mais adiante nesta documentação, encontrará um exemplo de boa prática.

Abra o ficheiro settings.py e adicione a seguinte configuração:

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'HOST': '<XXXXXX>.stackhero-network.com',
    'PORT': <PORT>,
    'OPTIONS': {
      'sslmode': 'require',
    },
    'NAME': 'admin',
    'USER': 'admin',
    'PASSWORD': '<ADMIN_PASSWORD>'
  }
}

Atenção: este exemplo não é recomendado para produção e destina-se apenas a fins de teste!

Uma vez que a sua conexão funcione, pode adotar um método mais seguro para armazenar credenciais. O exemplo seguinte usa django-environ e armazena credenciais num ficheiro .env.

1. Instale django-environ:

   pip install django-environ
   

2. Abra o ficheiro settings.py e modifique-o da seguinte forma:

   import environ
   env = environ.Env()
   environ.Env.read_env()
   
   DATABASES = {
     'default': {
       'ENGINE': 'django.db.backends.postgresql',
       'HOST': env('STACKHERO_POSTGRESQL_HOST'),
       'PORT': <PORT>,
       'OPTIONS': {
         'sslmode': 'require',
       },
       'NAME': 'admin',
       'USER': 'admin',
       'PASSWORD': env('STACKHERO_POSTGRESQL_ADMIN_PASSWORD')
     }
   }
   

3. Abra ou crie o ficheiro .env no mesmo diretório que settings.py e adicione:

   STACKHERO_POSTGRESQL_HOST=<XXXXXX>.stackhero-network.com
   STACKHERO_POSTGRESQL_ADMIN_PASSWORD=<ADMIN_PASSWORD>
   

4. Finalmente, adicione .env ao seu ficheiro .gitignore para garantir que as suas credenciais não sejam armazenadas no seu repositório Git:

echo ".env" >> .gitignore

Ativar a extensão PostGIS do PostgreSQL

A extensão PostGIS está incluída no nosso serviço PostgreSQL. Deve ativá-la em cada base de dados onde planeia usá-la.

Para ativar a extensão, conecte-se à sua base de dados e execute esta consulta:

CREATE EXTENSION postgis;

Pode verificar se o PostGIS está a funcionar verificando a sua versão:

SELECT PostGIS_Full_Version();

Alternativamente, recupere uma lista de todas as extensões PostGIS instaladas:

SELECT * FROM pg_extension WHERE extname LIKE 'postgis%';

Se necessário, pode adicionar outras extensões. No entanto, é fortemente recomendado que não ative extensões que não irá usar:

-- Ativar PostGIS
CREATE EXTENSION postgis;

-- Ativar suporte raster
CREATE EXTENSION postgis_raster;

-- Ativar Topologia
CREATE EXTENSION postgis_topology;

-- Correspondência difusa necessária para Tiger
CREATE EXTENSION fuzzystrmatch;

-- Normalizador baseado em regras
CREATE EXTENSION address_standardizer;

-- Conjunto de dados de exemplo de regras
CREATE EXTENSION address_standardizer_data_us;

-- Ativar Geocoder US Tiger
CREATE EXTENSION postgis_tiger_geocoder;

Atenção: não ative o PostGIS na base de dados postgres!

Desativar a extensão PostGIS do PostgreSQL

Para remover o PostGIS de uma base de dados, conecte-se à base de dados relevante e execute a seguinte consulta:

DROP EXTENSION postgis;

Ativar a extensão PgVector

Ativar a extensão PgVector no Stackhero é simples. Execute a seguinte consulta:

CREATE EXTENSION vector;

Ativar a extensão TimescaleDB

Ativar a extensão TimescaleDB no Stackhero é simples. Execute a seguinte consulta:

CREATE EXTENSION timescaledb;

Acompanhar estatísticas de planeamento e execução SQL (pg_stat_statements)

Para ativar o suporte para pg_stat_statements, primeiro ative-o na sua configuração PostgreSQL através do painel Stackhero. Depois, ative a extensão para a sua base de dados atual executando:

CREATE EXTENSION pg_stat_statements;

Para mais informações, consulte a documentação oficial do PostgreSQL.

Configurar PostgreSQL com Skyvia

Usar Skyvia com Stackhero para PostgreSQL é simples. Garanta uma conexão segura configurando as seguintes definições:

1. Defina Protocol para SSL
2. Defina Port para o seu porto PostgreSQL
3. Force a encriptação definindo SSL Mode para Require
4. Selecione um SSL TLS Protocol de 1.2 ou superior

Para mais detalhes sobre a configuração do Skyvia, consulte a documentação oficial do Skyvia.

Exemplo de configuração do Skyvia com Stackhero para PostgreSQL