Work with PostgreSQL partitioned tables

Datastream supports replicating partitioned tables for PostgreSQL source databases. You can replicate the tables as a single root table or as separate partitioned tables. If you want to replicate both the root table and the partitioned tables, you must create two different streams, with two different publications.

Replicate as a single root table

To replicate partitioned tables using the schema of the root table, do the following:

1. Use a publication with the publish_via_partition_root parameter set to true or create a new one:

   CREATE PUBLICATION PUBLICATION_NAME FOR TABLE
   SCHEMA1.TABLE1, SCHEMA2.TABLE2 WITH (publish_via_partition_root = true);
   

   Replace the following:

   • PUBLICATION_NAME: The name of the publication.
   • SCHEMA(n): The name of the schema that contains the table.
   • TABLE(n): The name of the table for which you create the publication.
   Note: Changing the value of the publish_via_partition_root parameter in a publication that's already used in a stream isn't supported and causes the stream to fail permanently.

   For more information about creating a publication, see Configure a source PostgreSQL database.

2. Complete the following in the Configure source step when you create your stream in Google Cloud:

   1. From the Objects to include list, select Specific schemas and tables.
   2. Select only the root table and make sure that the partitioned tables aren't selected.

   For information about creating a stream, see Create a stream.

Replicate as separate partitioned tables

To replicate partitioned tables as separate tables, do the following:

1. Use a publication with the publish_via_partition_root parameter set to false or create a new one. false is the default setting for the parameter.

   Note: Changing the value of the publish_via_partition_root parameter in a publication that's already used in a stream isn't supported and causes the stream to fail permanently.

   For more information about creating a publication, see Configure a source PostgreSQL database.

2. When you create your stream in Google Cloud, in the Configure source step, do the following:

   1. From the Objects to include list, select Specific schemas and tables.
   2. Select all partitioned tables and make sure that the root table isn't selected.

   For information about creating a stream, see Create a stream.

What's next

• Learn more about PostgreSQL as a source.
• Learn more about configuring a source PostgreSQL database.