Postgres Cluster: Master Patroni-Managed Clusters

Learn to install Oracle on Linux by creating a VirtualBox VM and configuring Oracle Linux, including downloading the ISO and following prerequisites, and using bridged networking for a networked environment.

Demonstrates installing Oracle Linux in a VirtualBox VM, mounting the ISO, configuring language, keyboard, time zone, and root password, with practical setup tips.

Choose a lightweight Oracle Linux server without gui to maximize performance. Then configure lvm partitions—root, boot, home, swap—with efi, and set a static ip in bridged mode before installation.

Reboot the Oracle Linux installation and log in as root with the provided password. Verify with uname -r and view release info with cat; explore with pwd, ls, and clear.

Disable the firewall on the Oracle Linux server, configure remote access with Putty, and use root-level commands to enable external access for a Postgres cluster.

Change the Linux IP address to a new static value using nmcli and ip, verify with ip, and ensure ssh works by editing ssh_config and restarting the ssh service.

Master essential Oracle Linux commands to navigate and manage the filesystem with cd, pwd, ls, mkdir, and rmdir; understand root and home directories, permissions, ownership, and absolute paths.

Master basic linux commands to create and edit files with nano or vim, view content with cat, and delete files or directories using rm with relative and absolute paths.

Explore the Linux directory structure, including root, home, bin, etc, dev, lib, lib64, opt, and tmp, and learn how permissions and root access shape system management and crontab schedules tasks.

Update Oracle Linux packages by refreshing the repository, applying updates, and upgrading installed packages, then reboot the system to complete security and stability improvements.

Explore Linux disk management basics using df and ls -l to monitor mounted file systems, plan capacity with the Linux team, and understand mounting and file system types.

Use the free -m command to view total, used, and available memory and swap on Linux, then use top to monitor real-time CPU and memory usage by processes.

Learn to use chmod to grant, change, or remove permissions for owner, group, and others, covering read, write, and execute, plus symbolic and octal methods with practical lab examples.

Learn how to use the chown command to change file and directory ownership in Linux, including owner and group management, root privileges, and owner:group syntax with practical examples.

Discover how to use linux cp and mv to copy and move files, leverage wildcards for bulk operations, and apply safe backup practices when managing files.

Master linux file search with find and grep, learning to locate files by name with wildcards and to search their contents, including recursive and root-level searches.

Explore how the Linux path variable guides program discovery, learn to monitor logs with tail -f in real time, and create practical aliases to simplify complex commands.

Master two PostgreSQL installation methods on Oracle Linux: in-VM download versus FTP-copy install, with VM cloning, static IPs, and PuTTY for command execution.

Learn to install Postgres 17 on Oracle Linux, disable pre-installed Postgres, initialize and start the service, manage Postgres user access, and create databases and users securely.

Install Postgres on Oracle Linux using the offline second method by copying the installer from another machine via FTP, then initialize the database and start the service.

Change to the postgres linux user, connect to the Postgres database with PgAdmin and SQL, and create databases, tables, and users while enforcing passwords.

Learn to connect a Linux-hosted PostgreSQL server with PgAdmin by registering the server IP, enabling external connections, and validating secure access with the Postgres user and password.

Switch to the Postgres context, edit the pg_hba.conf to set md5 authentication, and restart Postgres to require a password for Postgres connections on Oracle Linux.

Learn how to design a three-node high-availability PostgreSQL cluster using Patroni with etcd and haproxy, including automatic failover, load balancing, and robust backups.

Explain how Patroni, etcd, and HAProxy create a robust automatic failover PostgreSQL cluster, manage quorum, avoid split brain, and support synchronous and asynchronous replication.

Explain how quorum ensures a single leader in a Patroni managed PostgreSQL cluster by requiring majority among three or more nodes, preventing split-brain and ensuring reliable failover.

Install etcd on the first cluster node and configure it for cluster management, then stop and disable PostgreSQL so Patroni can manage it and handle failover.

Configure and activate etcd across all nodes in a Patroni-managed Postgres cluster, establishing a quorum-driven failover with leader and replica awareness.

Configure etcd for a Patroni-managed Postgres cluster, create and save config blocks, restart services, and troubleshoot startup messages in preparation for installing Patroni.

Install and configure Patroni to manage a PostgreSQL cluster, create a replication user, set up systemd service, start Patroni, and verify leader status and failover readiness with etcd and HAProxy.

Install and configure HAProxy across all nodes to route writes to the leader via Patroni, balance reads across replicas, and expose a single proxy IP with health portal on 8080.

Explore configuring a floating virtual IP with keepalived across a three-node Patroni-managed Postgres cluster, enabling automatic leader failover, seamless HAProxy routing, and reliable read/write access through a single IP.

Learn how Patroni-managed PostgreSQL clusters rely on physical replication, copying binary data to create identical read-only replicas with automatic failover, contrasting with logical replication.

Clone node 188 to create node 189, set its IP to 189, and join it as a standby replica in the Patroni-managed PostgreSQL cluster with HAProxy and etcd.

Configure etcd on the first cluster replica and add the new machine 189 to a Patroni-managed Postgres cluster, ensuring quorum, proper replication, and cluster health.

Configure Patroni on the first cluster replica, joining 189 to the etcd cluster for a three-node high-availability setup. Resolve replication permission errors by authorizing the replicator user and verifying status.

Update the pattern and pg_hba files on 188 and 189 to include 188, 189, and 190, set up replication with the replicator user, restart patroni, and verify status.

Finish configuring the first replica of the Patroni-managed Postgres cluster; validate streaming replication from leader 88 to replica 189, with 189 read-only, then test by stopping both nodes.

Finish configuring the first replica of the cluster by updating Patroni and HAProxy settings, enabling a single virtual IP, and validating failover between leader and replica for seamless application access.

Clone the existing vm 189 to create node 190 for a three-node patroni-managed postgres cluster, then change its ip and prepare it to join the high-availability quorum.

Add 190 to the Patroni cluster as node three and restart Patroni, HAProxy, and etcd on all nodes to verify a healthy three-node setup.

Diagnose a Patroni-managed Postgres cluster with three nodes by updating etcd configuration, verify node health and leadership, and explain why replica 188 fails to initialize while 190 remains leader.

Identify and resolve Patroni cluster issues by inspecting logs, fixing timeline mismatches, and safely restarting replicas after deleting data on the problematic node to re-sync.

Test and finalize Patroni, etcd, HAProxy, and keepalived configuration by performing failover tests, validating leader and replicas, and configuring asynchronous replication with plans to enable synchronous replication.

Explore quorum testing in a three-node patroni-managed Postgres cluster, simulate node failures, observe auto failover with a majority, and prepare for synchronous versus asynchronous replication.

Master synchronous and asynchronous replication in Patroni-managed PostgreSQL clusters. Learn how to switch modes, test failover, and ensure writes are replicated to disk across leader and replicas.

Join this bonus lesson to engage with a vibrant database community on Facebook, access LinkedIn and YouTube resources, and explore database topics like Power BI, SQL Server, and MySQL.