Come Pianificare Backup Automatici per PostgreSQL con pgAgent in pgAdmin

Introduzione

pgAgent è uno strumento utilizzato per pianificare lavori per i database PostgreSQL. Ha anche capacità di pianificazione più potenti rispetto al spesso usato cron perché è specificamente progettato per gestire compiti di Postgres. Ad esempio, pgAgent può pianificare più passaggi senza uno script batch o senza ripetere il comando. È importante notare che anche se hai pgAdmin installato, pgAgent deve essere scaricato indipendentemente, specificamente come estensione per pgAdmin.

In questo tutorial installerai pgAgent, configurerai il tuo database per utilizzare pgAgent sull’interfaccia grafica utente (GUI) di pgAdmin, configurerai pgAgent come daemon e quindi utilizzerai pgAgent per pianificare un lavoro che effettuerà il backup del tuo database.

Prerequisiti

Per seguire questo tutorial, avrai bisogno di:

• Un server Ubuntu 22.04 configurato con un utente non-root abilitato a sudo e un firewall. Puoi farlo leggendo la nostra Guida alla Configurazione Iniziale del Server
• PostgreSQL installato sul tuo server. Puoi configurarlo seguendo la nostra guida su Come Installare e Utilizzare PostgreSQL su Ubuntu 22.04. Mentre segui questa guida, assicurati di creare un nuovo ruolo e un nuovo database, poiché avrai bisogno di entrambi per connettere pgAdmin alla tua istanza di PostgreSQL
• pgAdmin installato con un account impostato. Puoi farlo seguendo il nostro tutorial su Come Installare e Configurare pgAdmin4 in Modalità Server su Ubuntu 22.04

Passaggio 1 — Installazione di pgAgent

Come già accennato, pgAgent non è configurato automaticamente durante l’installazione di pgAdmin. Puoi installare pgAgent dal tuo terminale eseguendo il comando apt install seguito dal nome del pacchetto pgagent come nel seguente comando:

sudo apt install pgagent

Dopo aver installato pgAgent, passa al passaggio successivo per configurare il tuo database per utilizzare pgAgent in pgAdmin.

Passaggio 2 — Configurazione del tuo Database per pgAgent

Avendo seguito i prerequisiti, pgAdmin è configurato e pronto all’uso. Puoi configurare il tuo database per l’uso di pgAgent attraverso pgAdmin. Apri il tuo browser web e vai all’applicazione pgAdmin su http://il_tuo_dominio. Una volta effettuato l’accesso al tuo account, vai al controllo dell’albero sul pannello laterale sinistro. Trova il database che hai creato chiamato sammy ed espandi l’elenco. Da questo elenco, ci sarà un’opzione chiamata Estensioni. Una volta individuata, fai clic con il pulsante destro del mouse su di essa e scegli l’opzione Strumento di Query:

pgAgent richiede un’estensione da caricare nel tuo database prima di poter essere utilizzato in pgAdmin. Per farlo, scrivi la seguente query e fai clic sulla freccia laterale ▶ che indica Esegui per eseguire il comando:

CREATE EXTENSION pgagent;

Questo passaggio con la funzione Strumento di Query è rappresentato nel seguente esempio:

Nella scheda Messaggi ci sarà un output che restituirà La query è stata eseguita correttamente in 300 msec. Ciò conferma che l’estensione pgAgent è stata creata con successo.

Output
ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.
SQL state: 42704

CREATE LANGUAGE plpgsql;

Dopo aver eseguito queste query, sotto Estensioni, ci saranno due voci elencate per pgagent e plpgsql:

A new item in the tree control on the left-hand side will appear called pgAgent Jobs. This signifies that pgAgent was successfully installed on your pgAdmin account. Next, you will set up pgAgent as a daemon so that it can run your jobs successfully.

Passaggio 3 — Configurare pgAgent come un Daemon

Ora che hai installato pgAgent e lo hai configurato per il tuo database tramite pgAdmin, devi impostare pgAgent come un daemon sul tuo server. Un daemon è un programma che viene eseguito come processo continuo in background ed esegue compiti di servizio. pgAgent viene eseguito come un daemon sui sistemi Unix e tipicamente sul server del database stesso.

Per configurare pgAgent come un daemon, hai bisogno di una stringa di connessione PostgreSQL in modo che quando programmi il tuo lavoro, il processo verrà eseguito correttamente. In questo caso, stai impostando una stringa di connessione libpq di PostgreSQL, il che significa una stringa specifica dell’utente e che si connette a determinati parametri che hai impostato. Puoi saperne di più sulle funzioni di connessione libpq di PostgreSQL nella documentazione di PostgreSQL.

Il tuo string di connessione fornirà le credenziali del tuo hostname, il nome del database e l’username. Nel nostro esempio, l’host utilizzerà una socket del dominio Unix, il nome del database è sammy e l’utente è sammy. Questa stringa sarà aggiunta a un comando pgagent per avviare il daemon. Nel tuo terminale, eseguirai il seguente codice:

pgagent host=/var/run/postgresql dbname=sammy user=sammy

Se non viene restituito nulla nel tuo output e non ricevi un messaggio di errore di connessione, allora la configurazione della stringa di connessione è stata effettuata con successo.

Dopo aver creato la stringa di connessione, sei pronto per pianificare un lavoro con pgAgent.

Passo 4 — Pianificare un Lavoro con pgAgent

pgAgent funge da agente di pianificazione che può eseguire e gestire lavori e può creare lavori con uno o più passaggi o pianificazioni. Ad esempio, un passaggio può consistere in diverse dichiarazioni SQL in uno script shell ed è eseguito consecutivamente dopo l’altro. In generale, puoi utilizzare pgAgent per pianificare, gestire, modificare o disabilitare i tuoi lavori.

Per gli scopi di questo tutorial, utilizzerai pgAgent per creare un lavoro che effettuerà il backup del tuo database sammy ogni minuto ogni giorno della settimana. Puoi iniziare cliccando con il pulsante destro su pgAgent Jobs e selezionando Create e quindi pgAgent Job… come segue:

Una volta fatto questo, comparirà una finestra di dialogo intitolata Crea – Lavoro di pgAgent, e potrai iniziare a compilare le informazioni richieste nella scheda Generale. In questo esempio, useremo il nome sammy_backup e non specificheremo un Host agente poiché vogliamo essere in grado di eseguire questo lavoro su qualsiasi host. Inoltre, lasceremo la Classe di lavoro come Mantenimento ordinario. Se desideri includere altri commenti, sentiti libero di farlo nella sezione Commento:

Successivamente, passa alla scheda Passaggi. Fai clic sul simbolo + nell’angolo in alto a destra per creare un passaggio. In questo esempio, chiameremo questo passaggio passo1. Quindi, per espandere le tue opzioni, fai clic sulla matita sul lato sinistro dell’icona del cestino. Il pulsante Abilitato? è impostato per essere attivato per impostazione predefinita e indica che questo passaggio verrà incluso quando questo lavoro verrà eseguito.

Per l’opzione di tipo puoi selezionare sia SQL che Batch, qui abbiamo selezionato Batch. Il motivo per cui si desidera scegliere Batch in questo esempio è perché questo eseguirà i comandi PostgreSQL appropriati che imposterai per i backup che desideri pianificare per il tuo database. L’opzione SQL è disponibile per pianificare un lavoro per eseguire SQL grezzo. In questo caso, abbiamo selezionato Locale per il tipo di connessione in modo che il passaggio venga eseguito sul server locale, ma se preferisci, puoi anche scegliere Remoto per un host remoto di tua scelta. Se preferisci farlo su un host remoto, devi specificare quel criterio nel campo Stringa di connessione. Se hai seguito il Passaggio 1, la tua stringa di connessione è già impostata e connessa.

Per il campo Database, assicurati di avere il database corretto selezionato, qui abbiamo specificato sammy. Con l’opzione Sull’errore, puoi personalizzare la risposta di pgAgent se si verifica un errore durante l’esecuzione di un passaggio. In questo caso, abbiamo selezionato Fail per essere avvisati se si verifica un errore durante il tentativo di elaborare un passaggio. Di nuovo, se desideri aggiungere note aggiuntive, puoi aggiungerle nella casella Commento:

All’interno della stessa scheda Passaggi c’è anche una scheda Codice. Se hai selezionato Batch come abbiamo fatto in questo esempio, vai alla scheda Codice. Una volta in questa scheda, c’è una riga vuota in cui inserire il comando PostgreSQL. Puoi sostituire questo comando di backup con il tuo insieme personalizzato di opzioni. Qualsiasi comando valido è accettabile.

Questo tutorial utilizzerà il comando pg_dump per eseguire il backup del tuo database Postgres sammy. In questo comando, includi il tuo nome utente specifico, il nome del database e il flag --clean, che aiuta con pg_dump eliminando o “pulendo” gli oggetti del database prima di emettere eventuali comandi che vengono creati. Per il flag --file, specificare la posizione esatta in cui verranno salvati i file di backup. La parte finale di questa dichiarazione date +%Y-%m-%d-%H-%M-%S serve per generare dinamicamente una data e più file per ciascun backup. Altrimenti, il file di backup sovrascriverà costantemente e salverà il file esistente. In questo modo, puoi tenere traccia di ciascuno dei tuoi file di backup per qualsiasi data o ora specificata che hai pianificato. Il tuo comando completo sarà il seguente:

pg_dump --username=sammy --dbname=sammy --clean --file=/home/sammy/backup-`date +%Y-%m-%d-%H-%M-%S`.sql

Una volta aggiunto il comando di backup, puoi navigare nella scheda denominata Schede. Similmente all’impostazione dei Passaggi, fai clic sul simbolo + per aggiungere una pianificazione, quindi fornisci il nome preferito e fai clic sull’icona della matita accanto all’icona del cestino per espandere le tue opzioni. Sotto la scheda Generale ci sarà il Nome che hai scritto, in questo esempio, è programma1. Di nuovo, per Abilitato, questo è predefinito per l’interruttore on per garantire che la pianificazione venga eseguita correttamente. Per le opzioni Inizio e Fine, specifica il giorno e l’ora di inizio e fine per il tuo lavoro pianificato. Poiché testerai il tuo lavoro pianificato, assicurati che l’ora corrente sia compresa nell’intervallo di Inizio e Fine. Aggiungi una nota in Commento se preferisci:

Ora procedi alla scheda Ripeti. Qui puoi personalizzare con quale frequenza desideri che questo lavoro programmato venga eseguito. Puoi essere il più specifico possibile con la settimana, il mese, la data, le ore o i minuti. Nota bene, se non effettui una selezione, è la stessa cosa che scegliere Seleziona tutto. Pertanto, se lasci vuoti i Giorni della settimana, il tuo programma terrà conto di tutti i giorni feriali. Allo stesso modo, con i Tempi, puoi lasciare vuote le ore o i minuti, e questo equivale a Seleziona tutto. Tieni presente che i tempi sono nel formato cron-style, quindi per questo esempio, per generare un backup ogni minuto, devi selezionare ogni minuto in un’ora (00 a 59). Per dimostrarlo, abbiamo scelto Seleziona tutto per i minuti. Vengono elencati tutti i minuti, ma lasciarlo vuoto otterrà comunque gli stessi risultati:

Se ci sono giorni o orari in cui non desideri eseguire un lavoro, puoi creare un programma temporale più dettagliato, oppure puoi impostarlo navigando nella scheda Eccezioni.

Quando hai finito di impostare e personalizzare l’orario che desideri eseguire, premi il pulsante Salva. Un nuovo lavoro pgAgent apparirà sul controllo ad albero sul lato sinistro con il nome del tuo lavoro. Per questo esempio, sammy_backup appare con i Piani e Passaggi elencati sotto di esso:

Ora che hai creato con successo un lavoro pgAgent, nel prossimo passaggio, imparerai come verificare che il tuo lavoro pgAgent sia in esecuzione con successo.

Passaggio 5 — Verifica del tuo Lavoro pgAgent

Puoi verificare se il tuo lavoro programmato per creare un file di backup del tuo database ogni minuto sta funzionando in diversi modi. In pgAdmin, puoi navigare sul controllo ad albero sul lato sinistro e fare clic su sammy_backup. Da lì, procedi alla scheda denominata Statistiche. La pagina Statistiche elencherà ogni istanza del tuo lavoro programmato che funziona come segue:

Si prega di notare che le statistiche potrebbero non apparire o aggiornarsi immediatamente, quindi potrebbe essere necessario navigare altrove o aggiornare il browser. Ricorda che il tuo lavoro è programmato per essere eseguito ad intervalli prestabiliti, quindi tienilo presente se stai impostando una data o un’ora periodica o di lunga durata.

Se preferisci verificare che il tuo lavoro funzioni dalla riga di comando, puoi eseguire il comando ls ~ per elencare i file nella tua directory home. In questo tutorial, imposterai la posizione esatta di questa directory home quando scriverai il comando pg_dump nel Passo 4:

ls ~

L’output elencherà ogni istanza dei file di backup che vengono salvati.

Passo 6 — Modificare, Disabilitare, Eliminare ed Eseguire Manualmente i Lavori di pgAgent (Opzionale)

pgAgent offre flessibilità quando si tratta di modificare o disabilitare un lavoro. Utilizzando pgAdmin, puoi navigare al lavoro specifico di pgAgent, in questo caso, sammy_backup. Quindi fai clic con il pulsante destro e scegli l’opzione Proprietà dalla lista. Da qui puoi premere l’icona della matita in alto per apportare modifiche al tuo lavoro:

Se sei in Proprietà e premi sull’icona della matita, puoi navigare nella scheda Pianificazioni per disabilitare il tuo lavoro come segue:

Inoltre, dall’albero di controllo sulla sinistra, se fai clic destro sul lavoro pgAgent, puoi selezionare la selezione ELIMINA/DROP per eliminare completamente il lavoro. Se desideri eseguire un lavoro manualmente, puoi ripetere lo stesso passo, ma questa volta scegliere la selezione ESEGUI ORA dalla lista:

Anche se queste sono solo alcune delle funzioni per utilizzare pgAgent per pianificare lavori, ci sono molte altre possibilità. Ad esempio, potresti voler pianificare un lavoro che crea un backup per tutti gli utenti e ruoli nel tuo database, specificamente con il comando pg_dumpall --globals only. Puoi persino utilizzare script più complessi con lavori batch, di cui puoi apprendere di più dalla pagina wiki di PostgreSQL.

Conclusione

pgAgent è utile per pianificare vari lavori che possono aiutare a ridurre alcune delle tue attività quotidiane più noiose ma necessarie, come fare il backup del tuo database in modo consistente. Una volta che ti senti più a tuo agio nell’uso di questo strumento, puoi persino provare a creare un lavoro multi-step o compiti SQL che richiedono una pianificazione complessa. Se desideri saperne di più, puoi leggere la documentazione ufficiale di pgAgent sul sito web di pgAdmin.