Sync Schema changes automatically with Logical Replication Postgresql

Question

I know there is limitation for logical replication to publish schema change to the subscriber. And for example if you add a column in publisher you have to manually add the same column to the subscriber to sync again.

Is there a workaround to do it automatically, tool or workflow that you use?

Answer 1

As you saw, logical replication does not replicate DDL. That's the first point of the documentation (see https://www.postgresql.org/docs/15/logical-replication-restrictions.html)

What I would do is:

• store my DDL in a .SQL file
• create a small program (pick your favorite language) that will connect to all your databases (You can describe that in a simple text file) and play the same SQL file on all of them using psql

To simplify the connection, I strongly advise using a service file and a password file (for security reason if you use a password-based connection).