is it possible to change postgresql schema on receiver using logical replication?

All we need is an easy explanation of the problem, so here it is.

I have a PostgreSQL 12 database set up to follow a remote database (version 12) using logical replication.

I do not control the sender database.

I need to perform queries on the DB filtering records based on values contained in a column which is not indexed, queries takes a long time to execute and so I would like to add an index on that column.

According to this documentation excerpt

(Note, however, that there is no need for the schemas to be absolutely
the same on both sides.)

as the schema is not replicated it’s possible to have different schemas on the receiver.

My questions are:

  1. can I add the index without breaking logical replication?
  2. what will happen if the sender will eventually add the index in the future?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The answers are, per a_horse_with_no_name

  1. yes
  2. nothing

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply