PostgreSQL for MySQL Admins

/ home / computer / postgresql / .

ToC PostgreSQL for MySQL administrators

PostgreSQL Operations

1. Installtion of PostgreSQL

   • Which O/S?
   • Which (Linux) distribution?
   • Branches and Forks (Licensen BSD)
   • Which version to use?
   • Installation methods?

2. Configuration of PostgreSQL

   • Configuration
   • PostgreSQL Architecture
   • Cluster / Database / Schema

3. PostgreSQL Client Utilities

   • CLI
   • GUI

4. 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!

5. Further operational tasks

   • User management
   • Securing PostgreSQL
   • Encryption
     • none in Vanilla?
   • Upgrade
   • Maintenance jobs
     • Vacuum / Optimize
     • Analyze
     • Table check and repair
   • Log Files
     • Error Log

High Availability (HA) and Architecture

1. PostgreSQL Architecture

   • Transactions: Autocommit (right place here?)
   • What exactly is the “Commit Log” (CLOG) for? https://pgpedia.info/c/clog.html
   • Logical Structure of Database Cluster
   • [Understanding the PostgreSQL Architecture](https://severalnines.com/blog/understanding-postgresql-architecture/
   • [Understanding PostgreSQL for MySQL developer and DBA](https://www.socallinuxexpo.org/scale/22x/presentations/understanding-postgresql-mysql-developer-and-dba
   • David Stokes, Percona: PostgreSQL for MySQL DBAs Episode 1
   • David Stokes, Percona: PostgreSQL for MySQL DBAs Episode 2
   • David Stokes, Percona: PostgreSQL for MySQL DBAs Episode 3
   • David Stokes, Percona: PostgreSQL for MySQL DBAs Episode 4
   • David Stokes, Percona: PostgreSQL for MySQL DBAs Episode 5
   • David Stokes, Percona: PostgreSQL for MySQL DBAs Episode 6 – Explaining EXPLAIN (And an Answer to a Bonus Question)

2. Reasons for HA

   • Availability / failover
   • Reporting replica
   • Backup replica?
   • Read scale-out?
   • Delayed replica?
   • Upgrade path

3. Logical Replication

   • Preparation
   • Set-up
   • Operations
   • Monitoring
   • Failover
   • Advanced topics

4. Physical Replication

   • Preparation
   • Set-up
   • Operations
   • Monitoring
   • Failover
   • Advanced topics

5. Active/passive failover Cluster

   • With SAN
   • With DRBD

6. 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/

7. Cluster management software

   • Patroni (leader election via etcd/Consul/Zookeeper)
   • repmgr (basic failover management)
   • pg_auto_failover (simplified two-node setups)
   • Pgpool-II (basic HA + connection pooling)

8. 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/

Performance Tuning

1. Performance definition

   • latency
   • throughput

2. Monitoring

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

3. Tuning measures

   • RAM, I/O, CPU, NW
   • O/S
   • PostgreSQL configuration parameters
     • Connection handling
     • Caches (shared_buffer, private cache)
     • Most important tuning parameters
   • Transaction handling
   • Temporary tables
   • Stored Procedures
   • Schema tuning
     • Data models / normalization
     • Data types
     • Indexing
     • Different index types - no Index Clustered Table (reorganize) - find unused indexes
   • Query tuning
   • Profiling
   • Performance views
   • Monitoring

Notes

See also:

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