Install & design
Version choice (support lifecycle), package/source installs, directory/tablespace/parameter standards, extension policy.
PostgreSQL
PostgreSQL design · HA · tuning · migration
Extensible and standards-compliant, PostgreSQL has become the go-to alternative to commercial databases. We deliver HA clusters with automatic failover, backups you can restore to a point in time, evidence-based tuning and migrations from Oracle and Tibero.
Architecture
Recommended HA architecture
The most widely used open-source combination: Patroni + a distributed configuration store (DCS) + PgBouncer/HAProxy + pgBackRest.
Patroni uses a DCS such as etcd, Consul or ZooKeeper to elect a leader and automatically promotes the most suitable replica when the primary fails. Applications always reach the current primary through HAProxy (or a VIP), while PgBouncer keeps connection counts under control.
WAL is archived continuously with pgBackRest, so a full backup plus WAL replay can restore the database to any chosen moment.
- Run the DCS on an odd number of nodes (3+) to avoid split-brain
- Choose synchronous vs asynchronous replication as an RPO vs write-latency trade-off
- Offload reads to replicas once the application’s lag tolerance is confirmed
- Rehearse failover and switchover regularly
Applicationservices / batch
connect
HAProxyseparate R/W · R/O ports
PgBouncertransaction pooling
route
PrimaryPatroni
Replica 1streaming
Replica 2streaming
state & backup
etcd ×3leader election (DCS)
pgBackRestfull/diff/incr + WAL
pg_stat_statementsGrafana / PMMAlerts
Scope
PostgreSQL service scope
HA & replication
Streaming (physical) replication, logical replication (publication/subscription), Patroni failover, replication-slot management.
Backup & PITR
pgBackRest, Barman, pg_basebackup, WAL archiving, target-time recovery and regular verification.
Tuning
shared_buffers, work_mem, WAL and checkpoints, autovacuum, plans, indexes and partitioning.
Monitoring
pg_stat_statements, pg_stat_activity, wait events, replication lag, bloat, Grafana/PMM dashboards.
Upgrades
pg_upgrade (e.g. --link) or low-downtime major upgrades via logical replication.
Security
SCRAM-SHA-256, minimal pg_hba.conf, role design, TLS, row-level security, pgaudit.
Oracle · Tibero migration
ora2pg assessment and conversion, PL/SQL → PL/pgSQL, data validation, re-tuning.
Replication
Replication options compared
| Option | What it is | Good for | Watch out for |
|---|---|---|---|
| Streaming replication | Physical replication shipping WAL byte-for-byte; sync or async | HA, read replicas, DR | Same major version and platform required |
| Logical replication | Table-level changes via publication/subscription | Partial replication, major upgrades, consolidation | DDL and sequences need separate handling |
| Patroni | DCS-based leader election, automatic failover, config management | Production HA standard | DCS availability and fencing design are key |
| PgBouncer | Lightweight pooler (session/transaction/statement modes) | Taming connection storms | Check session-state limits in transaction mode |
Backup & PITR
Backup and point-in-time recovery
With a full backup and a WAL archive you can rewind to just before a failure or a mistaken change. A backup only counts once you have restored it.
# 1) Stop the service and restore to the target time
sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main --delta \
--type=time "--target=2026-10-20 13:55:00+09" \
--target-action=promote restore
# 2) Start and confirm recovery has finished
sudo systemctl start postgresql
psql -c "select pg_is_in_recovery();" -- f = promoted-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT queryid, calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;Examples use standard options. In practice we run a verified runbook adapted to your stanza name, repository location, time zone and PostgreSQL version.
Tuning
Where we tune
Instance & configuration
- Memory: shared_buffers, work_mem, maintenance_work_mem, effective_cache_size
- WAL and checkpoints: max_wal_size, checkpoint_timeout, wal_compression
- autovacuum thresholds and workers, per-table settings for large tables
- Connection limits plus PgBouncer pooling
- OS: huge pages, I/O scheduler, filesystem mount options
Queries & schema
- Verify plans with EXPLAIN (ANALYZE, BUFFERS)
- Composite, partial and covering indexes; remove unused ones
- Declarative partitioning and partition pruning
- Statistics management (ANALYZE, extended statistics)
- Fix application patterns such as N+1 and chatty ORM queries
Migration
Oracle · Tibero → PostgreSQL
DBAs with commercial-database experience flag compatibility gaps up front.
Assess
Size objects and PL/SQL and estimate effort with an ora2pg report
Convert schema
Map types (NUMBER, DATE …), sequences, partitions and constraints
Convert code
Packages, procedures and triggers to PL/pgSQL; adjust application SQL
Migrate data
Full load then a change-sync approach; verify counts and checksums
Validate performance
Compare and tune plans for representative business queries
Cut over
Rehearse → cut over → monitor stabilisation
ora2pg targets Oracle. Tibero is Oracle-compatible, but whether a tool can connect to it directly must be confirmed per environment; where needed, schema extraction and conversion run as a separate procedure.
Lifecycle
Version lifecycle check (as of October 2026)
- The community supports each major version for five years after its first release.
- Supported in October 2026: 18 · 17 · 16 · 15 · 14 — PostgreSQL 14 is scheduled to reach end of life on 12 November 2026
- PostgreSQL 19 is at release-candidate stage in October 2026 (not yet GA); validate after GA before production use.
- Always run the latest minor release of your major version.
Source: postgresql.org Versioning Policy. Dates may change — check the official page before acting.
FAQ
Frequently asked questions
Isn’t streaming replication enough without Patroni?
Replication itself is built in, but detecting failure, promoting, fencing the old primary and redirecting connections need extra tooling. Patroni is the leading open-source tool that automates this.
Do we really need PgBouncer?
It pays off with many connections or frequent short-lived connections. In transaction pooling mode, check session-level features (some prepared-statement usage, session variables, etc.).
Can we do a major upgrade without downtime?
The usual approach is to sync a new-version cluster with logical replication and switch over in a short window. If you can accept downtime, pg_upgrade is simpler.
Won’t performance drop after leaving Oracle?
Optimiser, locking and transaction behaviour differ, so some queries need rewriting or index changes. We compare representative queries before and after and include tuning in the migration.
Shall we start by reviewing your cloud and databases as they are today?
Tell us your clouds, database versions, topology and concerns, and we will propose an assessment scope and approach.