рдкреНрд░рд╕реНрддрд╛рд╡рдирд╛ рдХрд┐рдВрд╡рд╛ рд╡рд┐рднрд╛рдЧ рдХрд░рдгреНрдпрд╛рдЪреА рдХрд▓реНрдкрдирд╛ рдХрд╢реА рдЖрд▓реА
рдХрдерд╛ рдпреЗрдереЗ рд╕реБрд░реВ рд╣реЛрддреЗ:
рдЧреАрддрд╛рддреНрдордХ рд╡рд┐рд╖рдпрд╛рдВрддрд░:
рддрдВрддреЛрддрдВрдд 'рддреНрдпрд╛ рдХреНрд╖рдгреА', рдХрд╛рд░рдг
рддрд░, рддреБрдореНрд╣реА рдЧреНрд░рд╛рд╣рдХрд╛рд▓рд╛ рдЖрдирдВрджреА рдХрд╕реЗ рдмрдирд╡реВ рд╢рдХрддрд╛ рдЖрдгрд┐ рддреНрдпрд╛рдЪ рд╡реЗрд│реА рддреБрдордЪреА рд╕реНрд╡рддрдГрдЪреА рдХреМрд╢рд▓реНрдпреЗ рд╕реБрдзрд╛рд░реВ рд╢рдХрддрд╛?
рд╕рд░реНрд╡рдХрд╛рд╣реА рд╢рдХреНрдп рддрд┐рддрдХреЗ рд╕реЛрдкреЗ рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА, рдирдВрддрд░ рдбреЗрдЯрд╛рдмреЗрд╕рдЪреНрдпрд╛ рдХрд╛рд░реНрдпрдкреНрд░рджрд░реНрд╢рдирд╛рдд рдХрд╛рд╣реАрддрд░реА рдореВрд▓рднреВрддрдкрдгреЗ рд╕реБрдзрд╛рд░рдгреНрдпрд╛рдЪреЗ рдлрдХреНрдд рджреЛрди рдорд╛рд░реНрдЧ рдЖрд╣реЗрдд:
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
рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреАрдЪреА рд╡реЗрд│: 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 рдордзреАрд▓ рд╡рд┐рднрд╛рдЬрди рдпрдВрддреНрд░рдгрд╛ рдХрдореА-рдЕрдзрд┐рдХ рдкреНрд░рдорд╛рдгрд╛рдд рдкрд╛рд░ рдкрд╛рдбреВ рд╢рдХрд▓реЛ - рдПрдХ рдЙрддреНрдХреГрд╖реНрдЯ рдкрд░рд┐рдгрд╛рдо.
рдЧреАрддрд╛рддреНрдордХ рд╡рд┐рдЪрд▓рди
рдЖрдгрдЦреА рдЪрд╛рдВрдЧрд▓реЗ рдХрд░рдгреЗ рд╢рдХреНрдп рдЖрд╣реЗ рдХрд╛ - рд╣реЛрдп, рддреБрдореНрд╣реА рдХрд░реВ рд╢рдХрддрд╛!рд╣реЗ рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА рддреБрдореНрд╣рд╛рд▓рд╛ рдордЯреЗрд░рд┐рдЕрд▓рд╛рдЗрдЬреНрдб рд╡реНрд╣реНрдпреВ рд╡рд╛рдкрд░рдгреЗ рдЖрд╡рд╢реНрдпрдХ рдЖрд╣реЗ.
рдордЯреЗрд░рд┐рдпрд▓рд╛рдЗрдЬреНрдб рд╡реНрд╣реНрдпреВ 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