06 Update Umzug Migration Postgres 13 17 (ab Prodat 2104) » Historie » Revision 11
Revision 10 ([X] Daniel S, 21.04.2023 11:26) → Revision 11/16 ([X] Daniel S, 21.04.2023 14:41)
{{toc}}
h1. Update Postgres 13/15 (ab Prodat 2104)
* Postgres 13/15 Installieren
* pg_hba.conf übernehmen
* ggf eigene cluster für mandanten wenn diese getrennt sein sollen. > https://redmine.prodat-sql.de/issues/17033#note-15
* MoveDB Script ausführen. Dazu das Script direkt anpassen (Server und Port). Anweisungen beachten und unbedingt einhalten!
+ ..\Administration\DB_Admin_BackUp_TestDB\DB_Move_DB_Cluster__too.bat
h2. Update Version 12 (Postgres 9) auf Version 2206 (Postgres 13/15)
* Postgres 13/15 installieren
* gem. oberer Beschreibung den Datenbankcluster in Postgres 13/15 umziehen. Somit wird am Bestandssystem NICHTS geändert.
* Nachdem der Cluster umgezogen ist, müssen die *2 Files im Anhang* ausgeführt werden
+ Durch diese Files werden Dummy Funktionen eingespielt, die Kompatiblilität mit dem neuen Client herstellen. Teilweise werden nuf Funktionsrümpfe eingespielt, damit die Oberflächen starten. Durch die DBupdates werden dann die richtigen Funktionen eingespielt
+ Wenn es beim Einspielen der Files zu fehlern kommt, muss man checken ob die Funktionen doch bereits vorhanden sind und dann auskommentieren
h2. Update V11
* Gem. V12
*# 010 Functions System.sql
+ CREATE SCHEMA IF NOT EXISTS TSystem;
+ CREATE SCHEMA IF NOT EXISTS z_99_deprecated;
*# z0 dcache + comp.sql
*# z0 role System.sql
*# 0 0 SQL Fielgenerator.sql
*#
<pre><code class="sql">
CREATE TABLE recnogroup (
reg_id serial PRIMARY KEY,
reg_schema varchar(50), -- Schema wo die Tabele aus reg_tablename liegt
reg_tablename varchar(80), -- (früher reg_tform) Modulname zur Identifikation der F2s
reg_bez varchar(100), -- Bezeichnung der Artikeleigenschaft Bsp: Oberfläche
reg_gruppe varchar(80), -- Gruppe zu der sie zugeordnet werden kann
reg_ac_n varchar(9), -- X TableContraints: REFERENCES artcod ON UPDATE CASCADE; -- Artikel-Code kann zugeordent werden
reg_pos integer, -- Position des Parameters innerhalb einer Gruppe für Sortierung in GUI
reg_pname varchar(40) NOT NULL, -- eindeutiger Parametername, damit in Triggern / Funktionen nicht mit ID gearbeitet werden muss.
reg_gruppe_textno integer, -- Textnr. Übersetzung für Gruppe
reg_bez_textno integer, -- Textnr. Übersetzung für Bezeichnung
reg_paramtype varchar(30), -- Parametertypen 'ptVARCHAR', 'ptINTEGER', 'ptNUMERIC', 'ptBOOLEAN', 'ptTRISTATEBOOLEAN', 'ptTIMESTAMP', 'ptDATE', 'ptENUM', 'ptTEXT'
reg_default varchar(100), -- Vorgabe bzw. Standardwert des Parameters (in varchar konvertiert)
reg_minvalue integer, -- Min-Wert für Zahlen, Mindestlänge bei varchar
reg_maxvalue integer, -- Max-Wert für Zahlen, Maximallänge bei varchar
reg_vorgabenliste boolean DEFAULT FALSE, -- Vorgabenliste: Enum-Set dient als "Vorgabetabelle" für ein Modul.
reg_visible boolean DEFAULT TRUE, -- Für 'normale' Nutzer in Register Eigenschaften sichtbar
reg_exclusive boolean, -- Bei Parametertyp ptENUM darf nur ein Enumerationswert angegeben sein, sonst als SET zu betrachten
reg_autoinsert boolean NOT NULL DEFAULT FALSE,-- Version 1: Flag in Zieltabellentrigger (z.Bsp. am QAB) abfragen, ob RecnoKeyword automatisch
-- angelegt werden soll. Version 2: In table_modified verschieben und komplett automatisieren.
reg_field_name varchar(50), -- Feldname für Vorgabetabelle-Enums, siehe wiki
reg_field_value varchar(100), -- Wert für Vorgabetabelle-Enums, siehe wiki
reg_m_iso varchar(10), -- # 7163 EAV - Datentyp Mengeneinheit
-- CHECK for existing schema
CONSTRAINT recnogroup__schema__existing CHECK (
reg_schema IS NULL
OR tsystem.schema_exists( reg_schema )
),
-- CHECK for existing table within the current searchpath
CONSTRAINT recnogroup__table__existing CHECK (
reg_tablename IS NULL
OR tsystem.table_exists( reg_tablename, reg_schema )
),
-- CHECK for existing column
CONSTRAINT recnogroup__column__existing CHECK (
reg_field_name IS NULL
OR tsystem.column_exists( reg_tablename, reg_field_name, coalesce( reg_schema, 'public' ) )
),
--- #18368 CHECK reg_paramtype in Parametertypen-Liste
CONSTRAINT reg_paramtype_in_liste CHECK (
reg_paramtype IN ( 'ptVARCHAR', 'ptINTEGER', 'ptNUMERIC', 'ptBOOLEAN', 'ptTRISTATEBOOLEAN', 'ptTIMESTAMP', 'ptDATE', 'ptENUM', 'ptTEXT' )
)
);
CREATE TABLE SettingsDyn (
sd_id INTEGER PRIMARY KEY CHECK (sd_id > 0), -- [SYNCRO:SyncID]
sd_name VARCHAR NOT NULL, -- TODO [SYNCRO:SyncID]
sd_parentid INTEGER NOT NULL,
sd_type VARCHAR(20),
sd_sort NUMERIC(7, 2), -- Sortierungsreihenfolge
sd_config TEXT,
sd_settingsname VARCHAR(80),
sd_settingstype VARCHAR(20),
sd_vartxtnr INTEGER,
sd_deleted BOOLEAN DEFAULT False -- [SYNCRO:Deleted]
-- System (tables__generate_missing_fields)
--modified_date TIMESTAMP(0) -- [SYNCRO:Modified]
);
-- for tri gram indicies for example on dbupdates
-- CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- CREATE EXTENSION IF NOT EXISTS btree_gist;
-- [SYNCRO-TABLE] DBUpdates
CREATE TABLE dbupdates
(
upd_id varchar(30) NOT NULL DEFAULT currenttime()::VARCHAR PRIMARY KEY, -- [SYNCRO:SyncID] ID des Updates
upd_parent varchar(30), -- parent (Baumstruktur)
upd_minver varchar(11), -- [SYNCRO:Version] Mindest-Programmversion
upd_kunde varchar(20), -- [SYNCRO:Kunde]
upd_donedat timestamp(0), -- [SYNCRO:NotThisFields] update eingespielt am
upd_bez varchar(150) NOT NULL, -- betreff/bezeichnung
upd_txt text,
upd_sql text,
upd_projekt varchar(100),
upd_noerr boolean NOT NULL DEFAULT false,
upd_sperr boolean NOT NULL DEFAULT false,
-- System (tables__generate_missing_fields)
dbrid varchar(32) NOT NULL DEFAULT nextval('db_id_seq'),
insert_date date,
insert_by varchar(32),
modified_by varchar(32),
modified_date timestamp(0) -- [SYNCRO:Modified]
);
-- CREATE INDEX ON dbupdates USING gist ( dbrid gist_trgm_ops );
-- CREATE INDEX ON dbupdates USING gist ( upd_bez gist_trgm_ops );
-- CREATE INDEX ON dbupdates USING gist ( upd_id gist_trgm_ops );
-- CREATE INDEX ON dbupdates USING gist ( upd_projekt gist_trgm_ops );
CREATE INDEX ON dbupdates (upd_donedat);
-- gist index for upd_sql gets to big and fails with
-- ERROR: index row requires 8304 bytes, maximum size is 8191
-- CREATE INDEX ON dbupdates USING gin ( upd_sql gin_trgm_ops );
CREATE INDEX dbupdates__donedat ON dbupdates ( upd_parent );
-- Triggerfunktion fürs automatisches Schließen der DB-Updates nach Version
CREATE OR REPLACE FUNCTION dbupdates__b_i() RETURNS TRIGGER AS $$
BEGIN
IF EXISTS(SELECT true FROM settings WHERE s_vari = 'DBU_date_for_auto_done') THEN
IF new.upd_id::DATE <= TSystem.Settings__Get('DBU_date_for_auto_done')::DATE THEN
new.upd_donedat:= currenttime();
new.upd_txt:= E'[DBU-AUTO-DONE]\n' || COALESCE(new.upd_txt, '');
END IF;
END IF;
RETURN new;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER dbupdates__b_i BEFORE INSERT ON dbupdates FOR EACH ROW EXECUTE PROCEDURE dbupdates__b_i();
CREATE OR REPLACE FUNCTION dbupdates__filtered__nodes__upd_ids__get() RETURNS SETOF VARCHAR AS $$
WITH _nodes AS (
SELECT upd_id FROM dbupdates
WHERE upd_donedat >= current_timestamp - '90 days'::INTERVAL
OR upd_donedat IS NULL
OR coalesce(modified_date, insert_date::TIMESTAMP) >= current_timestamp - '90 days'::INTERVAL
OR upd_sperr
),
_parents AS (
WITH RECURSIVE _tree AS (
SELECT a.upd_id, a.upd_parent, a.upd_bez, a.upd_donedat, a.modified_date, a.insert_date, a.upd_sperr, 0 AS depth FROM dbupdates a WHERE upd_id IN (SELECT upd_id FROM _nodes)
UNION
SELECT a.upd_id, a.upd_parent, a.upd_bez, a.upd_donedat, a.modified_date, a.insert_date, a.upd_sperr, depth - 1 FROM dbupdates a JOIN _tree p ON a.upd_id = p.upd_parent
)
SELECT upd_id FROM _tree
WHERE depth < 0
ORDER BY depth
)
SELECT * FROM _nodes
UNION
SELECT * FROM _parents
$$ LANGUAGE SQL;
</code></pre>
<pre><code class="sql">
--
CREATE OR REPLACE FUNCTION tsystem.syncro__dbupdates(IN _dblink VARCHAR DEFAULT 'host=pg.prodat-erp.de port=5432 dbname=PRODAT-18.08 user=syncro password=syncro') RETURNS void AS $$
BEGIN
SET LOCAL SESSION AUTHORIZATION syncro;
DELETE FROM dbupdates;
INSERT INTO dbupdates ( upd_id,
upd_parent,
upd_minver,
upd_kunde,
-- upd_donedat,
upd_bez,
upd_txt,
upd_sql,
upd_projekt,
upd_noerr,
upd_sperr,
--,
dbrid,
insert_date,
insert_by,
modified_by,
modified_date
)
SELECT *
FROM dblink(_dblink,
'SELECT upd_id,
upd_parent,
upd_minver,
upd_kunde,
-- upd_donedat,
upd_bez,
upd_txt,
upd_sql,
upd_projekt,
upd_noerr,
upd_sperr,
--,
dbrid,
insert_date,
insert_by,
modified_by,
modified_date
FROM dbupdates
WHERE
upd_kunde IS null
AND upd_minver <= ' || TSystem.quote_literal__connstr_param( TSystem.Settings__Get( 'ProdatVersion' ) ) || '
')
AS ( upd_id varchar(30),
upd_parent varchar(30), -- parent (Baumstruktur)
upd_minver varchar(11), -- [SYNCRO:Version] Mindest-Programmversion
upd_kunde varchar(20), -- [SYNCRO:Kunde]
-- upd_donedat timestamp(0), -- [SYNCRO:NotThisFields] update eingespielt am
upd_bez varchar(150),
upd_txt text,
upd_sql text,
upd_projekt varchar(100),
upd_noerr boolean,
upd_sperr boolean,
-- System (tables__generate_missing_fields)
dbrid varchar(32),
insert_date date,
insert_by varchar(32),
modified_by varchar(32),
modified_date timestamp(0) -- [SYNCRO:Modified]
);
END $$ LANGUAGE plpgsql;
</code></pre>
<pre><code class="sql">
SELECT TSystem.tablefieldinfo__recreate();
SELECT TSystem.tables__generate_missing_fields();
SELECT TSystem.Settings__Set( 'ProdatVersion', '22.06.23.00' );
CREATE OR REPLACE FUNCTION tsystem.quote_literal__connstr_param(
IN _text varchar
) RETURNS varchar AS $$
DECLARE
_result varchar;
BEGIN
_result := REPLACE( REPLACE( _text, E'\\', E'\\\\' ), E'\'', E'\\\'');
_result := concat( '''',
_result,
''''
);
RETURN _result;
END; $$ LANGUAGE plpgsql;
CREATE EXTENSION IF NOT EXISTS dblink;
SELECT tsystem.syncro__dbupdates();
UPDATE dbupdates SET upd_donedat = '1899-01-01' WHERE upd_id IN ('2015-06-01 15:56:01', '2015-11-02 16:47:42', '2016-09-06 12:25:29', '2017-06-08 17:43:29', '2018-06-10 10:09:01', '2018-07-05 16:58:01')
</code></pre>