Aan de slag met PostgreSQL op Mac OSX

I. Introductie

Onlangs begon ik aan een nieuw Node.js project en wilde ik PostgreSQL gebruiken als mijn database backend. Als je deze tutorial leest, probeer je hopelijk hetzelfde te doen!

Deze tutorial leert je hoe je PostgreSQL kunt instellen, configureren en gebruiken op MacOSX 10.7 (Lion) en hoger. U zult op zijn minst een basisniveau van comfort nodig hebben in het gebruik van de commandoregel met behulp van de ingebouwde terminal van MacOSX, iTerm2, Zsh, of iets dergelijks. Tegen de tijd dat we klaar zijn, zult u:

• een draaiende PostgreSQL 9.5.4 instance
• weten hoe databases en gebruikers aan te maken en te beheren
• weten hoe zowel command-line als UI tools te gebruiken om uw database te beheren

II. Over PostgreSQL

PostgreSQL is een ACID-compliant Object Relational Database Management System, of ORDBMS (een hele mond vol!). Simpel gezegd, het is een database die je in staat stelt om een stuk data te relateren aan een ander stuk data (dus “relationeel”). Het draait op bijna elk besturingssysteem, inclusief Linux, Unix en Windows. Het is een krachtige en zeer schaalbare database, die grote hoeveelheden gegevens en internetapplicaties met een hoge belasting en duizenden gelijktijdige gebruikers aankan. De unieke combinatie van eenvoud en kracht maakt het een populaire keuze voor individuele gebruikers en kleine bedrijven, maar ook voor bedrijven als Yahoo, Uber, Disqus, en TripAdvisor.

Postgres ondersteunt een lange lijst van database functies, waaronder een aantal enterprise functies. Naast de standaard relationele database-functies, zijn enkele van de meest opvallende functies in Postgres:

• Streaming replicatie
• Schema’s
• Gebruiker gedefinieerde objecten zoals operatoren, datatypes, en functies
• Nested transacties
• Tabel overerving
• Partitionering
• Een aantal ongebruikelijke datatypes, zoals Geld, Geometrie, IP-adressen, JSON, en data ranges.
• Kan opgeslagen procedures uitvoeren in meer dan een dozijn programmeertalen, waaronder Java, Perl, Python, Ruby, en C/C++.

Dankzij zijn eersteklas ondersteuning voor JSON, is Postgres vaak een goed alternatief voor “No-SQL” databases zoals MongoDB. Lees meer over PostgreSQL

III. Aan de slag

Het eerste wat we gaan doen is Postgres installeren. Er zijn twee manieren om Postgres op uw computer te krijgen:

1. Een grafisch installatieprogramma zoals BigSQL of Postgres.app
2. Een package manager gebruiken om via de opdrachtregel te installeren.

U kunt de optie kiezen die het beste bij u past. Voor deze tutorial, laten we eens kijken hoe te installeren op de command line.

1. Homebrew

Om PostgreSQL op de command line te installeren zullen we een package manager genaamd Homebrew gebruiken. Als u Homebrew al geïnstalleerd heeft, kunt u naar PostgreSQL installeren gaan. Anders gaan we verder.

Laten we beginnen met het kopiëren en plakken van het volgende commando in onze commandoregel:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Maak je geen zorgen over wat dat allemaal doet- zolang je MacOSX 10.7 of hoger gebruikt, is de Ruby interpreter al voorgeïnstalleerd. De rest van dit commando (het curl commando) is een hulpmiddel om webverzoeken te doen vanaf je commandoregel. Deze download het script op de gegeven URL, en dan voert de Ruby interpreter het uit. U zou uitvoer moeten zien vergelijkbaar met de volgende:

Installeren van Homebrew

Nu we Homebrew hebben, kunnen we verder gaan met het installeren van Postgres!

2. Postgres installeren

Nu we Homebrew hebben, is het installeren van PostgreSQL erg eenvoudig. Voer het volgende commando uit om PostgreSQL te installeren met Homebrew:

brew install postgresql

U zou dan de volgende uitvoer moeten zien:

*Installatie *

Homebrew zorgt voor het oplossen van de afhankelijkheden van Postgres, die in dit geval OpenSSL en Readline zijn. Het zorgt er ook voor dat je weet wat de laatste versies zijn, waar je ze kunt krijgen en hoe je ze moet uitpakken. Het belangrijkste zijn de gemakkelijk te missen instructies helemaal aan het eind van de installatie:

Services starten

Laten we nu Postgres gaan draaien, en ervoor zorgen dat Postgres iedere keer dat uw computer opstart, wordt gestart. Voer het volgende commando uit:

pg_ctl -D /usr/local/var/postgres start && brew services start postgresql

Tot slot, laten we ervoor zorgen dat Postgres is geïnstalleerd en draait. Laten we controleren welke versie wordt uitgevoerd:

postgres -V

Uw uitvoer zou er als volgt uit moeten zien:

Postgres geïnstalleerd

Dat is het! U hebt Postgres geïnstalleerd. Nu zijn we eindelijk klaar om te leren hoe we het kunnen gebruiken!

3. Postgres configureren

Postgres doet zijn best om zichzelf direct bruikbaar te maken zonder dat u iets hoeft te doen. Standaard wordt automatisch de gebruiker postgres aangemaakt. Laten we eens kijken welke andere gebruikers het heeft aangemaakt. Laten we beginnen met het psql hulpprogramma, een hulpprogramma dat met Postgres is geïnstalleerd en waarmee u administratieve functies kunt uitvoeren zonder dat u de eigenlijke SQL commando’s hoeft te kennen.

Begin door het volgende op de opdrachtregel in te voeren:

psql postgres

(Het kan zijn dat u sudo psql postgres moet gebruiken om dit commando te laten werken, afhankelijk van hoe uw systeem is geconfigureerd).

U ziet uitvoer zoals het volgende:

psql uitvoer

Dat is de psql commandoregel. We kunnen nu een commando invoeren om te zien welke gebruikers zijn geïnstalleerd:

postgres=# \du

Onder de dekmantel voert dit commando een SQL query uit (daarover later meer) die alle gebruikers in de database ophaalt. Op mijn machine levert dit het volgende op:

psql gebruikerslijst

We zien de Postgres gebruiker die ik noemde, maar wat is die andere gebruiker, engineerapart? Dit is een van de dingen die Postgres doet om je het leven gemakkelijker te maken als je het voor het eerst installeert. Op de meeste Linux-gebaseerde besturingssystemen is de gebruikersnaam die standaard door alle processen wordt gebruikt, diegene waarmee je bent ingelogd. Je hoeft je gebruikersnaam niet door te geven aan de meeste programma’s. Maar als een bepaald programma, zoals Postgres, uw gebruikersnaam niet geconfigureerd heeft, zal het falen!

Dus als Postgres is geïnstalleerd, maakt het automatisch een database gebruiker aan die overeenkomt met uw gebruikersnaam, zodat u meteen aan de slag kunt.

A. Gebruikers aanmaken

Postgres beheert niet direct gebruikers of groepen, zoals de meeste standaard permissie modellen doen. In plaats daarvan beheert het direct wat het rollen noemt.

Hoewel het zeker handig is dat Postgres een set standaard gebruikers voor u instelt, is het een zeer slecht idee om ze te gebruiken voor iets anders dan lokale ontwikkeling, omdat ze zeer wijd en zijd bekend zijn en, nog belangrijker, het zijn super gebruiker accounts-ze kunnen alles doen, inclusief databases verwijderen. Dit is niet veilig voor een productiedatabase-we hebben gebruikers nodig met beperkte permissies. Dus hoe maken en gebruiken we nieuwe gebruikers (rollen)?

Er zijn twee manieren om dit te doen:

• Direct de CREATE ROLE SQL query uitvoeren op de database
• Gebruik het createuser hulpprogramma dat met Postgres is geïnstalleerd (en dat slechts een omhulsel is voor het uitvoeren van CREATE ROLE).

Laten we eens naar beide gevallen kijken.

A.1. CREATE ROLE met psql

De basissyntaxis voor CREATE ROLE ziet er als volgt uit:

CREATE ROLE username WITH LOGIN PASSWORD 'quoted password' 

Waarbij username de gebruiker is die u wilt aanmaken, en het wachtwoord komt aan het eind tussen aanhalingstekens. We komen later nog op de opties.

Laten we beginnen met opnieuw in te loggen in onze handige psql tool:

psql uitvoer

Nu we hier toch zijn, laten we het wachtwoord instellen voor het standaard postgres account-dat standaard geen wachtwoord heeft.

postgres=# \password postgres

U wordt gevraagd het wachtwoord in te voeren en te bevestigen. Laten we nu onze nieuwe rol aanmaken:

postgres=# CREATE ROLE patrick WITH LOGIN PASSWORD 'Getting started'; postgres=# \du

Uw uitvoer zou er als volgt uit moeten zien:

psql CREATE ROLE

Wacht. De attributenlijst voor de gebruiker patrick is helemaal leeg. Waarom?

Dit is hoe Postgres veilig standaards beheert. Deze gebruiker kan elke database, tabel of rij lezen waar hij rechten voor heeft, maar verder niets – hij kan geen databases maken of beheren en heeft geen admin bevoegdheden. Dit is een goede zaak! Het helpt uw database veilig te houden.

Dus laten we de CREATEDB permissie aan onze nieuwe gebruiker toevoegen om hem toe te staan databases aan te maken:

postgres=# ALTER ROLE patrick CREATEDB; postgres=# \du postgres=# \q # quits

Jouw uitvoer zou er zo uit moeten zien:

psql ALTER ROLE

Documentatie voor CREATE ROLE

Documentatie voor ALTER ROLE

A.2. De createuser utility

Postgres wordt geleverd met een aantal zeer nuttige opdrachtregelhulpprogramma’s die het bovenstaande proces veel eenvoudiger maken. In plaats van in te loggen in psql, SQL queries uit te voeren, en de details van de query statements te moeten kennen, kun je een vertrouwde command line interface gebruiken om dezelfde taken uit te voeren. Een paar van deze tools zijn:

• createuser: maakt een gebruiker aan
• createdb: maakt een database aan
• dropuser: verwijdert een gebruiker
• dropdb: verwijdert een database
• postgres: voert de SQL-server zelf uit (die zagen we hierboven toen we onze Postgres-versie controleerden!)
• pg_dump: dumpt de inhoud van een enkele database naar een bestand
• pg_dumpall: dumpt alle databases naar een bestand
• psql: die herkennen we!

Volledige lijst van client applicaties

Dus laten we createuser gebruiken om hetzelfde te doen wat we hierboven deden: de patrick gebruiker aanmaken:

createuser patrick

Dit creëert de gebruiker patrick met alle standaard attributen, opnieuw, zonder de mogelijkheid om databases aan te maken. Als we de mogelijkheid wilden hebben om een database aan te maken, dan zou je het volgende uitvoeren:

createuser patrick --createdb

Gelukkig genoeg is er geen command line tool om hetzelfde te bereiken als ALTER ROLE. Om gebruikers te wijzigen nadat ze zijn aangemaakt, moet u psql gebruiken.

Documentatie voor createuser

A.3. Samenvatting

Dat was het! Nu is onze nieuwe gebruiker ingesteld en kan hij databases maken. Laten we beginnen met het beheren van onze database met die nieuwe gebruiker.

B. Een database aanmaken

Net als bij het aanmaken van een gebruiker zijn er twee manieren om een database aan te maken:

• Het direct uitvoeren van SQL commando’s met psql
• Het createdb command line utility.

B.1. CREATE DATABASE met psql

De kern SQL syntaxis voor het maken van een database in PostgreSQL is:

CREATE DATABASE databasename;

We zullen hetzelfde proces doorlopen als hierboven:

psql postgres -U patrick

psql user login

U zult merken dat de prompt iets anders is – de # is veranderd in een >. Dit geeft aan dat u niet langer een Super User-account gebruikt.

postgres=> CREATE DATABASE super_awesome_application;

Documentatie voor CREATE DATABASE

Als dit is gebeurd, moet u ten minste één gebruiker toevoegen die toestemming heeft om de database te benaderen (afgezien van de supergebruikers, die overal toegang toe hebben). Om dat te doen, gaan we een nieuw SQL-commando leren:

postgres=> GRANT ALL PRIVILEGES ON DATABASE super_awesome_application TO patrick; postgres=> \list postgres=> \connect super_awesome_application postgres=> \dt postgres=> \q

Documentatie voor GRANT

Hier heb ik u ook een paar nieuwe commando’s laten zien die u kunt gebruiken met psql:

• \list: geeft een lijst van alle databases in Postgres
• \connect: maak verbinding met een specifieke database
• \dt: lijst met tabellen in de momenteel verbonden database

psql CREATE DATABASE

En dat is het.

U kunt nu gegevens aanmaken, lezen, bijwerken en verwijderen in onze super_awesome_application database met de gebruiker patrick!

B.2. De createdb Utility

Net als hierboven is het maken van een database met createdb eenvoudiger, met het voorbehoud dat u de database niet kunt beheren nadat hij is gemaakt. Daarvoor is psql nodig.

createdb super_awesome_application -U patrick

Hier heb ik het createdb hulpprogramma aangeroepen en het de patrick gebruiker doorgegeven om te gebruiken voor het verbinden met de database.

Het is zeer, zeer zeldzaam dat u een database wilt wijzigen nadat deze is gemaakt. De enige dingen die u kunt veranderen zijn de naam en enkele configuratieparameters. Het veranderen van configuratie parameters (zoals collation of character sets) heeft implicaties die ver buiten deze tutorial gaan. We kunnen echter wel de naam veranderen als we dat willen.

Ook hier geldt dat er geen command line tool is om een database te veranderen als hij eenmaal is gemaakt. We moeten psql gebruiken:

psql postgres -U patrick postgres=> ALTER DATABASE super_awesome_application RENAME TO even_more_awesome_application; postgres=> \q

En de uitvoer zou er ongeveer zo uit moeten zien:

psql ALTER DATABASE

Documentatie voor ALTER DATABASE

B.3. Samenvatting

Dat was het! We hebben een gebruiker aangemaakt, een database gemaakt, en de gebruiker rechten gegeven om de database te gebruiken. Onderweg hebben we geleerd om enkele van de voorgeïnstalleerde Postgres command line tools te gebruiken. Dit geeft ons een goede basis voor het begrijpen van complexer gedrag in de volgende sectie.

Nu gaan we eens kijken naar een aantal populaire grafische tools voor het beheren van PostgreSQL op MacOSX.

IV. Populaire GUIs voor PostgreSQL op MacOSX

Tot nu toe hebben we ons vrij sterk gericht op de command line tools voor het beheren van onze Postgres installatie. Echter, voor sommige dingen is dat omslachtig en tijd-intensief: Als je bijvoorbeeld een snel overzicht wilt hebben van de gegevens in een tabel, kost het meer tijd om dat uit de command line tools te halen dan om een GUI te gebruiken. Doe je dat 100 keer per dag tijdens het ontwikkelen, dan begint die tijd op te lopen!

Dus natuurlijk heeft een groep ondernemende ontwikkelaars een aantal indrukwekkende GUIs gebouwd die gebruikt kunnen worden om je lokale (en remote!) Postgres servers te beheren. We zullen eens kijken naar een paar van de meest populaire en gebruiksvriendelijke.

1. Postico (https://eggerapps.at/postico/)

Postico is een moderne Postgres client voor OSX, gebouwd door dezelfde ontwikkelaar die Postgres.app (hierboven genoemd) heeft gebouwd. Het is gratis, maar je kunt een licentie kopen om extra power features te ontgrendelen. Dit is de GUI die ik gebruik om Postgres te beheren, omdat hij speciaal voor Mac is gebouwd en een mooie, zeer eenvoudig te gebruiken (maar krachtige) UI heeft. Het bevat ook een SQL editor voor complexe queries.

Om met Postico aan de slag te gaan, volstaat het volgende:

• Download het op https://eggerapps.at/postico/download/
• Dubbelklik op het gedownloade Zip-bestand in Finder
• Sleep het uitgepakte Postico.app-bestand naar je Applications-map
• Vind Postico in Launchpad en start de app.

U krijgt een scherm te zien dat er als volgt uitziet (zonder geconfigureerde databaseverbindingen):

Postico

Om verbinding te maken met uw lokale database:

• Klik op ‘Nieuwe Favoriet’
• Geef het een gemakkelijk te onthouden naam
• U kunt de standaardwaarden in de vakjes laten staan
• Als u het postgres gebruikerswachtwoord hierboven heeft gewijzigd, voer het dan in het wachtwoordvak in
• Doe de Options naar beneden en selecteer “Toon alle databases” – anders vraagt u zich af waar uw databases zijn!

Postico nieuwe favoriet

• Klik op ‘Gereed’ om het op te slaan
• Klik vervolgens op de knop ‘Verbinden’.
• U bent klaar!

Postico verbonden

Lees de Postico Documentatie om te leren hoe u de geweldige functies van Postico kunt gebruiken!

2. pgAdmin (https://www.pgadmin.org/)

pgAdmin is de oudste van de Postgres GUIs, de eerste versie werd uitgebracht slechts een paar maanden na Postgre’s eerste release in 1996. Het is meerdere malen herschreven en draait op Linux, MacOSX en Windows. Het bevat krachtige database management tools, waaronder een syntax-highlighted SQL editor. Ontworpen om zowel op client machines als op servers te draaien, kan pgAdmin geavanceerde zaken aan die Postico niet kan.

Om met pgAdmin aan de slag te gaan, volstaat het:

• Download het op https://www.pgadmin.org/download/macos4.php
• Dubbel-klik op de gedownloade disc image (DMG) bestand in uw Downloads map
• Sleep de pgAdmin 4 app naar uw Applications map
• Vind pgAdmin in Launchpad en start de app.

U krijgt een scherm te zien dat er als volgt uitziet:

pgAdmin

Om verbinding te maken met uw lokale database:

• Rechtsklik op ‘Servers’ en selecteer Create => Server
• Geef het een gemakkelijk te onthouden naam en vink het vakje “Nu verbinden” uit
• Voor serveradres, localhost
• U kunt de standaardwaarden in de vakjes laten
• Als u het postgres gebruikerswachtwoord hierboven hebt gewijzigd, voer het dan in het wachtwoordvak in

Postico nieuwe favoriet

• Klik op ‘Opslaan’ om op te slaan
• Jouw nieuwe server verschijnt in de lijst met servers
• Klik op je nieuwe server en voer het wachtwoord nog een keer in om verbinding te maken
• Je bent klaar!

pgAdmin verbonden

Lees de pgAdmin Documentatie om te leren hoe u de geweldige functies van Postico kunt gebruiken!

3. Navicat (https://www.navicat.com/products/navicat-for-postgresql)

Navicat is zonder twijfel een van de beste Enterprise-level management GUIs voor PostgreSQL. Navicat heeft niet alleen een SQL editor en database, tabel en rij weergave en beheer, maar ook een Data Modeler waarmee u grafisch uw database schema’s kunt ontwerpen en debuggen. Hoewel Navicat 14 dagen gratis kan worden uitgeprobeerd, variëren de licenties van $100-$250.

Om met Navicat aan de slag te gaan, hoeft u alleen maar:

• Download het programma op https://www.navicat.com/download/navicat-for-postgresql
• Dubbel-klik op de gedownloade disc image (navicat112pgsqlen.dmg) bestand in uw Downloads map
• Sleep de Navicat app naar uw Applications map
• Zoek Navicat in Launchpad en start de app.

U krijgt een scherm te zien dat er als volgt uitziet:

Navicat

Om verbinding te maken met uw lokale database:

• Klik op “Verbinding”
• Vul het gegeven dialoogvenster in met dezelfde informatie als hierboven
• Klik op “Ok” om de verbinding op te slaan.
• Dubbel-klik op de nieuwe verbinding in de lijst aan de linkerkant, en je bent verbonden. Dat is het!

Navicat connect

Lees de Navicat Online Handleiding om te leren hoe u de verbazingwekkende functies van Postico kunt gebruiken!

V. Samenvatting

PostgreSQL is een van de top relationele databases in de wereld, gebruikt door zowel individuen, kleine bedrijven, als grote ondernemingen. Ondanks zijn hoge performance en krachtige feature set, is het ongelofelijk eenvoudig om ermee aan de slag te gaan. In deze tutorial hebben we geleerd hoe je:

• De PostgreSQL server op MacOSX installeert met behulp van Homebrew
• De Postgres command line tools gebruikt om Postgres te configureren:
  • Gebruik de psql command line tool om informatie over de database te bekijken
  • Maak een nieuwe gebruiker aan met psql en createuser
  • Wijzig het wachtwoord van een gebruiker
  • Maak een database met psql en createdb
  • Een databasenaam wijzigen
  • Toegangsrechten voor een database aan een specifieke gebruiker verlenen

We hebben ook een korte blik geworpen op enkele populaire GUI-gebaseerde management tools voor Postgres, waaronder Postico, pgAdmin en Navicat.

Goed geluk! Veel plezier bij het Postgres-ing!

VI. Referenties

PostgreSQL Homepage
PostgreSQL Documentatie

• Volledige lijst van client applicaties
• Documentatie voor createuser
• Documentatie voor createdb
• Documentatie voor CREATE ROLE
• Documentatie voor ALTER ROLE
• Documentatie voor CREATE DATABASE
• Documentatie voor ALTER DATABASE
• Documentatie voor GRANT

Populaire Postgres GUI Clients

• Postico Documentatie
• pgAdmin Documentatie
• Navicat Online Handleiding