- Newest
- Most votes
- Most comments
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.
Relevant content
- Accepted Answerasked 2 years ago
- asked 2 years ago
- AWS OFFICIALUpdated 3 months ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 9 months ago
Thank you for your kind suggestion, i'll try it out.