PostgreSQL Database Administration on Windows/Linux- Part 2

Set up log-based shipping standby on Windows by enabling archive mode and archive_command to copy wal files to a shared archive dir, and prepare master and standby with admin privileges.

Learn to set up log shipping for PostgreSQL on Windows, configure backup mode, copy data to standby, enable standby mode, and validate asynchronous wal file replication.

Set up log shipping standby on linux by configuring ssh key based access, rsync transfers, and archive parameters (archive_mode, archive_command, archive_timeout) across master and standby.

Demonstrates manual failover from master to standby in a log-based shipping setup. Promotes the standby with pg_ctl, verifies the new master accepts connections and write operations, and explains reconnecting applications.

Learn to set up streaming replication on linux, configure master and standby, create a replication user, adjust postgresql conf and pg_hba, perform a base backup, and verify real-time WAL streaming.

Monitor streaming replication in PostgreSQL using generic views like pg_stat_replication to track master and standby communication, lag, and LSNs, and verify recovery mode and current WAL status.

Learn how to add a new standby to a PostgreSQL cluster, configure it to follow the primary, manage automatic failover with node priority, and rejoin after failures.

A witness server acts as a referee for a PostgreSQL primary–standby, not part of replication or the cluster, preventing split-brain by confirming primary availability and guiding promotions.

Configure and customize event notification using rpm conf file parameters to monitor events, trigger scripts or emails on node promotions, rejoin, and switchover, and log for DBA oversight.

Learn how repmgr monitoring tracks replication lag, wal delay, and node health using the monitoring history table. Compare event notification with proactive background monitoring that logs cluster metrics.

Set up logical replication between Windows source and Linux destination by configuring wal_level and listen address, creating a publication and subscription, and checking status.

Add a new table to an existing publication in PostgreSQL logical replication, then refresh the subscription to propagate and verify replication; you can restrict the publication to inserts and updates.

Explore PostgreSQL 15's logical replication enhancements, including alter subscription skip, column-level and schema-wide publications, row-filtered replication, disable on error, and new error stats view.

Discover PostgreSQL 16's logical replication enhancements, including standby-based replication, parallel subscription processing, binary initial load, bidirectional replication, and new pg_create_subscription role.

Demonstrate bidirectional replication with postgresql 16 on two linux boxes, using publications and subscriptions to enable multi-master replication and illustrate how to stop the infinite loop.

Learn how to configure bidirectional replication in PostgreSQL 16, avoid infinite loops by using origin none and replica identity full, and validate with cross-node inserts and log checks.

Explore binary copy in PostgreSQL 16 for fast initial data load from master to target. Benefit from binary format that improves performance up to 30–40% with matching data types.

Explore PostgreSQL 16 logical replication with publications and subscriptions, compare text and binary initial sync times (29 seconds vs 21 seconds), and highlight binary copy as a major performance improvement.

Configure streaming to parallel with multiple background workers to speed up applying changes to the subscription. Use max parallel apply per subscription to tune performance.

Explore parallel apply in PostgreSQL 16 by creating a publication and subscription, increasing max apply workers, and comparing streaming parallel performance with 3 million inserts.

PostgreSQL 16 introduces logical replication from standby, enabling standby to become a source for publications and subscriptions while streaming replication remains between primary and standby for workload distribution.