In this post I would like to discuss the new feature added to the upcoming PostgreSQL 15 by the Fujitsu OSS team in collaboration with the PostgreSQL open source community, to enable logical replication of tables in a schema.
Logical replication of specific tables or all tables in the database from publisher to subscriber is supported since PostgreSQL 10. If users want to publish tables present in one schema or multiple schemas, they have to prepare the table list manually by querying the database, and then create a publication by using the manually prepared list.
When there are only a few tables in the schema, then preparing the list is quick and easy, but if there are hundreds or thousands of tables, then this will be a tedious task.
To overcome this problem, the upcoming PostgreSQL 15 will add the optionTABLES IN SCHEMA, which will allow one or more schemas to be specified, whose tables are selected by the publisher for sending data to the subscriber.
Note: In this post, I will refer to this new feature that enables logical replication of tables in schema asschema publication.
The figure below illustrates the working of logical replication of schema publication:
Let's go step by step through the stages in the diagram above:
1 User performs various DML operations on tables and executes prepare/commit.
2 The backend process will generate data/WAL for the operations performed by the user.
3 The backend sends the SIGUSR1 signal to the WAL sender process to notify that WAL records are available for processing.
4 The walsender process starts logical decoding of the WAL records - the pgoutput plugin transforms the changes read from the WAL.
5 The pgoutput plugin checks if the data is part of the schema publication or not. If it is, then data is continuously transferred to the Apply Worker using the streaming replication protocol.
6 The Apply Worker maps the data to local tables and applies the individual changes as they are received, in correct transactional order.
You can view further details of the implementation in thePostgreSQL git repository.
The new syntax allows including the tables in the specified schemas when creating or altering the publication.
or
In addition to specifying schemas, the new syntax also allows specifying individual tables when creating or altering publication.
or
Note that adding schemas to a publication that is already subscribed to will require anALTER SUBSCRIPTION … REFRESH PUBLICATION action on the subscribing side in order to become effective.
A new system table pg_publication_namespace will be added, to maintain the schemas that the user has specified for publication.
Users can use pg_publication_namespace and pg_publication to get the schema mapping with the publication, as in the example below.
The pgoutput plugin was modified to check if the relation is part of schema publication and publish the changes to the subscriber.
The\d family of commands was updated to display schema publications, and the\dRp+ variant will
now display the schemas associated with the publication.
The pg_dump client tool has been updated to identify if the publication was created to publish tables in schema and dumps the ddl for the publication including theTABLES IN SCHEMA option.
Sample of the DDL generated by pg_dump:
The psql client tool has been updated to support tab completion of theTABLES IN SCHEMA option.
Here are the steps to use the newTABLES IN SCHEMA option.
1Create a few schemas and tables in the publisher and the subscriber:
2Create the publication in the publisher:
3Create the subscription in the subscriber by specifying the publisher host and the publisher port 6666:
4Insert some data into the publisher:
5Check that the data published by the publisher is logically replicated to the subscriber:
With the changes that went into this feature in PostgreSQL 15, we now have the infrastructure that allows decoding of tables present in a schema.
The next step is to implement the support for skipping few tables present in the schema, in the later versions of PostgreSQL.
If you would like to learn more about the pg_stat_replication_slots view, my colleague Takamichi Osumi wrote a blog post on it -How to gain insight into the pg_stat_replication_slots view by examining logical replication.
Based on community feedback we have changed "ALL TABLES IN SCHEMA" syntax to "TABLES IN SCHEMA".
Updated November 9,2022
Read our most recent articles regarding all aspects of PostgreSQL and Fujitsu Enterprise Postgres.
Fill the form to receive notifications of future posts