DEV Community

minnogit
minnogit

Posted on

Ripristinare il backup di un database PostgreSQL su AWS senza accesso diretto al server DB

#postgressql #linux

In molte infrastrutture AWS ben progettate, il server PostgreSQL non è esposto su Internet:
non ha un IP pubblico statico e non è accessibile direttamente via SSH.

Il database risiede in una subnet privata all’interno di una VPC, mentre l’accesso avviene tramite un server ponte (jump host / bastion host) con IP pubblico.

In questo articolo vediamo come ripristinare un database PostgreSQL partendo da un file .sql presente sul proprio PC, analizzando le diverse soluzioni possibili, i pro e contro, e raccogliendo in fondo una serie di comandi PostgreSQL di uso comune come note operative.

Scenario di riferimento

  • PC locale: Debian Linux, con file db.sql
  • Server ponte: accessibile via SSH con chiave (serverponte.dominioaziendale.it)

  • Server PostgreSQL:

    • Debian
    • IP privato statico (es. serverpostgresql.ipprivato.domioaziendale.it)
    • Accessibile solo dalla VPC

  • Utenti DB disponibili:

    • postgres (superuser)
    • admin (utente applicativo)

  • Obiettivo: ripristinare il database NomeDB da script SQL

Problema tipico

Lo script SQL non viene eseguito correttamente usando DBeaver con l’utente admin e produce errori come:

  • permessi insufficienti
  • impossibilità di creare estensioni
  • errori su OWNER TO postgres
  • messaggi legati a pg_hba.conf

👉 Questo accade perché un file .sql spesso contiene operazioni da superuser, anche se il database esiste già.

Le possibili soluzioni

✔️ Soluzione 1 – Tunnel SSH + psql locale (consigliata)

È la soluzione più pulita, sicura e professionale.
Non richiede di copiare file sui server e sfrutta il port forwarding SSH.

Schema logico 

PC locale → tunnel SSH → server ponte → rete privata → PostgreSQL
Enter fullscreen mode Exit fullscreen mode

Apertura del tunnel

Dal PC locale:

ssh -L 5433:serverpostgresql.ipprivato.domioaziendale.it:5432 serverponte.dominioaziendale.it
Enter fullscreen mode Exit fullscreen mode
  • 5433 → porta locale
  • 5432 → porta PostgreSQL remota
  • il tunnel resta attivo finché la sessione SSH è aperta

Ripristino del database

In un secondo terminale locale:

psql -h localhost -p 5433 -U admin -d NomeDB < db.sql
Enter fullscreen mode Exit fullscreen mode

⚠️ In questo scenario l’utente admin deve essere SUPERUSER, altrimenti lo script potrebbe fallire.

✔️ Soluzione 2 – Promuovere temporaneamente admin a superuser

Se pg_hba.conf blocca le connessioni dell’utente postgres da remoto (caso molto comune in ambienti gestiti con Puppet), la soluzione più pragmatica è:

ALTER USER admin WITH SUPERUSER;
Enter fullscreen mode Exit fullscreen mode

Dopo il ripristino:

ALTER USER admin WITH NOSUPERUSER;
Enter fullscreen mode Exit fullscreen mode

✔️ Veloce
✔️ Non richiede modifiche a pg_hba.conf
✔️ Adatta a interventi manuali controllati

✔️ Soluzione 3 – Copiare il file sul server DB ed eseguire localmente

Indicata per file molto grandi o connessioni instabili.

scp db.sql serverponte.dominioaziendale.it:/tmp/
# poi dal jump host verso il DB
scp /tmp/db.sql serverpostgresql.ipprivato.domioaziendale.it:/tmp/
Enter fullscreen mode Exit fullscreen mode

Sul server DB:

su - postgres
psql NomeDB < /tmp/db.sql
Enter fullscreen mode Exit fullscreen mode

✔️ Massima affidabilità
❌ Più passaggi manuali

Perché DBeaver spesso non basta

DBeaver funziona bene per:

  • restore da dump binari
  • operazioni standard
  • utenti applicativi

Ma fallisce con script SQL complessi che contengono:

  • CREATE EXTENSION
  • ALTER OWNER
  • SET ROLE
  • operazioni su schemi di sistema

👉 In questi casi psql è lo strumento giusto.

Note operative – Comandi PostgreSQL di uso comune

Creare un database 

CREATE DATABASE NomeDB;
Enter fullscreen mode Exit fullscreen mode

Creare una copia da un database esistente 

CREATE DATABASE testCopia WITH TEMPLATE 'NomeDB';
Enter fullscreen mode Exit fullscreen mode

Assegnare tutti i permessi a un utente 

GRANT ALL PRIVILEGES ON DATABASE NomeDB TO admin;
Enter fullscreen mode Exit fullscreen mode

Collegarsi a un database con un utente specifico 

psql -U admin -d NomeDB -h localhost
Enter fullscreen mode Exit fullscreen mode

Eliminare un database 

$ psql -U postgresql -h localhost
DROP DATABASE database_da_eliminare WITH (FORCE);
Enter fullscreen mode Exit fullscreen mode

Backup con pg_dump

Creare un dump SQL 

pg_dump -f /tmp/nome_database_dump.sql -U postgres -W nome_database
Enter fullscreen mode Exit fullscreen mode

Significato opzioni:

  • -f → file di output
  • -U postgres → utente DB
  • -W → richiede password
  • nome_database → nome database

Evitare la richiesta password (automazioni)

Metodo consigliato: .pgpass

File ~/.pgpass:

hostname:port:database:username:password
Enter fullscreen mode Exit fullscreen mode

Permessi obbligatori:

chmod 600 ~/.pgpass
Enter fullscreen mode Exit fullscreen mode

Metodo rapido (meno sicuro) 

export PGPASSWORD='password'
Enter fullscreen mode Exit fullscreen mode

Ripristino di un database PostgreSQL: scegliere il comando giusto

Il comando da usare per il ripristino dipende dal formato del backup, ovvero da come è stato creato con pg_dump.

È un aspetto fondamentale: usare lo strumento sbagliato porta a errori o a ripristini incompleti.

1️⃣ Ripristino da file SQL (Plain Text)

Se il backup è stato creato senza opzioni di formato (output leggibile, .sql):

pg_dump nome_database > backup.sql
Enter fullscreen mode Exit fullscreen mode

oppure:

pg_dump -f /tmp/pnd_dump.sql nome_database
Enter fullscreen mode Exit fullscreen mode

il ripristino va fatto con psql, che esegue sequenzialmente le istruzioni SQL contenute nel file.

psql -U postgres -d nome_database -f /tmp/nome_database_dump.sql
Enter fullscreen mode Exit fullscreen mode

Note importanti:

  • Il database di destinazione deve già esistere
  • Il file è leggibile e modificabile
  • Su database molto grandi può essere più lento

2️⃣ Ripristino da file Custom o Directory (.dump, .bak)

Se il backup è stato creato con:

pg_dump -Fc nome_database > backup.dump
Enter fullscreen mode Exit fullscreen mode

oppure:

pg_dump -Fd nome_database -f backup_dir
Enter fullscreen mode Exit fullscreen mode

si tratta di un formato binario/speciale, che non può essere eseguito con psql.

In questo caso va usato pg_restore:

pg_restore -U postgres -d nome_database -v backup.dump
Enter fullscreen mode Exit fullscreen mode

Opzioni utili:

  • -v → modalità verbose
  • --clean → elimina gli oggetti prima di ricrearli
  • --if-exists → evita errori se gli oggetti non esistono

Ripristino in parallelo (solo con pg_restore)

Uno dei grandi vantaggi dei formati custom o directory è la possibilità di usare più core CPU:

pg_restore -j 4 -U postgres -d nome_database backup.dump
Enter fullscreen mode Exit fullscreen mode

👉 Ideale per database grandi in ambienti server.

Tabella rapida di riferimento

Formato backup Strumento Quando usarlo
.sql (plain text) psql Backup leggibile, semplice
.dump (custom) pg_restore Backup compresso, selettivo
directory pg_restore Ripristino veloce e parallelo

Nota operativa importante

Se il database di destinazione è già in uso, il ripristino può fallire per:

  • connessioni attive
  • oggetti già esistenti
  • lock sulle tabelle

In questi casi è consigliabile:

  • ripristinare su un database vuoto
  • oppure usare --clean con attenzione

Conclusione

In ambienti AWS con PostgreSQL in subnet private:

  • non si espone mai il DB su Internet
  • il jump host è la chiave
  • psql è lo strumento più affidabile
  • i permessi contano più della GUI

Il tunnel SSH + psql resta la soluzione più solida, ripetibile e sicura per ripristinare database PostgreSQL in infrastrutture cloud ben progettate.

Top comments (0)