Questions tagged with PostgreSQL
Content language: English
Sort by most recent
## Issue
We have an Aurora PostgreSQL version 14.5 RDS cluster. We have a secret in SecretsManager with credentials for a user we want to rotate the password for. When rotating the secret, the Lambda gets stuck at the `setSecret` step with the error `Unable to log into database with previous, current, or pending secret`. We have determined that this relates to the `password_encryption` option in the cluster parameter group. If we set it to `md5` (whereas the default is, I believe, `scram-sha-256`) the rotation will work again _after_ we update it manually. We can then rotate it as many times as we want.
### Question
How can we get the secret rotation to work while using the default cluster parameter group for an Aurora PostgreSQL cluster?
### To reproduce
1. Have a secret [formatted as expected](https://docs.aws.amazon.com/secretsmanager/latest/userguide/reference_secret_json_structure.html#reference_secret_json_structure_rds-postgres).
2. Have a Lambda running the [python code provided by AWS](https://github.com/aws-samples/aws-secrets-manager-rotation-lambdas/blob/master/SecretsManagerRDSPostgreSQLRotationSingleUser/lambda_function.py).
3. Have a version 14.5 Aurora PostgreSQL cluster using the `default.aurora-postgresql14` cluster parameter group.
4. Click the "Rotate secret immediately" button in the console
5. In Lambda logs, see the error `setSecret: Unable to log into database with previous, current, or pending secret of secret arn arn:aws:secretsmanager:....`
### How to Recover
1. Create a new cluster parameter group that is a copy of `default.aurora-postgresql14`
2. Change the `password_encryption` to be `md5`
3. Apply this new parameter group to the cluster
3. Cancel the secret rotation: `aws secretsmanager cancel-rotate-secret --secret-id ....`
4. Manually change the password on the user to a new one
5. Update the secret with the new password
6. click the "Rotate secret immediately" button in the console
I followed this link
https://aws.amazon.com/getting-started/hands-on/create-mariadb-db/
to create and host my MariaDB on this platform, but even know I selected the free tier and disabled Multi-AZ deployment by selecting "do not create stand by instance", I was still charged $89, most of which are name:
'$0.342 per RDS db.m6i.large Multi-AZ instance hour'
'$0.2 per IOPS-month of provisioned io1 IOPS for Multi-AZ deployments running MariaDB'
'$0.25 per GB-month of provisioned io1 storage for Multi-AZ deploy'
Furthermore, under the configuration page, I can see that the value for Multi-AZ is no, according to this link
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html
For Multi-AZ DB instance deployment characteristics, I only meet the first two characteristics, the third one, "The value of Multi-AZ is Yes." which does not apply because of the above. And the Multi-AZ DB cluster deployment is unrelated at all to my case.
why was I charged for a service I did not select to begin with?
I have created AWS Aurora Read Replica from RDS PostgreSQL Primary database.
After creating Aurora Read Replica I have enabled Performance Insights for it, once it is enabled I started using Aurora Read Replica connection string in several places of my application.
I was able to see that my application got connected to Aurora Read Replica and all the queries are also getting executed successfully, but while navigating to Performance Insights Tab for Aurora Read Replica I am unable to see anything like Top Users, Top SQL Queries etc.
P.S. I have disabled and re-enabled Performance Insights several times to check weather it resolves the issue or not, but no luck.
On most instances of Postgres I've tried, this query works just fine if `my_table` does not exist:
```
alter table if exists public.my_table add column foo boolean;
NOTICE: relation "my_table" does not exist, skipping
ALTER TABLE
```
I've tried on multiple Postgres versions, including `15.2`.
However, on AWS RDS specifically, their `15.2` version will raise an error if the table does not exist:
```
=> alter table if exists public.my_table add column foo boolean;
ERROR: relation "public.my_table" does not exist
```
This does not appear to be an issue with other versions of RDS Postgres.
Any idea why this might be happening? Is there a parameter that's flipped on in 15.2 by default that causes this to happen?
Hi Team,
We created Postgres Aurora read replica for Postgres RDS and enabled performance insights for Aurora read replica.
In performance insights for this read replica, it is not tracking DB CPU, SQLs, etc.
Can you please help how we can track the metrics correctly through performance insights?
Thanks,
Tushar
Greetings.
A while ago I created an Aurora instance to migrate my database, but it had a lot of performance issues. Responses are 2-3 times slower than normal on the current host (digital ocean). How can I improve this?
[This document](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process) indicates that PostGIS needs to be updated before a major version upgrade. And, [this list](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-15x) shows the supported PostGIS version under PostgreSQL 15.2 is 3.3.2.
However, the latest PostGIS version available on RDS under PostgreSQL 14.7 [appears to be 3.1.7](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-14x). Indeed, after running this:
`SELECT postgis_extensions_upgrade();`
Then, attempting the upgrade, I get the following:
```
------------------------------------------------------------------
Upgrade could not be run on ...
------------------------------------------------------------------
The instance could not be upgraded from 14.7.R1 to 15.2.R1 because of following reasons. Please take appropriate action on databases that have usages incompatible with requested major engine version upgrade and try again.
- Following usages in database 'postgres' need to be corrected before upgrade:
-- The instance could not be upgraded because there are one or more databases with an older version of PostGIS extension or its dependent extensions (address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology, postgis_raster) installed. Please upgrade all installations of PostGIS and drop its dependent extensions and try again.
----------------------- END OF LOG ----------------------
```
Trying the specific target version also fails:
`ALTER EXTENSION postgis UPDATE TO '3.3.2';`
```
ERROR: extension "postgis" has no update path from version "3.1.7" to version "3.3.2"
SQL state: 22023
```
Any suggestions?
I'm newbie to AWS. When I looked at my Bill today, the RDS Backup services were the cause of the rise in the bill.
There isn't a database,nor snapshots or anything operating at the RDS Dashboard, only the standard mysql:80.
How can I terminate the RDS Backup Services?
Hi,
I'm looking to connect the PostgreSQL Database using IAM authentication from Lambda written on Node.js
I see some java script code here but nothing specific for Node.js https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.IAMDBAuth.Connecting.Java.html#UsingWithRDS.IAMDBAuth.Connecting.Java.AuthToken.Connect
I would like to authenticate connection to Aurora PostgreSQL using IAM Authentication.
Any pointer on this is really appreciated.
Postgres database upgrade failed (11.6 -> 13.7) with error:
*pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6052; 1259 720106 INDEX unq_nickname_ci dba
pg_restore: error: could not execute query: ERROR: function public.unaccent(unknown, text) does not exist
LINE 3: SELECT public.unaccent('public.unaccent', $1) -- schema-qua...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.*
After the first failed upgrade, the index and function were deleted, so they don't exist in the catalog, but subsequent upgrades still ends with the same error. Do you have any idea how to proceed?
How do i migrate RDS encrypted cluster to another aws account?
1. Its encrypted DB
2. its a cluster
Based on above,
1. Sharing snapshot wont work
2. copy clustered DB snapshot is not available yet
3. manual export import is not an option
Please suggest ways
Newer versions of Amazon Aurora PostgreSQL-compatible edition are now available and database cluster(s) running Aurora PostgreSQL minor versions 11.13, 11.14, 11.15, 12.8, 12.10, 13.4, 13.5, and 13.6 need to be upgraded by September 15, 2023. These newer minor versions include important updates that will improve the operations of your Aurora PostgreSQL instances and workloads. We strongly encourage you to upgrade to at least a recommended minimum minor version at your earliest convenience.
* For PostgreSQL Minor Version 11.13, 11.14, and 11.15, the recommended minimum minor version is 11.18.
* For PostgreSQL Minor Version 12.8 and 12.10, the recommended minimum minor version is 12.13.
* For PostgreSQL Minor Version 13.4, 13.5 and 13.6, the recommended minimum minor version is 13.9.
Starting on or after 12:00 PM PDT on September 15, 2023, if your Amazon Aurora PostgreSQL cluster has not been upgraded to a newer minor version, we will schedule the relevant recommended minimum minor version to be automatically applied during your next maintenance window. Changes will apply to your cluster during your next maintenance window even if auto minor version upgrade is disabled.
Restoration of Amazon Aurora PostgreSQL 11.13, 11.14, 11.15, 12.8, 12.10, 13.4, 13.5, and 13.6 database snapshots after September 15, 2023 will result in an automatic upgrade of the restored database to a supported version at the time.
How to Determine Which Instances are Running These Minor Versions?
* In the Amazon RDS console, you can see details about a database cluster, including the Aurora PostgreSQL version of instances in the cluster, by choosing Databases from the console's navigation pane.
* To view DB cluster information by using the AWS CLI, use the describe-db-clusters command.
* To view DB cluster information using the Amazon RDS API, use the DescribeDBClusters operation. You can also query a database directly to get the version number by querying the aurora_version() system function i.e., "SELECT * FROM aurora_version();".
How to Apply a New Minor Version
You can apply a new minor version in the AWS Management Console, via the AWS CLI, or via the RDS API. Customers using CloudFormation are advised to apply updates in CloudFormation. We advise you to take a manual snapshot before upgrading. For detailed upgrade procedures, please see the available User Guide [1]. Please be aware that your cluster will experience a short period of downtime when the update is applied.
Visit the Aurora Version Policy [2] and the documentation [3] for more information and detailed release notes about minor versions, including existing supported versions.
If you have any questions or concerns, the AWS Support Team is available on AWS re:Post and via Premium Support [4].
[1] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html
[2] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.VersionPolicy.html
[3] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Updates.20180305.html
[4] https://aws.amazon.com/support
