Wie man Datenbanken in PostgreSQL verwaltet: Ein vollständiger Leitfaden für Linux VPS-Benutzer

PostgreSQL (häufig Postgres genannt) ist eines der leistungsstärksten, funktionsreichsten und quelloffenen relationalen Datenbankmanagementsysteme, die heute verfügbar sind. Von Startups und Enterprise-Teams gleichermaßen vertraut, zeichnet es sich durch die Verarbeitung komplexer Abfragen, großer Datenmengen und skalierbarer Anwendungsarchitekturen aus. Egal ob Sie eine SaaS-Plattform, einen E-Commerce-Shop oder ein datenintensives API-Backend aufbauen – PostgreSQL bietet die Zuverlässigkeit und Leistung, die Ihr Projekt benötigt.

Das Ausführen von PostgreSQL in einer hochperformanten VPS Hosting-Umgebung – mit NVMe SSD-Speicher, vollständigem Root-Zugriff und integriertem DDoS-Schutz – gibt Ihnen vollständige Kontrolle über Ihre Datenbankinfrastruktur ohne den Overhead verwalteter Cloud-Lösungen. Dieser Leitfaden führt Sie durch alle wesentlichen PostgreSQL-Verwaltungsaufgaben, von der anfänglichen Anmeldung und Datenbankerstellung bis hin zu Benutzergenehmigungen, Sicherungen und Sicherheitshärtung.

Voraussetzungen

Stellen Sie vor dem Fortfahren sicher, dass Sie folgende Voraussetzungen erfüllen:

• Ein Linux VPS mit Ubuntu, Debian oder CentOS und installiertem PostgreSQL
• Root- oder Sudo-Zugriff auf Ihren Server
• Grundlegende Vertrautheit mit der Linux-Befehlszeile

Falls Sie Ihre Serverumgebung noch nicht eingerichtet haben, machen AlexHost’s VPS Control Panels es einfach, Ihren Stack schnell zu konfigurieren.

1. Zugriff auf die PostgreSQL-Befehlszeilenschnittstelle

Alle PostgreSQL-Verwaltungsaufgaben beginnen an der Befehlszeilenschnittstelle (CLI), auch bekannt als psql. Standardmäßig erstellt PostgreSQL während der Installation einen Systembenutzer namens postgres, und dieses Konto wird zur Authentifizierung mit der Datenbank-Engine verwendet.

Schritt 1: Zum PostgreSQL-Systembenutzer wechseln

sudo -i -u postgres

Schritt 2: PostgreSQL CLI starten

psql

Nach der Verbindung sehen Sie die interaktive Eingabeaufforderung:

postgres=#

Dies bestätigt, dass Sie sich in der PostgreSQL-Umgebung befinden und SQL-Befehle und Meta-Befehle ausführen können.

> Profi-Tipp: Sie können sich auch direkt verbinden, ohne Benutzer zu wechseln, indem Sie sudo -u postgres psql aus Ihrer regulären Shell-Sitzung ausführen.

2. Neue Datenbank erstellen

Datenbanken sind die Container auf oberster Ebene für alle Ihre Tabellen, Indizes und gespeicherten Daten. Verwenden Sie die CREATE DATABASE-Anweisung, um eine neue Datenbank bereitzustellen.

Syntax

CREATE DATABASE database_name;

Beispiel

CREATE DATABASE my_database;

Dies erstellt eine Datenbank namens my_database, die dem aktuell aktiven PostgreSQL-Benutzer gehört.

Überprüfen Sie, ob die Datenbank erstellt wurde

Verwenden Sie den l-Meta-Befehl, um alle Datenbanken auf dem Server aufzulisten:

l

Sie sehen eine Tabelle mit Datenbanknamen, Besitzern, Kodierungen und Zugriffsprivilegien.

3. Datenbankbenutzer erstellen und verwalten

Eine ordnungsgemäße Benutzerverwaltung ist für die Datenbanksicherheit von entscheidender Bedeutung. Anstatt allen Anwendungen Zugriff unter dem postgres-Superuser-Konto zu gewähren, sollten Sie dedizierte Benutzer mit eingeschränkten Berechtigungen erstellen.

Neuen Benutzer erstellen

CREATE USER username WITH PASSWORD 'your_secure_password';

Beispiel

CREATE USER dbuser WITH PASSWORD 'StrongP@ssword123';

Vollständigen Zugriff auf eine bestimmte Datenbank gewähren

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Beispiel

GRANT ALL PRIVILEGES ON DATABASE my_database TO dbuser;

Dies gewährt dbuser vollständigen Lese- und Schreibzugriff auf my_database, einschließlich der Möglichkeit, Tabellen zu erstellen und zu löschen.

Alle Benutzer auflisten

Um alle vorhandenen Rollen und Benutzer anzuzeigen:

du

4. Verbindung zu einer Datenbank herstellen

Sobald eine Datenbank vorhanden ist, müssen Sie sich damit verbinden, bevor Sie Tabellen erstellen oder Abfragen dagegen ausführen können.

Zu einer Datenbank wechseln

c my_database

Die Eingabeaufforderung wird aktualisiert, um die aktive Datenbank widerzuspiegeln:

my_database=#

Sie arbeiten nun innerhalb von my_database und können alle DDL- und DML-Anweisungen dagegen ausführen.

5. Tabellen erstellen und verwalten

Tabellen sind die Kernstruktureinheit jeder relationalen Datenbank. Jede Tabelle definiert ein Schema – eine Reihe benannter Spalten mit spezifischen Datentypen und Einschränkungen.

Tabelle erstellen

CREATE TABLE table_name (
    column1 data_type PRIMARY KEY,
    column2 data_type,
    column3 data_type
);

Praktisches Beispiel: Mitarbeiterdatensätze-Tabelle

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    department  VARCHAR(50),
    salary      NUMERIC(10, 2)
);

Hier inkrementiert SERIAL die employee_id bei jeder neuen Zeile automatisch, und NOT NULL erzwingt, dass jeder Mitarbeiter einen Namen haben muss.

6. Daten einfügen, abfragen, aktualisieren und löschen

Datensatz einfügen

INSERT INTO employees (name, department, salary)
VALUES ('Jane Smith', 'Engineering', 72000);

Alle Datensätze abfragen

SELECT * FROM employees;

Ergebnisse mit einer WHERE-Klausel filtern

SELECT name, salary FROM employees WHERE department = 'Engineering';

Vorhandenen Datensatz aktualisieren

UPDATE employees
SET salary = 78000
WHERE name = 'Jane Smith';

Bestimmten Datensatz löschen

DELETE FROM employees
WHERE name = 'Jane Smith';

> Best Practice: Verwenden Sie immer eine WHERE-Klausel mit UPDATE– und DELETE-Anweisungen. Das Weglassen betrifft jede Zeile in der Tabelle.

7. Datenbankzugriff und Sicherheit verwalten

Sicherheit ist ein erstklassiges Anliegen bei jeder PostgreSQL-Produktionsbereitstellung. Das Prinzip der geringsten Berechtigung – Benutzern nur die Berechtigungen zu gewähren, die sie tatsächlich benötigen – reduziert Ihre Angriffsfläche erheblich.

Alle Berechtigungen von einem Benutzer widerrufen

REVOKE ALL PRIVILEGES ON DATABASE my_database FROM dbuser;

Schreibgeschützten Zugriff auf eine bestimmte Tabelle gewähren

GRANT SELECT ON TABLE employees TO dbuser;

Dies ermöglicht dbuser, die employees-Tabelle abzufragen, verhindert aber alle Einfügungen, Aktualisierungen oder Löschungen.

Spezifische DML-Berechtigungen gewähren

GRANT SELECT, INSERT, UPDATE ON TABLE employees TO dbuser;

Zusätzliche Sicherheitsempfehlungen

• Verwenden Sie starke, eindeutige Passwörter für jeden Datenbankbenutzer
• Deaktivieren Sie Fernzugriff für den postgres-Superuser in pg_hba.conf
• Aktivieren Sie SSL-Verbindungen, um Daten während der Übertragung zu verschlüsseln – kombinieren Sie dies mit einem vertrauenswürdigen SSL-Zertifikat auf Ihrem Server
• Überprüfen Sie regelmäßig Benutzerprivilegien mit du und dp
• Halten Sie PostgreSQL aktualisiert, um bekannte Sicherheitslücken zu beheben

8. Datenbanken sichern und wiederherstellen

Regelmäßige Sicherungen sind für jede Produktionsdatenbank unverzichtbar. PostgreSQL bietet die pg_dump– und psql-Dienstprogramme für unkomplizierte Sicherungs- und Wiederherstellungs-Workflows.

Datenbank in eine SQL-Datei sichern

pg_dump my_database > my_database_backup.sql

Dies exportiert das gesamte Datenbankschema und die Daten als einfaches SQL-Skript.

In komprimiertem Format sichern (empfohlen für große Datenbanken)

pg_dump -Fc my_database > my_database_backup.dump

Das benutzerdefinierte Format (-Fc) erzeugt eine komprimierte Binärdatei und unterstützt parallele Wiederherstellung.

Datenbank aus einer SQL-Sicherung wiederherstellen

psql my_database < my_database_backup.sql

Aus einer benutzerdefinierten Sicherung wiederherstellen

pg_restore -d my_database my_database_backup.dump

Sicherungen mit einem Cron-Job automatisieren

Planen Sie tägliche Sicherungen durch Hinzufügen eines Cron-Eintrags:

crontab -e

0 2 * * * pg_dump my_database > /var/backups/postgres/my_database_$(date +%F).sql

Dies führt täglich um 2:00 Uhr eine Sicherung durch und hängt das Datum an den Dateinamen an, um eine einfache Versionierung zu ermöglichen.

9. Tabellen und Datenbanken löschen

Wenn eine Tabelle oder Datenbank nicht mehr benötigt wird, verwenden Sie den DROP-Befehl, um sie dauerhaft zu entfernen.

Tabelle löschen

DROP TABLE table_name;

Beispiel

DROP TABLE employees;

Tabelle nur löschen, falls sie vorhanden ist (sicherere Syntax)

DROP TABLE IF EXISTS employees;

Datenbank löschen

Sie müssen die Verbindung zur Zieldatenbank trennen, bevor Sie sie löschen. Wechseln Sie zuerst zur Standard-postgres-Datenbank:

c postgres

Dann löschen Sie das Ziel:

DROP DATABASE my_database;

> Warnung: DROP DATABASE ist nicht rückgängig zu machen. Überprüfen Sie immer, dass Sie eine aktuelle Sicherung haben, bevor Sie diesen Befehl in der Produktion ausführen.

10. Nützliche PostgreSQL Meta-Befehle Referenz

Die psql CLI enthält eine umfangreiche Reihe von Meta-Befehlen (mit vorangestellt), die Navigation und Inspektion vereinfachen: