рдкреНрд░рд╕реНрддрд╛рд╡рдирд╛ рд╡рд╛ рдЦрдгреНрдбреАрдХрд░рдгрдХреЛ рд╡рд┐рдЪрд╛рд░ рдХрд╕рд░реА рдЖрдпреЛ
рдХрдерд╛ рдпрд╣рд╛рдБ рд╕реБрд░реБ рд╣реБрдиреНрдЫ:
рд▓рд┐рд░рд┐рдХрд▓ рдбрд┐рдЧреНрд░реЗрд╢рди:
рдареНрдпрд╛рдХреНрдХреИ 'рддреНрдпрд╕ рдХреНрд╖рдгрдорд╛', рдХрд┐рдирднрдиреЗ
рддреНрдпрд╕реЛрднрдП, рддрдкрд╛рдЗрдБ рдХрд╕рд░реА рдЧреНрд░рд╛рд╣рдХрд▓рд╛рдИ рдЦреБрд╕реА рдмрдирд╛рдЙрди рд╕рдХреНрдиреБрд╣реБрдиреНрдЫ, рд░ рдПрдХреИ рд╕рдордпрдорд╛ рддрдкрд╛рдЗрдБрдХреЛ рдЖрдлреНрдиреИ рд╕реАрдкрд╣рд░реВ рд╕реБрдзрд╛рд░ рдЧрд░реНрди рд╕рдХреНрдиреБрд╣реБрдиреНрдЫ?
рд╕рдХреЗрд╕рдореНрдо рд╕рдмреИ рдХреБрд░рд╛ рд╕рд░рд▓ рдмрдирд╛рдЙрди, рддреНрдпрд╕рдкрдЫрд┐ рддреНрдпрд╣рд╛рдБ рдбрд╛рдЯрд╛рдмреЗрд╕рдХреЛ рдкреНрд░рджрд░реНрд╢рдирдорд╛ рдХреЗрд╣реА рд╕реБрдзрд╛рд░ рдЧрд░реНрдирдХрд╛ рд▓рд╛рдЧрд┐ рдХреЗрд╡рд▓ рджреБрдИ рддрд░рд┐рдХрд╛рд╣рд░реВ рдЫрдиреН:
1) рд╡рд┐рд╕реНрддреГрдд рдорд╛рд░реНрдЧ - рд╣рд╛рдореА рд╕реНрд░реЛрддрд╣рд░реВ рдмрдврд╛рдЙрдБрдЫреМрдВ, рдХрдиреНрдлрд┐рдЧрд░реЗрд╕рди рдкрд░рд┐рд╡рд░реНрддрди рдЧрд░реНрдЫреМрдВ;
2) рдЧрд╣рди рдорд╛рд░реНрдЧ - рдХреНрд╡реЗрд░реА рдЕрдиреБрдХреВрд▓рди
рдХрд┐рдирдХрд┐, рдо рджреЛрд╣реЛрд░реНрдпрд╛рдЙрдБрдЫреБ, рддреНрдпрд╕ рд╕рдордпрдорд╛ рдпреЛ рдЕрдм рд╕реНрдкрд╖реНрдЯ рдерд┐рдПрди рдХрд┐ рдЧрддрд┐ рдмрдврд╛рдЙрдирдХреЛ рд▓рд╛рдЧрд┐ рдЕрдиреБрд░реЛрдзрдорд╛ рдЕрд░реВ рдХреЗ рдкрд░рд┐рд╡рд░реНрддрди рдЧрд░реНрдиреЗ, рдмрд╛рдЯреЛ рд░реЛрдЬрд┐рдПрдХреЛ рдерд┐рдпреЛ - рддрд╛рд▓рд┐рдХрд╛ рдбрд┐рдЬрд╛рдЗрди рдкрд░рд┐рд╡рд░реНрддрдиред
рддреНрдпрд╕реИрд▓реЗ, рдореБрдЦреНрдп рдкреНрд░рд╢реНрди рдЙрдареНрдЫ: рд╣рд╛рдореА рдХреЗ рд░ рдХрд╕рд░реА рдкрд░рд┐рд╡рд░реНрддрди рд╣реБрдиреЗрдЫ?
рдкреНрд░рд╛рд░рдореНрднрд┐рдХ рд╕рд░реНрддрд╣рд░реВ
рдкрд╣рд┐рд▓реЗ, рддреНрдпрд╣рд╛рдБ рдпреЛ ERD рдЫ (рд╕рд╢рд░реНрдд рд╕рд░рд▓реАрдХреГрдд рддрд░рд┐рдХрд╛рдорд╛ рджреЗрдЦрд╛рдЗрдПрдХреЛ):
рдореБрдЦреНрдп рд╕реБрд╡рд┐рдзрд╛рд╣рд░реВ:
- рдзреЗрд░реИ-рдзреЗрд░реИ рд╕рдореНрдмрдиреНрдзрд╣рд░реВ
- рддрд╛рд▓рд┐рдХрд╛рдорд╛ рдкрд╣рд┐рд▓реЗ рдиреИ рд╕рдореНрднрд╛рд╡рд┐рдд рд╡рд┐рднрд╛рдЬрди рдХреБрдЮреНрдЬреА рдЫ
рдореВрд▓ рдЕрдиреБрд░реЛрдз:
SELECT
p."PARAMETER_ID" as parameter_id,
pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
extract(year from s."SHIPMENT_DATE") AS year,
extract(month from s."SHIPMENT_DATE") as month,
s."REPORT_NAME" AS report_name,
p."SPARAM_NAME" AS SPARAM_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
INNER JOIN ( SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s2 INNER JOIN data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
GROUP BY w2."LASERMARK"
) md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
WHERE
s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30' ;
рдкрд░реАрдХреНрд╖рдг рдбрд╛рдЯрд╛рдмреЗрд╕рдорд╛ рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрдирдХреЛ рдирддрд┐рдЬрд╛:
рд▓рд╛рдЧрдд : 502 997.55
рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╕рдордп: рекрел рд╕реЗрдХреЗрдиреНрдбред
рд╣рд╛рдореА рдХреЗ рджреЗрдЦреНрдЫреМрдВ? рд╕рдордпрдХреЛ рдЯреБрдХреНрд░рд╛рдорд╛ рдЖрдзрд╛рд░рд┐рдд рдирд┐рдпрдорд┐рдд рдЕрдиреБрд░реЛрдзред
рд╕рдмреИрднрдиреНрджрд╛ рд╕рд░рд▓ рддрд╛рд░реНрдХрд┐рдХ рдзрд╛рд░рдгрд╛ рдмрдирд╛рдЙрдиреБрд╣реЛрд╕реН: рдпрджрд┐ рддреНрдпрд╣рд╛рдБ рд╕рдордп рдЯреБрдХреНрд░рд╛рдХреЛ рдирдореВрдирд╛ рдЫ рднрдиреЗ, рдпрд╕рд▓реЗ рд╣рд╛рдореАрд▓рд╛рдИ рдорджреНрджрдд рдЧрд░реНрдиреЗрдЫ? рддреНрдпреЛ рд╕рд╣реА рдЫ - рд╡рд┐рднрд╛рдЬрдиред
рдХреЗ рдЦрдгреНрдб рдЧрд░реНрдиреЗ?
рдкрд╣рд┐рд▓реЛ рдирдЬрд░рдорд╛, рдЫрдиреМрдЯ рд╕реНрдкрд╖реНрдЯ рдЫ - "SHIPMENT_DATE" рдХреБрдЮреНрдЬреА рдкреНрд░рдпреЛрдЧ рдЧрд░реЗрд░ "рд╢рд┐рдкрдореЗрдиреНрдЯ" рддрд╛рд▓рд┐рдХрд╛рдХреЛ рдШреЛрд╖рдгрд╛рддреНрдордХ рд╡рд┐рднрд╛рдЬрди (рдзреЗрд░реИ рдЯрд╛рдврд╛ рдЬрдореНрдк рдЧрд░реНрджреИ - рдЕрдиреНрддрдорд╛ рдпреЛ рдЙрддреНрдкрд╛рджрди рдорд╛ рдЕрд▓рд┐ рдЧрд▓рдд рднрдпреЛ).
рдХрд╕рд░реА рд╡рд┐рднрд╛рдЬрди рдЧрд░реНрдиреЗ?
рдпреЛ рдкреНрд░рд╢реНрди рдкрдирд┐ рдзреЗрд░реИ рдЧрд╛рд╣реНрд░реЛ рдЫреИрдиред рд╕реМрднрд╛рдЧреНрдп рджреЗрдЦрд┐, PostgreSQL 10 рдорд╛, рддреНрдпрд╣рд╛рдБ рдЕрдм рдорд╛рдирд╡ рд╡рд┐рднрд╛рдЬрди рд╕рдВрдпрдиреНрддреНрд░ рдЫред
рддреНрдпрд╕реИрд▓реЗ:
- рд╕реНрд░реЛрдд рддрд╛рд▓рд┐рдХрд╛рдХреЛ рдбрдореНрдк рдмрдЪрдд рдЧрд░реНрдиреБрд╣реЛрд╕реН - pg_dump рд╕реНрд░реЛрдд_рддрд╛рд▓рд┐рдХрд╛
- рдореВрд▓ рддрд╛рд▓рд┐рдХрд╛ рдореЗрдЯрд╛рдЙрдиреБрд╣реЛрд╕реН - рдбреНрд░рдк рддрд╛рд▓рд┐рдХрд╛ рд╕реНрд░реЛрдд_рддрд╛рд▓рд┐рдХрд╛
- рджрд╛рдпрд░рд╛ рд╡рд┐рднрд╛рдЬрдирдХреЛ рд╕рд╛рде рдЕрднрд┐рднрд╛рд╡рдХ рддрд╛рд▓рд┐рдХрд╛ рдмрдирд╛рдЙрдиреБрд╣реЛрд╕реН - рддрд╛рд▓рд┐рдХрд╛ рд╕реНрд░реЛрдд_рддрд╛рд▓рд┐рдХрд╛ рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрдиреБрд╣реЛрд╕реН
- рдЦрдгреНрдбрд╣рд░реВ рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрдиреБрд╣реЛрд╕реН - рддрд╛рд▓рд┐рдХрд╛ рд╕реНрд░реЛрдд_рддрд╛рд▓рд┐рдХрд╛ рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрдиреБрд╣реЛрд╕реН, рдЕрдиреБрдХреНрд░рдордгрд┐рдХрд╛ рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрдиреБрд╣реЛрд╕реН
- рдЪрд░рдг 1 рдорд╛ рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░рд┐рдПрдХреЛ рдбрдореНрдк рдЖрдпрд╛рдд рдЧрд░реНрдиреБрд╣реЛрд╕реН - pg_restore
рд╡рд┐рднрд╛рдЬрдирдХрд╛ рд▓рд╛рдЧрд┐ рд▓рд┐рдкрд┐рд╣рд░реВ
рд╕рд░рд▓рддрд╛ рд░ рд╕реБрд╡рд┐рдзрд╛рдХреЛ рд▓рд╛рдЧрд┐, рдЪрд░рдг 2,3,4 рд▓рд╛рдИ рдПрдЙрдЯреИ рд▓рд┐рдкрд┐рдорд╛ рдЬреЛрдбрд┐рдПрдХреЛ рдЫред
рддреНрдпрд╕реИрд▓реЗ:
рд╕реНрд░реЛрдд рддрд╛рд▓рд┐рдХрд╛рдХреЛ рдбрдореНрдк рдмрдЪрдд рдЧрд░реНрдиреБрд╣реЛрд╕реН
pg_dump postgres --file=/dump/shipment.dmp --format=c --table=shipment --verbose > /dump/shipment.log 2>&1
рд╕реНрд░реЛрдд рддрд╛рд▓рд┐рдХрд╛ рдореЗрдЯрд╛рдЙрдиреБрд╣реЛрд╕реН + рджрд╛рдпрд░рд╛ рд╡рд┐рднрд╛рдЬрдирдХреЛ рд╕рд╛рде рдЕрднрд┐рднрд╛рд╡рдХ рддрд╛рд▓рд┐рдХрд╛ рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрдиреБрд╣реЛрд╕реН + рд╡рд┐рднрд╛рдЬрдирд╣рд░реВ рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрдиреБрд╣реЛрд╕реН
--create_partition_shipment.sql
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;
RAISE NOTICE 'DROP TABLE shipment';
drop table shipment cascade ;
CREATE TABLE public.shipment
(
"SHIPMENT_ID" integer NOT NULL DEFAULT nextval('shipment_shipment_id_seq'::regclass),
"SHIPMENT_NAME" character varying(30) COLLATE pg_catalog."default",
"SHIPMENT_DATE" timestamp without time zone,
"REPORT_NAME" character varying(40) COLLATE pg_catalog."default"
)
PARTITION BY RANGE ("SHIPMENT_DATE")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
RAISE NOTICE 'CREATE PARTITIONS FOR TABLE shipment';
current_year:='0';
current_month:='0';
begin_year := '0' ;
begin_month := '0' ;
next_year := '0' ;
next_month := '0' ;
FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ;
IF to_number(current_month,'99') < 10 THEN
current_month := '0'||current_month ;
END IF ;
--Init borders
IF begin_year = '0' THEN
first_flag := true ; --first time flag
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
-- Check current date into borders NOT for First time
IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND
to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND
NOT first_flag
THEN
CONTINUE ;
ELSE
--NEW borders only for second and after time
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
partition_name := 'shipment_shipment_date_'||begin_year||'-'||begin_month||'-01-'|| next_year||'-'||next_month||'-01' ;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF shipment FOR VALUES FROM ( %L ) TO ( %L ) ' , current_year||'-'||current_month||'-01' , next_year||'-'||next_month||'-01' ) ;
index_name := partition_name||'_shipment_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
--Drop first time flag
first_flag := false ;
END LOOP;
end
$$;
рдбрдореНрдк рдЖрдпрд╛рдд рдЧрд░реНрджреИ
pg_restore -d postgres --data-only --format=c --table=shipment --verbose shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1
рд╡рд┐рднрд╛рдЬрди рдкрд░рд┐рдгрд╛рдорд╣рд░реВ рдЬрд╛рдБрдЪ рдЧрд░реНрджреИ
рдирддрд┐рдЬрд╛рдХреЛ рд░реВрдкрдорд╛ рд╣рд╛рдореАрд╕рдБрдЧ рдХреЗ рдЫ? рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рдпреЛрдЬрдирд╛рдХреЛ рдкреВрд░реНрдг рдкрд╛рда рдареВрд▓реЛ рд░ рдмреЛрд░рд┐рдВрдЧ рдЫ, рддреНрдпрд╕реИрд▓реЗ рдпреЛ рдЖрдлреИрд▓рд╛рдИ рдЕрдиреНрддрд┐рдо рд╕рдВрдЦреНрдпрд╛рд╣рд░реВрдорд╛ рд╕реАрдорд┐рдд рдЧрд░реНрди рд╕рдореНрднрд╡ рдЫред
рдерд┐рдпреЛ
рд▓рд╛рдЧрдд: 502 997.55
рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╕рдордп: 505 рд╕реЗрдХреЗрдиреНрдбред
рднрдПрдХреЛ рдЫ
рд▓рд╛рдЧрдд: 77 872.36
рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╕рдордп: 79 рд╕реЗрдХреЗрдиреНрдбред
рдПрдХрджрдо рд░рд╛рдореНрд░реЛ рдирддрд┐рдЬрд╛ред рдХрдо рд▓рд╛рдЧрдд рд░ рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╕рдордпред рдпрд╕рд░реА, рд╡рд┐рднрд╛рдЬрдирдХреЛ рдкреНрд░рдпреЛрдЧрд▓реЗ рдЕрдкреЗрдХреНрд╖рд┐рдд рдкреНрд░рднрд╛рд╡ рджрд┐рдиреНрдЫ рд░, рд╕рд╛рдорд╛рдиреНрдпрддрдпрд╛, рдХреБрдиреИ рдЖрд╢реНрдЪрд░реНрдп рдЫреИрдиред
рдЧреНрд░рд╛рд╣рдХрд▓рд╛рдИ рдЦреБрд╕реА рдмрдирд╛рдЙрдиреБрд╣реЛрд╕реН
рдкрд░реАрдХреНрд╖рдг рдкрд░рд┐рдгрд╛рдорд╣рд░реВ рд╕рдореАрдХреНрд╖рд╛рдХреЛ рд▓рд╛рдЧрд┐ рдЧреНрд░рд╛рд╣рдХрд▓рд╛рдИ рдкреНрд░рд╕реНрддреБрдд рдЧрд░рд┐рдпреЛред рд░ рдпрд╕рд▓рд╛рдИ рд╕рдореАрдХреНрд╖рд╛ рдЧрд░реЗрдкрдЫрд┐, рдЙрдиреАрд╣рд░реВрд▓рд╛рдИ рдХреЗрд╣реА рд╣рджрд╕рдореНрдо рдЕрдкреНрд░рддреНрдпрд╛рд╢рд┐рдд рдирд┐рд░реНрдгрдп рджрд┐рдЗрдпреЛ: "рдареВрд▓реЛ, "рдбреЗрдЯрд╛" рддрд╛рд▓рд┐рдХрд╛ рд╡рд┐рднрд╛рдЬрди рдЧрд░реНрдиреБрд╣реЛрд╕реНред
рд╣реЛ, рддрд░ рд╣рд╛рдореАрд▓реЗ рдкреВрд░реНрдг рд░реВрдкрдорд╛ рдлрд░рдХ "рдвреБрд╡рд╛рдиреА" рддрд╛рд▓рд┐рдХрд╛ рдЬрд╛рдБрдЪреНрдпреМрдВ; "рдбреЗрдЯрд╛" рддрд╛рд▓рд┐рдХрд╛рдорд╛ "SHIPMENT_DATE" рдлрд┐рд▓реНрдб рдЫреИрдиред
рдХреБрдиреИ рд╕рдорд╕реНрдпрд╛ рдЫреИрди, рдердкреНрдиреБрд╣реЛрд╕реН, рдкрд░рд┐рд╡рд░реНрддрди рдЧрд░реНрдиреБрд╣реЛрд╕реНред рдореБрдЦреНрдп рдХреБрд░рд╛ рдпреЛ рд╣реЛ рдХрд┐ рдЧреНрд░рд╛рд╣рдХ рдкрд░рд┐рдгрд╛рдо рд╕рдВрдЧ рд╕рдиреНрддреБрд╖реНрдЯ рдЫ; рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╡рд┐рд╡рд░рдг рд╡рд┐рд╢реЗрд╖ рдЧрд░реА рдорд╣рддреНрддреНрд╡рдкреВрд░реНрдг рдЫреИрдиред
рдореБрдЦреНрдп рддрд╛рд▓рд┐рдХрд╛ "рдбреЗрдЯрд╛" рд╡рд┐рднрд╛рдЬрди рдЧрд░реНрджреИ
рд╕рд╛рдорд╛рдиреНрдпрддрдпрд╛, рдХреБрдиреИ рд╡рд┐рд╢реЗрд╖ рдХрдард┐рдирд╛рдЗрд╣рд░реВ рдЙрддреНрдкрдиреНрди рднрдПрдирдиреНред рдпрджреНрдпрдкрд┐, рд╡рд┐рднрд╛рдЬрди рдПрд▓реНрдЧреЛрд░рд┐рдереНрдо, рдЕрд╡рд╢реНрдп рдкрдирд┐, рдХреЗрд╣рд┐ рдкрд░рд┐рд╡рд░реНрддрди рднрдПрдХреЛ рдЫред
"рдбреЗрдЯрд╛" рддрд╛рд▓рд┐рдХрд╛рдорд╛ "SHIPMENT_DATA" рд╕реНрддрдореНрдн рдердкреНрджреИ
psql -h ╤Е╨╛╤Б╤В -U ╨▒╨░╨╖╨░ -d ╤О╨╖╨╡╤А
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;
"рдвреБрд╡рд╛рдиреА" рддрд╛рд▓рд┐рдХрд╛рдмрд╛рдЯ рдЙрд╣реА рдирд╛рдордХреЛ рд╕реНрддрдореНрднрдХреЛ рдорд╛рдирд╣рд░реВрд╕рдБрдЧ "рдбреЗрдЯрд╛" рддрд╛рд▓рд┐рдХрд╛рдорд╛ "SHIPMENT_DATA" рд╕реНрддрдореНрднрдХреЛ рдорд╛рдирд╣рд░реВ рднрд░реНрдиреБрд╣реЛрд╕реНред
-----------------------------
--update_data.sql
--updating for altered table "data" to values of "shipment_data" from the table "shipment"
--version 1.0
do language plpgsql $$
declare
rec_shipment_data RECORD ;
shipment_date timestamp without time zone ;
row_count integer ;
total_rows integer ;
begin
select count(*) into total_rows from shipment ;
RAISE NOTICE 'Total %',total_rows;
row_count:= 0 ;
FOR rec_shipment_data IN SELECT * FROM shipment LOOP
update data set "SHIPMENT_DATE" = rec_shipment_data."SHIPMENT_DATE" where "SHIPMENT_ID" = rec_shipment_data."SHIPMENT_ID";
row_count:= row_count +1 ;
RAISE NOTICE 'row count = % , from %',row_count,total_rows;
END LOOP;
end
$$;
"рдбреЗрдЯрд╛" рддрд╛рд▓рд┐рдХрд╛рдХреЛ рдбрдореНрдк рдмрдЪрдд рдЧрд░реНрдиреБрд╣реЛрд╕реН
pg_dump postgres --file=/dump/data.dmp --format=c --table=data --verbose > /dump/data.log 2>&1</source
рд╡рд┐рднрд╛рдЬрди рдЧрд░рд┐рдПрдХреЛ рддрд╛рд▓рд┐рдХрд╛ "рдбреЗрдЯрд╛" рдкреБрди: рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрдиреБрд╣реЛрд╕реН
--create_partition_data.sql
--create partitions for the table "wafer data" by range column "shipment_data" with one month duration
--version 1.0
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;
RAISE NOTICE 'DROP TABLE data';
drop table data cascade ;
RAISE NOTICE 'CREATE PARTITIONED TABLE data';
CREATE TABLE public.data
(
"RUN_ID" integer,
"LASERMARK" character varying(20) COLLATE pg_catalog."default" NOT NULL,
"LOTID" character varying(80) COLLATE pg_catalog."default",
"SHIPMENT_ID" integer NOT NULL,
"PARAMETER_ID" integer NOT NULL,
"INTERNAL_VALUE" character varying(75) COLLATE pg_catalog."default",
"REPORTED_VALUE" character varying(75) COLLATE pg_catalog."default",
"LOWER_SPEC_LIMIT" numeric,
"UPPER_SPEC_LIMIT" numeric ,
"SHIPMENT_DATE" timestamp without time zone
)
PARTITION BY RANGE ("SHIPMENT_DATE")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default ;
RAISE NOTICE 'CREATE PARTITIONS FOR TABLE data';
current_year:='0';
current_month:='0';
begin_year := '0' ;
begin_month := '0' ;
next_year := '0' ;
next_month := '0' ;
i := 1;
FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ;
--Init borders
IF begin_year = '0' THEN
RAISE NOTICE '***Init borders';
first_flag := true ; --first time flag
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
-- RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
-- RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
-- RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;
-- Check current date into borders NOT for First time
RAISE NOTICE 'Current data = %',to_char( to_date( current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Begin data = %',to_char( to_date( begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Next data = %',to_char( to_date( next_year||'.'||next_month, 'YYYY.MM'), 'YYYY.MM');
IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND
to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND
NOT first_flag
THEN
RAISE NOTICE '***CONTINUE';
CONTINUE ;
ELSE
--NEW borders only for second and after time
RAISE NOTICE '***NEW BORDERS';
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
IF to_number(current_month,'99') < 10 THEN
current_month := '0'||current_month ;
END IF ;
IF to_number(begin_month,'99') < 10 THEN
begin_month := '0'||begin_month ;
END IF ;
IF to_number(next_month,'99') < 10 THEN
next_month := '0'||next_month ;
END IF ;
RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;
partition_name := 'data_'||begin_year||begin_month||'01_'||next_year||next_month||'01' ;
RAISE NOTICE 'PARTITION NUMBER % , TABLE NAME =%',i , partition_name;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L ) ' , begin_year||'-'||begin_month||'-01' , next_year||'-'||next_month||'-01' ) ;
index_name := partition_name||'_shipment_id_parameter_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_lasermark_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_parameter_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_date_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ;
--Drop first time flag
first_flag := false ;
END LOOP;
end
$$;
рдЪрд░рдг 3 рдорд╛ рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░рд┐рдПрдХреЛ рдбрдореНрдк рд▓реЛрдб рдЧрд░реНрдиреБрд╣реЛрд╕реНред
pg_restore -h ╤Е╨╛╤Б╤В -╤О╨╖╨╡╤А -d ╨▒╨░╨╖╨░ --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1
рдкреБрд░рд╛рдиреЛ рдбрд╛рдЯрд╛рдХреЛ рд▓рд╛рдЧрд┐ рдЫреБрдЯреНрдЯреИ рдЦрдгреНрдб рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрдиреБрд╣реЛрд╕реН
---------------------------------------------------
--create_partition_for_old_dates.sql
--create partitions for keeping old dates
--version 1.0
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
begin
SELECT min("SHIPMENT_DATE") AS min_date INTO rec_shipment_date from data ;
RAISE NOTICE 'Old date is %',rec_shipment_date.min_date ;
partition_name := 'data_old_dates' ;
RAISE NOTICE 'PARTITION NAME IS %',partition_name;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L ) ' , '1900-01-01' ,
to_char( rec_shipment_date.min_date,'YYYY')||'-'||to_char(rec_shipment_date.min_date,'MM')||'-01' ) ;
index_name := partition_name||'_shipment_id_parameter_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_lasermark_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_parameter_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_date_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ;
end
$$;
рдЕрдиреНрддрд┐рдо рдирддрд┐рдЬрд╛:
рдерд┐рдпреЛ
рд▓рд╛рдЧрдд: 502 997.55
рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╕рдордп: рекрел рд╕реЗрдХреЗрдиреНрдбред
рднрдПрдХреЛ рдЫ
рд▓рд╛рдЧрдд: 68 533.70
рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╕рдордп: 69 рд╕реЗрдХреЗрдиреНрдб
рдпреЛрдЧреНрдп, рдзреЗрд░реИ рдпреЛрдЧреНрдпред рд░ рдпреЛ рд╡рд┐рдЪрд╛рд░ рдЧрд░реНрджрд╛ рд╣рд╛рдореАрд▓реЗ PostgreSQL 10 рдорд╛ рд╡рд┐рднрд╛рдЬрди рдЧрд░реНрдиреЗ рд╕рдВрдпрдиреНрддреНрд░рд▓рд╛рдИ рдХрдо рд╡рд╛ рдХрдо рдорд╛рд╕реНрдЯрд░ рдЧрд░реНрди рд╡реНрдпрд╡рд╕реНрдерд┐рдд рдЧрд░реНрдпреМрдВ - рдПрдХ рдЙрддреНрдХреГрд╖реНрдЯ рдкрд░рд┐рдгрд╛рдоред
рд▓рд┐рд░рд┐рдХрд▓ рдбрд┐рдЧреНрд░реЗрд╢рди
рдХреЗ рдпреЛ рдЕрдЭ рд░рд╛рдореНрд░реЛ рдЧрд░реНрди рд╕рдореНрднрд╡ рдЫ - рд╣реЛ, рддрдкрд╛рдИрдВ рд╕рдХреНрдиреБрд╣реБрдиреНрдЫ!рдпреЛ рдЧрд░реНрдирдХреЛ рд▓рд╛рдЧрд┐ рддрдкрд╛рдИрд▓реЗ рднреМрддрд┐рдХ рджреГрд╢реНрдп рдкреНрд░рдпреЛрдЧ рдЧрд░реНрди рдЖрд╡рд╢реНрдпрдХ рдЫред
MATERIALized рджреГрд╢реНрдп LASERMARK_VIEW рд╕рд┐рд░реНрдЬрдирд╛ рдЧрд░реНрдиреБрд╣реЛрд╕реН
CREATE MATERIALIZED VIEW LASERMARK_VIEW
AS
SELECT w."LASERMARK" , MAX(s."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s INNER JOIN data w ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
GROUP BY w."LASERMARK" ;
CREATE INDEX lasermark_vw_shipment_date_ind on lasermark_view USING btree ("SHIPMENT_DATE") TABLESPACE pg_default;
analyze lasermark_view ;
рдПрдХ рдкрдЯрдХ рдлреЗрд░рд┐ рд╣рд╛рдореА рдЕрдиреБрд░реЛрдз рдкреБрди: рд▓реЗрдЦреНрдЫреМрдВ:
рднреМрддрд┐рдХ рджреГрд╢реНрдп рдкреНрд░рдпреЛрдЧ рдЧрд░реЗрд░ рдХреНрд╡реЗрд░реА
SELECT
p."PARAMETER_ID" as parameter_id,
pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
extract(year from s."SHIPMENT_DATE") AS year,
extract(month from s."SHIPMENT_DATE") as month,
s."REPORT_NAME" AS report_name,
p."STC_NAME" AS STC_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
INNER JOIN LASERMARK_VIEW md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
WHERE
s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30';
рд░ рд╣рд╛рдореА рдЕрд░реНрдХреЛ рдкрд░рд┐рдгрд╛рдо рдкреНрд░рд╛рдкреНрдд:
рдерд┐рдпреЛ
рд▓рд╛рдЧрдд: 502 997.55
рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╕рдордпреж. 505 рд╕реЗрдХреЗрдиреНрдб
рднрдПрдХреЛ рдЫ
рд▓рд╛рдЧрдд: 42 481.16
рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╕рдордп: 43 рд╕реЗрдХреЗрдиреНрдбред
рдпрджреНрдпрдкрд┐, рдирд┐рд╕реНрд╕рдиреНрджреЗрд╣, рдпрд╕реНрддреЛ рдЖрд╢рд╛рдЬрдирдХ рдирддрд┐рдЬрд╛ рднреНрд░рд╛рдордХ рдЫ; рд╡рд┐рдЪрд╛рд░рд╣рд░реВ рддрд╛рдЬрд╛ рдЧрд░реНрди рдЖрд╡рд╢реНрдпрдХ рдЫред рддреНрдпрд╕реИрд▓реЗ рдбрд╛рдЯрд╛ рдкреНрд░рд╛рдкреНрдд рдЧрд░реНрдирдХреЛ рд▓рд╛рдЧрд┐ рдХреБрд▓ рд╕рдордпрд▓реЗ рдзреЗрд░реИ рдорджреНрджрдд рдЧрд░реНрджреИрдиред рддрд░ рдПрдХ рдкреНрд░рдпреЛрдЧрдХреЛ рд░реВрдкрдорд╛ рдпреЛ рдзреЗрд░реИ рд░реЛрдЪрдХ рдЫред
рд╡рд╛рд╕реНрддрд╡рдорд╛, рдпреЛ рдмрд╛рд╣рд┐рд░ рдирд┐рд╕реНрдХрд┐рдпреЛ, рдлреЗрд░рд┐ рдзрдиреНрдпрд╡рд╛рдж
рдкрдЫрд┐рд╢рдмреНрдж
рддреНрдпрд╕реИрд▓реЗ, рдЧреНрд░рд╛рд╣рдХ рд╕рдиреНрддреБрд╖реНрдЯ рдЫрдиреНред рд░ рдЪрд╛рд╣рд┐рдиреНрдЫ рд╕реНрдерд┐рддрд┐рдХреЛ рдлрд╛рдЗрджрд╛ рд▓рд┐рдиреБрд╣реЛрд╕реНред
рдирдпрд╛рдБ рдХрд╛рд░реНрдп: рдЧрд╣рд┐рд░реЛ рд░ рд╡рд┐рд╕реНрддрд╛рд░ рдЧрд░реНрди рддрдкрд╛рдИрдВ рдХреЗ рдЧрд░реНрди рд╕рдХреНрдиреБрд╣реБрдиреНрдЫ?
рд░ рддреНрдпрд╕рдкрдЫрд┐ рдорд▓рд╛рдИ рдпрд╛рдж рдЫ - рд╕рд╛рдереАрд╣рд░реВ, рд╣рд╛рдореАрд╕рдБрдЧ рд╣рд╛рдореНрд░реЛ PostgreSQL рдбрд╛рдЯрд╛рдмреЗрд╕рд╣рд░реВрдХреЛ рдирд┐рдЧрд░рд╛рдиреА рдЫреИрдиред
рд╣реГрджрдпрдорд╛ рд╣рд╛рдд, AWS рдорд╛ рдХреНрд▓рд╛рдЙрдб рд╡рд╛рдЪрдХреЛ рд░реВрдкрдорд╛ рдЕрдЭреИ рдкрдирд┐ рдХреЗрд╣реА рдирд┐рдЧрд░рд╛рдиреА рдЫред рддрд░ DBA рдХреЛ рд▓рд╛рдЧрд┐ рдпреЛ рдирд┐рдЧрд░рд╛рдиреА рдХреЛ рд▓рд╛рдн рдХреЗ рд╣реЛ? рд╕рд╛рдорд╛рдиреНрдпрддрдпрд╛, рд╡реНрдпрд╛рд╡рд╣рд╛рд░рд┐рдХ рд░реВрдкрдорд╛ рдХреБрдиреИ рдкрдирд┐ред
рдпрджрд┐ рддрдкрд╛рдИрдВрд╕рдБрдЧ рдЖрдлреНрдиреЛ рд▓рд╛рдЧрд┐ рдЙрдкрдпреЛрдЧреА рд░ рдЪрд╛рдЦрд▓рд╛рдЧреНрджреЛ рдХреБрд░рд╛ рдЧрд░реНрдиреЗ рдореМрдХрд╛ рдЫ рднрдиреЗ, рддрдкрд╛рдИрдВрд▓реЗ рдпреЛ рдореМрдХрд╛рдХреЛ рдлрд╛рдЗрджрд╛ рдЙрдард╛рдЙрди рд╕рдХреНрдиреБрд╣реБрдиреНрди ...
рдХреЛ рд▓рд╛рдЧреА
рдпрд╕рд░реА рд╣рд╛рдореА рд╕рдмреИрднрдиреНрджрд╛ рд░реЛрдЪрдХ рднрд╛рдЧрдорд╛ рдЖрдЙрдБрдЫреМрдВ:
рдбрд┐рд╕реЗрдореНрдмрд░ 3, 2018ред
PostgreSQL рдкреНрд░рд╢реНрдирд╣рд░реВрдХреЛ рдХрд╛рд░реНрдпрд╕рдореНрдкрд╛рджрди рдЕрдиреБрдЧрдордирдХреЛ рд▓рд╛рдЧрд┐ рдЙрдкрд▓рдмреНрдз рдХреНрд╖рдорддрд╛рд╣рд░реВрдорд╛ рдЕрдиреБрд╕рдиреНрдзрд╛рди рд╕реБрд░реБ рдЧрд░реНрдиреЗ рдирд┐рд░реНрдгрдп рдЧрд░реНрджреИред
рддрд░ рддреНрдпреЛ рдПрдХрджрдо рдлрд░рдХ рдХрдерд╛ рд╣реЛред
рдлреЗрд░рд┐ рдЬрд╛рд░реА рд░рд╛рдЦреМрдВрд▓рд╛тАж
рд╕реНрд░реЛрдд: www.habr.com