PostgreSQL: Aan de slag

Hoe te beginnen met PostgreSQL

👋 Welkom bij de Stackhero-documentatie!

Stackhero biedt een gebruiksklare PostgreSQL cloud oplossing die tal van voordelen biedt, waaronder:

  • Onbeperkte verbindingen en datatransfers.
  • PgAdmin webinterface inbegrepen.
  • Veel modules inbegrepen zoals PostGIS, TimescaleDB en PgVector.
  • Moeiteloze updates met slechts één klik.
  • Optimale prestaties en robuuste beveiliging aangedreven door een privé en toegewijde VM.

Bespaar tijd en vereenvoudig uw leven: het kost slechts 5 minuten om de PostgreSQL cloud hosting oplossing van Stackhero te proberen!

Gebruik van de PostgreSQL psql CLI

U kunt PostgreSQL beheren met de officiële psql CLI. Deze is beschikbaar voor installatie op uw computer, zodat u uw PostgreSQL-service op afstand kunt beheren. U kunt het ook via Docker uitvoeren. Deze methode wordt vaak geprefereerd omdat het installeren van psql direct op uw computer wordt vermeden en het gemakkelijker maakt om tussen versies te schakelen.

Om toegang te krijgen tot psql met Docker, voert u de volgende opdracht uit (zorg ervoor dat u 18 vervangt door het hoofdversienummer van uw PostgreSQL-service):

docker run -v $(pwd):/mnt -it postgres:18-alpine /bin/bash

Verbind vervolgens met uw PostgreSQL-service met behulp van:

cd /mnt
psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=admin

Een gebruiker en database maken in PostgreSQL

Standaard wordt er een admin gebruiker aangemaakt met administratieve rechten. Het is een goede gewoonte om een toegewijde gebruiker en database te maken voor elk project dat u van plan bent te hosten.

Gebruik van de PgAdmin webinterface

Om de PgAdmin webinterface te gebruiken, opent u uw PostgreSQL-domein met HTTPS (bijvoorbeeld, https://<XXXXXX>.stackhero-network.com). Log in met admin als gebruikersnaam en het wachtwoord dat u hebt ingesteld in uw serviceconfiguratie (zichtbaar in uw Stackhero-dashboard).

Een gebruiker aanmaken

  1. Ga naar Servers / PostgreSQL, klik met de rechtermuisknop op Login/Group Roles, en selecteer Create / Login/Group Role:

    Maak een gebruiker in PostgreSQL met PgAdminMaak een gebruiker in PostgreSQL met PgAdmin

  2. Stel de inlognaam in:

    Definieer gebruikersloginDefinieer gebruikerslogin

  3. Stel een veilig wachtwoord in om brute force-aanvallen te voorkomen:

    Definieer gebruikerswachtwoordDefinieer gebruikerswachtwoord

  4. Zorg er ten slotte voor dat alleen het privilege "Can login" is geselecteerd:

    Definieer gebruikersrechtenDefinieer gebruikersrechten

Klik op de knop "Save" om uw gebruiker aan te maken.

Een database aanmaken

  1. Ga naar Servers / postgresql, klik met de rechtermuisknop op Databases, en selecteer Create / Database...:

    Maak een database met PgAdminMaak een database met PgAdmin

Het is een goede gewoonte om dezelfde naam te gebruiken voor zowel de database als de gebruiker. Bijvoorbeeld, als uw project "superWebsite" heet, overweeg dan een gebruiker genaamd "superWebsite" en een database genaamd "superWebsite" aan te maken.

  1. Stel de databasenaam in en kies de eigenaar (de gebruiker die u zojuist hebt aangemaakt):

    Definieer databasenaam en eigenaarDefinieer databasenaam en eigenaar

Uw database is nu aangemaakt.

Gebruik van de psql CLI

Een gebruiker aanmaken

Om een gebruiker op PostgreSQL aan te maken met behulp van de psql CLI, voert u de volgende SQL-query uit:

CREATE ROLE "myProject" WITH
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT
  NOREPLICATION
  CONNECTION LIMIT -1
  PASSWORD 'secretPassword';

Vergeet niet myProject te vervangen door uw projectnaam en secretPassword door een veilig wachtwoord. Het is ook een goede gewoonte om uw projectnaam te gebruiken als zowel de login- als databasenaam. Als uw projectnaam "superWebsite" is, overweeg dan een gebruiker genaamd "superWebsite" en een database genaamd "superWebsite" aan te maken.

U kunt een veilig wachtwoord genereren met deze opdrachtregel: openssl rand -base64 24 | tr -d '\n' | cut -c1-32

Een database aanmaken

Om een database op PostgreSQL aan te maken met behulp van de psql CLI, voert u de volgende SQL-query uit:

CREATE DATABASE "myProject"
  WITH
  OWNER = "myProject"
  ENCODING = 'UTF8'
  CONNECTION LIMIT = -1
  IS_TEMPLATE = false;

Het is een goede gewoonte om dezelfde naam te gebruiken voor zowel de database als de gebruiker. Bijvoorbeeld, als uw projectnaam "superWebsite" is, overweeg dan een gebruiker genaamd "superWebsite" en een database genaamd "superWebsite" aan te maken.

Gegevens importeren van uw computer naar PostgreSQL

Een van de eenvoudigste manieren om gegevens van uw computer naar uw PostgreSQL-instantie te importeren, is door de PostgreSQL CLI te gebruiken. Voer op uw computer de volgende opdracht uit (vervang <DB_NAME> door uw databasenaam en data.pgsql door de naam van uw SQL-bestand):

psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  < data.pgsql

Gegevens exporteren van PostgreSQL naar uw computer

Gegevens exporteren van uw PostgreSQL-instantie naar uw computer is net zo eenvoudig met behulp van de PostgreSQL CLI.

  1. Om de gehele database te exporteren (vervang <DB_NAME> door uw databasenaam):

    pg_dump \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  > data.pgsql

  2. Om een enkele tabel te exporteren (vervang <DB_NAME> door uw databasenaam en <TABLE_NAME> door uw tabelnaam):

    pg_dump \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  --table=<TABLE_NAME> \
  > data.pgsql

Gebruik van PostgreSQL met Node.js

Om PostgreSQL te verbinden met Node.js en het pg pakket (ook bekend als node-postgres), kunt u het volgende voorbeeld overwegen. Let op het ssl argument, dat belangrijk is voor het waarborgen van TLS-encryptie:

const { Client } = require('pg');

(async () => {
  const pg = new Client({
    host: '<XXXXXX>.stackhero-network.com',
    port: <PORT>,
    user: '<USERNAME>',
    password: '<PASSWORD>',
    database: '<DATABASE>',
    ssl: {}
  });

  await pg.connect();

  const result = await pg.query('SELECT 1');

  await pg.end();
})().catch(error => {
  console.error('');
  console.error('🐞 Er is een fout opgetreden!');
  console.error(error);
  process.exit(1);
});

Een volledig voorbeeld van het gebruik van PostgreSQL met Node.js en de pg bibliotheek (en async/await) is beschikbaar op deze Git-repository: https://github.com/stackhero-io/postgresqlGettingStarted.

Gebruik van PostgreSQL met Node.js/TypeORM

Om verbinding te maken met PostgreSQL vanuit Node.js met behulp van TypeORM, stelt u de ssl vlag in op true zoals getoond in het onderstaande voorbeeld:

createConnection({
  type: 'postgres',
  host: '<XXXXXX>.stackhero-network.com',
  port: <PORT>,
  username: '<USERNAME>',
  password: '<PASSWORD>',
  database: '<DATABASE>',
  extra: {
    ssl: true
  }
});

Gebruik van PostgreSQL met Django

Als het nog niet is geïnstalleerd, installeer dan de psycopg module, die zal worden gebruikt om verbinding te maken met PostgreSQL:

pip install psycopg

In deze eerste stap slaat u het wachtwoord direct op in het settings.py bestand. Deze methode is alleen voor testen omdat het niet veilig is. Later in deze documentatie vindt u een voorbeeld van best practice.

Open het settings.py bestand en voeg de volgende configuratie toe:

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'HOST': '<XXXXXX>.stackhero-network.com',
    'PORT': <PORT>,
    'OPTIONS': {
      'sslmode': 'require',
    },
    'NAME': 'admin',
    'USER': 'admin',
    'PASSWORD': '<ADMIN_PASSWORD>'
  }
}

Let op: dit voorbeeld wordt niet aanbevolen voor productie en is alleen bedoeld voor testdoeleinden!

Zodra uw verbinding werkt, kunt u een veiligere methode aannemen om inloggegevens op te slaan. Het volgende voorbeeld gebruikt django-environ en slaat inloggegevens op in een .env bestand.

  1. Installeer django-environ:

    pip install django-environ

  2. Open het settings.py bestand en wijzig het als volgt:

    import environ
env = environ.Env()
environ.Env.read_env()

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'HOST': env('STACKHERO_POSTGRESQL_HOST'),
    'PORT': <PORT>,
    'OPTIONS': {
      'sslmode': 'require',
    },
    'NAME': 'admin',
    'USER': 'admin',
    'PASSWORD': env('STACKHERO_POSTGRESQL_ADMIN_PASSWORD')
  }
}

  3. Open of maak het .env bestand in dezelfde directory als settings.py en voeg toe:

    STACKHERO_POSTGRESQL_HOST=<XXXXXX>.stackhero-network.com
STACKHERO_POSTGRESQL_ADMIN_PASSWORD=<ADMIN_PASSWORD>

  4. Voeg ten slotte .env toe aan uw .gitignore bestand om ervoor te zorgen dat uw inloggegevens niet in uw Git-repository worden opgeslagen:

echo ".env" >> .gitignore

Activeren van de PostgreSQL PostGIS-extensie

De PostGIS-extensie is inbegrepen bij onze PostgreSQL-service. U moet deze activeren op elke database waar u deze wilt gebruiken.

Om de extensie te activeren, verbindt u met uw database en voert u deze query uit:

CREATE EXTENSION postgis;

U kunt controleren of PostGIS werkt door de versie te controleren:

SELECT PostGIS_Full_Version();

Haal eventueel een lijst op van alle geïnstalleerde PostGIS-extensies:

SELECT * FROM pg_extension WHERE extname LIKE 'postgis%';

Indien nodig kunt u andere extensies toevoegen. Het wordt echter sterk aanbevolen om geen extensies te activeren die u niet zult gebruiken:

-- Activeer PostGIS
CREATE EXTENSION postgis;

-- Activeer rasterondersteuning
CREATE EXTENSION postgis_raster;

-- Activeer Topologie
CREATE EXTENSION postgis_topology;

-- Fuzzy matching nodig voor Tiger
CREATE EXTENSION fuzzystrmatch;

-- Regelgebaseerde standaardisator
CREATE EXTENSION address_standardizer;

-- Voorbeeld dataset voor regels
CREATE EXTENSION address_standardizer_data_us;

-- Activeer US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

Let op: activeer PostGIS niet op de database postgres!

Deactiveren van de PostgreSQL PostGIS-extensie

Om PostGIS van een database te verwijderen, verbindt u met de betreffende database en voert u de volgende query uit:

DROP EXTENSION postgis;

Activeren van de PgVector-extensie

Het activeren van de PgVector extensie op Stackhero is eenvoudig. Voer de volgende query uit:

CREATE EXTENSION vector;

Activeren van de TimescaleDB-extensie

Het activeren van de TimescaleDB extensie op Stackhero is eenvoudig. Voer de volgende query uit:

CREATE EXTENSION timescaledb;

Statistieken van SQL-planning en -uitvoering volgen (pg_stat_statements)

Om ondersteuning voor pg_stat_statements in te schakelen, schakelt u deze eerst in uw PostgreSQL-configuratie in via het Stackhero-dashboard. Schakel vervolgens de extensie in voor uw huidige database door:

CREATE EXTENSION pg_stat_statements;

Voor meer informatie, raadpleeg de officiële PostgreSQL-documentatie.

Configureren van PostgreSQL met Skyvia

Het gebruik van Skyvia met Stackhero voor PostgreSQL is eenvoudig. Zorg voor een veilige verbinding door de volgende instellingen te configureren:

  1. Stel Protocol in op SSL
  2. Stel Port in op uw PostgreSQL-poort
  3. Forceer encryptie door SSL Mode in te stellen op Require
  4. Selecteer een SSL TLS Protocol van 1.2 of hoger

Voor meer details over de configuratie van Skyvia, raadpleeg de officiële documentatie van Skyvia.

Voorbeeld van Skyvia-configuratie met Stackhero voor PostgreSQLVoorbeeld van Skyvia-configuratie met Stackhero voor PostgreSQL

Other articles for PostgreSQL