Table of Contents Previous Next


7 Common Operations : 7.10 Replicating Postgres Partitioned Tables

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 Chapter 10 “Table Partitioning” in the EDB Postgres Advanced Server 10.0 Database Compatibility for Oracle Developers Guide available from the EnterpriseDB website located at:
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.
If you are using native PostgreSQL version 9.6 or earlier, you must use a technique called table inheritance where you first create a parent table from which you then create one or more child tables that inherit the columns of the parent. Each child is an independent table in its own right except that it includes the column definitions of its parent. You then define a trigger on the parent table to direct which child table an inserted row is to be stored. Table inheritance can be used on Advanced Server as well.
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:
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.
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.
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.
Section 7.10.2 shows creation of the publication when using partitioning compatible with Oracle databases or declarative partitioning on a Postgres 10 or later database server.
Follow the directions in Section 6.2 to create a master 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 5.2.)
Create additional master nodes as described in Section 6.3. (For a single-master replication system, create the subscription database and subscription according to the directions in Section 5.3.)
Note: If you are using table inheritance, you must still use the process described in Section 7.10.1 even when creating the publication on a Postgres 10 or later database server.
Follow the directions in Section 6.2 to create a master 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 5.2.)
Create additional master nodes as described in Section 6.3. (For a single-master replication system, create the subscription database and subscription according to the directions in Section 5.3.)

7 Common Operations : 7.10 Replicating Postgres Partitioned Tables

Table of Contents Previous Next