Part 045 — PostgreSQL On-Prem and Hybrid Deployment

1. Why this part matters

On-prem and hybrid PostgreSQL change the responsibility boundary.

In managed cloud PostgreSQL, the platform provider usually owns many infrastructure operations:

• physical host lifecycle;
• managed storage integration;
• managed backup execution;
• managed patch workflows;
• managed monitoring integration;
• managed HA primitives;
• control-plane automation.

In self-managed or on-prem PostgreSQL, those responsibilities move closer to the engineering, DBA, SRE, platform, customer operations, or infrastructure team.

The dangerous assumption is:

PostgreSQL is PostgreSQL, so deployment model does not matter.

The SQL engine is still PostgreSQL, but the failure model changes dramatically.

A Java/JAX-RS service can use the same JDBC URL shape and the same MyBatis mapper, yet production behavior can differ because of:

• storage latency;
• filesystem configuration;
• kernel limits;
• network path;
• DNS behavior;
• firewall state;
• TLS certificates;
• failover mechanism;
• backup storage;
• restore runbook;
• monitoring maturity;
• patch cadence;
• version drift;
• customer-specific operational policies.

For a CSG-like Quote & Order platform, this matters because database deployment differences can affect:

• quote creation latency;
• order submission reliability;
• catalog lookup response time;
• migration execution windows;
• CDC/Kafka publishing delay;
• disaster recovery ability;
• upgrade strategy;
• supportability across customer environments;
• incident response ownership.

This part is not a Linux tuning cookbook. It is a senior-engineer mental model for reviewing PostgreSQL in on-prem and hybrid production environments.

2. Deployment model taxonomy

Self-managed PostgreSQL can appear in several shapes.

The first design question is not where is PostgreSQL installed?.

The first design question is:

Who owns each failure domain, and how quickly can they act?

Internal verification checklist

Verify in the CSG/team/customer context:

• Which deployment models are supported for this product?
• Are SaaS, customer-managed cloud, on-prem, and hybrid deployments all supported?
• Are all environments using the same PostgreSQL major version?
• Are schema migrations identical across deployment models?
• Who owns PostgreSQL installation in each model?
• Who owns OS patching?
• Who owns backup/restore?
• Who owns failover?
• Who owns monitoring and alert routing?
• Who can run emergency database commands?

3. Responsibility boundary matrix

For each deployment model, map ownership explicitly.

Do not allow ownership gaps.

A production incident often starts with a gap like:

The app team thought the DBA owned it.
The DBA thought the platform team owned it.
The platform team thought the customer owned it.
The customer thought the vendor-owned product handled it.

Senior engineering rule

For every PostgreSQL deployment model, there must be a named owner for:

• backup execution;
• restore drill;
• failover operation;
• version upgrade;
• extension approval;
• parameter changes;
• emergency index creation;
• long-running query kill;
• migration rollback/roll-forward;
• data repair approval;
• audit evidence extraction.

4. OS and kernel resource awareness

PostgreSQL is a database engine, but it depends heavily on OS resources.

In self-managed deployments, the team must understand at least the symptoms of OS-level limits:

• file descriptor exhaustion;
• process limits;
• shared memory limits;
• huge page configuration;
• kernel overcommit behavior;
• disk scheduler behavior;
• TCP keepalive behavior;
• ephemeral port exhaustion;
• clock drift;
• DNS resolver behavior;
• certificate trust store issues.

A backend engineer usually should not randomly tune kernel parameters during an incident.

But a senior backend engineer should recognize when an application symptom may be caused by OS/database host constraints.

Example symptom mapping:

Useful commands for incident orientation

These commands are examples only. Use approved internal runbooks and permissions.

# host pressure orientation
uptime
free -h
df -h
df -i
iostat -xz 1
vmstat 1
ss -tan | wc -l
ulimit -a

-- PostgreSQL pressure orientation
select now();
show max_connections;
show shared_buffers;
show work_mem;
show maintenance_work_mem;
show effective_cache_size;
show checkpoint_timeout;
show max_wal_size;

Internal verification checklist

• Who owns OS tuning for PostgreSQL hosts?
• Is there a standard PostgreSQL host baseline?
• Are file descriptor/process limits documented?
• Are huge pages used or intentionally disabled?
• Are kernel parameter changes version-controlled?
• Are host-level metrics visible to application teams?
• Are database and application clocks synchronized?

5. Disk layout and storage failure model

PostgreSQL performance and durability depend on storage.

Important PostgreSQL storage paths include:

• data directory;
• WAL directory;
• temporary files;
• tablespaces;
• backup staging area;
• archive/WAL shipping destination;
• logs.

The key production question is:

Which write path becomes critical when workload increases?

For OLTP systems, WAL latency is often more important than raw throughput. A commit-heavy quote/order system can become slow if WAL fsync latency increases.

Common storage design concerns

Disk-full failure mode

Disk full is one of the most direct PostgreSQL production risks.

Failure chain:

data growth / WAL growth / temp files / logs
  -> filesystem fills
  -> writes fail
  -> transactions fail
  -> application errors increase
  -> migration/backfill/CDC may stall

Emergency response should follow a runbook. Do not randomly delete files from PostgreSQL data directories.

Internal verification checklist

• Are data, WAL, temp, logs, and backups on separate storage classes or volumes?
• Is disk usage monitored per mount point?
• Is WAL growth alerting configured?
• Is temp file usage tracked?
• Is backup storage in a separate failure domain?
• Are storage latency percentiles visible?
• Is there a documented disk-full emergency runbook?

6. Filesystem and durability assumptions

PostgreSQL relies on correct filesystem and storage semantics for durability.

Do not treat filesystem choice as cosmetic.

Review:

• fsync behavior;
• write cache behavior;
• storage controller battery/cache protection;
• filesystem mount options;
• snapshot consistency;
• storage replication semantics;
• VM snapshot behavior;
• backup agent behavior.

The dangerous pattern is taking infrastructure snapshots without understanding whether they are PostgreSQL-consistent.

A VM snapshot that captures files without coordinating with PostgreSQL/WAL state can be misleading. Some platforms provide crash-consistent snapshots; others require database-aware workflows.

Senior review question

Can we restore this backup and start PostgreSQL cleanly, or are we only assuming the files look copied?

Internal verification checklist

• What filesystem is used?
• Are mount options standardized?
• Are snapshots crash-consistent or database-aware?
• Is pg_basebackup or equivalent used where appropriate?
• Is WAL archiving configured for PITR?
• Are restore drills performed from the actual backup medium?

7. Network, firewall, and DNS considerations

Hybrid/on-prem deployments often fail through networking before PostgreSQL itself fails.

Important dimensions:

• latency;
• packet loss;
• firewall state timeout;
• NAT behavior;
• TLS termination;
• DNS TTL;
• split-horizon DNS;
• proxy/load balancer behavior;
• cross-region/cross-site routing;
• private endpoint/VPN/direct link failure;
• customer firewall change windows.

A Java service may experience these as:

• connection refused;
• connection timeout;
• read timeout;
• broken pipe;
• SSL handshake failure;
• stale DNS target after failover;
• intermittent pool exhaustion;
• increased p95/p99 latency.

JDBC/DNS nuance

Java runtimes and containers may cache DNS results. In failover designs that depend on DNS endpoint movement, verify how quickly the application resolves the new target.

Do not assume database failover is complete just because the database promoted a standby.

Application recovery also requires:

• old connections to fail fast;
• pool to discard broken connections;
• DNS/proxy endpoint to point to writable primary;
• retry logic to avoid duplicating non-idempotent operations;
• health checks to reflect DB write ability.

Internal verification checklist

• What network path exists between app and PostgreSQL?
• Is there a proxy/load balancer/VIP?
• What is DNS TTL and Java DNS cache behavior?
• Are firewall idle timeouts documented?
• Are TCP keepalive settings configured?
• Is TLS used end-to-end?
• Are certificates rotated through a tested process?
• Has failover been tested from the application perspective?

8. TLS, authentication, and secrets

In self-managed environments, database security often involves more custom decisions.

Review:

• PostgreSQL authentication method;
• pg_hba.conf rules;
• TLS mode;
• certificate authority;
• hostname verification;
• service account credentials;
• password rotation;
• secret storage;
• migration account credentials;
• read-only account credentials;
• emergency admin access.

Dangerous patterns

Java/JAX-RS impact

Credential or certificate rotation must be tested against connection pools.

Questions:

• Does the pool pick up rotated secrets without restart?
• Is restart acceptable?
• Are old connections drained?
• Is there a credential overlap window?
• How are failed authentication spikes alerted?

Internal verification checklist

• Are app, migration, read-only, DBA, and monitoring roles separate?
• Is TLS required by policy?
• Is hostname verification enabled where applicable?
• Where are secrets stored?
• How are secrets injected into Kubernetes/VM/customer deployments?
• Is credential rotation tested?
• Is pg_hba.conf version-controlled or audited?

9. HA topology in self-managed PostgreSQL

PostgreSQL HA is not a single feature. It is a system of replication, promotion, routing, fencing, monitoring, and operational decision-making.

Common components:

• primary server;
• standby server;
• streaming replication;
• WAL archiving;
• replication slots if used;
• failover manager;
• virtual IP, DNS, proxy, or load balancer;
• fencing/split-brain prevention;
• monitoring and alerting;
• promotion runbook;
• application retry behavior.

Conceptual topology:

Java/JAX-RS services
  -> DB endpoint / proxy / VIP / DNS
  -> current PostgreSQL primary
  -> streaming replication
  -> standby
  -> backup/WAL archive

HA tooling examples

Depending on environment, teams may use tools/concepts such as:

• Patroni;
• repmgr;
• Pacemaker/Corosync;
• Stolon;
• custom failover scripts;
• storage-level failover;
• vendor/customer HA platform.

Do not assume any of these are used internally. Treat them as concepts to verify.

Split-brain risk

Split brain means more than one node can accept writes as primary.

For PostgreSQL, this is catastrophic because two divergent histories may be created.

Senior review questions:

• What prevents two primaries?
• What is the source of truth for leader election?
• What fencing mechanism exists?
• What happens if network partitions occur?
• What happens if the old primary comes back?
• How are clients routed after promotion?

Internal verification checklist

• What HA mechanism is used?
• Is failover automatic or manual?
• What prevents split brain?
• How does the app discover the new primary?
• Are read replicas used by application traffic?
• Is synchronous replication used anywhere?
• What is the expected failover time?
• Has failover been tested under write traffic?

10. Backup storage and restore discipline

On-prem backup strategy must be explicit.

A backup strategy is not complete until restore is proven.

Minimum concepts:

• base backup;
• WAL archiving;
• PITR;
• logical dump for selected objects;
• backup encryption;
• backup retention;
• offsite/cross-site copy;
• immutable backup if required;
• restore drill;
• restore target environment;
• RPO/RTO validation;
• evidence for audit/compliance.

Hybrid risk

In hybrid deployments, backups may exist in one environment while applications or integrations exist in another.

Questions:

• Can backups be restored in the same environment?
• Can they be restored in a DR environment?
• Is bandwidth sufficient for restore time target?
• Are encryption keys available during DR?
• Are dependencies such as extensions available in restore target?
• Are object stores/cloud storage reachable from on-prem?
• Are customer policies limiting backup export?

Internal verification checklist

• Where are backups stored?
• Are backups encrypted?
• Are WAL archives retained long enough for PITR?
• When was the last restore drill?
• Was the restored DB validated by application smoke tests?
• Are restore credentials and keys available during incident?
• Is backup ownership different per deployment model?

11. Monitoring stack in on-prem environments

Managed cloud platforms provide integrated metrics. On-prem environments may require custom monitoring.

Minimum monitoring layers:

Observability anti-pattern

The database host is monitored, but SQL workload is invisible.

Host metrics alone cannot explain:

• which query caused CPU spike;
• which transaction blocked others;
• which migration caused table rewrite;
• which endpoint created connection storm;
• which outbox publisher increased WAL retention.

Internal verification checklist

• Is pg_stat_statements enabled if approved?
• Are slow query logs collected centrally?
• Are PostgreSQL metrics correlated with service metrics?
• Are backup/replication alerts routed to the correct owner?
• Are customer-managed deployments observable by vendor support?
• Are incident dashboards standardized?

12. Patch management and version upgrade

Self-managed PostgreSQL requires disciplined patching.

Types of changes:

• OS security patch;
• PostgreSQL minor version upgrade;
• PostgreSQL major version upgrade;
• extension version upgrade;
• HA tool upgrade;
• backup tool upgrade;
• monitoring agent upgrade;
• driver version upgrade;
• application compatibility migration.

Upgrade risk dimensions

Backend engineer responsibility

Even if DBA/SRE owns PostgreSQL upgrade, backend engineers should help validate:

• critical SQL plans;
• mapper integration;
• migration scripts;
• function/procedure syntax;
• extension dependencies;
• CDC/outbox behavior;
• retry/failover behavior;
• application smoke tests;
• performance baseline.

Internal verification checklist

• What PostgreSQL versions are supported?
• Is there a version compatibility matrix per product release?
• Who approves major upgrades?
• Are extension versions included in upgrade planning?
• Are critical query plans captured before/after upgrade?
• Is pgJDBC version compatibility tested?
• Are customer-managed upgrade procedures documented?

13. Air-gapped deployment concerns

Air-gapped environments add friction.

Common concerns:

• offline package installation;
• extension package availability;
• OS patch import;
• vulnerability scanning process;
• backup export restrictions;
• log export restrictions;
• support bundle creation;
• license/package repository mirrors;
• certificate authority management;
• time synchronization without public NTP;
• container image registry mirroring;
• migration artifact delivery;
• emergency hotfix process.

Application impact

A Java/JAX-RS service that assumes cloud-native convenience may fail in air-gapped deployment if it depends on:

• external secret manager not available;
• external object store not reachable;
• external telemetry endpoint blocked;
• online package repository;
• dynamic extension installation;
• public CA chain not installed;
• internet-based time source;
• unmanaged DNS dependency.

Internal verification checklist

• Are air-gapped deployments supported?
• How are PostgreSQL packages and extensions delivered?
• How are migration artifacts delivered?
• How are logs and metrics exported?
• How are backups moved or retained?
• How are certificates rotated?
• How are emergency fixes applied?

14. Hybrid connectivity patterns

Hybrid deployments may connect:

• cloud app to on-prem PostgreSQL;
• on-prem app to cloud PostgreSQL;
• cloud and on-prem services sharing events;
• on-prem PostgreSQL publishing CDC to cloud Kafka;
• cloud reporting system reading on-prem replica;
• customer network integrating with vendor-hosted service.

The main risk is not only latency.

The main risk is unclear consistency and ownership across network boundaries.

Hybrid questions

• What is the expected round-trip latency?
• Is the workload sensitive to p99 latency?
• Are transactions crossing network links?
• Is CDC crossing the link?
• Is replication crossing the link?
• What happens if the link is degraded but not down?
• Are writes still accepted during partial connectivity?
• What is the reconciliation process?

Avoid distributed transaction expectations

Do not design hybrid PostgreSQL integration assuming distributed transaction guarantees unless explicitly provided and tested.

Prefer:

• outbox;
• CDC;
• idempotent consumers;
• reconciliation;
• compensating actions;
• clear ownership boundaries;
• read model replication;
• asynchronous integration.

Internal verification checklist

• What hybrid paths exist in supported deployments?
• Are database connections crossing cloud/on-prem boundary?
• Is replication crossing boundary?
• Is CDC/Kafka crossing boundary?
• Are latency and packet loss monitored?
• Is there a degraded-mode runbook?
• Is reconciliation owned and tested?

15. Java/JAX-RS service impact

Deployment model leaks into Java service design.

Important application concerns:

• connection timeout;
• socket timeout;
• statement timeout;
• transaction timeout;
• pool max lifetime;
• pool validation;
• failover retry;
• idempotency key;
• optimistic locking;
• read-only handling;
• startup dependency on database;
• health check semantics;
• graceful shutdown;
• migration execution timing;
• DNS refresh behavior.

Failure chain example

on-prem primary fails
  -> HA tool promotes standby
  -> DNS/VIP changes
  -> old JDBC connections break
  -> HikariCP starts replacing connections
  -> in-flight requests fail
  -> retry layer retries non-idempotent POST
  -> duplicate quote/order side effect possible

The database failover may be technically successful while the business operation is still incorrect.

Service design rules

• Keep transactions short.
• Use idempotency keys for externally retried commands.
• Make retry policy SQLState-aware.
• Do not retry unknown side effects blindly.
• Health checks should distinguish read ability from write ability when required.
• Configure connection pool to recover from network/failover failures.
• Avoid startup migration races across multiple replicas.

Internal verification checklist

• Are DB timeouts configured consistently?
• Does retry distinguish transient DB errors from domain errors?
• Are POST/command endpoints idempotency-safe?
• How does service behave during failover?
• Are health checks DB-aware without overloading DB?
• Is Java DNS cache behavior configured/understood?

16. MyBatis/JDBC impact

MyBatis itself does not solve deployment failure modes.

The mapper can be correct while the system fails due to:

• long transaction over unreliable network;
• missing timeout;
• large streaming result interrupted by firewall;
• batch operation holding locks too long;
• retry around non-idempotent mapper operation;
• SELECT FOR UPDATE blocked by remote latency;
• migration changing function/extension availability;
• database user privilege differs by environment.

Mapper review additions for on-prem/hybrid

For mapper touching large or operationally sensitive tables, check:

• expected row count;
• timeout requirement;
• lock behavior;
• streaming/fetch size requirement;
• retry safety;
• privilege requirement;
• dependency on extension/function;
• behavior under read replica;
• behavior under failover;
• logging/redaction of SQL parameters.

Internal verification checklist

• Are mapper integration tests run against the same PostgreSQL major version as on-prem?
• Are extension-dependent queries tested where extensions are absent?
• Are SQL timeouts applied at service/pool/session level?
• Are batch operations chunked?
• Are privilege differences covered in test environments?

17. Migration execution in on-prem and hybrid deployments

Migration complexity increases when the team does not fully control runtime environment.

Questions:

• Who runs the migration?
• Is it automatic or manual?
• Is there a maintenance window?
• Are multiple app versions running during migration?
• Is the database local to the app or remote?
• Can migration take locks that block customer traffic?
• Can migration be resumed?
• Is rollback possible or is roll-forward required?
• How is failure communicated to support/customer teams?

Migration risk in customer-managed deployments

Customer-managed deployments may have:

• different data volume;
• different hardware;
• different extension availability;
• stricter change windows;
• limited observability;
• slower emergency access;
• custom reports querying internal tables;
• customer-modified configuration.

That means a migration that passes in SaaS staging may still fail in customer deployment.

Internal verification checklist

• Are on-prem/customer-managed migrations tested on realistic data volume?
• Are lock timings measured?
• Are migration prechecks included?
• Are customer-specific extension/version constraints known?
• Is there a manual runbook?
• Is support trained for failed migration triage?

18. Failure modes and first checks

Minimal SQL orientation

select pid, usename, application_name, state, wait_event_type, wait_event,
       now() - xact_start as xact_age,
       left(query, 120) as query
from pg_stat_activity
where state <> 'idle'
order by xact_age desc nulls last;

select locktype, mode, granted, pid, relation::regclass
from pg_locks
order by granted, locktype, mode;

select datname, numbackends, xact_commit, xact_rollback,
       blks_read, blks_hit, temp_files, temp_bytes
from pg_stat_database
order by numbackends desc;

19. Senior review checklist

Before approving an on-prem/hybrid PostgreSQL-related design, ask:

Deployment

• Which deployment models must support this change?
• Is behavior identical across SaaS, cloud, on-prem, and hybrid?
• What assumptions depend on customer infrastructure?

Reliability

• What happens during failover?
• Is retry idempotent?
• Is restore tested?
• Is RPO/RTO affected?

Performance

• Is storage latency considered?
• Is query plan stable across PostgreSQL versions?
• Is connection pool sizing adjusted per deployment?
• Is network latency acceptable?

Security

• Are roles least-privilege?
• Are secrets and certificates managed safely?
• Is TLS enforced where required?
• Are logs redacted?

Migration

• Can migration run within customer/on-prem window?
• Is it resumable?
• Does it require extension/version support?
• Are prechecks and rollback/roll-forward documented?

Operations

• Who owns alerts?
• Who can take emergency action?
• Is the runbook tested?
• Is observability available to the support path?

20. Internal verification checklist

Use this checklist against the real CSG/team environment.

Deployment inventory

• List all PostgreSQL deployment models supported by the product.
• Identify PostgreSQL major/minor versions per environment.
• Identify OS/platform/container/VM/bare-metal details where applicable.
• Identify managed vs self-managed boundaries.

Infrastructure

• Verify storage layout.
• Verify disk/IO metrics.
• Verify WAL/archive configuration.
• Verify network path, DNS, firewall, TLS, certificates.
• Verify clock synchronization.

Operations

• Verify HA/failover mechanism.
• Verify backup/restore process.
• Verify restore drill evidence.
• Verify patch/upgrade policy.
• Verify monitoring/alert routing.

Application integration

• Verify JDBC URL and failover behaviour.
• Verify connection pool timeout/lifetime settings.
• Verify retry/idempotency behavior.
• Verify migration execution model.
• Verify MyBatis mapper dependencies on extensions/functions.

Supportability

• Verify customer/on-prem support runbooks.
• Verify support bundle contents.
• Verify access constraints.
• Verify escalation path.
• Verify data repair approval flow.

21. Practical senior-engineer heuristics

• Treat deployment model as part of architecture, not environment detail.
• Do not approve database designs that only work in the easiest environment.
• Assume storage latency will surface as application latency.
• Assume DNS/failover behavior must be tested from Java, not only from psql.
• Assume backups are unproven until restore has succeeded.
• Assume customer-managed deployments have version/config drift unless controlled.
• Keep migrations boring, observable, resumable, and reversible when possible.
• Avoid hidden dependencies on extensions, OS packages, or cloud-only services.
• Make ownership explicit before incidents happen.

22. Closing mental model

PostgreSQL on-prem and hybrid deployment is not mainly about installing PostgreSQL.

It is about owning the full operational chain:

hardware / VM / OS / storage / filesystem / network / TLS / PostgreSQL
  -> HA / backup / restore / monitoring / patching / upgrade
  -> JDBC / pool / MyBatis / transaction / migration / API correctness
  -> customer impact / incident response / recovery evidence

A senior backend engineer does not need to replace a DBA or SRE.

But a senior backend engineer must understand how database deployment decisions affect application correctness, latency, operability, and incident blast radius.