CODEWITHSUNDEEP
postgresqlreplicationamazon-rds
Stan Bridge
Stan Bridge

Reputation: 79

How does AWS postgres RDS read replication handle schema switching?

I am wanting to know how an AWS postgres RDS does replication where I rename schemas to "swap" them within the read/write instance of the database.

Does it replicate this action to the read-replicas by sending on the "alter schema" rename commands I gave to my read/write instance? Or after my renames, does it see wholly different sets of data in the schemas and do a whole new copy of each out to the read-replicas?

For example...

In my RDS instance I have a read/write instance of "my_mega_database" which I want to create read-replicas of for my applications to connect to.

Typically, in "my_mega_database" there are two schemas "my_data" and "my_data_old", whereby "my_data" contains data that was delivered last night, and "my_data_old" contains data from the previous night. Each contains many tables and huge amounts of data.

If I were to do the following...

ALTER SCHEMA my_data_old RENAME TO my_data_tmp;
ALTER SCHEMA my_data RENAME TO my_data_old;
ALTER SCHEMA my_data_tmp RENAME TO my_data;

... I have affectively swapped these around.

My expectation is that these actions are replicated via the postgres WAL (ie: it sends the rename commands out to the replicas) and AWS RDS replication won't try and waste time copying huge amounts of data all over the place.

Is this correct?

Upvotes: 0

Views: 860

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248125

(Speaking about PostgreSQL here, but RDS is probably similar.)

Renaming a schema (or any other object) is a small update in a catalog table, and no data are moved. Internally PostgreSQL uses only the numeric object ID, which stays the same.

You might wrap the three statements in a transaction to make the whole magic atomic.

The same is true on the standby, it is a trivial (meta)data modification.

The only thing that might be a problem are concurrent sessions holding locks.

Upvotes: 0

Related Questions