Wissen

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== PostgreSQL mit partitionierten Tabellen ======

[[::Datenbank:PostgreSQL]]

__Man kann in **PostgreSQL ab Version 11** eine partitionierte Tabelle haben, die Fremdschlüssel enthält oder referenziert wird.__\\
**Aber es gibt Einschränkungen** (//''UNIQUE'' / ''PRIMARY KEY'' ist zwingend erforderlich//) je nach PostgreSQL-Version und konkreter Konstellation.

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             
---------------------------------------
 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1)
(1 row)

 server_version_num 
--------------------
 160014
(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 
----+-------------+----------------------------+----------+--------+-----------+-------------+----------------
  1 | 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 |    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>