Building an atomic PostgreSQL database rollout with Bash and psql

Deploying a database can seem like a daunting task that turns into a trial-and-error ritual: run SQL script(s) to create database schema objects — tables, foreign keys, indexes, functions, and stored procedures. Then hope that script_01.sql, script_02.sql, and so on are in the appropriate sequence to create prerequisite objects before the objects that depend on them.

What inevitably happens is that a prerequisite object has not yet been created, an environment configuration has not been set up, or database authentication fails, causing the whole pipeline to get stuck and leaving the practitioner to diagnose the failure point and decide how to handle it. Should we raise an exception? Write to a log? Roll the entire operation back, or allow the script(s) to run to a checkpoint?

In this article we present a case study from a real production rollout and the deployment process developed to make future PostgreSQL deployments more reliable, repeatable, and controlled.

Requirements

Before the deployment scripts can run, the deployment client must be able to reach PostgreSQL and authenticate against the databases used during the rollout. Because the process initially connects to the postgres maintenance database in order to drop and recreate liberanalytics, pg_hba.conf must permit the deployment role to connect to both postgres and liberanalytics. Network access, PostgreSQL host-based authentication, and role privileges therefore form the bootstrap requirements for the deployment process.

TYPE        DATABASE        USER            ADDRESS                 METHOD
hostssl     postgres        liberanalytics  <client-ip>/32          scram-sha-256
hostssl     liberanalytics  liberanalytics  <client-ip>/32          scram-sha-256

The deployment client must already be configured to authenticate to PostgreSQL. We will be using the psql client which is the terminal-based command-line interface (CLI) for PostgreSQL.

Bash Orchestration Script

We will use a Bash script to help orchestrate the database rollout. The script shown below manages several important parts of the deployment process: it defines the psql connection parameters as variables, executes the SQL driver script within an if/then control structure, and reports the result of that execution. On success, the script returns a clear completion message. On failure, it captures and displays the exact psql exit code before terminating with that same code.

The failure-handling component is particularly useful because it provides additional context when diagnosing a deployment failure and allows the shell-level orchestration to clearly distinguish between a successful and unsuccessful database rollout.

SQL Driver Script

The SQL driver script is where the real database work is defined. It is designed with idempotent behavior where practical and manages several critical deployment components:

• Sets psql error-message verbosity to verbose, providing additional diagnostic details for errors and notices.
• Connects to the postgres database so the existing liberanalytics database can be dropped and terminates any active sessions on the liberanalytics database.
• Drops database liberanalytics if it exists, runs the database creation .sql script with the psql \ir command, connects to database liberanalytics, then prints the current connection context.
• Starts a transaction so the remaining database rollout can succeed or fail as a single unit, providing atomicity.
• Creates the schemas, tables, and functions using the psql \ir command to include each .sql script by relative path.
• Performs a smoke test in an anonymous PL/pgSQL block to verify that the final table and function DDL included by the \ir commands completed successfully.
• Populates the database with initial reference and operational data.
• Performs a final smoke test to verify that the last seed data was inserted successfully.
• Commits the transaction, permanently applying all changes; if an earlier statement caused the transaction to fail, the changes are rolled back instead.

Understanding the SQL Driver Script

Once the transaction has been opened with the BEGIN; statement, the individual .sql scripts are executed through the psql include-relative \ir meta-command, beginning with schema creation, followed by tables, and ending with functions. During this phase, the execution order of the scripts is critical. For example, if a table contains a foreign-key reference, the referenced table must be created before the table containing the foreign-key definition. If the sequence is incorrect, PostgreSQL raises an error and the transaction enters an aborted state, causing the statements executed within the BEGIN; block to be rolled back.

In the DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM liberanalytics.post_img) … block, the assertion is intentionally lightweight. It verifies that the final seeded table contains data and provides a simple rollout smoke test; it does not establish that every expected row or value was loaded correctly. More rigorous deployments could add row-count assertions, key-record checks, referential-integrity checks, or source-to-target reconciliation.

Failed Versus Successful Deployment Example

During a test rollout, a missing comma in a seed-data script caused PostgreSQL to raise a syntax error. Because ON_ERROR_STOP was enabled, psql terminated execution and returned a non-zero exit code. The Bash orchestration layer captured that result and surfaced a concise deployment-level failure message shown below as "Deployment failed. psql exit code: 3", while preserving the underlying PostgreSQL diagnostic.

On a successful deployment, the Bash terminal displays PostgreSQL command-status messages confirming that each seed-data insert completed. For example, INSERT 0 7 indicates that seven rows were inserted; the 0 represents the legacy OID field, which is no longer populated for ordinary user tables. More importantly, the final line shown below — "Deployment completed successfully." — is generated by the if / then / else block in init.sh when psql returns a successful exit code, confirming that the database rollout completed as intended.

Deployment Lessons and Final Considerations

A stable production database deployment should be managed as an all-or-nothing atomic operation where possible. Deployments should also be idempotent where practical so repeated executions are easier to manage, particularly after a failure. Using a Bash shell script can simplify connection handling, deployment flow, and SQL script sequencing while also providing diagnostics beyond the database engine and client messages.