DBA: competenter organize synchronisations et importat

Complicata processus magnarum notitia occidit (diversim Processus ETL: importat conversiones et synchronizationes cum fonte externo) saepe necessitas tempus "memini" et statim cito processus quid volt.

Opus typicum huiusmodi plerumque aliquid tale sonat: "Iure hic" hac ratione expositae ab huius ripae ultimae pensiones receptae, debes cito eas in locum inserere et ad rationes tuas coniunge".

Cum autem volumen huius "aliquid" in centenis megabytarum metiri coeperit, et ministerium laborare cum 24x7 datorum perseveret, multae partes effectus oriuntur quae vitam tuam perdent.

Cum illis in PostgreSQL agere (et non solum in eo), optimizations uti potes, quae omnia citius et minus subsidii consummatio te expediet.

1. Ubi est navis?

Primum, statuamus ubi notitias quas volumus "processus."

1.1. Tabulae temporales (TEMPORARIUS TABLE)

In principio, pro PostgreSQL Tabulae temporariae eaedem sunt ac quaevis aliae. Ergo superstitiones huiusmodi "Omne ibi in memoria conditum est, et finire potest".. Sed multae etiam sunt differentiae significantium.

Tuum proprium "nominis" pro se nexum ad database

Si duo simul copulare conantur CREATE TABLE xErgo aliquis certus adepto non-unicitatis error database obiecti.

Sin utrumque facere conantur CREATE TEMPORARY TABLE xtunc ambo regulariter facient, et quisque obtinebit tuum exemplum mensae. Et nihil commune inter eos erit.

"Sui destruct" cum disconnecting

Vinculo clauso, omnes tabulae temporales automatice deletae sunt, ut manually DROP TABLE x Nullam id nisi...

Si vos es opus per pgbouncer in transaction modustunc datorum pergit credere hanc connexionem adhuc activam esse, in qua haec mensa temporalis adhuc exsistit.

Ergo iterum creare conatur, ex alio nexu ad pgbouncer, in errore proveniet. Sed hoc circumveniri potest utendo CREATE TEMPORARY TABLE IF NOT EXISTS x.

Verum, melius est ne id usquam facias, quia tunc "subito" notitias "repente" ibi manentes a "previo domino" invenire potes. Sed multo melius est manualiter legere et videre, cum mensam creando addi possit ON COMMIT DROP — id est, cum res peracta est, tabella ipso facto deleta est.

Non replicatio

Quia solum ad certam connexionem pertinent, tabulae temporales non repplicantur. Sed hoc opus duplici memoria excludit in notitia in acervo + WAL, ita multo velocius INDO/UPDATE/DELETE.

Sed cum mensa temporalis adhuc sit mensa "fere ordinaria", non potest replicari vel creari. Saltem nunc, licet commissura correspondentia diu grassata est.

1.2. UNLOGGED TABLE

Sed quid faceres, exempli gratia, si habes aliquem processum gravissimum ETL, qui intra unam rem perfici non potest, sed adhuc habes pgbouncer in transaction modus? ..

Aut fluxus notitia tam magna est Sed in uno nexu non satis est ex database (legere, unus processus per CPU)?..

Aut aliquas res geruntur asynchronously in diversis connexionibus?..

Una tantum est optio hic - tempus creare a non-tempus mensam. Pun, yeah. Ille est:

• creatum "meum" mensas cum maxime temere nomina ut non secet cum aliquo
• extractReplevit eos notitia ab extrinseco
• transformConversi, repleti in campis clavem conjunctio
• load; Parata notitia in scopum tables
• delevit "meae" mensae

Et nunc musca in unguento. Nam Omnes scribit in PostgreSQL bis fiet - primus in WALIAin tabulam/indicem corporum. Haec omnia fiunt ad sustentationem ACID et rectam datam visibilitatem inter COMMIT'Nutty et' ROLLBACK'Nulla transactions.

Sed hoc non opus est! Habemus totum processum Aut bene aut non erat.. Non refert quot intermediae res futurae sint - non interest "processui a medio continuari", praesertim ubi non liquet ubi esset.

Ad hoc, tincidunt PostgreSQL in versione 9.1 relato, rem talem intulerunt UNLOGGED tables:

Hoc indicio, mensa inexplicabilis creatur. Data ad tabulas inogandatas scriptas non procedit per logam scribentem (cf. cap. 29), causando tales tabulas multo velocius solito. Sed defectui immunes non sunt; si de servo defectum vel subitis shutdown, inceptum mensam automatice mutilum. Accedit, contenta mensae unlogatae non replicatur servis servis. Indices quivis in inloga mensa creati automatice incoepti facti sunt.

Breviter loquendo; multo citius eritat si ministrans datorum "cadit", injucundum erit. Sed quoties hoc evenit, et processus tuus ETL scit quomodo hoc recte "a medio" corrigere datorum post "renovare"?...

Sin minus, et causa superior similis est tuo, utere UNLOGGEDsed numquam hoc non enable attributum in tabulis realibusMauris vitae quam mi.

1.3. DE COMMITTO { DELETE ANTIS | RORO}

Haec constructio permittit ut latis mores specificare cum negotium conficiatur cum mensam creando.

in ON COMMIT DROP Iam supra scripsi, generat . DROP TABLEsed cum ON COMMIT DELETE ROWS condicio magis interesting - generatur hic TRUNCATE TABLE.

Cum tota infrastructura ad tabulam descriptionem tabulae temporalis accommodandam prorsus eadem sit ac tabula regularis, tum. Constans creatio et deletio mensarum temporalium inducit ad graves "tumores" tabularum systematis pg_class, pg_attribute, pg_attrdef, pg_depend,…

Nunc finge te operarium habere in directo nexu cum datorum, qui novam rem omnem secundam aperit, mensam temporalem creat, implet, processus delet. hoc faciet extra ardua dumos ad singulas operationes.

In communi, hoc ne feceris! In hoc casu multo efficacius est CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS Sume eam ex cyclo transactionis - tunc ab initio cuiusque novae transactionis tabulae iam sunt erit (Nisi vocatio CREATE), sed vacua erit, gratias TRUNCATE (Vocatio etiam servata est) cum praecedente transactione perficiendo.

1.4. SICUT... INCLUDING...

In principio monui unum usum casuum typicorum pro tabulis temporalibus esse varias species importat - et elit fesse copy-pastorum elenchus agrorum scopo mensae in declarationem temporis sui...

Sed pigritia est machinatio profectus! Ille est cur creare novam mensam "secundum specimen" multo simplicius esse potest;

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Cum postea multam notitias in hanc tabulam generare potes, per eam quaerens numquam ieiunabit. Sed est traditam solutionem huius - indicis! Et, etiam, ad tempus mensam quoque habere indices.

Cum saepe, indices inquisiti cum indicibus tabulae scopo coincidunt, simpliciter scribere potes LIKE target_table INCLUDING INDEXES.

Si etiam opus DEFAULT-values ​​(exempli gratia, replere valores principales clavis), uti potes LIKE target_table INCLUDING DEFAULTS. Aut simpliciter - LIKE target_table INCLUDING ALL - Exemplar defaltis, indices, angustias, ...

Sed hic debes intelligere, si tu creatus es tabulam importare statim cum indicibus, tunc notitia longioris onerandi capietquam si prius omnia compleveris, tum demum indices subvolve, quomodo hoc in exemplum vide pg_dump.

Fere RTFM!

2. Quomodo scribere?

Dicam modo - utere COPY-flow pro "sarcina" INSERT, acceleratio interdum. Etiam directe ex file praegenerato potes.

3. Quomodo processus?

Intromittamus ergo aliquid simile hoc;

• Habes mensam cum client notitia repono in database 1M records
• cotidie clientem mittit novum plena "imago"
• ab experientia nosti quod a tempore usque ad tempus non magis quam 10K records sunt mutata

A classic exemplum talis condicio est KLADR base - Multae inscriptiones sunt in summa, sed in singulis septimanis onerariis admodum paucae sunt mutationes (renominationum habitationum, platearum iungentium, novarum domorum species) etiam in nationali.

3.1. Plena synchronisation algorithmus

Pro simplicitate, dicamus te ne opus quidem reficere notitias - modo mensam in formam optatam affere, hoc est:

• remove omne quod non est
• update omne quod iam erat et debet renovari
• inserere omne quod nondum factum est

Cur in hoc ordine operantur? Quia haec est quomodo magnitudo mensae minime crescet (memento MVCC!).

Delere a dst

Utique duobus iustis operationibus impetrare potes.

• remove (DELETE) Omnia generatim
• inserere omnia nova imago

Sed simul gratias MVCC; Magnitudo mensae prorsus bis augebitur! Imagines +1M monumentorum in tabula questus propter 10K renovationem tam-sic redundantiae...

TRUNCUS dst

Elit peritior scit totam tabulam satis viliter purgari posse;

• clean (TRUNCATE) Tota mensa
• inserere omnia nova imago

Modus efficax; interdum satis convenitsed quaestio est.

Quae media:

• incipiens erant longum cursus transaction
• TRUNCATE imponit Aditus exclusiva-blocking
• insertionem facimus diu, et omnes alii hoc tempore non potest SELECT

Aliquid non bene...

ALTER TABLE.

Vel potest impleri omnia in novam mensam separatam et tunc simpliciter nominare pro veteri. A duobus sordidis parva sunt:

• adhuc etiam Aditus exclusiva, licet significantly minus temporis
• omnia consilia query / statistica haec mensa sunt reset; ENODO currere necesse est
• omnibus externis claves contrita sunt (FK) ad mensam

Fuit WIP patch de Simon Riggs qui suggessit facere ALTER-an operatio ad reponendas mensas corporis in gradu tabellae, sine tactu statistica et FK, non tamen collecta quorum.

DELETO, UPDATE, INDO

Sic trium operationum optionem non impediendo componimus. Fere tres... Quomodo hoc aptissime?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие

-- вставляем отсутствующие
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2. Import post processus

In eodem KLADR, omnes tabulae mutatae insuper percurrere debent per processus, normalizatas, keywords luces et ad debitas structuras reduci. Sed unde scis - quae prorsus mutatasine codice synchronisationi implicato, specimen sine tactu omnino?

Si modo processus tuus in tempore synchronizationis accessum habet scribe, tunc uti potes felis qui omnes mutationes nobis colliget:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Nunc felis ante synchronization incipiens possumus adhibere (vel per eos perfice ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

Et tunc omnes mutationes quae e tabulis inscriptae desideramus placide extrahunt et per tractores adiectis discurrimus.

3.3. Importat coniunctum Sets

Superius considerandum est de causis, cum notitiae structurae fontis et destinationis eaedem sunt. Sed quid, si impositio ab externo systemate formam habet diversam ab structura repono in nostro database?

Exemplum accipiamus pro clientium repositione earumque rationum, optioni classicae "multa-ad unum";

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

Sed abstractio ab extrinseco ad nos venit in forma « omnia in uno »;

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

Patet, emptoris notitia in hac versione duplicari potest, et principale testimonium "ratio" est:

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

Ad exemplar, notitias nostras simpliciter inseremus, sed memento - COPY efficacius!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

Primum exaggeremus illa quae "secant" ad quae nostra facta referunt. In nostro casu, cautiones ad clientes spectant:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Ut rationes cum IDs emptoribus recte coniungas, primum necesse est has identificatores invenire vel generare. Addamus agros sub illis;

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

Tabula synchronisationi methodo supra descripta parva emendatione utatur - nihil renovationis aut delere in mensa scopo, quia clientes "appendi-tantum" importamus:

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

Nam omnia in invoice_import Nunc contactum habemus in agro repleti client_idcum qua cautionem inseremus.

Source: www.habr.com