PostgreSQL for MySQL Admins

/ home / computer / postgresql / .

PostgreSQL for MySQL administrators

• Daniel Guzman Burgos, 2026-03-31: PostgreSQL Internals for the MySQL DBA - You already know how a database works. This is about how a different database works.

PostgreSQL Operations

1. Installation of PostgreSQL

   • Which Operating System
   • Which (Linux) distribution
   • Branches and Forks
   • Which version to use
   • Installation methods
   • Installation of PostgreSQL from Source

2. PostgreSQL Client Utilities

   • CLI utilities
   • CLI commands
   • GUI

3. PostgreSQL Architecture

   • Naming Conventions
   • Process Architecture
   • Shared Memory

4. Configuration of PostgreSQL

   • Configuration Files
   • Configuration Variables

5. Backup, Restore and Point-in-Time-Recovery (PiTR)

   • Logical Backup
   • Physical Backup
   • Backup methods
     • File system copy
     • Dump
     • Snapshots
     • PostgreSQL physical backup
   • Point-in-Time-Recovery
     • The Write-Ahead-Log (WAL)
     • Managing the WAL
     • Doing a PiTR
   • Testing your restore!

6. Further PostgreSQL operational tasks

   • Accessing the database
   • User management
   • Securing PostgreSQL
   • Upgrading PostgreSQL
   • Maintenance tasks
   • Error Reporting and Logging

High Availability (HA) and Architecture

1. PostgreSQL Replication

2. Active/passive fail-over Cluster

   • With shared disk (SAN)
   • With DRBD

3. Load balancing / Proxying

   • pgpool https://stormatics.tech/blogs/which-postgresql-ha-solution-fits-your-needs-pgpool-or-patroni
   • patroni https://stormatics.tech/blogs/which-postgresql-ha-solution-fits-your-needs-pgpool-or-patroni
   • ProxySQL for PostgreSQL https://proxysql.com/documentation/proxysql-configuration-postgresql/

4. Cluster management software

   • Patroni (leader election via etcd/Consul/Zookeeper)
     • Patroni, Spock (pgEdge), etcd → fail-safe redundancy, instant failover without hiccups, multi-master system
   • repmgr (basic failover management)
   • pg_auto_failover (simplified two-node setups)
   • Pgpool-II (basic HA + connection pooling)

5. Other HA / Cluster solutions

   • pgstream
   • pgactive (Amazon): PostgreSQL active-active replication: https://percona.community/blog/2025/06/18/postgresql-active-active-replication-do-you-really-need-it/

PostgreSQL Performance Tuning

1. Performance definition

   • latency
   • throughput

2. PostgreSQL Tuning

   • Hardware Tuning
   • Operating System Tuning
   • PostgreSQL Server Configuration Parameters
   • Transaction handling
   • Temporary tables
   • Stored Procedures
   • Schema tuning
   • SQL Query tuning
   • Profiling
   • Performance views

3. Monitoring

   • O/S (CPU, RAM, I/O, Network)
   • PostgreSQL (Processlist, Performance metrics)

PostgreSQL for MySQL developers

• DML - Befehle

PostgreSQL internals

• Index Maintenance

Notes

See also:

• Spickliste
• PostgreSQL for a rusty MySQL brain
• PostgreSQL cheat sheet for MySQL users