PostgreSQL in production: what to settle after the proof of concept works

A PostgreSQL installation passes almost any proof of concept you put in front of it. It goes in quickly, it handles the query load, your developers like the tools, and the licence cost is zero. Then the project reaches production, and a different set of questions arrives.

Who gets paged if the primary stops answering? Has anyone restored a backup onto a clean server? What happens when a developer asks for a copy of production data for testing? Can you show which user read a specific row last March?

None of those questions are about SQL. They are about how the database is operated, and they decide whether the platform stays quiet two years from now or becomes a monthly argument.

PostgreSQL handles a lot of enterprise workloads well. Settling five decisions before cutover is what makes that true in practice.

1. How the cluster fails over

One PostgreSQL server is easy to run. The night it matters, you need a standby that takes over without a person deciding what to do.

Patroni is the open-source framework most teams build on. It keeps a leader and one or more replicas in step, and it hands the leader role to a healthy node when the current one stops responding. CYBERTEC provides enterprise support around Patroni and contributes to the project.

Four things worth settling:

• Where the consensus layer runs. Three etcd nodes on separate hosts, not two.

• What applications connect to. A floating endpoint or a connection pooler, not a server IP typed into a config file.

• Whether failover has been tested in a maintenance window. Configured is not the same as tested.

• Who is on the rota, and what the first three steps of their runbook are.

Configuring failover takes an afternoon. Testing it takes a maintenance window. Teams that skip the window usually meet the difference during an incident.

2. Whether the backup has ever been restored

A backup job that reports success is evidence that a file was written. It is not evidence that you can bring the database back.

Worlber's Quick Deploy platform deploys PostgreSQL clusters and supports backups through pgBackRest, WAL-G or pg_probackup, so the tool choice is usually familiar. What matters more is the schedule around it: a full restore test once a quarter, into an isolated environment, timed and written down. The number you want is not "backup completed" but "restore took this long, and here is the log".

Status matters too. Quick Deploy is a self-hosted platform with a one-time perpetual licence, and it ships a web console plus a REST API. Worlber still lists it as in progress, so ask for current release notes before you plan a rollout on it.

3. Data at rest, and the copy in the test environment

Two separate problems get filed under the same heading.

The first is encryption on disk. PGEE, CYBERTEC's enterprise PostgreSQL distribution, ships Transparent Data Encryption, which covers data at rest, the client and server channel, and replication traffic. Key management is the part to plan: an external key manager rather than a key file sitting on the database server, and key rotation that does not require downtime.

The second is the copy. A test environment loaded with live customer records is one of the more common ways a routine production incident turns into a data protection problem, and it usually exists because someone needed data and had no faster option.

PGEE masks at the column level. You attach a masking function to a column, and exports come out obfuscated while production stays readable. Two limits are worth knowing before you design around it. Only immutable functions are accepted, so a mask cannot depend on volatile values, and the masking applies to the export path rather than to the production table. Masked logical replication uses a publication option, so a replica can carry obfuscated data while the primary keeps the real values.

4. Who can see what, and what you can prove later

Access control that exists only in the application is not access control. The database needs its own answer to four questions:

• Which role can read which table.

• Which events are logged, where the log lives, and whether the database's own user can edit it.

• What happens after repeated failed logins.

• Which accounts are still holding privileges they no longer need.

PGEE covers this with a full audit event trail, kept under a separate operating system user so the database process cannot quietly rewrite it, failed login tracking with automatic account locking, password strength and history rules, and permissions that can be limited to a maintenance window instead of granted permanently. Stored procedures can be held in encrypted form, which matters when a procedure contains pricing or scoring logic you would rather not expose.

pgwatch, the open-source monitoring tool maintained by CYBERTEC, is the companion piece: metrics, dashboards and alerting over the same instance list.

5. The inventory nobody has

You cannot patch, secure or size what you cannot list, and most PostgreSQL estates outgrow the spreadsheet that started them.

PG-Inventory is Worlber's own open-source tool for this, published under the MIT licence on GitHub, so it costs nothing to try. It holds instance records with development, QA and production categories, credentials encrypted with AES-256-GCM, role-based access with TOTP two-factor authentication, and probes every two minutes reporting status, version, operating system, CPU and memory. It reads Patroni cluster topology from the Patroni REST API, and it exports to CSV or Excel. It deploys with Docker Compose.

The point is not the dashboard. The point is having one current list you can hand to an auditor, a capacity planner or an incident responder.

Where the enterprise distribution earns its place

Plain PostgreSQL and PGEE differ in a specific set of places, and it is worth being precise about which ones, because the difference decides whether you need PGEE at all.

PGEE adds transparent data encryption, data masking and obfuscation, encrypted stored procedures, security anomaly detection, time-based permissions, optimizer enhancements, active execution plan tracking, automatic incident inspection, job scheduling, automatic storage reorganization, and 24/7 support with immediate bug fixes. Spatial data, cloud readiness and pgwatch monitoring are available in the community build too, but they need separate installation.

The optimizer work targets reporting queries. In a typical case, a query aggregates a large fact table and then joins a small lookup table to get a label. PGEE can aggregate on the numeric key during the scan and join the much smaller result, instead of grouping text keys after the join. CYBERTEC reports performance gains from this class of optimization, and the benchmark methodology is worth requesting before you repeat any number internally.

Support is tiered, with published response times of one hour on the standard tier, 45 minutes on the professional tier, and 30 minutes on the enterprise tier, which runs around the clock and includes PGEE.

CYBERTEC builds PGEE. Worlber's offering includes CYBERTEC's PostgreSQL products and services, and our technical team installs and supports these clusters.

If you are also moving off Oracle or SQL Server

Migration is a separate project from operations, and it has its own order: inventory and scoping first, then a quick assessment that separates easy targets from hard ones, then a detailed assessment with the application owners, then execution, then aftercare and tuning. Worlber and CYBERTEC run that as a staged engagement, with service tiers that differ in how much analysis and automation you get.

For SQL Server estates, the compatibility layer is Babelfish, which speaks the TDS protocol and translates T-SQL, so some applications move without code changes. Suitability is not uniform. Development, test and staging environments, along with small databases that use little T-SQL, are the strong cases. Heavy T-SQL procedure logic sits in the middle. Systems leaning on MS SQL specific features should expect a full migration rather than a shortcut. The layer is not fully compatible, and a plan that assumes otherwise will produce surprises late in the schedule.

That is not an argument against the approach. It is an argument for assessing the estate before committing to a date, which is what the first two stages exist to do.

What this comes down to

Five decisions, one pattern. Each one costs a few days before go-live and a quarter of someone's time after it, and the cost only moves in one direction.

If the proof of concept has already worked and the production plan is being written, the useful conversation is about which of these five your current design still does not answer. Bring the architecture diagram and the backup schedule, and Worlber will go through them with you.

Bring us the production plan

Worlber's offering includes CYBERTEC's PostgreSQL products and services, and our technical team installs and supports these clusters. Send us the architecture diagram and the backup schedule, and we will tell you which of the five decisions your design already answers, and which one it does not.

Talk to Worlber