Replicating Postgres Partitioned Tables v6.2

Suggest edits

Both PostgreSQL and Advanced Server support partitioned tables, which can be replicated with xDB Replication Server in either a single-master or multi-master replication system.

The following are the various partitioning techniques:

Advanced Server partitioning compatible with Oracle databases
Postgres declarative partitioning (applies to both PostgreSQL and Advanced Server version 10 and later)
Postgres table inheritance (applies to both PostgreSQL and Advanced Server)

If you are using Advanced Server, partitioned tables can be created using the CREATE TABLE statement with partitioning syntax compatible with Oracle databases. For information on partitioning compatible with Oracle databases, see Database Compatibility: Table Partitioning in the EDB Postgres Advanced Server documentation.

If you are using version 10 or later of PostgreSQL or Advanced Server, declarative partitioning can be used to create partitioned tables. The CREATE TABLE syntax for creating a declarative partitioned table is similar to the partitioning compatible with Oracle databases, but the individual partitions of the declarative partitioned table must be separately created with their own CREATE TABLE statements.

For information on declarative partitioning and table inheritance, see the PostgreSQL core documentation available at:

https://www.postgresql.org/docs/current/static/ddl-partitioning.html

Regardless of the partitioning method, the resulting partitioned table is comprised of a parent table with a set of child tables.

Replication of these Postgres partitioned tables in a single-master or multi-master replication system is accomplished in the same manner.

Note the following general restrictions when the publication contains a partitioned table:

SQL Server cannot be used as a subscription database.
When using table inheritance, the subscription databases must be Postgres – they cannot be Oracle or SQL Server.

All three partitioning techniques are illustrated on the emp table used as an example throughout this document. 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 Section Creating a Postgres Version 10 or Later Partitioned Table Publication.

The following creates the partitioned table in 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 Advanced Server 10 or later using declarative partitioning:

Note

When creating a declarative partitioned table that is to be replicated using xDB Replication Server, the PRIMARY KEY constraint must be included in the CREATE TABLE statements of the individual partitions, not in the CREATE TABLE statement of the parent table to be 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 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 illustrates the types of SQL queries that can be made on the parent and child tables to show which tables actually 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.

```text
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 amongst 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)

Section Creating a Postgres Version 10 or Later Partitioned Table Publication shows creation of 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

The following describes creating the publication using either partitioning compatible with Oracle databases or Postgres declarative partitioning on a Postgres 10 or later database server.

Note

If you are using table inheritance, you must still use the process described in Section 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:

The log-based method of synchronization replication must be selected for the publication database. The trigger-based method cannot be used.
In a single-master replication system, the subscription databases must be Postgres version 10 or later. Oracle and SQL Server cannot be used 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 directions in Section 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 according to the directions in Section Creating a Publication.)

When creating the publication, only the parent table appears and is selected.

Creating a publication for a Postgres 10 or later partitioned table

Figure 7-109: Creating a publication for a Postgres 10 or later partitioned table

The following shows the resulting replication tree for the partitioned table in the primary definition node:

Publication containing a Postgres 10 or later partitioned table

Figure 7-110: Publication containing a Postgres 10 or later partitioned table

Create additional primary nodes as described in Section Creating Additional Primary nodes. (For a single-master replication system, create the subscription database and subscription according to the directions in Section Creating a Subscription.)

The following shows the resulting multi-master replication system after you have added an additional primary node.

MMR system with a Postgres 10 or later partitioned table

Figure 7-111: MMR system with a Postgres 10 or later partitioned table

The partitioned table can now be kept synchronized on the primary nodes of the multi-master replication system.

On this page
Creating a Postgres Version 10 or Later Partitioned Table Publication

← Prev

Loading Tables From an External Data Source (Offline Snapshot)

Using Secure Sockets Layer (SSL) Connections

Replicating Postgres Partitioned Tables v6.2

Note

Creating a Postgres Version 10 or Later Partitioned Table Publication

Note

← Prev

Next →