Enabling Read-Only Mode in PostgreSQL Using Extensions

2024-11-25

When discussing how PostgreSQL DBAs prepare for upgrades (or migrations), you might get a variety of responses, but most of them follow a similar high-level process:

  1. Set up the new cluster and initialize data
  2. Configure logical replication (for Change Data Capture or CDC)
  3. Failover to a replica using DNS, proxy, VIP, or other methods
  4. Failover to the new primary after resolving replication lag

This process is widely accepted, and to mitigate risks associated with data loss, steps 2 and 3 focus on reducing this risk. However, imagine if PostgreSQL could be placed in read-only mode. This feature, already available in MySQL, would allow for a more streamlined upgrade or migration process by eliminating intermediary steps. Let’s explore how a similar functionality can be implemented in PostgreSQL.

Note: If your application(s) using the database can’t tolerate write down-time, this method is not for you. There are other methods to handle such scenarios which is outside of this topic.

Why Read-Only Mode?

A read-only mode allows DBAs to safely prevent data modification during specific operations, such as migrations or maintenance, without stopping the database service. This capability enables skipping directly from step 1 to step 4, bypassing the need for logical replication setup and reducing the risk of data loss.

There are many other uses for this feature. Some of those include forensics analysis lockdown, rollback safety, fine-grained access control (beyond the built-in permission system), and many other use cases. An alternative approach in achieving the read-only mode is to revoke permissions from the users; however, this is a sensitive process and the DBA should make sure all the permissions are rolled back correctly to the previous state.

Note: If your application cannot tolerate downtime with write operations, this approach may not be suitable. There are alternative strategies for such cases, but they fall outside the scope of this discussion.

Leveraging PostgreSQL Extensions

PostgreSQL’s extensibility is one of its strengths. Extensions written in C, the same language PostgreSQL is developed with, allow developers to enhance the database’s capabilities dynamically or during server startup. Instead of altering PostgreSQL’s core codebase, we can achieve the desired read-only functionality using a custom extension.

One key component for implementing this is the ProcessUtility_hook. This hook replaces the standard_ProcessUtility function, enabling developers to intercept and filter queries like Data Definition Language (DDL) and Data Manipulation Language (DML) statements.

Key Function: ProcessUtility Hook

Here’s the function signature for ProcessUtility:

ProcessUtility(PlannedStmt *pstmt,
               const char *queryString,
               bool readOnlyTree,
               ProcessUtilityContext context,
               ParamListInfo params,
               QueryEnvironment *queryEnv,
               DestReceiver *dest,
               QueryCompletion *qc)

Using this hook, you can inspect queries and reject any DDL or DML operations by returning an error to the client. This is the foundation of our read-only mode.

Introducing a GUC Variable

To enable or disable the read-only feature at runtime, we’ll define a custom configuration parameter, known as a GUC (Grand Unified Configuration) variable. PostgreSQL allows extensions to define custom GUC variables using the following function:

/*
 * Functions for extensions to call to define their custom GUC variables.
 */
void
DefineCustomBoolVariable(const char *name,
                         const char *short_desc,
                         const char *long_desc,
                         bool *valueAddr,
                         bool bootValue,
                         GucContext context,
                         int flags,
                         GucBoolCheckHook check_hook,
                         GucBoolAssignHook assign_hook,
                         GucShowHook show_hook)

By introducing a GUC variable (e.g., block_writes.enabled), we can dynamically enable or disable write operations without restarting the server.

Steps to Implement and Use the Extension

1. Develop the Extension

2. Compile and Install the Extension

Compile the extension like any other PostgreSQL extension, and update the configuration file to preload it:

#Add this line to postgresql.conf
shared_preload_libraries = 'pg_read_only' #you can choose a different name

3. Restart PostgreSQL

After adding the extension to postgresql.conf, restart the PostgreSQL service:

#Restart the service
sudo systemctl restart postgresql

4. Enable the Feature (Block Write Operations)

Enable read-only mode by altering the GUC variable:

SET pg_read_only.enabled = true;

Once enabled, all DDL and DML queries will be rejected. DML and DDL operations are disabled on this server.

5. Disable The Feature (Allow Write Operations)

SET pg_read_only.enabled = false;

Summary

Although PostgreSQL doesn’t currently include a built-in read-only mode like MySQL, its extension framework provides a powerful way to customize behavior. By leveraging ProcessUtility_hook and a custom GUC variable, DBAs can implement an extension to add the read-only mode feature to Postgres. This capability can have many use cases and can simplify upgrade processes, but also enhances data protection during critical operations and unlocks many other use cases beyond those described here.

If you’re not an expert in database management, I suggest using a managed database service. Akamai provides this service for both MySQL and Postgres, delivering exceptional performance at a highly competitive price.

Join the conversation on LinkedIn