PostgreSQL: Iniziare

Come iniziare con PostgreSQL

👋 Benvenuti nella documentazione di Stackhero!

Stackhero offre una soluzione cloud PostgreSQL pronta all'uso che fornisce una serie di vantaggi, tra cui:

  • Connessioni e trasferimenti di dati illimitati.
  • Interfaccia web PgAdmin inclusa.
  • Molti moduli inclusi come PostGIS, TimescaleDB e PgVector.
  • Aggiornamenti senza sforzo con un solo clic.
  • Prestazioni ottimali e sicurezza robusta grazie a una VM privata e dedicata.

Risparmia tempo e semplifica la tua vita: ci vogliono solo 5 minuti per provare la soluzione di cloud hosting PostgreSQL di Stackhero!

Utilizzo della CLI psql di PostgreSQL

È possibile gestire PostgreSQL con la sua CLI ufficiale psql. È disponibile per l'installazione sul tuo computer, permettendoti di gestire il tuo servizio PostgreSQL da remoto. In alternativa, puoi eseguirla tramite Docker. Questo metodo è spesso preferito perché evita di installare psql direttamente sul tuo computer e facilita il passaggio tra versioni.

Per accedere a psql utilizzando Docker, esegui il seguente comando (assicurati di sostituire 18 con il numero di versione principale del tuo servizio PostgreSQL):

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

Quindi connettiti al tuo servizio PostgreSQL utilizzando:

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

Creazione di un utente e di un database in PostgreSQL

Per impostazione predefinita, viene creato un utente admin con diritti amministrativi. È buona pratica creare un utente e un database dedicati per ogni progetto che intendi ospitare.

Utilizzo dell'interfaccia web di PgAdmin

Per utilizzare l'interfaccia web di PgAdmin, apri il tuo dominio PostgreSQL con HTTPS (ad esempio, https://<XXXXXX>.stackhero-network.com). Accedi con admin come nome utente e la password che hai impostato nella configurazione del tuo servizio (visibile nel tuo dashboard Stackhero).

Creazione di un utente

  1. Vai su Servers / PostgreSQL, fai clic con il tasto destro su Login/Group Roles e seleziona Create / Login/Group Role:

    Creare un utente in PostgreSQL con PgAdminCreare un utente in PostgreSQL con PgAdmin

  2. Imposta il nome di accesso:

    Definire il login utenteDefinire il login utente

  3. Imposta una password sicura per evitare attacchi di forza bruta:

    Definire la password utenteDefinire la password utente

  4. Infine, assicurati che solo il privilegio "Can login" sia selezionato:

    Definire i diritti utenteDefinire i diritti utente

Clicca sul pulsante "Save" per creare il tuo utente.

Creazione di un database

  1. Vai su Servers / postgresql, fai clic con il tasto destro su Databases e seleziona Create / Database...:

    Creare un database con PgAdminCreare un database con PgAdmin

È buona pratica utilizzare lo stesso nome per il database e l'utente. Ad esempio, se il tuo progetto si chiama "superWebsite", considera di creare un utente chiamato "superWebsite" e un database chiamato "superWebsite".

  1. Imposta il nome del database e scegli il proprietario (l'utente che hai appena creato):

    Definire il nome e il proprietario del databaseDefinire il nome e il proprietario del database

Il tuo database è ora creato.

Utilizzo della CLI psql

Creazione di un utente

Per creare un utente su PostgreSQL utilizzando la CLI psql, esegui la seguente query SQL:

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

Non dimenticare di sostituire myProject con il nome del tuo progetto e secretPassword con una password sicura. È anche buona pratica utilizzare il nome del tuo progetto sia come nome di accesso che come nome del database. Se il tuo progetto si chiama "superWebsite", considera di creare un utente chiamato "superWebsite" e un database chiamato "superWebsite".

Puoi generare una password sicura con questa riga di comando: openssl rand -base64 24 | tr -d '\n' | cut -c1-32

Creazione di un database

Per creare un database su PostgreSQL utilizzando la CLI psql, esegui la seguente query SQL:

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

È buona pratica utilizzare lo stesso nome per il database e l'utente. Ad esempio, se il tuo progetto si chiama "superWebsite", considera di creare un utente chiamato "superWebsite" e un database chiamato "superWebsite".

Importazione di dati dal tuo computer a PostgreSQL

Uno dei modi più semplici per importare dati dal tuo computer alla tua istanza PostgreSQL è utilizzare la CLI di PostgreSQL. Sul tuo computer, esegui il seguente comando (sostituisci <DB_NAME> con il nome del tuo database e data.pgsql con il nome del tuo file SQL):

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

Esportazione di dati da PostgreSQL al tuo computer

L'esportazione di dati dalla tua istanza PostgreSQL al tuo computer è altrettanto semplice utilizzando la CLI di PostgreSQL.

  1. Per esportare l'intero database (sostituisci <DB_NAME> con il nome del tuo database):

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

  2. Per esportare una singola tabella (sostituisci <DB_NAME> con il nome del tuo database e <TABLE_NAME> con il nome della tua tabella):

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

Utilizzo di PostgreSQL con Node.js

Per connettere PostgreSQL utilizzando Node.js e il pacchetto pg (noto anche come node-postgres), puoi considerare il seguente esempio. Nota l'argomento ssl, che è importante per garantire la crittografia 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('🐞 Si è verificato un errore!');
  console.error(error);
  process.exit(1);
});

Un esempio completo utilizzando PostgreSQL con Node.js e la libreria pg (e async/await) è disponibile su questo repository Git: https://github.com/stackhero-io/postgresqlGettingStarted.

Utilizzo di PostgreSQL con Node.js/TypeORM

Per connettersi a PostgreSQL da Node.js utilizzando TypeORM, imposta il flag ssl su true come mostrato nell'esempio seguente:

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

Utilizzo di PostgreSQL con Django

Se non è già installato, installa il modulo psycopg, che verrà utilizzato per connettersi a PostgreSQL:

pip install psycopg

In questo passaggio iniziale, memorizzerai la password direttamente nel file settings.py. Questo metodo è solo per test perché non è sicuro. Più avanti in questa documentazione, troverai un esempio di buona pratica.

Apri il file settings.py e aggiungi la seguente configurazione:

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

Attenzione: questo esempio non è raccomandato per la produzione ed è destinato solo a scopi di test!

Una volta che la tua connessione funziona, puoi adottare un metodo più sicuro per memorizzare le credenziali. L'esempio seguente utilizza django-environ e memorizza le credenziali in un file .env.

  1. Installa django-environ:

    pip install django-environ

  2. Apri il file settings.py e modificalo come segue:

    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. Apri o crea il file .env nella stessa directory di settings.py e aggiungi:

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

  4. Infine, aggiungi .env al tuo file .gitignore per assicurarti che le tue credenziali non siano memorizzate nel tuo repository Git:

echo ".env" >> .gitignore

Attivazione dell'estensione PostGIS di PostgreSQL

L'estensione PostGIS è inclusa con il nostro servizio PostgreSQL. Devi attivarla su ogni database in cui prevedi di utilizzarla.

Per attivare l'estensione, connettiti al tuo database ed esegui questa query:

CREATE EXTENSION postgis;

Puoi verificare che PostGIS funzioni controllando la sua versione:

SELECT PostGIS_Full_Version();

In alternativa, recupera un elenco di tutte le estensioni PostGIS installate:

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

Se necessario, puoi aggiungere altre estensioni. Tuttavia, è fortemente consigliato non attivare estensioni che non utilizzerai:

-- Abilita PostGIS
CREATE EXTENSION postgis;

-- Abilita supporto raster
CREATE EXTENSION postgis_raster;

-- Abilita Topologia
CREATE EXTENSION postgis_topology;

-- Corrispondenza fuzzy necessaria per Tiger
CREATE EXTENSION fuzzystrmatch;

-- Standardizzatore basato su regole
CREATE EXTENSION address_standardizer;

-- Set di dati di esempio delle regole
CREATE EXTENSION address_standardizer_data_us;

-- Abilita US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

Attenzione: non attivare PostGIS sul database postgres!

Disattivazione dell'estensione PostGIS di PostgreSQL

Per rimuovere PostGIS da un database, connettiti al database pertinente ed esegui la seguente query:

DROP EXTENSION postgis;

Attivazione dell'estensione PgVector

Attivare l'estensione PgVector su Stackhero è semplice. Esegui la seguente query:

CREATE EXTENSION vector;

Attivazione dell'estensione TimescaleDB

Attivare l'estensione TimescaleDB su Stackhero è semplice. Esegui la seguente query:

CREATE EXTENSION timescaledb;

Monitoraggio delle statistiche di pianificazione ed esecuzione SQL (pg_stat_statements)

Per abilitare il supporto per pg_stat_statements, abilitalo prima nella tua configurazione PostgreSQL tramite il dashboard Stackhero. Quindi, abilita l'estensione per il tuo database corrente eseguendo:

CREATE EXTENSION pg_stat_statements;

Per ulteriori informazioni, si prega di fare riferimento alla documentazione ufficiale di PostgreSQL.

Configurazione di PostgreSQL con Skyvia

Utilizzare Skyvia con Stackhero per PostgreSQL è semplice. Assicurati una connessione sicura configurando le seguenti impostazioni:

  1. Imposta Protocol su SSL
  2. Imposta Port sulla tua porta PostgreSQL
  3. Forza la crittografia impostando SSL Mode su Require
  4. Seleziona un SSL TLS Protocol di 1.2 o superiore

Per ulteriori dettagli sulla configurazione di Skyvia, si prega di fare riferimento alla documentazione ufficiale di Skyvia.

Esempio di configurazione di Skyvia con Stackhero per PostgreSQLEsempio di configurazione di Skyvia con Stackhero per PostgreSQL

Other articles for PostgreSQL