PG_分区表

发表于 2025-06-16 本文字数： 22k 阅读时长 ≈ 20 分钟

从原表复制表结构，如果分区键不是原表主键需要一步一步创建

从其它表复制过来的表不能使用like origin_table including all,只能用like origin_table 不带including，除非原表的主键和分区表要使用的分区键是同一个，index索引后续手动创建。

CREATE TABLE dy_fin_pf_account_move_line_detail_pb (
    -- 复制原表的列
    LIKE dy_fin_pf_account_move_line_detail,
    primary key(id,date)

) partition by range(date);

create index dy_fin_pf_account_move_line_detail_pb_account_class_id_index on dy_fin_pf_account_move_line_detail_pb (account_class_id);
create index dy_fin_pf_account_move_line_detail_pb_account_id_index on dy_fin_pf_account_move_line_detail_pb (account_id);
create index dy_fin_pf_account_move_line_detail_pb_analytic_id_index on dy_fin_pf_account_move_line_detail_pb (analytic_id);
create index dy_fin_pf_account_move_line_detail_pb_company_id_index on dy_fin_pf_account_move_line_detail_pb (company_id);
create index dy_fin_pf_account_move_line_detail_pb_journal_id_index on dy_fin_pf_account_move_line_detail_pb (journal_id);
create index dy_fin_pf_account_move_line_detail_pb_move_id_index on dy_fin_pf_account_move_line_detail_pb (move_id);
create index dy_fin_pf_account_move_line_detail_pb_partner_id_index on dy_fin_pf_account_move_line_detail_pb (partner_id);
create index dy_fin_pf_account_move_line_detail_pb_period_id_index on dy_fin_pf_account_move_line_detail_pb (period_id);
create index dy_fin_pf_line_detail_pb_source_document_index on dy_fin_pf_account_move_line_detail_pb (document_number, source_model);
create index dy_fin_pf_line_detail_pb_source_document_state_index on dy_fin_pf_account_move_line_detail_pb (document_number, source_model, state);

ALTER TABLE dy_fin_pf_account_move_line_detail_pb DISABLE TRIGGER ALL;
--ALTER TABLE dy_fin_pf_account_move_line_detail_pb ENABLE TRIGGER ALL;

SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_account_move_line_detail_pkey'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_account_move_line_detail_account_class_id_index'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_account_move_line_detail_account_id_index'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_account_move_line_detail_analytic_id_index'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_account_move_line_detail_company_id_index'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_account_move_line_detail_journal_id_index'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_account_move_line_detail_move_id_index'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_account_move_line_detail_partner_id_index'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_account_move_line_detail_period_id_index'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_line_detail_source_document_index'));
SELECT pg_size_pretty(pg_relation_size('dy_fin_pf_line_detail_source_document_state_index'));



Indexes:
    "dy_fin_pf_account_move_line_detail_pkey" PRIMARY KEY, btree (id)
    "dy_fin_pf_account_move_line_detail_account_class_id_index" btree (account_class_id)
    "dy_fin_pf_account_move_line_detail_account_id_index" btree (account_id)
    "dy_fin_pf_account_move_line_detail_analytic_id_index" btree (analytic_id)
    "dy_fin_pf_account_move_line_detail_company_id_index" btree (company_id)
    "dy_fin_pf_account_move_line_detail_journal_id_index" btree (journal_id)
    "dy_fin_pf_account_move_line_detail_move_id_index" btree (move_id)
    "dy_fin_pf_account_move_line_detail_partner_id_index" btree (partner_id)
    "dy_fin_pf_account_move_line_detail_period_id_index" btree (period_id)
    "dy_fin_pf_line_detail_source_document_index" btree (document_number, source_model)
    "dy_fin_pf_line_detail_source_document_state_index" btree (document_number, source_model, state)
Foreign-key constraints:
    "dy_fin_pf_account_move_line_detail_account_class_id_fkey" FOREIGN KEY (account_class_id) REFERENCES dy_fin_pf_account_class_define(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_account_id_fkey" FOREIGN KEY (account_id) REFERENCES account_account(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_analytic_id_fkey" FOREIGN KEY (analytic_id) REFERENCES dy_fin_account_analytic_plan_instance(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_company_id_fkey" FOREIGN KEY (company_id) REFERENCES res_company(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_currency_id_fkey" FOREIGN KEY (currency_id) REFERENCES res_currency(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_extra_information_id_fkey" FOREIGN KEY (extra_information_id) REFERENCES dy_account_move_line_extra_info(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_journal_id_fkey" FOREIGN KEY (journal_id) REFERENCES account_journal(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_log_id_fkey" FOREIGN KEY (log_id) REFERENCES dy_base_common_log(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_move_id_fkey" FOREIGN KEY (move_id) REFERENCES account_move(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_originator_id_fkey" FOREIGN KEY (originator_id) REFERENCES hr_employee(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_partner_id_fkey" FOREIGN KEY (partner_id) REFERENCES res_partner(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_period_id_fkey" FOREIGN KEY (period_id) REFERENCES dy_fin_account_period(id) ON DELETE SET NULL
    "dy_fin_pf_account_move_line_detail_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET NULL
Access method: heap
Options: autovacuum_vacuum_scale_factor=0.03, autovacuum_analyze_scale_factor=0.03

range范围分区键左闭右开

创建的分区子表分区键的值范围是左闭右开的状态 for values from (‘2020-01-01’) to (‘2021-01-01’);

相当于2020-01-01<=partition_values < 2021-01-01

create table dfpamld_2019 partition of dy_fin_pf_account_move_line_detail_pb for values from ('2000-01-01 00:00:00') to ('2020-01-01 00:00:00');
create table dfpamld_2020 partition of dy_fin_pf_account_move_line_detail_pb for values from ('2020-01-01 00:00:00') to ('2021-01-01 00:00:00');
create table dfpamld_2021 partition of dy_fin_pf_account_move_line_detail_pb for values from ('2021-01-01 00:00:00') to ('2022-01-01 00:00:00');
create table dfpamld_2022 partition of dy_fin_pf_account_move_line_detail_pb for values from ('2022-01-01 00:00:00') to ('2023-01-01 00:00:00');
create table dfpamld_2023 partition of dy_fin_pf_account_move_line_detail_pb for values from ('2023-01-01 00:00:00') to ('2024-01-01 00:00:00');

复制表

\copy dy_fin_pf_account_move_line_detail to '/usr/local/pg137/dy_fin_pf_account_move_line_detail' DELIMITER ',' CSV;

\copy下载相对于 insert into table select 要快。

恢复可以考虑使用copy，因为它可以分批提交 提高执行效率

\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 0 limit 30000000) to '/usr/local/pg137/01.csv' DELIMITER ',' CSV;

\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 30000000 limit 30000000) to '/usr/local/pg137/02.csv' DELIMITER ',' CSV;

\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 60000000 limit 30000000) to '/usr/local/pg137/03.csv' DELIMITER ',' CSV;

\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 90000000 limit 30000000) to '/usr/local/pg137/04.csv' DELIMITER ',' CSV;

\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 120000000 limit 30000000) to '/usr/local/pg137/05.csv' DELIMITER ',' CSV;


\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 150000000 limit 30000000) to '/usr/local/pg137/06.csv' DELIMITER ',' CSV;

\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 180000000 limit 30000000) to '/usr/local/pg137/07.csv' DELIMITER ',' CSV;


\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 210000000 limit 30000000) to '/usr/local/pg137/08.csv' DELIMITER ',' CSV;


\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 240000000 limit 30000000) to '/usr/local/pg137/09.csv' DELIMITER ',' CSV;


\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 270000000 limit 30000000) to '/usr/local/pg137/10.csv' DELIMITER ',' CSV;


\copy (select * from dy_fin_pf_account_move_line_detail order by id offset 300000000 limit 30000000) to '/usr/local/pg137/11.csv' DELIMITER ',' CSV;



-------------------------------------------------------------
psql -U odoo -p 11791 -d flih_1107 -c "
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/01.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/02.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/03.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/04.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/05.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/06.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/07.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/08.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/09.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/10.csv' DELIMITER ',' CSV;
copy dy_fin_pf_account_move_line_detail_pb from '/usr/local/pg137/11.csv' DELIMITER ',' CSV;
"

-------------------------------------------------------------
insert into dy_fin_pf_account_move_line_detail_pb (select * from dy_fin_pf_account_move_line_detail order by id offset 180000000 limit 30000000);
insert into dy_fin_pf_account_move_line_detail_pb (select * from dy_fin_pf_account_move_line_detail order by id offset 210000000 limit 30000000);
insert into dy_fin_pf_account_move_line_detail_pb (select * from dy_fin_pf_account_move_line_detail order by id offset 240000000 limit 30000000);
insert into dy_fin_pf_account_move_line_detail_pb (select * from dy_fin_pf_account_move_line_detail order by id offset 270000000 limit 30000000);
insert into dy_fin_pf_account_move_line_detail_pb (select * from dy_fin_pf_account_move_line_detail order by id offset 300000000 limit 30000000);

-- 创建主分区表
CREATE TABLE dy_fin_pf_account_move_line_detail_main (
    -- 复制原表的列
    LIKE dy_fin_pf_account_move_line_detail,
    primary key(id,date)

) partition by range(date);
create table dfpamld_202301 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-01-01 00:00:00') to ('2023-02-01 00:00:00');
create table dfpamld_202302 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-02-01 00:00:00') to ('2023-03-01 00:00:00');
create table dfpamld_202303 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-03-01 00:00:00') to ('2023-04-01 00:00:00');
create table dfpamld_202304 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-04-01 00:00:00') to ('2023-05-01 00:00:00');
create table dfpamld_202305 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-05-01 00:00:00') to ('2023-06-01 00:00:00');
create table dfpamld_202306 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-06-01 00:00:00') to ('2023-07-01 00:00:00');
create table dfpamld_202307 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-07-01 00:00:00') to ('2023-08-01 00:00:00');
create table dfpamld_202308 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-08-01 00:00:00') to ('2023-09-01 00:00:00');
create table dfpamld_202309 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-09-01 00:00:00') to ('2023-10-01 00:00:00');
create table dfpamld_202310 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-10-01 00:00:00') to ('2023-11-01 00:00:00');
create table dfpamld_202311 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-11-01 00:00:00') to ('2023-12-01 00:00:00');
create table dfpamld_202312 partition of dy_fin_pf_account_move_line_detail_main for values from ('2023-12-01 00:00:00') to ('2024-01-01 00:00:00');

-- 创建归档分区表
CREATE TABLE dy_fin_pf_account_move_line_detail_back (
    -- 复制原表的列
    LIKE dy_fin_pf_account_move_line_detail,
    primary key(id,date)

) partition by range(date);

-- 数据复制或同步
insert into dy_fin_pf_account_move_line_detail_main (select * from dy_fin_pf_account_move_line_detail where date between '2023-01-01' and '2024-01-01' order by id offset 0 limit 30000000);
insert into dy_fin_pf_account_move_line_detail_main (select * from dy_fin_pf_account_move_line_detail where date between '2023-01-01' and '2024-01-01' order by id offset 30000000 limit 30000000);
insert into dy_fin_pf_account_move_line_detail_main (select * from dy_fin_pf_account_move_line_detail where date between '2023-01-01' and '2024-01-01' order by id offset 60000000 limit 30000000);
insert into dy_fin_pf_account_move_line_detail_main (select * from dy_fin_pf_account_move_line_detail where date between '2023-01-01' and '2024-01-01' order by id offset 90000000 limit 30000000);
insert into dy_fin_pf_account_move_line_detail_main (select * from dy_fin_pf_account_move_line_detail where date between '2023-01-01' and '2024-01-01' order by id offset 120000000 limit 30000000);

-- 恢复索引
create index dy_fin_pf_account_move_line_detail_main_account_class_id_index on dy_fin_pf_account_move_line_detail_main (account_class_id);
create index dy_fin_pf_account_move_line_detail_main_account_id_index on dy_fin_pf_account_move_line_detail_main (account_id);
create index dy_fin_pf_account_move_line_detail_main_analytic_id_index on dy_fin_pf_account_move_line_detail_main (analytic_id);
create index dy_fin_pf_account_move_line_detail_main_company_id_index on dy_fin_pf_account_move_line_detail_main (company_id);
create index dy_fin_pf_account_move_line_detail_main_journal_id_index on dy_fin_pf_account_move_line_detail_main (journal_id);
create index dy_fin_pf_account_move_line_detail_main_move_id_index on dy_fin_pf_account_move_line_detail_main (move_id);
create index dy_fin_pf_account_move_line_detail_main_partner_id_index on dy_fin_pf_account_move_line_detail_main (partner_id);
create index dy_fin_pf_account_move_line_detail_main_period_id_index on dy_fin_pf_account_move_line_detail_main (period_id);
create index dy_fin_pf_line_detail_pb_source_document_index on dy_fin_pf_account_move_line_detail_main (document_number, source_model);
create index dy_fin_pf_line_detail_pb_source_document_state_index on dy_fin_pf_account_move_line_detail_main (document_number, source_model, state);

-- 外键恢复   
alter table dy_fin_pf_account_move_line_detail_main 
add constraint "dy_fin_pf_account_move_line_detail_main_account_class_id_fkey" FOREIGN KEY (account_class_id) REFERENCES dy_fin_pf_account_class_define(id) ON DELETE SET NULL,
add constraint "dy_fin_pf_account_move_line_detail_main_account_id_fkey" FOREIGN KEY (account_id) REFERENCES account_account(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_analytic_id_fkey" FOREIGN KEY (analytic_id) REFERENCES dy_fin_account_analytic_plan_instance(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_company_id_fkey" FOREIGN KEY (company_id) REFERENCES res_company(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_currency_id_fkey" FOREIGN KEY (currency_id) REFERENCES res_currency(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_extra_information_id_fkey" FOREIGN KEY (extra_information_id) REFERENCES dy_account_move_line_extra_info(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_journal_id_fkey" FOREIGN KEY (journal_id) REFERENCES account_journal(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_log_id_fkey" FOREIGN KEY (log_id) REFERENCES dy_base_common_log(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_move_id_fkey" FOREIGN KEY (move_id) REFERENCES account_move(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_originator_id_fkey" FOREIGN KEY (originator_id) REFERENCES hr_employee(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_partner_id_fkey" FOREIGN KEY (partner_id) REFERENCES res_partner(id) ON DELETE SET null,
add constraint "dy_fin_pf_account_move_line_detail_main_period_id_fkey" FOREIGN KEY (period_id) REFERENCES dy_fin_account_period(id) ON DELETE SET null,
add  constraint "dy_fin_pf_account_move_line_detail_main_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET null

-- 修改原表名
alter table dy_fin_pf_account_move_line_detail rename to dy_fin_pf_account_move_line_detail_old;
-- 修改主表分区名
alter table dy_fin_pf_account_move_line_detail_main rename to dy_fin_pf_account_move_line_detail;


-- 卸载分区
alter table dy_fin_pf_account_move_line_detail detach partition dfpamld_202310;

-- 挂载分区
alter table dy_fin_pf_account_move_line_detail_back attach partition dfpamld_202310 for values from ('2023-10-01') to ('2023-11-01');







select sm.id
from stock_move as sm
where sm.create_entries = 'sum' 
  and sm.move_id is null;
  
  
select sm.id,dfpamld.move_id
from stock_move as sm,
  dy_fin_pf_account_move_line_detail dfpamld
where sm.create_entries = 'sum' 
  and sm.move_id is null
  and dfpamld.source_model = 'stock.move'
  and dfpamld.document_number = sm.id::varchar;
  
create or replace view check_stock_move_move_id_view as
select distinct sm.id, dfpamld.move_id
from stock_move as sm left join dy_fin_pf_account_move_line_detail dfpamld on (dfpamld.source_model = 'stock.move' and dfpamld.document_number = sm.id::varchar and dfpamld.date between '2023-01-01' and '2023-01-31')
where sm.create_entries = 'sum' 
  and sm.move_id is null;
  
explain
update stock_move set move_id = t.move_id
from (
select dfpamld.document_number::int stock_move_id,dfpamld.move_id
from dy_fin_pf_account_move_line_detail dfpamld
where dfpamld.source_model = 'stock.move' 
  and dfpamld.move_id is not null
  and dfpamld.date between '2023-01-01' and '2023-01-31'
  and exists (select 1 from stock_move as sm where sm.id = dfpamld.document_number::int and sm.move_id is null and sm.date between '2023-01-01' and '2023-01-31')
) t
where t.stock_move_id = stock_move.id;



update stock_move set move_id = t.move_id
from (
select dfpamld.document_number::int stock_move_id,dfpamld.move_id,dfpamld.date
from dy_fin_pf_account_move_line_detail dfpamld
where dfpamld.source_model = 'stock.move' 
  and dfpamld.move_id is not null
  and dfpamld.date between '2021-09-01' and '2021-09-30'
  and exists (select 1 from stock_move as sm where sm.id = dfpamld.document_number::int and sm.move_id is null and sm.date between '2021-09-01' and '2021-09-30')
) t
where t.stock_move_id = stock_move.id;
  
SELECT
  move_id,
  document_number 
FROM
  dy_fin_pf_account_move_line_detail 
WHERE
  belong_module_type = 'scm' 
  AND STATE = 'gl_account_move' 
  AND source_model = 'stock.move'
  AND date::DATE BETWEEN '2023-10-16' AND '2023-10-16'
GROUP BY
  move_id,
  document_number;

create table t1 partition by list (id);
create table t1_1 partition of t1 for values in (1);
create table t1_1 partition of t1 for values in (2);
create table t1_1 partition of t1 for values in (3);