Como particionar a tabela existente no postgres?

Como o nº 1 requer a cópia de dados do mestre para o filho enquanto ele está em um ambiente de produção ativo, eu pessoalmente fui com o nº 2 (criar um novo mestre). Isso evita interrupções na tabela original enquanto ela estiver em uso ativo e, se houver algum problema, posso excluir facilmente o novo mestre sem problemas e continuar usando a tabela original. Aqui estão os passos para fazê-lo:

1. Crie uma nova tabela mestra.

   CREATE TABLE new_master (
       id          serial,
       counter     integer,
       dt_created  DATE DEFAULT CURRENT_DATE NOT NULL
   );
   

2. Crie filhos que herdam do mestre.

   CREATE TABLE child_2014 (
       CONSTRAINT pk_2014 PRIMARY KEY (id),
       CONSTRAINT ck_2014 CHECK ( dt_created < DATE '2015-01-01' )
   ) INHERITS (new_master);
   CREATE INDEX idx_2014 ON child_2014 (dt_created);
   
   CREATE TABLE child_2015 (
       CONSTRAINT pk_2015 PRIMARY KEY (id),
       CONSTRAINT ck_2015 CHECK ( dt_created >= DATE '2015-01-01' AND dt_created < DATE '2016-01-01' )
   ) INHERITS (new_master);
   CREATE INDEX idx_2015 ON child_2015 (dt_created);
   
   ...
   

3. Copie todos os dados históricos para a nova tabela mestra

   INSERT INTO child_2014 (id,counter,dt_created)
   SELECT id,counter,dt_created
   from old_master
   where dt_created < '01/01/2015'::date;
   

4. Pausar temporariamente novas inserções/atualizações no banco de dados de produção

5. Copie os dados mais recentes para a nova tabela mestra

   INSERT INTO child_2015 (id,counter,dt_created)
   SELECT id,counter,dt_created
   from old_master
   where dt_created >= '01/01/2015'::date AND dt_created < '01/01/2016'::date;
   

6. Renomeie as tabelas para que new_master se torne o banco de dados de produção.

   ALTER TABLE old_master RENAME TO old_master_backup;
   ALTER TABLE new_master RENAME TO old_master;
   

7. Adicione função para instruções INSERT em old_master para que os dados sejam passados ​​para a partição correta.

   CREATE OR REPLACE FUNCTION fn_insert() RETURNS TRIGGER AS $$
   BEGIN
       IF ( NEW.dt_created >= DATE '2015-01-01' AND
            NEW.dt_created < DATE '2016-01-01' ) THEN
           INSERT INTO child_2015 VALUES (NEW.*);
       ELSIF ( NEW.dt_created < DATE '2015-01-01' ) THEN
           INSERT INTO child_2014 VALUES (NEW.*);
       ELSE
           RAISE EXCEPTION 'Date out of range';
       END IF;
       RETURN NULL;
   END;
   $$
   LANGUAGE plpgsql;
   

8. Adicionar gatilho para que a função seja chamada em INSERTS

   CREATE TRIGGER tr_insert BEFORE INSERT ON old_master
   FOR EACH ROW EXECUTE PROCEDURE fn_insert();
   

9. Definir exclusão de restrição como ATIVADO

   SET constraint_exclusion = on;
   

10. Reative UPDATES e INSERTS no banco de dados de produção

11. Configure o gatilho ou cron para que novas partições sejam criadas e a função seja atualizada para atribuir novos dados à partição correta. Consulte este artigo para exemplos de código

12. Excluir old_master_backup

Existe uma nova ferramenta chamada pg_pathman ( https://github.com/postgrespro/pg_pathman ) que faria isso para você automaticamente.

Então, algo como o seguinte faria isso.

SELECT create_range_partitions('master', 'dt_created', 
   '2015-01-01'::date, '1 day'::interval);