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. Continue reading →
Numerous DBA’s (counting me), put questions all the time to PostgreSQL hackers/developers/architects on mailing list: Q1. Does PG has the ability to cache/warm a relation ? Q2. Is it possible to return to prior state of cache where it was left before shutting down the database server because of maintenance ? In earlier releases of PostgreSQL, there in no chance of warming a relation or storing a cache states, but from PostgreSQL 9. Continue reading →
With the beta release of PostgreSQL 9.4, DBA’s have been given some cool features like pg_prewarm, JSONB, ALTER SYSTEM, Replication Slots and many more. Out of numerous architectural level features presented in this version, likewise there are other few minor enhancements those I have attempted to cover in this blog. pg_stat_activity view included two new columns (backend_xid/backend_min) to track the transaction id information. pg_stat_activity.backend_xid column covers the id of top-level transaction currently begin executed and pg_stat_activity. Continue reading →
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. Continue reading →
As all of you know after upgrading the database server from one version to other major version, ANALYZE command should be executed to update the pg_catalogs on newly populated data. On a huge upgraded database, its a challenge for the application to gain its performance back without updating the statistics. In PostgreSQL 9.4, an option “vacuumdb –analyze-in-stages” will make this work faster to produce usable statistics required by the optimizer. It runs in three stages with different configuration settings(default_statistics_target/vacuum_cost_delay) to analyze the database. Continue reading →