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.

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

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

   1. From the Objects to includelist, 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.

   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 sourcestep, do the following:

   1. From the Objects to includelist, 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 .