PostgreSQL 15: ripristino con pg_restore dopo "could not open relation" e rollback dei permessi rotti

Quando un database PostgreSQL si rompe, il problema raro non è il backup. Il problema vero è il restore che sembra andare a buon fine e poi lascia dietro relazioni mancanti, permessi sbagliati o query improvvisamente lente.

In hosting succede spesso dopo un deploy, una migrazione o una replica promossa troppo in fretta. Il sintomo può essere banale: un errore could not open relation, un permission denied for table, oppure tempi di risposta triplicati su una query che ieri era stabile.

Qui lavoriamo sul caso peggiore: diagnosi rapida, rollback dei cambiamenti sbagliati, ripristino pulito con pg_restore e controlli finali su permessi, indici e replica.

Prerequisiti

Servono accesso SSH al server, credenziali PostgreSQL con privilegi adeguati e un dump recente in formato custom o directory.

Assumo anche che tu sappia distinguere tra ruolo, schema e database. In hosting questo dettaglio evita molti falsi allarmi.

Strumenti utili:

• psql per verifiche rapide.
• pg_restore per restore selettivo o completo.
• pg_dump per creare un punto di rollback.
• EXPLAIN (ANALYZE, BUFFERS) per le query lente.
• replication slots e controlli su pg_stat_replication se c’è streaming replication.

Warning: prima di ripristinare, congela scritture applicative o metti il sito in maintenance mode. Un restore “a caldo” può sovrascrivere dati già corretti.

Step 1: capire se il danno è nel database o nell’applicazione

Il primo errore è toccare tutto. Prima separa il sintomo dal guasto reale. Se l’app mostra 500, non significa che il database sia corrotto.

Controlla i log di PostgreSQL e prova una query minima. Se la connessione funziona, il problema può essere circoscritto a un ruolo, una tabella o un indice.

psql -h 127.0.0.1 -U app_user -d appdb -c "SELECT 1;"
psql -h 127.0.0.1 -U app_user -d appdb -c "SELECT now();"
grep -E "ERROR|FATAL|PANIC|could not open relation|permission denied" /var/log/postgresql/postgresql-15-main.log | tail -n 30

# Output:

 ?column?
----------
        1
(1 row)

Perché funziona: una query banale separa guasti di rete, autenticazione e corruzione logica.

Note: su MariaDB/MySQL useresti mysql -e 'SELECT 1' e i log errori del server, ma la logica resta uguale.

Step 2: creare un rollback prima di toccare il restore

Se il database è accessibile, crea subito un punto di ritorno. Anche un dump parziale dei soli schemi o dei dati critici può salvare la situazione.

Non aspettare il restore completo. Se qualcosa va storto durante il ripristino, ti serve un’ancora.

pg_dump -h 127.0.0.1 -U postgres -Fc -f /var/backups/appdb-pre-restore.dump appdb
ls -lh /var/backups/appdb-pre-restore.dump

# Output:

-rw-r--r-- 1 postgres postgres 248M Mar 24 07:55 /var/backups/appdb-pre-restore.dump

Perché funziona: il formato custom consente restore selettivo, più veloce e meno rischioso di un SQL piatto.

Note: su hosting condiviso potresti non avere spazio sufficiente. In quel caso copia il dump su storage remoto prima di procedere.

Step 3: fare restore selettivo quando il danno è parziale

Se il problema riguarda una o poche tabelle, evita il restore totale. Ripristina solo gli oggetti rotti e lascia il resto intatto.

Questo approccio riduce downtime e limita i conflitti con dati nuovi già arrivati in produzione.

pg_restore -h 127.0.0.1 -U postgres -d appdb --clean --if-exists --table=public.orders /var/backups/appdb.dump

# Output:

pg_restore: dropping TABLE public.orders
pg_restore: creating TABLE public.orders
pg_restore: processing data for table "public.orders"

Perché funziona: --clean rimuove l’oggetto rotto prima di ricrearlo, evitando residui incoerenti.

Warning: se la tabella riceve scritture continue, devi bloccare l’app o rischi di mescolare dati vecchi e nuovi.

Note: in alcune distribuzioni il client PostgreSQL ha nome pacchetto diverso. Su Debian e Ubuntu è spesso postgresql-client-15; su RHEL e derivate può cambiare il suffisso.

Step 4: correggere i permessi dopo un restore

Un restore eseguito come superuser ricrea gli oggetti, ma non sempre allinea i privilegi attesi. Il classico errore è vedere tabelle presenti ma non leggibili dall’app.

Qui il rollback è logico, non fisico: revoca i privilegi errati e riapplica una baseline coerente.

psql -U postgres -d appdb -c "REVOKE ALL ON SCHEMA public FROM PUBLIC;"
psql -U postgres -d appdb -c "GRANT USAGE ON SCHEMA public TO app_user;"
psql -U postgres -d appdb -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;"
psql -U postgres -d appdb -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;"

# Output:

REVOKE
GRANT
GRANT
ALTER DEFAULT PRIVILEGES

Perché funziona: separa i privilegi sullo schema dai privilegi sugli oggetti già esistenti e su quelli futuri.

Note: su MySQL/MariaDB il concetto equivalente passa da GRANT e REVOKE, ma non esiste il meccanismo identico di default privileges.

Step 5: verificare gli indici prima di inseguire il sintomo “query lente”

Dopo un restore, le query possono rallentare perché manca un indice, è stato ricreato in ritardo o il planner ha statistiche vecchie.

Non ottimizzare alla cieca. Prima guarda il piano reale.

psql -U app_user -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 18421 ORDER BY created_at DESC LIMIT 20;"
psql -U postgres -d appdb -c "\di+ public.*"

# Output:

Limit  (cost=0.43..12.45 rows=20 width=...) (actual time=0.031..0.092 rows=20 loops=1)
  ->  Index Scan using orders_customer_id_created_at_idx on orders ...

Perché funziona: il piano mostra se il problema è un seq scan, un indice assente o statistiche non aggiornate.

Se l’indice manca davvero, ricrealo con attenzione.

psql -U postgres -d appdb -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_created_at_idx ON public.orders (customer_id, created_at DESC);"
psql -U postgres -d appdb -c "ANALYZE public.orders;"

# Output:

CREATE INDEX
ANALYZE

Perché funziona: CREATE INDEX CONCURRENTLY riduce il blocco in produzione.

Warning: non usarlo dentro una transaction block. Se il client o l’ORM lo fa automaticamente, fallirà.

Step 6: controllare la replicazione prima del failback

Se il database era in replica, il ripristino manuale può aver rotto la catena di streaming. Il sintomo tipico è un standby che resta fermo o un primary che riceve dati incoerenti.

Prima di fare failback, verifica stato e ritardo. Un nodo “verde” non basta.

psql -U postgres -d postgres -c "SELECT client_addr, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
psql -U postgres -d postgres -c "SELECT pg_is_in_recovery();"

# Output:

 client_addr |   state   | sync_state | write_lag | flush_lag | replay_lag
-------------+-----------+------------+-----------+-----------+------------
 10.0.0.12   | streaming | sync       | 00:00:00  | 00:00:00  | 00:00:01

Perché funziona: ti dice se il nodo secondario è davvero allineato o sta solo accettando connessioni.

Note: in ambienti gestiti il provider può nascondere questi dettagli. Chiedi almeno l’equivalente del lag e dello stato di replica.

Step 7: rimettere online con un controllo di coerenza minimo

Non riaprire tutto subito. Verifica prima tabelle chiave, permessi applicativi e una query che tocca indice e join.

Se passi questo filtro, il problema è probabilmente sotto controllo.

psql -U app_user -d appdb -c "SELECT count(*) FROM orders WHERE created_at >= now() - interval '1 day';"
psql -U app_user -d appdb -c "SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id ORDER BY o.id DESC LIMIT 5;"

# Output:

 count
-------
   184
(1 row)

Perché funziona: combina carico leggero, permessi reali e piano di esecuzione plausibile.

Verifica finale

Prima di considerare chiuso l’intervento, esegui questa checklist.

• Il database accetta connessioni con l’utente applicativo.
• Le tabelle critiche esistono e contengono dati attesi.
• I permessi sullo schema e sulle tabelle sono coerenti.
• Le query lente hanno di nuovo un piano con indice, non un seq scan inutile.
• La replica, se presente, è in streaming e il lag è accettabile.
• Il backup pre-restore è stato conservato per almeno un ciclo di verifica.

Se vuoi un controllo ancora più concreto, confronta numero di righe e checksum applicativi sui record più importanti. Un restore corretto non deve solo “partire”. Deve essere consistente.

Troubleshooting

Errore: could not open relation with OID 16392

Causa: l’oggetto esiste nel catalogo, ma i file dati sono mancanti o il restore è incompleto.

Fix:

pg_restore -U postgres -d appdb --clean --if-exists --table=public.orders /var/backups/appdb.dump
psql -U postgres -d appdb -c "ANALYZE public.orders;"

Errore: permission denied for table orders

Causa: i privilegi non sono stati riallineati dopo il restore o il ruolo applicativo non ha USAGE sullo schema.

Fix:

psql -U postgres -d appdb -c "GRANT USAGE ON SCHEMA public TO app_user;"
psql -U postgres -d appdb -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;"

Errore: ERROR: relation "orders_customer_id_created_at_idx" does not exist

Causa: l’indice atteso non è stato ricreato, oppure il deploy ha cambiato nome o schema.

Fix:

psql -U postgres -d appdb -c "\di+ public.orders*"
psql -U postgres -d appdb -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_created_at_idx ON public.orders (customer_id, created_at DESC);"

Conclusione

Quando qualcosa va storto su PostgreSQL, il punto non è solo ripristinare i dati. Devi ripristinare anche permessi, indici e fiducia nel piano di esecuzione.

Il flusso giusto è semplice: diagnosi, backup di sicurezza, restore selettivo o completo, verifica finale. Il prossimo passo concreto è preparare uno script di emergenza con questi controlli già pronti e testarlo su un clone del database.