Replicating Postgres partitioned tables v7
Both PostgreSQL and EDB Postgres Advanced Server support partitioned tables, which you can replicate with Replication Server in either a single-master or multi-master replication system.
The following are the partitioning techniques:
- EDB Postgres Advanced Server partitioning compatible with Oracle databases
- Postgres declarative partitioning (applies to both PostgreSQL and EDB Postgres Advanced Server version 10 and later)
- Postgres table inheritance (applies to both PostgreSQL and EDB Postgres Advanced Server)
If you're using EDB Postgres Advanced Server, you can create partitioned tables using the CREATE TABLE statement with partitioning syntax compatible with Oracle databases. For information on partitioning compatible with Oracle databases, see Table partitioning.
If you're using version 10 or later of PostgreSQL or EDB Postgres Advanced Server, you can use declarative partitioning to create partitioned tables. The CREATE TABLE syntax for creating a declarative partitioned table is similar to the partitioning compatible with Oracle databases. However, you must create the individual partitions of the declarative partitioned table separately with their own CREATE TABLE statements.
For information on declarative partitioning and table inheritance, see the PostgreSQL core documentation.
Regardless of the partitioning method, the resulting partitioned table is made up of a parent table with a set of child tables.
You can accomplish replication of these Postgres partitioned tables in a single-master or multi-master replication system in the same manner.
Note the following general restrictions when the publication contains a partitioned table:
- You can't use SQL Server as a subscription database.
- When using table inheritance, the subscription databases must be Postgres. They can't be Oracle or SQL Server.
All three partitioning techniques are shown on the emp table. The partitioned table is then used in a publication of a multi-master replication system in the following sections:
- For creating a publication in Postgres 9.x, see Creating a Postgres 9.x partitioned table publication.
- For creating a publication in Postgres 10 or later, see Creating a Postgres version 10 or later partitioned table publication.
The following creates the partitioned table in EDB Postgres Advanced Server using partitioning compatible with Oracle databases:
CREATE TABLE emp ( empno NUMERIC(4) PRIMARY KEY, ename VARCHAR(10), job VARCHAR(9), mgr NUMERIC(4), hiredate DATE, sal NUMERIC(7,2), comm NUMERIC(7,2), deptno NUMERIC(2) ) PARTITION BY LIST(deptno) ( PARTITION dept_10 VALUES (10), PARTITION dept_20 VALUES (20), PARTITION dept_30 VALUES (30) ); -- Load the 'emp' table -- INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
The following creates the partitioned table in PostgreSQL or EDB Postgres Advanced Server 10 or later using declarative partitioning:
Note
When creating a declarative partitioned table to replicate using Replication Server, you must include the PRIMARY KEY in the CREATE TABLE statements of the individual partitions, not in the CREATE TABLE statement of the parent table being partitioned.
CREATE TABLE emp ( empno NUMERIC(4), ename VARCHAR(10), job VARCHAR(9), mgr NUMERIC(4), hiredate DATE, sal NUMERIC(7,2), comm NUMERIC(7,2), deptno NUMERIC(2) ) PARTITION BY LIST(deptno); -- -- Create the partitions -- -- The partitions must contain the PRIMARY KEY constraint -- CREATE TABLE emp_dept_10 PARTITION OF emp (empno PRIMARY KEY) FOR VALUES IN (10); CREATE TABLE emp_dept_20 PARTITION OF emp (empno PRIMARY KEY) FOR VALUES IN (20); CREATE TABLE emp_dept_30 PARTITION OF emp (empno PRIMARY KEY) FOR VALUES IN (30); -- -- Load the 'emp' table -- INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
The following creates the partitioned table in PostgreSQL or EDB Postgres Advanced Server using table inheritance:
-- -- Create the parent table -- CREATE TABLE emp ( empno NUMERIC(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY, ename VARCHAR(10), job VARCHAR(9), mgr NUMERIC(4), hiredate DATE, sal NUMERIC(7,2), comm NUMERIC(7,2), deptno NUMERIC(2) ); -- -- Create the child tables -- CREATE TABLE emp_dept_10 ( CHECK (deptno = 10) ) INHERITS (emp); CREATE TABLE emp_dept_20 ( CHECK (deptno = 20) ) INHERITS (emp); CREATE TABLE emp_dept_30 ( CHECK (deptno = 30) ) INHERITS (emp); ALTER TABLE emp_dept_10 ADD CONSTRAINT emp_dept_10_pk PRIMARY KEY (empno); ALTER TABLE emp_dept_20 ADD CONSTRAINT emp_dept_20_pk PRIMARY KEY (empno); ALTER TABLE emp_dept_30 ADD CONSTRAINT emp_dept_30_pk PRIMARY KEY (empno); -- -- Create the trigger function to insert into the proper child by deptno -- CREATE OR REPLACE FUNCTION emp_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF NEW.deptno = 10 THEN INSERT INTO emp_dept_10 VALUES (NEW.*); ELSIF NEW.deptno = 20 THEN INSERT INTO emp_dept_20 VALUES (NEW.*); ELSIF NEW.deptno = 30 THEN INSERT INTO emp_dept_30 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Department # out of range.'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; -- -- Create the trigger -- CREATE TRIGGER insert_emp_trigger BEFORE INSERT ON emp FOR EACH ROW EXECUTE PROCEDURE emp_insert_trigger(); -- -- Load the 'emp' table -- INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
The following shows the types of SQL queries that you can make on the parent and child tables to show which tables contain the rows.
Querying the parent table, emp, with the asterisk appended to the table name in the SELECT statement, shows the rows in the parent and child tables. This is the default behavior if the asterisk is omitted.
edb=# SELECT * FROM emp*;
empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+--------------------+---------+---------+-------- 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 (14 rows)
The following queries show how the rows are physically divided among the child tables. The use of the ONLY keyword results in rows only in the specified table of the SELECT statement and not from any of its children.
edb=# SELECT * FROM ONLY emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----+-----+----------+-----+------+-------- (0 rows)
edb=# SELECT * FROM ONLY emp_dept_10;
empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+--------------------+---------+------+-------- 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 (3 rows)
edb=# SELECT * FROM ONLY emp_dept_20;
empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+---------+------+--------------------+---------+------+-------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 (5 rows)
edb=# SELECT * FROM ONLY emp_dept_30;
empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+----------+------+--------------------+---------+---------+-------- 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 (6 rows)
Creating a Postgres version 10 or later partitioned table publication shows creating the publication when using partitioning compatible with Oracle databases or declarative partitioning on a Postgres 10 or later database server.
Creating a Postgres version 10 or later partitioned table publication
Create the publication using either partitioning compatible with Oracle databases or Postgres declarative partitioning on a Postgres 10 or later database server.
Note
If you're using table inheritance, you must use the process described in Creating a Postgres 9.x partitioned table publication even when creating the publication on a Postgres 10 or later database server.
The following restrictions apply when the publication contains a table with partitioning compatible with Oracle databases or declarative partitioning:
- You must select the log-based method of synchronization replication for the publication database. You can't use the trigger-based method.
- In a single-master replication system, the subscription databases must be Postgres version 10 or later. You can't use Oracle and SQL Server as a subscription database.
- In a multi-master replication system, all primary nodes must be Postgres version 10 or later with the same compatibility mode as the primary definition node (that is, either compatible with native PostgreSQL or compatible with Oracle databases). For more information on the multi-master replication system compatibility modes, see Permitted MMR database server configurations.
Follow the steps in Creating a publication to create a primary definition node along with a publication containing the partitioned table. (For a single-master replication system, create the publication database along with the publication following the steps in Creating a publication.)
When creating the publication, only the parent table appears and is selected.
The following shows the resulting replication tree for the partitioned table in the primary definition node:
Create more primary nodes as described in Creating more primary nodes. (For a single-master replication system, create the subscription database and subscription following the steps in Creating a subscription.)
The following shows the resulting multi-master replication system after you add another primary node.
You can now keep the partitioned table synchronized on the primary nodes of the multi-master replication system.