Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- datenbank:postgresql_mit_partitionierten_tabellen [2026-06-19 13:41:30] – angelegt manfred
+++ datenbank:postgresql_mit_partitionierten_tabellen [2026-06-22 14:11:54] (aktuell) – manfred
@@ Zeile 7: / Zeile 7: @@
 Der Fremdschlüssel wird auf der Partitionierungs-Root-Tabelle definiert und gilt automatisch für alle Partitionen.
+===== Eine bestehende Tabellen nachträglich partitionieren =====
+<code bash eine bestehende Tabelle partitionieren>
+Das geht bei PostgreSQL nicht, weil eine Tabelle mit Partitionen, intern ein anderer Tabellen-Typ ist!
+</code>
+===== Postgresql + PG-Cron + PG-Partman installieren =====
+<code bash Install>
+> apt install postgresql postgresql-contrib postgresql-16-cron postgresql-16-partman
+> echo "SELECT version(); SHOW server_version; SHOW server_version_num;" | psql -U postgres -d testdatenbank
+                                                                 version
+------------------------------------------------------------------------------------------------------------------------------------------
+ PostgreSQL 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
+(1 row)
+            server_version
+---------------------------------------
+.14 (Ubuntu 16.14-0ubuntu0.24.04.1)
+(1 row)
+ server_version_num
+--------------------
+(1 row)
+</code>
+<code c PG-Cron>
+# Wichtig zu verstehen:
+#
+# pg_cron läuft nur in EINER Datenbank,
+# diese wird fest definiert mit:
+cron.database_name
+# in diesem Fall:
+cron.database_name = "testdatenbank"
+</code>
+<code bash PG-Stand-Alone>
+> echo "shared_preload_libraries = 'pg_cron'" >> /etc/postgresql/16/main/postgresql.conf
+> echo "cron.database_name = 'testdatenbank'" >> /etc/postgresql/16/main/postgresql.conf
+> service postgresql restart
+</code>
+<code bash PG-Patroni-Cluster>
+> patronictl -c /etc/patroni/config.yml edit-config pgcluster
+postgresql:
+  parameters:
+    shared_preload_libraries: "pg_cron"
+    cron.database_name: "testdatenbank"
+> patronictl -c /etc/patroni/config.yml restart pgcluster
++ Cluster: pgcluster (7636009945722332383) ---------+----+-----------+
+| Member      | Host          | Role    | State     | TL | Lag in MB |
++-------------+---------------+---------+-----------+----+-----------+
+| pgsql-db-01 | 192.168.1.160 | Leader  | running   | 10 |           |
+| pgsql-db-02 | 192.168.1.174 | Replica | streaming | 10 |         0 |
+| pgsql-db-03 | 192.168.1.183 | Replica | streaming | 10 |         0 |
++-------------+---------------+---------+-----------+----+-----------+
+When should the restart take place (e.g. 2026-06-18T16:08)  [now]:
+Are you sure you want to restart members pgsql-db-01, pgsql-db-02, pgsql-db-03? [y/N]: y
+Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
+Success: restart on member pgsql-db-01
+Success: restart on member pgsql-db-02
+Success: restart on member pgsql-db-03
+</code>
+===== partitionierte Tabellen anlegen =====
+==== Typen der Tabellen-Partition ====
+<code sql Partitionierung nach Range>
+CREATE TABLE tab_name (
+    id BIGINT,
+    datum DATE,
+    PRIMARY KEY (id, datum)
+) PARTITION BY RANGE (datum);
+CREATE TABLE tab_name_2024 PARTITION OF tab_name FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
+CREATE TABLE tab_name_2025 PARTITION OF tab_name FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
+CREATE TABLE tab_name_default PARTITION OF tab_name DEFAULT;
+</code>
+<code sql Partitionierung nach LIST>
+-- Parent-Tabelle
+CREATE TABLE customers (
+    id BIGINT,
+    country TEXT NOT NULL,
+    name TEXT,
+    -- Primary Key muss Partition Key enthalten
+    PRIMARY KEY (id, country)
+)
+PARTITION BY LIST (country);
+-- Partitionen für einzelne Länder
+CREATE TABLE customers_de
+PARTITION OF customers
+FOR VALUES IN ('DE');
+CREATE TABLE customers_us
+PARTITION OF customers
+FOR VALUES IN ('US');
+CREATE TABLE customers_fr
+PARTITION OF customers
+FOR VALUES IN ('FR');
+-- Default-Partition (für alle anderen Länder)
+CREATE TABLE customers_default
+PARTITION OF customers
+DEFAULT;
+-- Optionaler Index (wird auf alle Partitionen angewendet)
+CREATE INDEX idx_customers_name
+ON customers (name);
+-- Testdaten
+INSERT INTO customers (id, country, name) VALUES
+(1, 'DE', 'Max'),
+(2, 'US', 'John'),
+(3, 'FR', 'Pierre'),
+(4, 'ES', 'Carlos');  -- landet in customers_default
+</code>
+<code sql Partitionierung nach HASH (MODULUS gibt die Anzahl der Partitionen an)>
+CREATE TABLE orders (
+    id BIGINT PRIMARY KEY
+) PARTITION BY HASH (id);
+CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
+CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
+CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
+CREATE TABLE orders_p4 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);
+</code>
+===== partitionierte Tabelle mit Fremdschlüssel anlegen =====
+Cluster-IP: ''192.168.1.1''
+<code sql komplettes Beispiel anlegen>
+-- ENUMs
+CREATE TYPE net_enum AS ENUM ('A','B','C','D','E','F','G','UNKNOWN');
+CREATE TYPE js_enum AS ENUM (
+    'ERROR','ABORTED','IN_PROGRESS','FINISHED',
+    'FINISHED_WITH_WARNINGS','FINISHED_WITHOUT_COMMIT'
+);
+CREATE TYPE modus_enum AS ENUM ('Zyklus','Statik','Bewegung');
+-- Parent-Tabelle (unpartitioniert bleibt so)
+CREATE TABLE log_tab (
+    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    js js_enum NOT NULL,
+    start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    end_time TIMESTAMP NULL,
+    modus modus_enum NOT NULL,
+    z_options VARCHAR(45) NOT NULL,
+    job_options VARCHAR(45) NOT NULL,
+    execution_time TIME NOT NULL DEFAULT '00:00:00'
+);
+-- Partitionierte Tabelle
+CREATE TABLE origtab_log (
+    id BIGINT GENERATED ALWAYS AS IDENTITY,
+    e_id BIGINT NOT NULL,
+    net net_enum NOT NULL DEFAULT 'UNKNOWN',
+    zyk CHAR(2) NOT NULL,
+    origtab VARCHAR(45) NOT NULL,
+    update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    -- WICHTIG: Partition Key im PK!
+    PRIMARY KEY (id, update_timestamp),
+    CONSTRAINT id_key_origtab_fraud
+        FOREIGN KEY (e_id)
+        REFERENCES log_tab (id)
+)
+PARTITION BY RANGE (update_timestamp);
+--------------------------------------------------------------------------------
+-- Beispiel-Partitionen anlegen (täglich)
+--CREATE TABLE origtab_log_p20260301 PARTITION OF origtab_log FOR VALUES FROM ('2026-03-01') TO ('2026-03-02');
+--CREATE TABLE origtab_log_p20260302 PARTITION OF origtab_log FOR VALUES FROM ('2026-03-02') TO ('2026-03-03');
+--CREATE TABLE origtab_log_p20260303 PARTITION OF origtab_log FOR VALUES FROM ('2026-03-03') TO ('2026-03-04');
+-- Default-Partition (sehr wichtig!)
+-- CREATE TABLE origtab_log_default PARTITION OF origtab_log DEFAULT;
+-- eine Beispiel-Partition abtrennen und löschen
+--ALTER TABLE public.origtab_log DETACH PARTITION public.origtab_log_p20260301;
+--DROP TABLE IF EXISTS origtab_log_p20260301 CASCADE;
+-- Extension installieren
+CREATE EXTENSION pg_cron;
+CREATE EXTENSION IF NOT EXISTS pg_partman;
+CREATE SCHEMA partman;
+-- Tabelle registrieren - legt fehlende Partitionen für die Zukunft an
+SELECT public.create_parent(
+        p_parent_table := 'public.origtab_log'::text,
+        p_control      := 'update_timestamp'::text,
+        p_type         := 'range'::text,
+        p_interval     := '1 day'::text,
+        p_premake      := 7::int   -- 7 Tage im Voraus
+);
+-- Retention: markiert alte Partitionen zum löschen
+UPDATE public.part_config
+SET retention = '30 days',
+        retention_keep_table = false
+WHERE parent_table = 'public.origtab_log';
+-- Automatisieren mit pg_cron
+SELECT cron.schedule(
+        'partman maintenance',
+        '0 1 * * *',  -- täglich um 01:00
+        $$SELECT public.run_maintenance();$$
+);
+-- Wartung laufen lassen
+-- löscht die markierten Partitionen und legt die fehlenden Partitionen an
+SELECT public.run_maintenance();
+--------------------------------------------------------------------------------
+-- Indizes (auf Parent!)
+CREATE INDEX bc_key       ON origtab_log (net, zyk, origtab);
+CREATE INDEX e_id_key_idx ON origtab_log (e_id);
+--------------------------------------------------------------------------------
+-- Partitionen anzeigen
+SELECT inhrelid::regclass AS partition FROM pg_inherits WHERE inhparent = 'origtab_log'::regclass;
+</code>
+<code bash verschiedene Infos>
+> echo "SELECT extname, extnamespace::regnamespace FROM pg_extension WHERE extname = 'pg_partman';" | psql -U postgres -d testdatenbank
+  extname   | extnamespace
+------------+--------------
+ pg_partman | public
+(1 row)
+> echo "SELECT * FROM log_tab;" | psql -U postgres -d testdatenbank
+ id | js | start_time | end_time | modus | z_options | job_options | execution_time
+----+----+------------+----------+-------+-----------+-------------+----------------
+(0 rows)
+> echo "SELECT * FROM origtab_log;" | psql -U postgres -d testdatenbank
+ id | e_id | net | zyk | origtab | update_timestamp
+----+------+-----+-----+---------+------------------
+(0 rows)
+> echo "\df public.create_parent" | psql -U postgres -d testdatenbank
+                                                                                                                                                                                                                                                  List of functions
+ Schema |     Name      | Result data type |                                                                                                                                                                                                                       Argument data types                                                                                                                                                                                                                        | Type
+--------+---------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------
+ public | create_parent | boolean          | p_parent_table text, p_control text, p_interval text, p_type text DEFAULT 'range'::text, p_epoch text DEFAULT 'none'::text, p_premake integer DEFAULT 4, p_start_partition text DEFAULT NULL::text, p_default_table boolean DEFAULT true, p_automatic_maintenance text DEFAULT 'on'::text, p_constraint_cols text[] DEFAULT NULL::text[], p_template_table text DEFAULT NULL::text, p_jobmon boolean DEFAULT true, p_date_trunc_interval text DEFAULT NULL::text | func
+(1 row)
+> echo "SELECT proname, proargnames FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE proname = 'create_parent';" | psql -U postgres -d testdatenbank
+    proname    |                                                                                        proargnames
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ create_parent | {p_parent_table,p_control,p_interval,p_type,p_epoch,p_premake,p_start_partition,p_default_table,p_automatic_maintenance,p_constraint_cols,p_template_table,p_jobmon,p_date_trunc_interval}
+(1 row)
+</code>
+<code bash Tabellen testen>
+> echo "\dt" | psql -U postgres -h192.168.1.1 -d testdatenbank
+                                 List of relations
+ Schema |            Name             |       Type        |  Owner
+--------+-----------------------------+-------------------+----------
+ public | log_tab                     | table             | postgres
+ public | origtab_log                 | partitioned table | postgres
+ public | origtab_log_default         | table             | postgres
+ public | origtab_log_p20260611       | table             | postgres
+ public | origtab_log_p20260612       | table             | postgres
+ public | origtab_log_p20260613       | table             | postgres
+ public | origtab_log_p20260614       | table             | postgres
+ public | origtab_log_p20260615       | table             | postgres
+ public | origtab_log_p20260616       | table             | postgres
+ public | origtab_log_p20260617       | table             | postgres
+ public | origtab_log_p20260618       | table             | postgres
+ public | origtab_log_p20260619       | table             | postgres
+ public | origtab_log_p20260620       | table             | postgres
+ public | origtab_log_p20260621       | table             | postgres
+ public | origtab_log_p20260622       | table             | postgres
+ public | origtab_log_p20260623       | table             | postgres
+ public | origtab_log_p20260624       | table             | postgres
+ public | origtab_log_p20260625       | table             | postgres
+ public | part_config                 | table             | postgres
+ public | part_config_sub             | table             | postgres
+ public | template_public_origtab_log | table             | postgres
+(21 rows)
+> echo "INSERT INTO log_tab (js, modus, z_options, job_options, execution_time) VALUES ('IN_PROGRESS','Statik','test','test','00:00:00');" | psql -U postgres -h192.168.1.1 -d testdatenbank
+INSERT 0 1
+> echo "INSERT INTO origtab_log (e_id, net, zyk, origtab) VALUES (1, 'A', '01', 'test');" | psql -U postgres -h192.168.1.1 -d testdatenbank
+INSERT 0 1
+> echo "SELECT * FROM log_tab;" | psql -U postgres -h192.168.1.1 -d testdatenbank
+ id |     js      |         start_time         | end_time | modus  | z_options | job_options | execution_time
+----+-------------+----------------------------+----------+--------+-----------+-------------+----------------
+| IN_PROGRESS | 2026-06-18 14:41:38.222129 |          | Statik | test      | test        | 00:00:00
+(1 row)
+> echo "SELECT * FROM origtab_log;" | psql -U postgres -h192.168.1.1 -d testdatenbank
+ id | e_id | net | zyk | origtab |      update_timestamp
+----+------+-----+-----+---------+----------------------------
+|    1 | A   | 01  | test    | 2026-06-18 14:41:38.225434
+(1 row)
+</code>
+<code bash aktuelle Einstellungen anzeigen>
+> echo "SELECT parent_table, control, partition_interval, retention, retention_keep_table, premake FROM public.part_config WHERE parent_table = 'public.origtab_log';" | psql -U postgres -h192.168.1.1 -d testdatenbank
+   parent_table     |     control      | partition_interval | retention | retention_keep_table | premake
+--------------------+------------------+--------------------+-----------+----------------------+---------
+ public.origtab_log | update_timestamp | 1 day              | 30 days   | f                    |       1
+(1 row)
+</code>
+<code sql aktuelle Einstellungen ändern>
+DROP EXTENSION pg_partman CASCADE;
+DROP TABLE IF EXISTS public.origtab_log_default CASCADE;
+DROP TABLE IF EXISTS public.template_public_origtab_log CASCADE;
+CREATE EXTENSION pg_partman;
+SELECT public.create_parent(
+        p_parent_table := 'public.origtab_log'::text,
+        p_control      := 'update_timestamp'::text,
+        p_type         := 'range'::text,
+        p_interval     := '1 day'::text,
+        p_premake      := 7::int   -- 7 Tage im Voraus
+);
+UPDATE public.part_config SET retention = '30 days', retention_keep_table = false WHERE parent_table = 'public.origtab_log';
+SELECT public.run_maintenance();
+</code>
+<code sql Änderungen anzeigen>
+\dt public.*;
+</code>
+<code bash komplettes Beispiel entfernen>
+-- Tabellen entfernen (inkl. Partitionen + Indizes + FK)
+DROP TABLE IF EXISTS origtab_log CASCADE;
+DROP TABLE IF EXISTS log_tab CASCADE;
+DROP TABLE IF EXISTS template_public_origtab_log CASCADE;
+-- ENUM-Typen entfernen
+DROP TYPE IF EXISTS net_enum CASCADE;
+DROP TYPE IF EXISTS js_enum CASCADE;
+DROP TYPE IF EXISTS modus_enum CASCADE;
+-- EXTENSION entfernen
+DROP EXTENSION pg_partman CASCADE;
+DROP EXTENSION pg_cron CASCADE;
+-- SCHEMA entfernen
+DROP SCHEMA partman CASCADE;
+</code>