PostgreSQL migration for accounting system admins

Moving an accounting system to PostgreSQL does not remove the administrator's work. It moves that work from the familiar console of a commercial DBMS into a combination of SQL views, configuration files, operating system tools, and the backup and monitoring products you choose. Licensing no longer dictates the architecture, but the team becomes responsible for tasks that used to come with the standard package or support contract.

For a concrete comparison, I use a typical on-premises Microsoft SQL Server installation. If you run another commercial DBMS, the screens and procedure names will differ, but the test remains the same: who patches the server, where query history is stored, how the database is restored to a selected moment, and who gets the overnight call. Those answers reveal the actual cost of migration.

The responsibility boundary changes, not just the DBMS

The main change for an administrator is that a ready-made set of solutions becomes an architecture that must be assembled and documented. PostgreSQL supplies the database server, system views, logs, the pg_dump, pg_restore, and pg_basebackup utilities, and streaming replication mechanisms. It does not choose your backup storage, failover manager, alerting system, WAL retention period, or support provider.

In a commercial environment, an administrator often inherits the vendor's recommended path: a graphical console, job agent, maintenance plans, proprietary monitoring, and one support line. That path is not necessarily configured well. Its boundaries are simply established already. PostgreSQL offers more freedom, so two installations of the same version may use different backup tools, failover methods, metrics collectors, and upgrade procedures.

Before designing the new environment, inventory the services the old DBMS provided almost invisibly. These may include agent jobs, notification delivery, linked servers, encryption, certificate management, plan history, auditing, log cleanup, and integration with enterprise backup software. For each item, decide whether it remains necessary, what will replace it, and who will verify the replacement. A forgotten overnight data-exchange job will not appear in a login acceptance test. Users will find it the morning after the first production day.

The PostgreSQL license has a practical advantage: its official text permits anyone to use, copy, modify, and distribute the software without a fee or written agreement, provided the copyright notices are retained. But eliminating a per-core database fee does not make the database free to operate. Team hours, a test environment, outside expertise, monitoring, backup storage, and on-call coverage move into the budget.

Before approving the migration, create a responsibility table. For each operation, name the owner, execution window, failure signal, and action to take when it fails. If database recovery, a full disk, or a minor version update points to a single enthusiast and nobody else, the system is not ready for production.

Routine maintenance becomes more explicit

PostgreSQL calls for fewer magical rituals and more understanding of table, transaction, and disk state. Daily work usually includes checking connections, locks, replication, data growth, WAL, logs, backup jobs, and background cleanup. Many checks happen through SQL queries and metrics rather than one interface.

An administrator needs to work confidently with postgresql.conf, pg_hba.conf, roles, pg_stat_* system views, and the server log. Some settings take effect after a configuration reload, while others need a restart. The change procedure must mark that distinction, or an innocent edit will either remain inactive or cause an unexpected outage window.

Scheduled jobs also remain necessary. Administrators run them through the operating system, an external orchestrator, or a specialized tool. The scheduler's name matters less than four properties: a separate service account, a predictable environment, a result log, and an alert on a nonzero exit code. A job that has failed silently for three weeks is worse than no job because it creates false confidence.

Plan capacity separately for data, indexes, temporary files, logs, and WAL. WAL growth depends on the write pattern, checkpoints, replication, and archiving, so do not carry over a free-space percentage from the old runbook without measurements. The alert must fire before the server loses the ability to finish operations, and the on-call engineer must see which consumer is growing. Never delete files from pg_wal with operating system tools. PostgreSQL manages that area, and manual cleanup can leave the cluster unrecoverable.

PostgreSQL upgrades fall into minor updates within a major branch and migrations between major versions. Minor updates install fixes over the existing data directory, but they still deserve testing on a copy of the environment and a rollback plan. A major version needs a separate project using pg_upgrade, logical replication, or dump and restore, plus checks for extensions, drivers, and downtime. Leaving a working server untouched for years turns a manageable task into a risky leap across several versions.

Autovacuum needs supervision

In PostgreSQL, an updated or deleted row version does not disappear from the file immediately, so autovacuum affects both performance and availability. Multiversion concurrency lets readers and writers interfere with each other less, but dead row versions remain until cleanup. When cleanup falls behind, tables and indexes grow, the planner gets an inaccurate picture of the data, and old transactions keep garbage from being reclaimed.

The PostgreSQL manual gives four reasons for routine VACUUM: reusing space, updating planner statistics, maintaining the visibility map, and preventing transaction ID wraparound. That list corrects the popular advice that autovacuum is enabled by default and can therefore be ignored. An enabled worker does perform the job, but default thresholds do not necessarily suit a journal table where a significant share of rows changes within an hour.

At minimum, monitor the age of the last vacuum and analyze operation, estimated live and dead row counts, long transactions, and the progress of an active VACUUM. Large tables with frequent changes often need table-specific autovacuum settings. One aggressive cluster-wide value usually creates unnecessary I/O on quiet tables while still arriving late for the busiest ones.

VACUUM FULL should not become a weekly cleanup ritual. Regular VACUUM makes space reusable within the table and runs alongside ordinary workload. VACUUM FULL rewrites the table and takes an ACCESS EXCLUSIVE lock, so an attempt to reclaim disk space during business hours can stop the accounting system. If it is needed regularly, investigate table growth, long transactions, and unsuitable cleanup thresholds first.

Another trap sits in the connection pool. A session in idle in transaction state can look harmless, but its open transaction holds an old data snapshot. Limit the duration of such transactions, fix the application, and monitor their age separately. Killing sessions on a schedule hides the defect and may terminate a legitimate user operation.

The recovery method defines the backup

With PostgreSQL, choose the required recovery point and recovery time before designing backups. A logical dump, physical base backup, and replica solve different problems. One nightly pg_dump is convenient for moving individual objects and selective restoration, but it cannot restore to an arbitrary second between dumps.

Point-in-time recovery requires a physical base backup and an unbroken chain of archived WAL. The PostgreSQL manual separates these mechanisms explicitly: pg_dump creates a logical dump and is not part of continuous archiving, while a physical backup plus WAL can replay changes to a selected moment. One missing segment in the middle of the chain limits the reachable point, even if later files are present.

pg_basebackup can take a base backup from a running cluster and creates a manifest that pg_verifybackup checks. Manifest verification confirms file membership and checksums, but it does not replace starting the restored instance. Access failures, a bad restore_command, a missing extension, or an overlooked configuration file appear only during a rehearsal.

Keep copies away from the primary node and manage the credentials used to write and read the archive separately. Encryption is useless if its key sits in the same directory and disappears with the server. The retention policy must cover daily, weekly, and longer recovery points, personal-data requirements, and reliable deletion after expiry. Protect the backup catalog as well: the team must be able to locate the required base backup and complete WAL chain without relying on one administrator's memory.

A working test must look like service recovery, not a glance at a green status icon:

1. Provision a clean node with the same PostgreSQL major version and install the required extensions.
2. Restore the latest base backup, supply WAL from backup storage, and set a target time.
3. Start the cluster on an isolated network, confirm that recovery completes, and measure the time from starting work until it accepts connections.
4. Check control entities in the accounting system: the accounting period, document count, totals for preselected registers, and the service role's permissions.
5. Record the achieved RPO and RTO, the reason for every manual step, and the owner of each correction.

Repeat this rehearsal after changing the major version, backup tool, encryption, storage, or authentication design. A backup without a measured restore remains an assumption.

A replica does not replace a backup

Streaming replication improves availability, but it normally repeats user errors and logical corruption. Dropping a table, running a bad bulk UPDATE, or writing corrupt application data enters WAL and is replayed on the standby. A replica helps when a server fails, while an archive with point-in-time recovery helps return to the state before a faulty operation.

Teams often blur this boundary because a replica looks like a fresh copy of the whole database. The consequence is expensive: after an accidental deletion, the administrator finds two equally healthy servers that both lack the required data. The design must therefore document backup, high availability, and disaster failover separately.

Automatic failover needs an external solution and a clear rule for selecting the primary node. PostgreSQL transmits WAL and supports standby servers, but distributed consensus, a virtual address, client routing, and protection against two primaries depend on the selected architecture. Before automating, verify that the accounting application reconnects correctly, the pool discards stale connections, and transactions lost during failover produce a result users can understand.

Synchronous replication reduces the risk of losing acknowledged transactions, but it adds latency and can stop commits if the required synchronous standby is unavailable. Asynchronous replication gives the primary more independence, but a failure can lose the latest acknowledged changes. The process owner makes that choice according to the acceptable RPO, not an administrator's personal preference.

Finally, rehearse the return to the former primary. A successful failover that cannot be reversed safely merely postpones the incident until the next maintenance window. The procedure must account for PostgreSQL's new timeline, divergence of the old primary, and the method for adding it back through a fresh base backup or pg_rewind when its conditions are met.

Performance diagnostics starts with a new baseline

Old counters and familiar maintenance plans cannot be moved mechanically to PostgreSQL. Microsoft SQL Server has Query Store, which retains a history of query texts, plans, runtime statistics, and waits in time intervals. In PostgreSQL, pg_stat_activity shows current activity, the pg_stat_* family holds cumulative statistics, and teams usually collect normalized query history with the pg_stat_statements extension and an external metrics system.

You must add pg_stat_statements to shared_preload_libraries in advance, restart the server, and create the extension in the required database. Turning it on after an incident does not recover previous history. This is a common post-migration surprise: the query is slow today, but nobody can compare it with yesterday's plan or duration.

Capture a baseline on the old platform before migration, then repeat it on PostgreSQL with the same business operations. Record median and slow executions, concurrent user count, read and write volume, lock duration, database size, and batch-job timing. Without starting measurements, a claim that the system became slower turns into an argument over memory. Account for cache warmup and equal data volume, or the first run of a new report will be compared with an old plan that has been warm for months.

The following queries capture a minimal diagnostic snapshot. They do not fix the fault, but they separate an active wait, a costly accumulated query, and a table where cleanup is lagging:

SELECT pid, usename, state, wait_event_type, wait_event,
       clock_timestamp() - query_start AS running_for,
       left(query, 160) AS query_text
FROM pg_stat_activity
WHERE datname = current_database()
  AND state <> 'idle'
ORDER BY query_start;

SELECT calls, round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       rows, left(query, 160) AS query_text
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;

The first query returns one row per server process, including its state and wait event. The second shows queries that consumed the most total time since statistics were reset, not necessarily the slowest single execution. The third returns estimated row counters, so treat them as an investigation signal rather than exact accounting figures.

After finding a query, examine EXPLAIN (ANALYZE, BUFFERS) on a safe copy or for a controlled SELECT. The ANALYZE option really executes the statement. Running it on a data-changing statement in production can repeat the change unless you wrap the test in a transaction, roll it back, and understand the side effects. Compare estimated and actual rows, buffer reads, join types, and node timing.

Do not start treatment by randomly changing shared_buffers or disabling sequential scans. First preserve the plan, query parameters, data volume, table statistics, wait events, and operating system metrics. PostgreSQL itself recommends using tools such as top, iostat, and vmstat alongside internal views: SQL text alone will not explain a disk queue, memory pressure on the node, or network latency.

Test application compatibility with real workload

Compatibility means correct results and acceptable response time, not merely successful table creation. Accounting systems often generate SQL through a platform or ORM, so the same user report may get different plans, parameter types, and locking behavior on different databases. Certification of the target PostgreSQL version by the application vendor is mandatory, but it does not replace tests of your extensions, reports, and data exchanges.

Check data types, monetary precision, string comparison rules, case handling, time zones, sequences, and null behavior. Custom queries that use vendor-specific syntax, optimizer hints, stored procedures, and implicit type conversions deserve special attention. An automated converter can move a construct without proving that its meaning survived.

Verify transferred data at two levels. Technical reconciliation counts rows, finds load errors, and compares selected checksums. Business reconciliation confirms balances, turnovers, closed periods, document references, and access rights. Matching total row counts are not enough because missing rows for one entity may be offset by duplicates for another. Agree on reconciliation rules with the accounting owner in advance and retain the results as part of the cutover record.

A load test must reproduce the accounting calendar. An average Tuesday says little about month-end close, mass document posting, payroll, a scheduled import, or a large report. Use an anonymized copy at production volume, fix a repeatable set of operations, and compare more than average duration. Measure tail latency, locks, WAL volume, table growth, and backup duration as well.

Test the connection pool separately. PostgreSQL allocates a process to each client connection, and an oversized pool consumes memory and increases contention. A pooler can limit connections, but transaction pooling may conflict with temporary tables, session state, and some prepared statements. Make the decision after tracing the application rather than applying a universal connection formula.

The rollback plan must account for changes made after cutover. Returning the application to the old database is easy only before users write new data. Bidirectional synchronization makes the project more complex and creates conflicts, so teams more often define a short final delta window, a control reconciliation, and a stop criterion before reopening access.

Security and change management become routine on-call work

PostgreSQL is not safe merely because its source is open, just as a commercial database is not safe because its license is paid. The administrator owns timely patches, network isolation, TLS, pg_hba.conf rules, roles, secrets, auditing, and operating system permissions. A mistake in the order of pg_hba.conf lines can permit a broader login method than intended because the server applies the first matching rule.

Do not give the application the database owner role or superuser access. Separate the object owner, schema migration role, runtime application role, monitoring role, and backup operator. If an application password leaks, that separation prevents it from automatically granting permission to change extensions, read every database in the cluster, or rewrite configuration.

Track extensions the same way you track application packages. Record the source, version, update owner, and compatibility with the next PostgreSQL major version. If an extension exists only in an unmanaged build maintained by one specialist, it becomes a hidden constraint on the next upgrade.

Logs should answer a defined question without collecting unnecessary personal data. Logging every SQL statement may help an investigation, but on a busy accounting database it quickly raises I/O, storage costs, and the chance of retaining sensitive values. In most cases it is better to collect slow-query duration, connection errors, lock waits, and structured events with a limited retention period.

Route changes through the same process every time: request, test environment check, recovery point, apply command, success criterion, and rollback. Store configuration as versioned text, but keep secrets separate. An emergency manual edit on the server is acceptable if the team then returns the change to managed configuration and reviews it.

Specialists are less common, so team design matters more

You can hire a PostgreSQL administrator, but the market and skill profile differ from a familiar commercial database. A strong specialist usually understands Linux, file systems, networking, automation, SQL, execution plans, WAL, replication, backup, and recovery. Someone who confidently used the right menu items in one console does not acquire those skills automatically after a short course.

Do not look for one hero who knows everything. An accounting system needs distributed responsibilities: the application owner understands data correctness and business peaks, the database administrator owns the DBMS, the infrastructure team owns nodes and storage, security owns access, and the on-call shift can execute the runbook. One person may hold several roles, but procedures and access must survive that person's vacation.

A good runbook does not repeat the PostgreSQL manual. It starts with the signal the on-call engineer will actually see and provides safe evidence-collection commands, an escalation threshold, contacts, and explicit warnings against dangerous actions. For a full disk, state which files may be moved and which must never be touched. For a lagging replica, state when to preserve it and when to rebuild it. Once a quarter, hand the runbook to someone who did not write it and watch where they have to guess.

During hiring, ask for an incident analysis rather than a list of settings. Have the candidate explain what they would do when pg_wal grows, vacuum stalls, a replica falls behind, or a user reports that a report became slow after an update. A good answer begins with evidence collection, considers intervention risk, and ends with a result check. A memorized shared_buffers value says little about restoring accounting operations at four in the morning.

Vendor or integrator support helps when the contract defines versions, response time, remote access, responsibility boundaries, and participation in recovery. A promise to support PostgreSQL without an escalation process will not close an incident. In Kazakhstan, GSE.kz can assemble the server and integration parts of a project and provide 24/7 technical support through a nationwide service network. That does not remove the need for an internal database owner and regular recovery drills.

Estimate costs over several years: infrastructure, backup storage, test environments, monitoring, training, on-call coverage, an external contract, and upgrade projects. Compare that total with the current platform's full cost, including licenses, support, and hardware constraints. PostgreSQL often gives teams more freedom when choosing servers and scaling, but savings appear only when operations are designed instead of being shifted into unpaid overnight work.

Make the decision after rehearsing operations

Move the accounting system when the application officially supports the selected PostgreSQL version, the team can restore it, and measured workload fits the business requirements. The reasons may include less dependence on licensing, a choice of local infrastructure, supply-chain transparency, or a consistent open stack. None of those reasons compensates for an untested recovery process.

Before cutover, run a complete operational cycle in a test environment: load an anonymized copy, run a normal day, execute a peak operation, fail a backup job, fill a disk to the alert threshold, lose the primary node, recover to a selected moment, and install a minor update. Every exercise needs a measured duration, an observable signal, and a final record. That work uncovers manual steps and hidden dependencies while users remain unaffected.

Write acceptance criteria before the test. Record acceptable downtime, acceptable data loss, timing for major operations, backup retention, minimum free disk space, maximum replication lag, and the person authorized to order rollback. If a measure cannot be observed, the team cannot prove readiness and will end up debating it during cutover.

Split the migration into a technical rehearsal and a separate production window. The rehearsal reveals the actual duration of dumping, transfer, indexing, analysis, and reconciliation. The production plan can then use measured durations, keep enough time for rollback, and prohibit improvised improvements on migration night.

Do not declare the project complete when access reopens. Keep increased monitoring through a period that includes peak accounting operations, check the first backups, and run an early test restore. Retire the old platform only after the checkpoint defined by the data owner and retention rules. The administrator should receive more than a new DBMS icon: the team must be able to explain, measure, and restore the system's behavior.