рдкреНрд░рд╕реНрддрд╛рд╡рдирд╛ рдпрд╛ рдЕрдиреБрднрд╛рдЧреАрдХрд░рдг рдХрд╛ рд╡рд┐рдЪрд╛рд░ рдХреИрд╕реЗ рдЖрдпрд╛
рдХрд╣рд╛рдиреА рдпрд╣рд╛рдВ рд╕реЗ рд╢реБрд░реВ рд╣реЛрддреА рд╣реИ:
рдЧреАрддрд╛рддреНрдордХ рд╡рд┐рд╖рдпрд╛рдВрддрд░:
рд╕рдЯреАрдХ рд░реВрдк рд╕реЗ 'рдЙрд╕ рдХреНрд╖рдг', рдХреНрдпреЛрдВрдХрд┐
рддреЛ, рдЖрдк рдЧреНрд░рд╛рд╣рдХ рдХреЛ рдХреИрд╕реЗ рдЦреБрд╢ рдХрд░ рд╕рдХрддреЗ рд╣реИрдВ рдФрд░ рд╕рд╛рде рд╣реА рдЕрдкрдиреЗ рдХреМрд╢рд▓ рдореЗрдВ рд╕реБрдзрд╛рд░ рдХреИрд╕реЗ рдХрд░ рд╕рдХрддреЗ рд╣реИрдВ?
рдЬрд┐рддрдирд╛ рд╕рдВрднрд╡ рд╣реЛ рд╕рдХреЗ рд╣рд░ рдЪреАрдЬрд╝ рдХреЛ рд╕рд░рд▓ рдмрдирд╛рдирд╛, рддреЛ рдбреЗрдЯрд╛рдмреЗрд╕ рдХреЗ рдкреНрд░рджрд░реНрд╢рди рдореЗрдВ рдореМрд▓рд┐рдХ рд░реВрдк рд╕реЗ рдХреБрдЫ рд╕реБрдзрд╛рд░ рдХрд░рдиреЗ рдХреЗ рдХреЗрд╡рд▓ рджреЛ рддрд░реАрдХреЗ рд╣реИрдВ:
1) рд╡реНрдпрд╛рдкрдХ рдкрде - рд╣рдо рд╕рдВрд╕рд╛рдзрди рдмрдврд╝рд╛рддреЗ рд╣реИрдВ, рдХреЙрдиреНрдлрд╝рд┐рдЧрд░реЗрд╢рди рдмрджрд▓рддреЗ рд╣реИрдВ;
2) рдЧрд╣рди рдкрде - рдХреНрд╡реЗрд░реА рдЕрдиреБрдХреВрд▓рди
рдЪреВрдБрдХрд┐, рдореИрдВ рджреЛрд╣рд░рд╛рддрд╛ рд╣реВрдБ, рдЙрд╕ рд╕рдордп рдпрд╣ рд╕реНрдкрд╖реНрдЯ рдирд╣реАрдВ рдерд╛ рдХрд┐ рдЧрддрд┐ рдмрдврд╝рд╛рдиреЗ рдХреЗ рдЕрдиреБрд░реЛрдз рдореЗрдВ рдФрд░ рдХреНрдпрд╛ рдмрджрд▓рд╛ рдЬрд╛рдП, рд░рд╛рд╕реНрддрд╛ рдЪреБрдирд╛ рдЧрдпрд╛ - рдЯреЗрдмрд▓ рдбрд┐рдЬрд╝рд╛рдЗрди рдореЗрдВ рдкрд░рд┐рд╡рд░реНрддрдиред
рддреЛ, рдореБрдЦреНрдп рдкреНрд░рд╢реНрди рдЙрдарддрд╛ рд╣реИ: рд╣рдо рдХреНрдпрд╛ рдФрд░ рдХреИрд╕реЗ рдмрджрд▓реЗрдВрдЧреЗ?
рдЖрд░рдВрднрд┐рдХ рд╕реНрдерд┐рддрд┐рдпрд╛рдВ
рд╕рдмрд╕реЗ рдкрд╣рд▓реЗ, рдпрд╣ рдИрдЖрд░рдбреА рд╣реИ (рд╕рд╢рд░реНрдд рд╕рд░рд▓ рддрд░реАрдХреЗ рд╕реЗ рджрд┐рдЦрд╛рдпрд╛ рдЧрдпрд╛ рд╣реИ):
рдореБрдЦреНрдп рд╡рд┐рд╢реЗрд╖рддрд╛рдПрдВ:
- рдЕрдиреЗрдХ-рд╕реЗ-рдЕрдиреЗрдХ рд╕рдВрдмрдВрдз
- рддрд╛рд▓рд┐рдХрд╛ рдореЗрдВ рдкрд╣рд▓реЗ рд╕реЗ рд╣реА рдПрдХ рд╕рдВрднрд╛рд╡рд┐рдд рд╡рд┐рднрд╛рдЬрди рдХреБрдВрдЬреА рд╣реИ
рдореВрд▓ рдЕрдиреБрд░реЛрдз:
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
рдирд┐рд╖реНрдкрд╛рджрди рд╕рдордп: 505 рд╕реЗрдХрдВрдбред
рд╣рдо рдХреНрдпрд╛ рджреЗрдЦрддреЗ рд╣реИрдВ? рд╕рдордп рдЦрдВрдб рдХреЗ рдЖрдзрд╛рд░ рдкрд░ рдПрдХ рдирд┐рдпрдорд┐рдд рдЕрдиреБрд░реЛрдзред
рдЖрдЗрдП рд╕рдмрд╕реЗ рд╕рд░рд▓ рддрд╛рд░реНрдХрд┐рдХ рдзрд╛рд░рдгрд╛ рдмрдирд╛рдПрдВ: рдпрджрд┐ рд╕рдордп рд╕реНрд▓рд╛рдЗрд╕ рдХрд╛ рдХреЛрдИ рдирдореВрдирд╛ рд╣реИ, рддреЛ рдХреНрдпрд╛ рдпрд╣ рд╣рдорд╛рд░реА рдорджрдж рдХрд░реЗрдЧрд╛? рдпрд╣ рд╕рд╣реА рд╣реИ - рд╡рд┐рднрд╛рдЬрди.
рдХреНрдпрд╛ рд╕реЗрдХреНрд╢рди рдХрд░реЗрдВ?
рдкрд╣рд▓реА рдирдЬрд╝рд░ рдореЗрдВ, рд╡рд┐рдХрд▓реНрдк рд╕реНрдкрд╖реНрдЯ рд╣реИ - "SHIPMENT_DATE" рдХреБрдВрдЬреА рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд░рдХреЗ "рд╢рд┐рдкрдореЗрдВрдЯ" рддрд╛рд▓рд┐рдХрд╛ рдХрд╛ рдШреЛрд╖рдгрд╛рддреНрдордХ рд╡рд┐рднрд╛рдЬрди (рдмрд╣реБрдд рдЖрдЧреЗ рдирд┐рдХрд▓ рдЬрд╛рдирд╛ - рдЕрдВрдд рдореЗрдВ рдЙрддреНрдкрд╛рджрди рдореЗрдВ рдпрд╣ рдереЛрдбрд╝рд╛ рдЧрд▓рдд рдирд┐рдХрд▓рд╛).
рд╡рд┐рднрд╛рдЬрди рдХреИрд╕реЗ рдХрд░реЗрдВ?
рдпреЗ рд╕рд╡рд╛рд▓ рдЬреНрдпрд╛рджрд╛ рдХрдард┐рди рднреА рдирд╣реАрдВ рд╣реИ. рд╕реМрднрд╛рдЧреНрдп рд╕реЗ, PostgreSQL 10 рдореЗрдВ, рдЕрдм рдПрдХ рдорд╛рдирд╡ рд╡рд┐рднрд╛рдЬрди рддрдВрддреНрд░ рд╣реИред
рддреЛ:
- рд╕реНрд░реЛрдд рддрд╛рд▓рд┐рдХрд╛ рдХрд╛ рдбрдВрдк рд╕рд╣реЗрдЬреЗрдВ - pg_dump source_table
- рдореВрд▓ рддрд╛рд▓рд┐рдХрд╛ рд╣рдЯрд╛рдПрдБ - рдбреНрд░реЙрдк рдЯреЗрдмрд▓ source_table
- рд╢реНрд░реЗрдгреА рд╡рд┐рднрд╛рдЬрди рдХреЗ рд╕рд╛рде рдПрдХ рдореВрд▓ рддрд╛рд▓рд┐рдХрд╛ рдмрдирд╛рдПрдВ - рддрд╛рд▓рд┐рдХрд╛ рдмрдирд╛рдПрдВ source_table
- рдЕрдиреБрднрд╛рдЧ рдмрдирд╛рдПрдВ - рддрд╛рд▓рд┐рдХрд╛ рдмрдирд╛рдПрдВ source_table, рдЕрдиреБрдХреНрд░рдордгрд┐рдХрд╛ рдмрдирд╛рдПрдВ
- рдЪрд░рдг 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
рдирд┐рд╖реНрдкрд╛рджрди рд╕рдордп: 505 рд╕реЗрдХрдВрдбред
рдпрд╣ рдмрди рдЧрдпрд╛
рд▓рд╛рдЧрдд: 68 533.70
рдирд┐рд╖реНрдкрд╛рджрди рд╕рдордп: 69 рд╕реЗрдХрдВрдб
рдпреЛрдЧреНрдп, рдХрд╛рдлрд╝реА рдпреЛрдЧреНрдпред рдФрд░ рдпрд╣ рджреЗрдЦрддреЗ рд╣реБрдП рдХрд┐ рдЬрд┐рд╕ рддрд░рд╣ рд╕реЗ рд╣рдо PostgreSQL 10 рдореЗрдВ рд╡рд┐рднрд╛рдЬрди рддрдВрддреНрд░ рдореЗрдВ рдХрдореЛрдмреЗрд╢ рдорд╣рд╛рд░рдд рд╣рд╛рд╕рд┐рд▓ рдХрд░рдиреЗ рдореЗрдВ рдХрд╛рдордпрд╛рдм рд░рд╣реЗ - рдПрдХ рдЙрддреНрдХреГрд╖реНрдЯ рдкрд░рд┐рдгрд╛рдоред
рдЧреАрддрд╛рддреНрдордХ рд╡рд┐рд╖рдпрд╛рдВрддрд░
рдХреНрдпрд╛ рдЗрд╕рд╕реЗ рднреА рдмреЗрд╣рддрд░ рдХрд░рдирд╛ рд╕рдВрднрд╡ рд╣реИ - рд╣рд╛рдБ, рдЖрдк рдХрд░ рд╕рдХрддреЗ рд╣реИрдВ!рдРрд╕рд╛ рдХрд░рдиреЗ рдХреЗ рд▓рд┐рдП рдЖрдкрдХреЛ MATERIALIZED VIEW рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд░рдирд╛ рд╣реЛрдЧрд╛ред
рднреМрддрд┐рдХреАрдХреГрдд рджреГрд╢реНрдп 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 рдкрд░ рдХреНрд▓рд╛рдЙрдб рд╡реЙрдЪ рдХреЗ рд░реВрдк рдореЗрдВ рдЕрднреА рднреА рдХреБрдЫ рдирд┐рдЧрд░рд╛рдиреА рд╣реИред рд▓реЗрдХрд┐рди рдбреАрдмреАрдП рдХреЗ рд▓рд┐рдП рдЗрд╕ рдирд┐рдЧрд░рд╛рдиреА рдХрд╛ рдХреНрдпрд╛ рд▓рд╛рдн рд╣реИ? рд╕рд╛рдорд╛рдиреНрдп рддреМрд░ рдкрд░, рд╡реНрдпрд╛рд╡рд╣рд╛рд░рд┐рдХ рд░реВрдк рд╕реЗ рдХреЛрдИ рдирд╣реАрдВред
рдпрджрд┐ рдЖрдкрдХреЗ рдкрд╛рд╕ рдЕрдкрдиреЗ рд▓рд┐рдП рдХреБрдЫ рдЙрдкрдпреЛрдЧреА рдФрд░ рджрд┐рд▓рдЪрд╕реНрдк рдХрд░рдиреЗ рдХрд╛ рдореМрдХрд╛ рд╣реИ, рддреЛ рдЖрдк рдЗрд╕ рдЕрд╡рд╕рд░ рдХрд╛ рд▓рд╛рдн рдирд╣реАрдВ рдЙрдард╛ рд╕рдХрддреЗ...
рдХреНрдпреЛрдВрдХрд┐
рдЗрд╕ рдкреНрд░рдХрд╛рд░ рд╣рдо рд╕рдмрд╕реЗ рджрд┐рд▓рдЪрд╕реНрдк рднрд╛рдЧ рдкрд░ рдЖрддреЗ рд╣реИрдВ:
3 рджрд┐рд╕рдВрдмрд░ 2018.
PostgreSQL рдХреНрд╡реЗрд░реАрдЬрд╝ рдХреЗ рдкреНрд░рджрд░реНрд╢рди рдХреА рдирд┐рдЧрд░рд╛рдиреА рдХреЗ рд▓рд┐рдП рдЙрдкрд▓рдмреНрдз рдХреНрд╖рдорддрд╛рдУрдВ рдкрд░ рд╢реЛрдз рд╢реБрд░реВ рдХрд░рдиреЗ рдХрд╛ рдирд┐рд░реНрдгрдп рд▓реЗрдирд╛ред
рд▓реЗрдХрд┐рди рдпрд╣ рдмрд┐рд▓реНрдХреБрд▓ рдЕрд▓рдЧ рдХрд╣рд╛рдиреА рд╣реИ.
рдХрд░рдиреЗ рдХреЗ рд▓рд┐рдП рдЬрд╛рд░реАтАж
рд╕реНрд░реЛрдд: www.habr.com