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

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

Apertura del tunnel

Dal PC locale:

ssh -L 5433:serverpostgresql.ipprivato.domioaziendale.it:5432 serverponte.dominioaziendale.it

• 
  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

⚠️ 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;

Dopo il ripristino:

ALTER USER admin WITH NOSUPERUSER;

✔️ 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/

Sul server DB:

su - postgres

psql NomeDB < /tmp/db.sql

✔️ 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;

Creare una copia da un database esistente

CREATE DATABASE testCopia WITH TEMPLATE 'NomeDB';

Assegnare tutti i permessi a un utente

GRANT ALL PRIVILEGES ON DATABASE NomeDB TO admin;

Collegarsi a un database con un utente specifico

psql -U admin -d NomeDB -h localhost

Eliminare un database

$ psql -U postgresql -h localhost

DROP DATABASE database_da_eliminare WITH (FORCE);

Backup con pg_dump

Creare un dump SQL

pg_dump -f /tmp/nome_database_dump.sql -U postgres -W nome_database

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

Permessi obbligatori:

chmod 600 ~/.pgpass

Metodo rapido (meno sicuro)

export PGPASSWORD='password'

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

oppure:

pg_dump -f /tmp/pnd_dump.sql nome_database

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

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

oppure:

pg_dump -Fd nome_database -f backup_dir

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

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

👉 Ideale per database grandi in ambienti server.

Tabella rapida di riferimento

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.