Installa e proteggi PostgreSQL
Installa PostgreSQL 18 dal repository ufficiale su Debian o Ubuntu: ruoli e permessi, accesso remoto in TLS, backup automatici e test di ripristino.
In questa pagina
I tuoi valori
Inseriscili una volta: i comandi della guida si aggiornano.
Questa guida ti spiega come installare PostgreSQL sul tuo VPS o server dedicato dal repository ufficiale del progetto, creare ruoli con permessi limitati, aprire se necessario un accesso remoto cifrato e configurare backup che saprai ripristinare.
Alla fine, PostgreSQL ascolta solo in locale, ogni applicazione dispone del proprio ruolo e un backup giornaliero viene prodotto e poi verificato con un test di ripristino.
Ordinare un server
Per ospitare il tuo database, HostMyServers propone diverse offerte:
Prerequisiti
- Un server con Debian 12 o 13, oppure Ubuntu 22.04 o 24.04
- Un accesso SSH con un utente che dispone di
sudo - Un sistema aggiornato (
sudo apt update && sudo apt upgrade) - Solo per l'accesso remoto diretto: un nome di dominio che punta al server
Porte da aprire
| Utilizzo | Porta | Da aprire? |
|---|---|---|
| Applicazioni sullo stesso server | 5432/tcp in locale | No |
| Accesso remoto tramite tunnel SSH (consigliato) | 22/tcp, o la tua porta SSH | Già aperta per l'amministrazione |
| Accesso remoto diretto in TLS | 5432/tcp | Sì, solo dall'indirizzo IP dei tuoi client |
Installare PostgreSQL dal repository ufficiale
Debian e Ubuntu includono PostgreSQL, ma la versione resta bloccata per tutta la vita della distribuzione. Il repository PGDG (apt.postgresql.org) propone tutte le versioni mantenute.
Al momento della stesura, l'ultima versione maggiore è PostgreSQL 18 (18.7). Consulta la politica delle versioni per conoscere la versione attuale.
-
Installa gli strumenti necessari e recupera la chiave del repository:
Terminalesudo apt install -y curl ca-certificates gpg sudo install -d /usr/share/postgresql-common/pgdg sudo curl -fsSL -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc -
Verifica l'impronta della chiave:
Terminalegpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.ascDeve corrispondere a
B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8. In caso contrario, fermati qui. -
Dichiara il repository:
Terminale. /etc/os-release sudo tee /etc/apt/sources.list.d/pgdg.sources > /dev/null <<EOF Types: deb URIs: https://apt.postgresql.org/pub/repos/apt Suites: ${VERSION_CODENAME}-pgdg Architectures: $(dpkg --print-architecture) Components: main Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc EOF -
Installa il server:
Terminalesudo apt update sudo apt install -y postgresql-18Il pacchetto crea e avvia automaticamente un'istanza chiamata
18/main.
Verificare l'installazione
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo -u postgres psql -c "SELECT version();"
sudo ss -tlnp | grep 5432pg_lsclusters deve mostrare l'istanza 18 main con lo stato online. ss deve mostrare un ascolto su 127.0.0.1:5432 e [::1]:5432, e non su 0.0.0.0.
| Elemento | Percorso |
|---|---|
| Configurazione | /etc/postgresql/18/main/ |
| Impostazioni aggiuntive | /etc/postgresql/18/main/conf.d/ |
| Regole di accesso | /etc/postgresql/18/main/pg_hba.conf |
| Dati | /var/lib/postgresql/18/main/ |
| Log | /var/log/postgresql/postgresql-18-main.log |
Capire l'autenticazione
Il file pg_hba.conf decide chi può connettersi, a quale database e con quale metodo. PostgreSQL legge le sue righe dall'alto verso il basso e applica la prima che corrisponde. Se quel metodo fallisce, le righe successive non vengono provate.
Mostra le regole attive:
sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"Su un'installazione nuova, devi trovare due metodi:
peerper le connessioni locali tramite socket: il nome dell'utente di sistema deve corrispondere al nome del ruolo. Per questosudo -u postgres psqlfunziona senza password.scram-sha-256per le connessioni TCP da127.0.0.1e::1: viene richiesta una password.
Creare ruoli e un database
Non usare il superutente postgres nelle tue applicazioni. L'esempio seguente crea un database appdb, un ruolo proprietario app_owner e un ruolo di sola lettura app_reader.
-
Crea i ruoli. Ogni comando chiede una password, che in questo modo non resta nella cronologia della shell:
Terminalesudo -u postgres createuser --pwprompt app_owner sudo -u postgres createuser --pwprompt app_reader -
Crea il database:
Terminalesudo -u postgres createdb --owner=app_owner appdb -
Limita l'accesso a questo database:
Terminalesudo -u postgres psql -d appdbSQLREVOKE ALL ON DATABASE appdb FROM PUBLIC; GRANT CONNECT ON DATABASE appdb TO app_reader; GRANT USAGE ON SCHEMA public TO app_reader; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader; ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO app_reader; \qL'ultima istruzione dà a
app_readeranche il permesso di lettura sulle future tabelle create daapp_owner. -
Verifica la connessione con password:
Terminalepsql -h 127.0.0.1 -U app_owner -d appdb -c "CREATE TABLE restore_check (id int PRIMARY KEY, note text);" psql -h 127.0.0.1 -U app_owner -d appdb -c "INSERT INTO restore_check VALUES (1, 'sauvegarde OK');" psql -h 127.0.0.1 -U app_reader -d appdb -c "SELECT * FROM restore_check;"Una scrittura con
app_readerdeve fallire conpermission denied for table restore_check. La tabellarestore_checkservirà più avanti per controllare i tuoi ripristini.
Accedere al database da remoto
Opzione consigliata: tunnel SSH
Il tunnel non richiede alcuna modifica a PostgreSQL né al firewall. Dal tuo computer:
ssh -N -L 5433:127.0.0.1:5432 TUO_USER@IP_DEL_TUO_SERVERLascia attivo questo comando, poi collega il tuo client alla porta locale 5433:
psql -h 127.0.0.1 -p 5433 -U app_owner -d appdbOpzione avanzata: connessione diretta in TLS
Usa questa opzione se un servizio esterno non può passare da SSH. Richiede un nome di dominio, ad esempio db.tuo-dominio.com, e un certificato valido: consulta la nostra guida Installare un certificato SSL con Let's Encrypt per ottenerlo con Certbot.
-
Crea uno script che copia il certificato per PostgreSQL e verrà eseguito a ogni rinnovo:
Terminalesudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.shTerminale#!/bin/sh set -e DOMAIN="db.tuo-dominio.com" [ "$RENEWED_LINEAGE" = "/etc/letsencrypt/live/$DOMAIN" ] || exit 0 install -o postgres -g postgres -m 644 "$RENEWED_LINEAGE/fullchain.pem" /etc/postgresql/18/main/server.crt install -o postgres -g postgres -m 600 "$RENEWED_LINEAGE/privkey.pem" /etc/postgresql/18/main/server.key systemctl reload postgresql@18-main -
Rendilo eseguibile e lancialo una prima volta:
Terminalesudo chmod 755 /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh sudo RENEWED_LINEAGE=/etc/letsencrypt/live/db.tuo-dominio.com /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh -
Crea il file di configurazione:
Terminalesudo nano /etc/postgresql/18/main/conf.d/90-remote-access.confConfigurazionelisten_addresses = '*' ssl = on ssl_cert_file = '/etc/postgresql/18/main/server.crt' ssl_key_file = '/etc/postgresql/18/main/server.key' ssl_min_protocol_version = 'TLSv1.2' -
Aggiungi alla fine di
/etc/postgresql/18/main/pg_hba.confuna regola limitata a un database, un ruolo e un indirizzo:Testohostssl appdb app_owner TUO_IP/32 scram-sha-256hostsslrifiuta qualsiasi connessione non cifrata. Per un indirizzo IPv6, usa il suffisso/128. -
Autorizza solo il tuo indirizzo nel firewall. Con UFW:
Terminalesudo ufw allow from TUO_IP to any port 5432 proto tcp -
Riavvia PostgreSQL, perché
listen_addressesnon viene applicato con un semplice ricaricamento:Terminalesudo systemctl restart postgresql@18-main sudo -u postgres psql -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"Il secondo comando non deve restituire alcuna riga.
-
Connettiti dal tuo computer con un client
psqlin versione 16 o successiva:Terminalepsql "host=db.tuo-dominio.com port=5432 dbname=appdb user=app_owner sslmode=verify-full sslrootcert=system"Controlla poi che la sessione sia cifrata:
SQLSELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();
Automatizzare i backup
pg_dump esporta un database senza fermarlo, ma non salva né i ruoli né le loro password. Lo script seguente esporta quindi anche questi elementi globali con pg_dumpall --globals-only.
-
Crea la cartella di destinazione:
Terminalesudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql -
Crea lo script:
Terminalesudo nano /usr/local/sbin/postgresql-backup.shTerminale#!/bin/bash set -euo pipefail umask 077 DEST="/var/backups/postgresql" RETENTION_DAYS=14 STAMP="$(date +%F_%H%M%S)" TARGET="$DEST/$STAMP" mkdir -p "$TARGET" pg_dumpall --globals-only -f "$TARGET/globals.sql" psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate" | while IFS= read -r db; do pg_dump -Fc -d "$db" -f "$TARGET/$db.dump" done find "$DEST" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} + -
Crea il servizio:
Terminalesudo chmod 755 /usr/local/sbin/postgresql-backup.sh sudo nano /etc/systemd/system/postgresql-backup.serviceConfigurazione[Unit] Description=PostgreSQL backup After=postgresql.service [Service] Type=oneshot User=postgres Group=postgres ExecStart=/usr/local/sbin/postgresql-backup.sh -
Crea il timer:
Terminalesudo nano /etc/systemd/system/postgresql-backup.timerConfigurazione[Unit] Description=Daily PostgreSQL backup [Timer] OnCalendar=*-*-* 02:30:00 RandomizedDelaySec=10min Persistent=true [Install] WantedBy=timers.target -
Attivalo e lancia un primo backup:
Terminalesudo systemctl daemon-reload sudo systemctl enable --now postgresql-backup.timer sudo systemctl start postgresql-backup.service sudo systemctl status postgresql-backup.service --no-pager sudo -u postgres ls -lR /var/backups/postgresql
Testare il ripristino
Un backup ha valore solo se sei già riuscito a ripristinarlo. Questo test usa un database temporaneo e non tocca il database di produzione.
STAMP="$(sudo -u postgres ls -1 /var/backups/postgresql | tail -n 1)"
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --no-owner --no-privileges --exit-on-error -d restore_test "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d restore_test -c "SELECT * FROM restore_check;"
sudo -u postgres dropdb restore_testDevi ritrovare la riga sauvegarde OK. Ripeti questo controllo regolarmente e confronta anche alcune tabelle importanti per la tua applicazione.
Ripristinare dopo un incidente
Su un server nuovo in cui PostgreSQL è installato, oppure dopo aver fermato le applicazioni ed eliminato il database danneggiato:
STAMP="2026-11-18_023000"
sudo -u postgres psql -X -f "/var/backups/postgresql/$STAMP/globals.sql" postgres
sudo -u postgres dropdb --if-exists appdb
sudo -u postgres pg_restore --create --exit-on-error -d postgres "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d appdb -c "ANALYZE;"Sostituisci il valore di STAMP con la cartella desiderata. L'errore role "postgres" already exists durante il ripristino dei ruoli è normale.
Aggiornare PostgreSQL
Le versioni minori (ad esempio da 18.6 a 18.7) correggono bug e vulnerabilità. Si installano con il sistema e riavviano il servizio:
sudo apt update
sudo apt upgradeUna nuova versione maggiore si installa accanto alla precedente e richiede una migrazione. Lancia prima un backup, poi adatta i numeri di versione:
OLD=18
NEW=19
sudo systemctl start postgresql-backup.service
sudo apt install -y "postgresql-$NEW"
pg_lsclusters
sudo pg_dropcluster --stop "$NEW" main
sudo pg_upgradecluster -v "$NEW" "$OLD" main
pg_lsclusterspg_dropcluster elimina qui l'istanza vuota creata dal nuovo pacchetto: verifica con pg_lsclusters che non contenga nulla di utile. pg_upgradecluster colloca poi la nuova versione sulla porta originale e conserva la vecchia, fermata. Una volta controllate le tue applicazioni, rimuovila:
sudo pg_dropcluster "$OLD" main
sudo apt purge "postgresql-$OLD" "postgresql-client-$OLD"Ricordati di adattare il numero di versione nello script di rinnovo del certificato, se lo usi.
In caso di problemi
FATAL: no pg_hba.conf entry for host
Nessuna regola corrisponde all'indirizzo, al ruolo, al database o alla cifratura. Il messaggio indica i valori ricevuti dal server. Verifica che corrispondano alla tua riga hostssl, compreso l'indirizzo IP pubblico reale del tuo client, poi ricarica la configurazione:
sudo systemctl reload postgresql@18-mainFATAL: password authentication failed for user
La password è errata oppure il ruolo non ne ha una. Ridefiniscila da una sessione di amministrazione:
sudo -u postgres psql -c "\password app_owner"FATAL: Peer authentication failed for user
Ti connetti tramite il socket locale con un ruolo diverso dal tuo utente di sistema. Aggiungi -h 127.0.0.1 per usare la password, oppure lancia il comando con l'utente giusto tramite sudo -u.
Connection refused o timeout
pg_lsclusters
sudo ss -tlnp | grep 5432
sudo ufw status
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.logConnection refused indica in genere che PostgreSQL è fermo o non ascolta su quell'indirizzo. Un timeout fa pensare piuttosto a un firewall.
permission denied for table o for schema public
Il ruolo non ha i permessi necessari. Riesegui i GRANT essendo connesso al database giusto. Per le tabelle esistenti create da un altro ruolo, ALTER DEFAULT PRIVILEGES non basta: riguarda solo le tabelle future.
Il servizio non si avvia più dopo una modifica
Il log indica il file e la riga in causa:
sudo journalctl -u postgresql@18-main -n 50 --no-pager
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.logCon un certificato personalizzato, verifica anche che server.key appartenga a postgres con i permessi 600.
could not change directory to "/root"
Questo messaggio compare quando lanci sudo -u postgres da una cartella che postgres non può leggere. Non ha conseguenze: spostati in /tmp per farlo sparire.