How do I prevent DDL operations in my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instances during blue/green deployments?

Short description

For RDS for PostgreSQL and Aurora PostgreSQL-Compatible, DDL operations during blue/green deployments can break logical replication between environments and require a complete redeployment.

This issue affects Amazon RDS for PostgreSQL only during major version upgrades when logical replication is used instead of physical replication. For more information, see Restrictions of Logical Replication on the PostgreSQL website.

For Aurora PostgreSQL, DDL changes affect major and minor version upgrades, because DDL operations change the replication state to replication degraded and cause the switchover to fail.

When a DDL operation runs on the Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible instance during a blue/green deployment with logical replication you get the following error message:

"Data definition language (DDL) changes aren't supported... Your green databases now have a status of REPLICATION_DEGRADED. Delete and recreate your blue/green deployment and avoid future DDL changes..."

When you try a switchover, Amazon RDS blocks the switchover and you get the following error message:

"Switchover from DB cluster to... was cancelled because there are DDL or Large Object changes on... that can't be replicated. Delete the blue/green deployment along with the target cluster, then re-create it."

Resolution

To prevent this issue, configure event triggers and trigger functions on the blue database before you start the blue/green deployment. The event triggers and trigger functions will notify you of any DDL operations.

You can't block or restrict the following DDL operations:

• Commands that target shared objects, such as databases, roles, or tablespaces
• Commands that target event triggers
• Large object modifications in pg_largeobject

Configure PostgreSQL event triggers and trigger functions

Create the event triggers and trigger functions before you configure a blue/green deployment. You must create each event trigger and trigger function separately in every database that requires DDL protection, because event triggers work at the database level. The green environment inherits event triggers and the trigger functions by default. To perform DDL operations in the green environment, first drop the triggers and functions that you created in the blue environment.

To configure the event triggers and trigger functions on the source instance, complete the following steps:

1. Run the following command to log in to the source database as a superuser:

   psql -U admin -h endpoint -d your_database

   Note: Replace admin with your user_name. Replace endpoint with the endpoint of your source database. Replace your_database with the name of your source database.

2. Run the following command to create the DDL blocking function:

   CREATE OR REPLACE FUNCTION schema_name.block_ddl_bg() RETURNS event_trigger   
   SECURITY DEFINER  
   AS $$  
   DECLARE  
       -- Define allowed users and roles here  
       allowed_users TEXT[] := ARRAY['rdsadmin', 'rdsrepladmin'];  
       allowed_roles TEXT[] := ARRAY['rdsrepladmin'];  
   BEGIN  
       -- Check if either the session user or current role is allowed  
       IF NOT (session_user = ANY(allowed_users) OR current_role = ANY(allowed_roles)) THEN  
           RAISE EXCEPTION 'DDL operations are blocked to prevent Blue Green Deployment replica degradation. User: %, Role: %, Status: BLOCKED',   
               session_user, current_role;  
       END IF;  
   END;  
   $$ LANGUAGE plpgsql;

   Note: In the preceding command, replace schema_name with your schema.

3. Run the following command to create the event trigger:

   CREATE EVENT TRIGGER block_ddl_trigger ON ddl_command_start  
   
   EXECUTE FUNCTION schema_name.block_ddl_bg(); 

   Note: In the preceding command, replace schema_name with your schema.

4. Run the following command to check if the function exists:

   SELECT proname, pronamespace::regnamespace   
   FROM pg_proc   
   WHERE proname = 'block_ddl_bg'; 

5. Run the following command to check if the event trigger exists:

   SELECT evtname, evtevent, evtowner::regrole   
   FROM pg_event_trigger   
   WHERE evtname = 'block_ddl_trigger';

6. Run the following command to create a test table and test the triggers:

   CREATE TABLE test_table (id int);  

   Note: In the expected output, you should receive a blocking message.

Remove the event trigger and trigger function

After you complete the blue/green deployment switchover or you decommission the blue/green deployment, run the following commands to drop the event trigger and trigger function:

DROP EVENT TRIGGER block_ddl_trigger;
DROP FUNCTION schema_name.block_ddl_bg();

Note: In the preceding command, replace schema_name with your schema.

Related information

Event triggers on the PostgreSQL website

Event trigger firing matrix on the PostgreSQL website

Aurora PostgreSQL limitations for blue/green deployments

RDS for PostgreSQL limitations for blue/green deployments with logical replication