Hoe Automatische Back-ups Plannen voor PostgreSQL met pgAgent in pgAdmin

Introductie

pgAgent is een tool die wordt gebruikt voor het plannen van taken voor PostgreSQL-databases. Het heeft ook krachtigere planningsmogelijkheden dan het vaak gebruikte cron, omdat het specifiek is gebouwd voor het afhandelen van Postgres-taken. Bijvoorbeeld, pgAgent kan meerdere stappen plannen zonder een batchscript of zonder het herhalen van de opdracht. Het is belangrijk op te merken dat zelfs als je pgAdmin hebt geïnstalleerd, pgAgent onafhankelijk moet worden gedownload, specifiek als een extensie voor pgAdmin.

In deze handleiding installeer je pgAgent, configureer je je database om pgAgent te gebruiken via de pgAdmin grafische gebruikersinterface (GUI), stel je pgAgent in als een daemon, en gebruik je pgAgent vervolgens om een taak te plannen die je database zal back-uppen.

Vereisten

Om deze handleiding te volgen, heb je nodig:

• Een Ubuntu 22.04-server opgezet met een niet-root sudo-ingeschakelde gebruiker en firewall. Dit kun je doen door onze Handleiding voor initiële serverconfiguratie te lezen.
• PostgreSQL geïnstalleerd op uw server. U kunt dit instellen door onze handleiding te volgen over Hoe PostgreSQL te installeren en gebruiken op Ubuntu 22.04. Terwijl u deze handleiding volgt, zorg ervoor dat u een nieuwe rol en database aanmaakt, aangezien u beide nodig zult hebben om pgAdmin te verbinden met uw PostgreSQL-instantie
• pgAdmin geïnstalleerd met een account ingesteld. U kunt dit doen door onze tutorial te volgen over Hoe pgAdmin4 te installeren en configureren in servermodus op Ubuntu 22.04

Stap 1 — pgAgent installeren

Zoals eerder vermeld, wordt pgAgent niet automatisch geconfigureerd bij de installatie van pgAdmin. U kunt pgAgent installeren vanuit uw terminal door apt install en de pakketnaam pgagent uit te voeren, zoals in het volgende commando:

sudo apt install pgagent

Nadat u pgAgent hebt geïnstalleerd, gaat u verder met de volgende stap om uw database te configureren om pgAgent in pgAdmin te gebruiken.

Stap 2 — Uw database configureren voor pgAgent

Na het volgen van de vereisten is pgAdmin geïnstalleerd en klaar voor gebruik. U kunt uw database configureren voor gebruik van pgAgent via pgAdmin. Open uw webbrowser en ga naar de pgAdmin-toepassing op http://your_domain. Zodra u bent ingelogd op uw account, navigeert u naar de boomstructuur aan de linkerkant van het scherm. Zoek de database die u hebt aangemaakt genaamd sammy en breid de lijst uit. U zult een optie genaamd Extensies zien. Zodra u deze heeft gevonden, klikt u er met de rechtermuisknop op en kiest u de optie Querytool:

pgAgent vereist een extensie die moet worden geladen in uw database voordat het kan worden gebruikt in pgAdmin. Schrijf hiervoor de volgende query en klik op de zijwaartse pijl ▶ die Uitvoeren aangeeft om het commando uit te voeren:

CREATE EXTENSION pgagent;

Deze stap met de Querytool-functie wordt weergegeven in het volgende voorbeeld:

Onder het tabblad Berichten wordt een uitvoer weergegeven die Query is met succes uitgevoerd in 300 msec. Dit bevestigt dat de pgAgent-extensie succesvol is aangemaakt.

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

CREATE LANGUAGE plpgsql;

Nadat u deze query’s hebt uitgevoerd, worden onder Extensies twee items vermeld voor pgagent en 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.

Stap 3 — pgAgent instellen als een daemon

Nu u pgAgent hebt geïnstalleerd en geconfigureerd voor uw database via pgAdmin, moet u pgAgent instellen als een daemon op uw server. Een daemon is een programma dat als een continu proces op de achtergrond draait en service taken uitvoert. pgAgent draait als een daemon op Unix-systemen en meestal op de databaseserver zelf.

Om pgAgent in te stellen als een daemon, heeft u een PostgreSQL-verbindingssnaren nodig, zodat wanneer u uw taak plant, het proces op de juiste manier wordt uitgevoerd. In dit geval stelt u een PostgreSQL libpq-verbindingssnaar in, wat betekent dat het een string is die specifiek is voor de gebruiker en verbinding maakt met bepaalde parameters die u hebt ingesteld. U kunt meer leren over PostgreSQL libpq-verbinding functies in de PostgreSQL-documentatie.

Uw verbindingsreeks zal de referenties van uw hostnaam, databasenaam en gebruikersnaam verstrekken. In ons voorbeeld zal de host een Unix-domeinsocket gebruiken, is de databasenaam sammy, en de gebruiker is sammy. Deze reeks wordt toegevoegd aan een pgagent-opdracht om de daemon te starten. In uw terminal voert u de volgende code uit:

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

Als er niets terugkeert in uw uitvoer en u geen verbindingsfoutmelding ontvangt, dan is de instelling van de verbindingsreeks succesvol geweest.

Nadat u de verbindingsreeks heeft gemaakt, bent u klaar om een taak in te plannen met pgAgent.

Stap 4 — Een taak plannen met pgAgent

pgAgent fungeert als een planningsagent die taken kan uitvoeren en beheren en taken van één of meer stappen of schema’s kan maken. Bijvoorbeeld, een stap kan bestaan uit meerdere SQL-opdrachten in een shellscript en wordt achtereenvolgens uitgevoerd na de andere. Over het algemeen kunt u pgAgent gebruiken om uw taken in te plannen, beheren, aanpassen of uitschakelen.

Voor de doeleinden van deze tutorial gebruikt u pgAgent om een taak te maken die uw sammy-database elke minuut op elke dag van de week zal back-uppen. U kunt beginnen door met de rechtermuisknop te klikken op pgAgent-taken en Maken en vervolgens pgAgent-taak… te selecteren zoals hieronder:

Zodra je dit doet, verschijnt er een prompt getiteld Maak – pgAgent Taak en kun je beginnen met het invullen van de benodigde informatie in het Algemeen tabblad. In dit voorbeeld zullen we de naam sammy_backup gebruiken en zullen we geen Host agent specificeren omdat we deze taak op elk host willen kunnen uitvoeren. Bovendien zullen we de Taakklasse als Routineonderhoud laten. Als je andere opmerkingen wilt toevoegen, voel je vrij om dit te doen in de Opmerking sectie:

Vervolgens navigeer je naar het Stappen tabblad. Klik op het + symbool in de rechterbovenhoek om een stap te maken. In dit voorbeeld zullen we deze stap stap1 noemen. Klik vervolgens op het potlood aan de linkerkant van het prullenbakpictogram om je opties uit te breiden. De Ingeschakeld? knop is standaard ingeschakeld en geeft aan dat deze stap wordt opgenomen wanneer deze taak wordt uitgevoerd.

Voor de Soort optie kunt u kiezen tussen SQL of Batch, hier hebben we Batch geselecteerd. De reden dat u Batch wilt kiezen in dit voorbeeld is omdat dit de juiste PostgreSQL-opdrachten zal uitvoeren die u heeft ingesteld voor de back-ups die u wilt plannen voor uw database. De SQL optie is beschikbaar voor het plannen van een taak om ruwe SQL uit te voeren. In dit geval hebben we Lokaal geselecteerd voor Verbindingstype zodat de stap wordt uitgevoerd op de lokale server, maar als u dat liever heeft, kunt u ook Extern kiezen voor een extern host naar keuze. Als u ervoor kiest om dit op een extern host te doen, moet u dat criterium specificeren in het Verbindingsreeks veld. Als u Stap 1 hebt gevolgd, is uw verbindingsreeks al ingesteld en verbonden.

Voor het Database veld, zorg ervoor dat u de juiste database hebt geselecteerd, hier hebben we sammy gespecificeerd. Met de Bij fout optie kunt u de pgAgent-reactie aanpassen als er een fout optreedt bij het uitvoeren van een stap. In dit geval hebben we Mislukken geselecteerd om ons te waarschuwen als er een fout optreedt wanneer een stap wordt verwerkt. Als u nog extra opmerkingen wilt toevoegen, kunt u deze toevoegen in het Opmerking vak.

Binnen hetzelfde Stappen-tabblad is er ook een Code-tabblad. Als je Batch hebt geselecteerd zoals we in dit voorbeeld hebben gedaan, navigeer dan naar dat Code-tabblad. Eenmaal in dit tabblad, is er een lege regel waar je je PostgreSQL-opdracht kunt invoegen. Je kunt hier je eigen back-upopdracht vervangen door je eigen set aangepaste opties. Elke geldige opdracht is acceptabel.

Deze tutorial zal de pg_dump-opdracht gebruiken om je Postgres-database sammy te back-uppen. In deze opdracht moet je je specifieke gebruikersnaam, de databasenaam, en de --clean-vlag opnemen, die helpt met pg_dump door de databaseobjecten te laten vallen of te “schoonmaken” voordat eventuele opdrachten worden uitgevoerd die worden gemaakt. Voor de --file-vlag geef je de exacte locatie op waar de back-upbestanden zullen worden opgeslagen. Het laatste deel van deze verklaring date +%Y-%m-%d-%H-%M-%S is om dynamisch een datum te genereren en meerdere bestanden voor elke back-up. Anders zal het back-upbestand constant overschreven worden en het bestaande overschrijven. Op deze manier kun je elk van je back-upbestanden bijhouden voor elke opgegeven tijd of datum die je hebt gepland. Je volledige opdracht zal als volgt zijn:

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

Zodra je je back-upcommando hebt toegevoegd, kun je naar het tabblad Schema’s navigeren. Net als bij het instellen van Stappen, klik je op het +-symbool om een schema toe te voegen, geef dan je voorkeursnaam op en klik op het potloodpictogram naast het prullenbakpictogram om je opties uit te breiden. Onder het Algemeen-tabblad staat de Naam die je hebt geschreven, in dit voorbeeld is het schema1. Opnieuw, voor Ingeschakeld staat dit standaard op de aan-stand om ervoor te zorgen dat het schema correct wordt uitgevoerd. Voor de Start– en Eind-opties, geef de start- en einddatum en -tijd op voor je geplande taak. Omdat je je geplande taak gaat testen, zorg ervoor dat de huidige tijd binnen het bereik van Start en Eind valt. Voeg een opmerking toe in Opmerking als je dat wilt:

Ga nu naar het Herhaal tabblad. Hier kunt u aanpassen hoe vaak u wilt dat deze geplande taak wordt uitgevoerd. U kunt zo specifiek mogelijk zijn met de week, maand, datum, uren of minuten. Let op, als u geen selectie maakt, is dit hetzelfde als Alles selecteren kiezen. Als u bijvoorbeeld de Weekdagen leeg laat, wordt uw planning rekening gehouden met alle weekdagen. Op dezelfde manier kunt u met Tijden de uren of minuten leeg laten, en dit komt overeen met Alles selecteren. Houd er rekening mee dat tijden in cron-stijl worden weergegeven, dus voor dit voorbeeld moet u elke minuut in een uur selecteren (00 tot 59). Ter illustratie hebben we Alles selecteren voor minuten gekozen. Alle minuten worden opgesomd, maar het leeg laten zal ook hetzelfde resultaat opleveren:

Als er dagen of tijden zijn waarop u geen taak wilt uitvoeren, kunt u een gedetailleerder tijdschema maken, of u kunt dit instellen door naar het Uitzonderingen tabblad te navigeren.

Wanneer je klaar bent met het instellen en aanpassen van het schema dat je wilt uitvoeren, druk dan op de Opslaan knop. Een nieuwe pgAgent-taak zal verschijnen in de boomstructuur aan de linkerkant met de naam van je taak. Voor dit voorbeeld verschijnt sammy_backup met de Schema’s en Stappen eronder vermeld:

Nu je succesvol een pgAgent-taak hebt aangemaakt, zal je in de volgende stap leren hoe je kunt controleren of je pgAgent-taak succesvol wordt uitgevoerd.

Stap 5 — Verifiëren van je pgAgent-taak

Je kunt op verschillende manieren controleren of je geplande taak om elk minuut een back-upbestand van je database te maken werkt. In pgAdmin kun je naar de boomstructuur aan de linkerkant navigeren en klikken op sammy_backup. Van daaruit ga je naar het tabblad met de naam Statistieken. De pagina Statistieken zal elke keer dat je geplande taak wordt uitgevoerd, vermelden zoals volgt:

Houd er rekening mee dat de statistieken mogelijk niet onmiddellijk verschijnen of vernieuwen, dus je moet mogelijk ergens anders naartoe navigeren of de browser vernieuwen. Onthoud dat je taak is gepland om met regelmatige tussenpozen uit te voeren, dus houd hier rekening mee als je een datum of tijd instelt die periodiek is of een langere duur heeft.

Als u liever wilt controleren of uw taak werkt vanaf de opdrachtregel, kunt u het ls ~ commando uitvoeren om uw thuismapbestanden op te lijsten. In deze zelfstudie stelt u de exacte locatie in op deze thuismap bij het schrijven van het pg_dump commando in Stap 4:

ls ~

De uitvoer zal elke instantie van de back-upbestanden tonen die worden opgeslagen.

Stap 6 — Wijzigen, Uitschakelen, Verwijderen en Handmatig Uitvoeren van pgAgent Taken (Optioneel)

pgAgent biedt flexibiliteit als het gaat om het wijzigen of uitschakelen van een taak. Met pgAdmin kunt u navigeren naar de specifieke pgAgent-taak, in dit geval sammy_backup. Klik vervolgens met de rechtermuisknop en kies de Eigenschappen optie uit de lijst. Vanaf hier kunt u op het potloodpictogram bovenaan drukken om wijzigingen aan te brengen in uw taak:

Als u in Eigenschappen bent en op het potloodpictogram drukt, kunt u naar het Schema’s tabblad navigeren om uw taak uit te schakelen zoals hieronder:

Daarnaast kunt u vanuit het boombeheer aan de linkerkant met de rechtermuisknop op de pgAgent-taak klikken en de selectie DELETE/DROP selecteren om de taak volledig te verwijderen. Als u een taak handmatig wilt uitvoeren, kunt u dezelfde stap herhalen, maar dit keer de selectie RUN NOW kiezen uit de lijst:

Hoewel dit slechts een paar functies zijn voor het plannen van taken met pgAgent, zijn er vele andere mogelijkheden. Zo kunt u bijvoorbeeld een taak plannen die een back-up maakt voor alle gebruikers en rollen in uw database, specifiek met het pg_dumpall --globals only commando. U kunt zelfs complexere scripts gebruiken met batchtaken, waar u meer over kunt leren op de PostgreSQL wiki pagina.

Conclusie

pgAgent is handig voor het plannen van verschillende taken die kunnen helpen bij het verminderen van enkele van uw meer alledaagse maar noodzakelijke taken, zoals het consistent back-uppen van uw database. Zodra u meer vertrouwd bent met het gebruik van deze tool, kunt u zelfs proberen een meerstaps taak te maken of SQL-taken die complexe planning vereisen. Als u meer wilt leren, kunt u de officiële pgAgent-documentatie lezen op de website van pgAdmin.