Blue Green Deployment for Postgres major version upgrade failing due to internal aws permissioning issue.

Question

I am doing a major version upgrade from postgres (with postgis extension) from version 15.7 to version 16.3 using aws blue/green deployment. I have followed the AWS guide and have encountered an error from the green deployment which is not documented.

The green deployment is created initially with the old db version and then it is updated to the newer version. It fails during this step:
This is from pg_upgrade_internal.log:

There were problems executing ""/rdsdbbin/postgres/bin/psql" --echo-queries --set ON_ERROR_STOP=on --no-psqlrc --dbname=template1 --host 
    /rdsdbdata/log/upgrade --port 8193 --username rdsadmin -f 
    "/rdsdbdata/upgrade/db/pg_upgrade_output.d/20240926T180613.989/dump/pg_upgrade_dump_globals.sql" >> 
    "/rdsdbdata/upgrade/db/pg_upgrade_output.d/20240926T180613.989/log/pg_upgrade_utility.log" 2>&1"
    Consult the last few lines of "/rdsdbdata/upgrade/db/pg_upgrade_output.d/20240926T180613.989/log/pg_upgrade_utility.log" for
    the probable cause of the failure.
    "/rdsdbbin/postgres/bin/pg_ctl" -w -D "/rdsdbdata/upgrade/db" -o "--config_file=/rdsdbdata/config_new/postgresql.conf" -m fast stop >> 
    "/rdsdbdata/upgrade/db/pg_upgrade_output.d/20240926T180613.989/log/pg_upgrade_server.log" 2>&1

This is from pg_upgrade_server.log:

2024-09-26 18:06:34.371 GMT [899] ERROR: permission denied for parameter session_replication_role
    2024-09-26 18:06:34.371 GMT [899] STATEMENT: GRANT ALL ON PARAMETER "session_replication_role" TO "rds_superuser";

From what I understand, rdsadmin is an internal aws user and should have the required permission. Also, any command to grant permissions to this user is failing since it's a protected role. I am not sure how to resolve this situation. I would like some assistance here, please have look.

Roy · Answer

The error you're encountering during the green deployment upgrade from Postgres 15.7 to 16.3 is related to a permissions issue with the session_replication_role parameter.

The error message indicates that the rdsadmin user, which is an internal AWS user, doesn't have the necessary permissions to grant permissions on the session_replication_role parameter.

This is likely a known issue with the AWS Managed Postgres service, and there's a specific procedure to follow to address it.

Here's what you can try:

Stop the Postgres Green Deployment: Before proceeding, stop the Green Deployment to ensure that the database is in a consistent state.

Create a New Temporary Superuser: Create a new temporary superuser account that can be used to perform the necessary actions. You can do this using the AWS CLI or the AWS Management Console.

CREATE USER my_temp_superuser WITH SUPERUSER PASSWORD 'your_strong_password';

Perform the Upgrade with the Temporary Superuser: Instead of using the rdsadmin user, use the newly created my_temp_superuser account to perform the Postgres upgrade. This should bypass the permission issues with the session_replication_role parameter.

pg_upgrade --old-datadir /rdsdbdata/db/data --new-datadir /rdsdbdata/upgrade/db --old-bindir /rdsdbbin/postgres/bin --new-bindir /rdsdbbin/postgres/bin --username my_temp_superuser

Drop the Temporary Superuser: After the upgrade is successful, drop the temporary superuser account.

DROP USER my_temp_superuser;

Verify the Upgraded Database: Thoroughly test the upgraded database to ensure that all the functionality and data integrity are intact.

Promote the Green Deployment: Once you've confirmed that the upgraded database is working as expected, you can promote the Green Deployment to become the new Primary Deployment.

By using the temporary superuser account, you should be able to bypass the permission issues with the session_replication_role parameter and successfully complete the Postgres upgrade.

If you continue to encounter issues, you may need to escalate this to AWS Support, as they might have additional workarounds or recommendations specific to the AWS Managed Postgres service.

Blue Green Deployment for Postgres major version upgrade failing due to internal aws permissioning issue.

관련 콘텐츠