PostgreSQL Installation und Grundlegende Verwaltung

# Paketliste aktualisieren
apt update

# PostgreSQL installieren
apt install -y postgresql postgresql-contrib

# Dienststatus pruefen
systemctl status postgresql

# Automatischen Start aktivieren
systemctl enable postgresql

# Erforderliche Tools installieren
apt install -y curl ca-certificates

# PostgreSQL-Signaturschlussel hinzufugen
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Repository hinzufugen
echo 'deb https://apt.postgresql.org/pub/repos/apt '$(lsb_release -cs)'-pgdg main' > /etc/apt/sources.list.d/pgdg.list

# Aktualisieren und installieren
apt update
apt install -y postgresql-16

# Offizielles PostgreSQL-Repository hinzufugen
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Eingebautes PostgreSQL-Modul deaktivieren
dnf -qy module disable postgresql

# PostgreSQL 16 installieren
dnf install -y postgresql16-server postgresql16-contrib

# Datenbank initialisieren
/usr/pgsql-16/bin/postgresql-16-setup initdb

# Dienst starten und aktivieren
systemctl enable --now postgresql-16

# Dienststatus pruefen
systemctl status postgresql-16

psql --version
# Ausgabe: psql (PostgreSQL) 16.x

# Dienstinformationen
psql -U postgres -c 'SELECT version();'

# Zum postgres-Systembenutzer wechseln
su - postgres

# Mit der PostgreSQL-Konsole verbinden
psql

# Direkt als Root verbinden
sudo -u postgres psql

# Mit einer bestimmten Datenbank verbinden
sudo -u postgres psql -d mydb

-- In der PostgreSQL-Konsole
-- Neuen Benutzer erstellen
CREATE USER appuser WITH PASSWORD 'StarkesPasswort123!';

-- Superuser erstellen (mit Vorsicht verwenden)
CREATE USER adminuser WITH PASSWORD 'AdminPass!' SUPERUSER;

-- Benutzer auflisten
\du

-- Benutzerpasswort andern
ALTER USER appuser WITH PASSWORD 'NeuesPasswort456!';

-- Datenbankerstell-Berechtigung vergeben
ALTER USER appuser CREATEDB;

-- Datenbank erstellen
CREATE DATABASE myapp OWNER appuser ENCODING 'UTF8' LC_COLLATE 'de_DE.UTF-8' LC_CTYPE 'de_DE.UTF-8' TEMPLATE template0;

-- Datenbanken auflisten
\l

-- Mit einer Datenbank verbinden
\c myapp

-- Vollstandige Berechtigungen vergeben
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

-- Schema-Berechtigung (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO appuser;

-- Datenbank loschen
DROP DATABASE alte_datenbank;

-- Tabelle erstellen
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Daten einfugen
INSERT INTO users (username, email) VALUES ('hans', 'hans@example.com');

-- Daten abfragen
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;

-- Daten aktualisieren
UPDATE users SET email = 'neu@example.com' WHERE id = 1;

-- Daten loschen
DELETE FROM users WHERE id = 5;

-- Tabellen auflisten
\dt

-- Tabellenstruktur anzeigen
\d users

-- Index erstellen
CREATE INDEX idx_users_email ON users(email);

# Konfigurationsdatei-Speicherort finden
sudo -u postgres psql -c 'SHOW config_file;'

# Bearbeiten
nano /etc/postgresql/16/main/postgresql.conf

# listen_addresses-Zeile andern
listen_addresses = '*'

# Port (Standard 5432)
port = 5432

nano /etc/postgresql/16/main/pg_hba.conf

# Zeile fur Remote-Verbindungen hinzufugen (am Ende der Datei)
# TYPE  DATABASE  USER      ADDRESS         METHOD
host    myapp     appuser   192.168.1.0/24  scram-sha-256
host    all       all       0.0.0.0/0       scram-sha-256

# Dienst neu starten
systemctl restart postgresql

# Port mit UFW offnen
ufw allow 5432/tcp

# Mit iptables
iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

# Einzelne Datenbank sichern
pg_dump -U postgres myapp > myapp_backup.sql

# Komprimiertes Format (schnellere Wiederherstellung)
pg_dump -U postgres -Fc myapp > myapp_backup.dump

# Alle Datenbanken sichern
pg_dumpall -U postgres > all_databases.sql

# Von einem Remote-Server sichern
pg_dump -h 192.168.1.100 -U postgres myapp > myapp_remote.sql

# Aus SQL-Format wiederherstellen
psql -U postgres myapp < myapp_backup.sql

# Aus komprimiertem Format wiederherstellen
pg_restore -U postgres -d myapp myapp_backup.dump

# Alle Datenbanken wiederherstellen
psql -U postgres < all_databases.sql

# Crontab bearbeiten
crontab -e

# Jede Nacht um 02:00 Uhr Backup erstellen
0 2 * * * pg_dump -U postgres myapp | gzip > /backup/myapp_$(date +\%Y\%m\%d).sql.gz

# Backups alteren als 30 Tage loschen
0 3 * * * find /backup -name 'myapp_*.sql.gz' -mtime +30 -delete

# Dienst starten
systemctl start postgresql

# Dienst stoppen
systemctl stop postgresql

# Dienst neu starten
systemctl restart postgresql

# Konfiguration neu laden (kein Neustart erforderlich)
systemctl reload postgresql

# Protokolle anzeigen
journalctl -u postgresql -f
tail -f /var/log/postgresql/postgresql-16-main.log