Every new release of PostgreSQL comes with a packed of exciting features. To benefit new features, database server should be upgraded. Choosing traditional upgrade paths like pg_dump/pg_restore or pg_upgrade requires a significant downtime of application. Today, if you are looking for minimum downtime upgrade path among major PostgreSQL versions with perfect rollback plan, then it will be accomplished by asynchronous Slony-I replication. Since Slony-I (know more about it here) has the capability to replicate between different PostgreSQL versions,OS and bit architectures easily, so upgrades are doable without requiring a substantial downtime.
In the first place, we need to know about why such requirement needed. IMO, its absolutely a business necessity to maintain some kind of historical data on the target database(Slave Node). Especially, out of multiple slave nodes one of the slave node to retain the very first form of the data when it initially written into the database.
To accomplish this requirement, we should come up with some kind of filters like TRIGGERs/RULEs on Slave Node so that it avoids relaying DELETE and UPDATE statements.
WARNING: Mismatch found between sl_table and pg_class. Slonik command REPAIR CONFIG may be useful to rectify this.2014-04-26 07:32:54 PDT FATAL slon_node_health_check() returned false - fatal health problem!REPAIR CONFIG may be helpful to rectify this problem You see this WARNING message in logs and immediate stop of replication, if Slony has observed a mismatch of pg_class.oid and sl_table.tabreloid of a replicating table in a node. Because, by architecture slony holds all replicating objects OID information in its catalogs captured at configure time from pg_class.
Thanks to Slony-I team for releasing Slony-I 2.2.0Beta with many enhancements on key areas. Few are mentioned here:
Event capturing protocol changed for performance (sl_log_1 / sl_log_2):
Earlier release, any DML(INSERT/UPDATE/DELETE) event data must store as SQL statement in two tables(sl_log_1/sl_log_2). Now, the protocol has completely modified to COPY format, as a result of this there’ll be sensible performance improvement like lower processing overhead, lower memory consumption, less query processing work on subscriber database in slon process.
My title might be slight contradictory, as per Slony-I, swapping can be achieved among nodes, if nodes are connected to each other by anyway as PROVIDER or RECEIVER and replicating. If you see in my diagram, “DR-Prod” is nowhere related to Slony replicating nodes, still swapping is possible(with some extra care). Here are some valueable inputs from Steve & Jan.Thanks.
When we need such kind of swapping, if you are planning to move “PROD” from one data center to another or from existing disk volumn to another (many reasons).