PostgreSQL: Erste Schritte

Wie man mit PostgreSQL beginnt

👋 Willkommen in der Stackhero-Dokumentation!

Stackhero bietet eine einsatzbereite PostgreSQL-Cloud-Lösung, die zahlreiche Vorteile bietet, darunter:

  • Unbegrenzte Verbindungen und Datenübertragungen.
  • PgAdmin Web-UI inklusive.
  • Viele Module enthalten wie PostGIS, TimescaleDB und PgVector.
  • Mühelose Updates mit nur einem Klick.
  • Optimale Performance und robuste Sicherheit durch eine private und dedizierte VM.

Sparen Sie Zeit und vereinfachen Sie Ihr Leben: Es dauert nur 5 Minuten, um die PostgreSQL-Cloud-Hosting-Lösung von Stackhero auszuprobieren!

Verwendung der PostgreSQL psql CLI

Sie können PostgreSQL mit der offiziellen psql CLI verwalten. Diese ist zur Installation auf Ihrem Computer verfügbar und ermöglicht die Verwaltung Ihres PostgreSQL-Dienstes aus der Ferne. Alternativ können Sie sie über Docker ausführen. Diese Methode wird oft bevorzugt, da sie die direkte Installation von psql auf Ihrem Computer vermeidet und den Wechsel zwischen Versionen erleichtert.

Um psql mit Docker zu nutzen, führen Sie den folgenden Befehl aus (stellen Sie sicher, dass Sie 18 durch die Hauptversionsnummer Ihres PostgreSQL-Dienstes ersetzen):

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

Verbinden Sie sich dann mit Ihrem PostgreSQL-Dienst mit:

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

Erstellen eines Benutzers und einer Datenbank in PostgreSQL

Standardmäßig wird ein admin-Benutzer mit administrativen Rechten erstellt. Es ist eine gute Praxis, für jedes Projekt, das Sie hosten möchten, einen dedizierten Benutzer und eine Datenbank zu erstellen.

Verwendung der PgAdmin-Weboberfläche

Um die PgAdmin-Weboberfläche zu nutzen, öffnen Sie Ihre PostgreSQL-Domain mit HTTPS (zum Beispiel https://<XXXXXX>.stackhero-network.com). Melden Sie sich mit admin als Benutzername und dem Passwort an, das Sie in Ihrer Dienstkonfiguration festgelegt haben (sichtbar in Ihrem Stackhero-Dashboard).

Erstellen eines Benutzers

  1. Gehen Sie zu Servers / PostgreSQL, klicken Sie mit der rechten Maustaste auf Login/Group Roles und wählen Sie Create / Login/Group Role:

    Erstellen eines Benutzers in PostgreSQL mit PgAdminErstellen eines Benutzers in PostgreSQL mit PgAdmin

  2. Legen Sie den Anmeldenamen fest:

    Benutzeranmeldung definierenBenutzeranmeldung definieren

  3. Legen Sie ein sicheres Passwort fest, um Brute-Force-Angriffe zu vermeiden:

    Benutzerpasswort definierenBenutzerpasswort definieren

  4. Stellen Sie schließlich sicher, dass nur das Privileg "Can login" ausgewählt ist:

    Benutzerrechte definierenBenutzerrechte definieren

Klicken Sie auf die Schaltfläche "Save", um Ihren Benutzer zu erstellen.

Erstellen einer Datenbank

  1. Gehen Sie zu Servers / postgresql, klicken Sie mit der rechten Maustaste auf Databases und wählen Sie Create / Database...:

    Erstellen einer Datenbank mit PgAdminErstellen einer Datenbank mit PgAdmin

Es ist eine gute Praxis, denselben Namen für die Datenbank und den Benutzer zu verwenden. Wenn Ihr Projekt zum Beispiel "superWebsite" heißt, sollten Sie einen Benutzer namens "superWebsite" und eine Datenbank namens "superWebsite" erstellen.

  1. Legen Sie den Datenbanknamen fest und wählen Sie den Besitzer (den Benutzer, den Sie gerade erstellt haben):

    Datenbankname und Besitzer definierenDatenbankname und Besitzer definieren

Ihre Datenbank ist jetzt erstellt.

Verwendung der psql CLI

Erstellen eines Benutzers

Um einen Benutzer in PostgreSQL mit der psql CLI zu erstellen, führen Sie die folgende SQL-Abfrage aus:

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

Vergessen Sie nicht, myProject durch Ihren Projektnamen und secretPassword durch ein sicheres Passwort zu ersetzen. Es ist auch eine gute Praxis, Ihren Projektnamen sowohl als Anmeldenamen als auch als Datenbanknamen zu verwenden. Wenn Ihr Projekt "superWebsite" heißt, sollten Sie einen Benutzer namens "superWebsite" und eine Datenbank namens "superWebsite" erstellen.

Sie können ein sicheres Passwort mit diesem Befehl generieren: openssl rand -base64 24 | tr -d '\n' | cut -c1-32

Erstellen einer Datenbank

Um eine Datenbank in PostgreSQL mit der psql CLI zu erstellen, führen Sie die folgende SQL-Abfrage aus:

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

Es ist eine gute Praxis, denselben Namen für die Datenbank und den Benutzer zu verwenden. Wenn Ihr Projekt zum Beispiel "superWebsite" heißt, sollten Sie einen Benutzer namens "superWebsite" und eine Datenbank namens "superWebsite" erstellen.

Importieren von Daten von Ihrem Computer nach PostgreSQL

Eine der einfachsten Möglichkeiten, Daten von Ihrem Computer in Ihre PostgreSQL-Instanz zu importieren, ist die Verwendung der PostgreSQL CLI. Führen Sie auf Ihrem Computer den folgenden Befehl aus (ersetzen Sie <DB_NAME> durch den Namen Ihrer Datenbank und data.pgsql durch den Namen Ihrer SQL-Datei):

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

Exportieren von Daten von PostgreSQL auf Ihren Computer

Das Exportieren von Daten von Ihrer PostgreSQL-Instanz auf Ihren Computer ist ebenso einfach mit der PostgreSQL CLI.

  1. Um die gesamte Datenbank zu exportieren (ersetzen Sie <DB_NAME> durch den Namen Ihrer Datenbank):

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

  2. Um eine einzelne Tabelle zu exportieren (ersetzen Sie <DB_NAME> durch den Namen Ihrer Datenbank und <TABLE_NAME> durch den Namen Ihrer Tabelle):

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

Verwendung von PostgreSQL mit Node.js

Um PostgreSQL mit Node.js und dem pg-Paket (auch bekannt als node-postgres) zu verbinden, können Sie das folgende Beispiel in Betracht ziehen. Beachten Sie das ssl-Argument, das wichtig ist, um TLS-Verschlüsselung sicherzustellen:

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('🐞 Ein Fehler ist aufgetreten!');
  console.error(error);
  process.exit(1);
});

Ein vollständiges Beispiel zur Verwendung von PostgreSQL mit Node.js und der pg-Bibliothek (und async/await) ist in diesem Git-Repository verfügbar: https://github.com/stackhero-io/postgresqlGettingStarted.

Verwendung von PostgreSQL mit Node.js/TypeORM

Um sich von Node.js mit TypeORM mit PostgreSQL zu verbinden, setzen Sie das ssl-Flag auf true, wie im folgenden Beispiel gezeigt:

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

Verwendung von PostgreSQL mit Django

Falls noch nicht installiert, installieren Sie das psycopg-Modul, das zur Verbindung mit PostgreSQL verwendet wird:

pip install psycopg

In diesem ersten Schritt speichern Sie das Passwort direkt in der settings.py-Datei. Diese Methode ist nur für Tests gedacht, da sie nicht sicher ist. Weiter in dieser Dokumentation finden Sie ein Beispiel für bewährte Praktiken.

Öffnen Sie die settings.py-Datei und fügen Sie die folgende Konfiguration hinzu:

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

Achtung: Dieses Beispiel wird nicht für die Produktion empfohlen und ist nur für Testzwecke gedacht!

Sobald Ihre Verbindung funktioniert, können Sie eine sicherere Methode zur Speicherung von Anmeldeinformationen übernehmen. Das folgende Beispiel verwendet django-environ und speichert Anmeldeinformationen in einer .env-Datei.

  1. Installieren Sie django-environ:

    pip install django-environ

  2. Öffnen Sie die settings.py-Datei und ändern Sie sie wie folgt:

    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. Öffnen oder erstellen Sie die .env-Datei im selben Verzeichnis wie settings.py und fügen Sie hinzu:

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

  4. Fügen Sie schließlich .env zu Ihrer .gitignore-Datei hinzu, um sicherzustellen, dass Ihre Anmeldeinformationen nicht in Ihrem Git-Repository gespeichert werden:

echo ".env" >> .gitignore

Aktivierung der PostgreSQL PostGIS-Erweiterung

Die PostGIS-Erweiterung ist in unserem PostgreSQL-Dienst enthalten. Sie müssen sie in jeder Datenbank aktivieren, in der Sie sie verwenden möchten.

Um die Erweiterung zu aktivieren, verbinden Sie sich mit Ihrer Datenbank und führen Sie diese Abfrage aus:

CREATE EXTENSION postgis;

Sie können überprüfen, ob PostGIS funktioniert, indem Sie seine Version überprüfen:

SELECT PostGIS_Full_Version();

Alternativ können Sie eine Liste aller installierten PostGIS-Erweiterungen abrufen:

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

Falls erforderlich, können Sie weitere Erweiterungen hinzufügen. Es wird jedoch dringend empfohlen, keine Erweiterungen zu aktivieren, die Sie nicht verwenden werden:

-- PostGIS aktivieren
CREATE EXTENSION postgis;

-- Rasterunterstützung aktivieren
CREATE EXTENSION postgis_raster;

-- Topologie aktivieren
CREATE EXTENSION postgis_topology;

-- Fuzzy-Matching für Tiger erforderlich
CREATE EXTENSION fuzzystrmatch;

-- Regelbasierter Standardisierer
CREATE EXTENSION address_standardizer;

-- Beispieldatensatz für Regel
CREATE EXTENSION address_standardizer_data_us;

-- US Tiger Geocoder aktivieren
CREATE EXTENSION postgis_tiger_geocoder;

Vorsicht: Aktivieren Sie PostGIS nicht in der Datenbank postgres!

Deaktivierung der PostgreSQL PostGIS-Erweiterung

Um PostGIS aus einer Datenbank zu entfernen, verbinden Sie sich mit der betreffenden Datenbank und führen Sie die folgende Abfrage aus:

DROP EXTENSION postgis;

Aktivierung der PgVector-Erweiterung

Die Aktivierung der PgVector-Erweiterung auf Stackhero ist einfach. Führen Sie die folgende Abfrage aus:

CREATE EXTENSION vector;

Aktivierung der TimescaleDB-Erweiterung

Die Aktivierung der TimescaleDB-Erweiterung auf Stackhero ist einfach. Führen Sie die folgende Abfrage aus:

CREATE EXTENSION timescaledb;

Verfolgung von Statistiken zur SQL-Planung und -Ausführung (pg_stat_statements)

Um die Unterstützung für pg_stat_statements zu aktivieren, aktivieren Sie sie zuerst in Ihrer PostgreSQL-Konfiguration über das Stackhero-Dashboard. Aktivieren Sie dann die Erweiterung für Ihre aktuelle Datenbank, indem Sie ausführen:

CREATE EXTENSION pg_stat_statements;

Für weitere Informationen lesen Sie bitte die offizielle PostgreSQL-Dokumentation.

Konfiguration von PostgreSQL mit Skyvia

Die Verwendung von Skyvia mit Stackhero für PostgreSQL ist einfach. Stellen Sie eine sichere Verbindung sicher, indem Sie die folgenden Einstellungen konfigurieren:

  1. Setzen Sie Protocol auf SSL
  2. Setzen Sie Port auf Ihren PostgreSQL-Port
  3. Erzwingen Sie die Verschlüsselung, indem Sie SSL Mode auf Require setzen
  4. Wählen Sie ein SSL TLS Protocol von 1.2 oder höher

Für weitere Details zur Skyvia-Konfiguration lesen Sie bitte die offizielle Skyvia-Dokumentation.

Beispiel für die Skyvia-Konfiguration mit Stackhero für PostgreSQLBeispiel für die Skyvia-Konfiguration mit Stackhero für PostgreSQL

Other articles for PostgreSQL