Questions tagged with Aurora PostgreSQL

Hello, I have created a PostgreSQL database, including the PostGIS extension, however, I'm unable to create GiST index on my rast column. I'm trying to execute the following line: ``` CREATE INDEX IF NOT EXISTS rast_index ON public.raster_data USING gist(rast); ``` I get the following response: ``` ERROR: data type raster has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. SQL state: 42704 ```

Hi All, We've identified that our RDS Aurora (Postgresql-13.6) cluster is a key bottleneck in our application's performance. Until we can rearchitect the code, we were hoping to try and mitigate some of this through infrastructure changes. We're going to add additional read replicas to help reduce the amount of calls and hopefully allow more parallel processing to take place. I'm having trouble pinpointing if there's a way to use a single endpoint, as we do now with our single write instance, and through some AWS feature have the queries be routed properly to the read and write instances based on the type of query. As opposed to making code changes where we have two sessions, one for read and one for write. I assume there must be a way but I can't seem find an exact answer. If someone can post relevant documentation for me to review, that would be perfect, thank you!

We are evaluating Babelfish migration from SQL Server but would like to know does IAM authentication works with Babelfish? Any guidance would be greatly appreciated.

I wanted to migrate Database from On-Prem which is My SQL Server to AWS Managed RDS using DMS which can support database partitioning. I migrated My SQL server to AWS Aurora(MY SQL) using DMS but this is not supporting database partitioning. So is there any new version of AWS MY SQL supports data partitioning? or how can we migrate using DMS from on prem MY SQL Server to AWS Managed RDS (Any suggestion of RDS) which support Database partitioning along with performance.

In server setup, the connections to RDS DB (Aurora) instances has pool where the connections are bit long lived and cached. This will give us faster connectivity. I would like to understand the guidance on this scenario for serverless/lambda. With lambda there is no sessions and no connections are cached. I think it will create connections every time, it might impact the performance. Are there any workaround or solves/guidance for Lambda ? Is it okay to open the connection every time with the DB instances ? Any leads on this will be super helpful ?

We are trying to migrate data from AWS Aurora Postgres (v13.4) to another AWS Aurora Postgres database (Serverless v2) but the data migration fails for tables wherein we add a column filter. Our table names and column names are in the upper camel case. I checked the logs it shows the following error: ``` 2022-04-27T09:31:12 [TRANSFORMATION ]E: Failed to init filter for column CompanyId [20014] (manipulator.c:605) 2022-04-27T09:31:12 [TRANSFORMATION ]W: The metadata transformations defined for table 'public.Tenants' were not performed as at least one of the transformation expressions contains an error (manipulator.c:147) ``` Here is the mapping rule: ``` { "rules": [ { "rule-type": "selection", "rule-id": "046273916", "rule-name": "046273916", "object-locator": { "schema-name": "public", "table-name": "Tenants" }, "rule-action": "include", "filters": [ { "filter-type": "source", "column-name": "CompanyId", "filter-conditions": [ { "filter-operator": "eq", "value": "xxxxxx" } ] } ] }, { "rule-type": "selection", "rule-id": "042087387", "rule-name": "042087387", "object-locator": { "schema-name": "public", "table-name": "Plans" }, "rule-action": "include", "filters": [] } ] } ```

Newer versions of Amazon Aurora PostgreSQL-compatible edition are now available and database cluster(s) running Aurora PostgreSQL minor versions 10.13, 10.14, 10.16, 11.8, 11.11, 12.4, and 12.6 need to be upgraded by July 15, 2022. 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 Versions 10.13, 10.14 and 10.16, the recommended minimum minor version is 10.17. * For PostgreSQL Minor Versions 11.8 and 11.11, the recommended minimum minor version is 11.12. * For PostgreSQL Minor Versions 12.4 and 12.6, the recommended minimum minor version is 12.7. Starting on or after 12:00 PM PDT on July 15, 2022, 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 10.13, 10.14, 10.16, 11.8, 11.11, 12.4, and 12.6 database snapshots after July 15, 2022 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 ****

I see on my aurora postgres rds up to 70 million write IOPS (count/second) therefore triggering cloudwatch and at the same time, the Billed Volume Write IOPS is 0. What comprises non-billable I/O?

Hi, I am have an info in the console that upgrade is available. When I am clicking on - apply at next maintenance window. It does not work. After maintenance window time is over I still see that upgrade is available? Is it a global issue?

Hello, We have received a notification for upgrading our 10.14 aurora postgres serverless instances but on attempting to upgrade I get a failed `InvalidParameterCombination` error with the cli. Full error with cmd ``` aws rds modify-db-cluster \ --db-cluster-identifier test-cluster \ --engine-version 11.7 \ --allow-major-version-upgrade \ --apply-immediately An error occurred (InvalidParameterCombination) when calling the ModifyDBCluster operation: Cannot find upgrade target from 10.serverless_14 with requested version 11.7 for serverless engine mode. ``` Now I read in our notification that `Aurora PostgreSQL 11 for Amazon Aurora Serverless v1 will be available in Q1 2022. Detailed instructions on how to upgrade will be published when Aurora PostgreSQL 11 is available for Amazon Aurora Serverless v1 PostgreSQL-compatible edition. ` But I cannot find this documention for the `detailed instructions` . `10.14` to `11.7` seems to be a valid upgrade path [going on the docs](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion). So I am not sure what is going on. Would anybody be able to clarify?

According to the AWS doc(https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_Limits.html) Aurora's total instance storage per region is 100,000 GB(100TB). And It is said that the aurora cluster is automatically scaled up to 128 TiB. If I create 4 aurora clusters in RDS and one cluster is scaled up to 128 TiB, does an error occur when scaling up the other 3 clusters? Or is it possible to scale up all 4 clusters to 128 TiB each?(Are total instance storage per region and Aurora Volume separate?)

I want to change the instance type of the current Reader (PostgreSQL), and I am having 2 Reader instances. I will replace the instance one by one, wait for it to succeed, then replace the next instance. Will this cause downtime for my Aurora Cluster, and if yes, how long will it be?

Hello. I was having CPU usage issues where the CPU would reach over 50%. So I upgraded from a db.t4g.medium to a db.r6g.xlarge. Now, all is good and running smooth. But one thing intrigues me: Why after the upgrade, represented in the image by the red line, did the number of committed transactions decrease?  https://i.ibb.co/jJBwWZg/Captura-de-Tela-2022-04-12-a-s-17-28-32.png

From the documentation it is clear that Aurora PostgreSQL is right fit for highly concurrent workload. When to use RDS PostgreSQL compared to Aurora PostgreSQL? For example: RDS PostgreSQL offers more major and minor versions compared to Aurora PostgreSQL, similarly are there any limitations that we need to aware of before deciding on RDS PostgreSQL or Aurora PostgreSQL?

I export data from an Aurora Postgres instance to S3 via the `aws_s3.query_export_to_s3` function. The destination bucket does not have default encryption enabled. When I try to download one of the files I get the following error: > The ciphertext refers to a customer mast3r key that does not exist, does not exist in this region, or you are not allowed to access. Note: I had to change the word mast3r because this forum doesn't allow me to post it as it is a "non-inclusive" word... The reasons seems to be that the files got encrypted with the AWS managed RDS key which has the following policy: ``` { "Version": "2012-10-17", "Id": "auto-rds-2", "Statement": [ { "Sid": "Allow access through RDS for all principals in the account that are authorized to use RDS", "Effect": "Allow", "Principal": { "AWS": "*" }, "Action": [ "kms:Encrypt", "kms:Decrypt", "kms:ReEncrypt*", "kms:GenerateDataKey*", "kms:CreateGrant", "kms:ListGrants", "kms:DescribeKey" ], "Resource": "*", "Condition": { "StringEquals": { "kms:CallerAccount": "123456789", "kms:ViaService": "rds.eu-central-1.amazonaws.com" } } }, { "Sid": "Allow direct access to key metadata to the account", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::123456789:root" }, "Action": [ "kms:Describe*", "kms:Get*", "kms:List*", "kms:RevokeGrant" ], "Resource": "*" } ] } ``` I assume that the access doesn't work because of the `ViaService` condition when trying to decrypt the file via S3. I tried to access to files with the root user instead of an IAM user and it works. Is there any way to get access with an IAM user? As far as I know, you cannot modify the policy of an AWS managed key. I also don't understand why the root user can decrypt the file as the policy doesn't explicitly grant decrypt permissions to it other than the permissions when called from RDS.

I'm trying ensure migrating from Postgres to Aurora works on my account using methods 1. snapshots and 2. read replicas here: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Migrating.html I have tried various versions of postgres from 10.x, 11.x, 13.4 but 1. "Migrate Snapshot" option is greyed out under snapshot > actions 2. "create aurora replica" not showing under actions I don't believe there is an issue with my account because I can "Migrate Snapshot" of other public postgres snapshots fine. it's just my own instances I cannot migrate from.

Is there any way to compress s3 export data using aws_s3.query_export_to_s3 on Aurora PostgreSQL? Redshift has various compression options in its UNLOAD command but I cannot see anything similar for Aurora PostgreSQL.

Hello, I have some question about configuration App Runner Service. I have two DB instances (eg. Oracle and PostgreSQL) and I wonder if I can configure App Runner so that my Web API connects to the specified base by entering environment variables ([Key][Value]) during configuration? Without interfering with the code itself. I would like to apply one universal Docker image to several App Runner services. EG. App Runner (Key: Oracle, Value: Oracle ConnectionStrings) -> My universal Docker Image -> Oracle DB App Runner (Key: PostgreSQL, Value: PostgreSQL ConnectionStrings) -> My universal Docker Image -> PostgreSQL DB

We recently upgraded from PostgreSQL13.5 to 13.6 and since then have seen a pattern of spiky write IOPS activity. The database receives batch updates once daily and has very little query traffic. We are experiencing these spikes even when there is no user activity on the database. The data updates that occur are almost entirely additive -- that is, there are little to no updates or deletions. The cluster has a single instance with no replicas. Given the timing of this behavior, it seems likely it is connected to the upgrade as nothing else has changed. An image of the CloudWatch Write IOPS stats can be found here: https://postimg.cc/fVWjbTdn

Have an aurora postgresql cluster that has been down for multiple hours. Tried rebooting instances in the cluster, adding new read replicas and upgrading instances. Nothing seems to work. One of the existing instances I see the follow log seeming to indicate that the process is flapping. I've done some searching, but nothing seems to document what `errno = 32` actually means. ``` 2022-03-31 20:18:43 UTC::@:[18331]:LOG: setting shmemname /mnt/hugetlbfs/aurora-postgres-buffer-cache 2022-03-31 20:18:44 UTC::@:[18331]:LOG: Storage initialization failed. result = 1988, errno = 32 2022-03-31 20:18:44 UTC::@:[18329]:FATAL: Can't handle storage runtime process crash 2022-03-31 20:18:44 UTC::@:[18329]:LOG: database system is shut down ``` Any idea what's wrong with my cluster and/or how I can fix it?

Looking for a way to get the database instance name for the current session in postgresql, just like the variable "aurora_server_id" did in mysql. When running commands, there needs to be a way to verify that we are connected to the host we think we are connected to. "inet_server_addr" gives IP but we want to know the instance name.

Importing tens of thousands of rows into a PostGres table from S3 is awesome and incredibly fast, but is there any way to perform Upserts using the same process without creating staging tables? Ideally it would be nice to have the equivalent of the conflict keyword, and perform an update if the PK is already present, but is there anything simpler than a staging+merge operation? At the moment it looks like it needs: - Create temporary table - aws_s3.table_import_from_s3 into temporary table - MERGE operation into main table - Clear temporary table and it needs this for all 20 tables we're merging... Open to any suggestions

According to https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Migrating.html#AuroraPostgreSQL.Migrating.RDSPostgreSQL.Replica.Create I should be able to create a Aurora Postgres Read Replica from RDS Postgres 11.12 instance. But the option does not exist in the dropdown menu when the DB primary is selected (or read replicas). The region is us-east-1, N. Virginia. Thanks for the help!

I'm trying to associate a role with an Aurora DB instance, and I'm getting the error `IAM role ARN value is invalid or does not include the required permissions for: AWS_ROLE_INTEGRATION` I can't find an reference to AWS_ROLE_INTEGRATION in the documentation, and the single Google result referring to this "AWS_ROLE_INTEGRATION" leads to a user asking the same question but never getting a response.

Hi We are creating a DMS task that we were expecting to use a view in a Aurora Postgres service as data source, for a single use table migration. But it seems that views arent actually supported in this specific source case .... any reason for this? Theres a column which serves as primary key for that view Below is the message when creating the DMS task Error in mapping rules. Rule with ruleId = 042389161 failed validation. view selection is not available for aurora-postgresql source type In the meantime, im looking towards AWS Glue to do the procedure, and in the "worst" case, create a procedure to do the task / load process Any tips would be great for the ones that may fall for the same case best regards

Hi, I would like to request that %a is added to the log_line_prefix default in the amazon aurora parameter group setting. The current default is: "%t:%r:%u@%d:[%p]:t". We need this is order to help identify easily where our connection is orginating from. This is to enable better log filtering and metrics via CloudWatch logs for a given application. Regards Mat

I am trying to connect to my Aurora PostgresQL database using hibernate ORM. I was able to connect Using EF .net I am able to retrive the token like ``` var pw= Amazon.RDS.Util.RDSAuthTokenGenerator.GenerateAuthToken(host, port, username); ``` Then in my nhibernate config and connectionstring i replace the password with pw in this call: ``` var connectionString = ShardManagerBuilder.RebuildConnectionString(nhibernateConfiguration); Console.WriteLine($"nhibernateConfiguration:{connectionString }"); var fluentSessionFactory = Fluently.Configure(nhibernateConfiguration); ... ... // build factory return fluentSessionFactory.BuildSessionFactory(); ``` The question: Is it even possible to use IAM authentication in the context off Hibernate? Thanks ps: the code work when i use username and password (not IAM AUTH)

We have an EC2 instance with SQL Server installed and AWS DMS Replication set up to sync changes from Database A - Table A to an Aurora Postgres Database A Table A. This has been working fine for the last year for inserts/updates/deletes. We recently added a new field (LastUpdateDate) into Table A in SQL Server of type datetimeoffset, this was automatically added to the corresponding Table A in Aurora Postgres of type varchar(34). This works fine for insert and updates. However if the column LastUpdateDate is NULL and we delete this row on the source (SQL Server), an error is throw in DMS where it is attempting to do an update of the corresponding row in Aurora Postgres but trying to set the LastUpdateDate to an invalid date (10000-01-01 04:17:59.0000000 +04:18), this is 35 characters long and so fails to insert into the LastUpdateField which is on 34 characters. My question is we are only doing a delete, where is this update coming from? Where is this strange date coming from? It is not occurring on the SQL Server side as we have no triggers on the table, have cdc enabled and can see that only a Delete operation was done. The delete operation does succeed but we still have these update errors showing up in our 'public.awsdms_apply_exceptions table.' Any help would be appreciated. Thanks

My Postgres RDS Database is got restarted. It says heavy consumption of memory. But around 20GB freeable memory available at the time of restart. As I checked AAS Graph there is more locking happening. Below are the logs. ``` PL/pgSQL function evaluate_program_payout_version(character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying) line 141 at SQL statement 2022-03-08 11:28:42 UTC:10.10.3.18(33366):pmli_bre_uat@dmsclientdb:[10065]:WARNING: terminating connection because of crash of another server process 2022-03-08 11:28:42 UTC:10.10.3.18(33366):pmli_bre_uat@dmsclientdb:[10065]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-08 11:28:42 UTC:10.10.3.18(33366):pmli_bre_uat@dmsclientdb:[10065]:HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-08 11:28:42 UTC:10.10.3.18(33366):pmli_bre_uat@dmsclientdb:[10065]:CONTEXT: SQL statement "delete FROM EvalSlabResult WHERE contextid = par_context_id and program_code = par_program_code and start_date = var_start_date" PL/pgSQL function evaluate_slab_version(character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying) line 139 at SQL statement 2022-03-08 11:28:42 UTC:10.10.3.18(33300):pmli_bre_uat@dmsclientdb:[9065]:WARNING: terminating connection because of crash of another server process 2022-03-08 11:28:42 UTC:10.10.3.18(33300):pmli_bre_uat@dmsclientdb:[9065]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-08 11:28:42 UTC:10.10.3.18(33300):pmli_bre_uat@dmsclientdb:[9065]:HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-08 11:28:42 UTC:10.10.3.18(33286):pmli_bre_uat@dmsclientdb:[8793]:WARNING: terminating connection because of crash of another server process 2022-03-08 11:28:42 UTC:10.10.3.18(33286):pmli_bre_uat@dmsclientdb:[8793]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-08 11:28:42 UTC:10.10.3.18(33286):pmli_bre_uat@dmsclientdb:[8793]:HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-08 11:28:42 UTC::@:[13295]:FATAL: Can't handle storage runtime process crash 2022-03-08 11:28:42 UTC::@:[13295]:LOG: database system is shut down 2022-03-08 11:28:42 UTC:10.10.3.18(33448):pmli_bre_uat@dmsclientdb:[11092]:WARNING: terminating connection because of crash of another server process 2022-03-08 11:28:42 UTC:10.10.3.18(33448):pmli_bre_uat@dmsclientdb:[11092]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-08 11:28:42 UTC:10.10.3.18(33448):pmli_bre_uat@dmsclientdb:[11092]:HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-08 11:28:42 UTC:10.10.3.18(33448):pmli_bre_uat@dmsclientdb:[11092]:CONTEXT: SQL statement "delete FROM DVResult WHERE contextid = par_context_id and program_code = par_program_code and start_date = var_start_date" PL/pgSQL function evaluate_dv_version(character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying) line 143 at SQL statement 2022-03-08 11:28:42 UTC:10.10.3.18(33378):pmli_bre_uat@dmsclientdb:[10069]:WARNING: terminating connection because of crash of another server process 2022-03-08 11:28:42 UTC:10.10.3.18(33378):pmli_bre_uat@dmsclientdb:[10069]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-08 11:28:42 UTC:10.10.3.18(33378):pmli_bre_uat@dmsclientdb:[10069]:HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-08 11:28:42 UTC:10.10.3.18(33378):pmli_bre_uat@dmsclientdb:[10069]:CONTEXT: SQL statement "SELECT ess.entityid, ess.version_id FROM evalslabsummary ess WHERE ess.contextid = par_contextid AND ess.program_code = par_program_code AND ess.start_date = var_start_date" PL/pgSQL function evaluate_slab(character varying,character varying,character varying,character varying,character varying) line 71 at SQL statement 2022-03-08 11:28:42 UTC:10.10.3.18(33408):pmli_bre_uat@dmsclientdb:[10455]:WARNING: terminating connection because of crash of another server process 2022-03-08 11:28:42 UTC:10.10.3.18(33408):pmli_bre_uat@dmsclientdb:[10455]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-08 11:28:42 UTC:10.10.3.18(33408):pmli_bre_uat@dmsclientdb:[10455]:HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-08 11:28:42 UTC:10.10.3.18(33408):pmli_bre_uat@dmsclientdb:[10455]:CONTEXT: SQL statement "delete FROM DVResult WHERE contextid = par_context_id and program_code = par_program_code and start_date = var_start_date" PL/pgSQL function evaluate_dv_version(character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying) line 143 at SQL statement 2022-03-08 11:28:42 UTC:10.10.3.18(33328):pmli_bre_uat@dmsclientdb:[9247]:WARNING: terminating connection because of crash of another server process 2022-03-08 11:28:42 UTC:10.10.3.18(33328):pmli_bre_uat@dmsclientdb:[9247]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-08 11:28:42 UTC:10.10.3.18(33328):pmli_bre_uat@dmsclientdb:[9247]:HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-08 11:28:42 UTC:10.10.3.18(33328):pmli_bre_uat@dmsclientdb:[9247]:CONTEXT: SQL statement "delete FROM ProgramPayoutResult WHERE contextid = par_context_id and program_code = par_program_code and start_date = var_start_date" PL/pgSQL function evaluate_program_payout_version(character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying) line 141 at SQL statement 2022-03-08 11:28:42 UTC:10.10.3.18(33474):pmli_bre_uat@dmsclientdb:[12157]:WARNING: terminating connection because of crash of another server process 2022-03-08 11:28:42 UTC:10.10.3.18(33474):pmli_bre_uat@dmsclientdb:[12157]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-08 11:28:42 UTC:10.10.3.18(33474):pmli_bre_uat@dmsclientdb:[12157]:HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-08 11:28:42 UTC:10.10.3.18(33474):pmli_bre_uat@dmsclientdb:[12157]:CONTEXT: SQL statement "delete FROM ProgramPayoutResult WHERE contextid = par_context_id and program_code = par_program_code and start_date = var_start_date" PL/pgSQL function evaluate_program_payout_version(character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying) line 141 at SQL statement 2022-03-08 11:28:42 UTC:10.10.3.18(33236):pmli_bre_uat@dmsclientdb:[7485]:WARNING: terminating connection because of crash of another server process 2022-03-08 11:28:42 UTC:10.10.3.18(33236):pmli_bre_uat@dmsclientdb:[7485]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-08 11:28:42 UTC:10.10.3.18(33236):pmli_bre_uat@dmsclientdb:[7485]:HINT: In a moment you should be able to reconnect to the database and repeat you ```

I'm attempting to create a Lambda where I can make calls to various stored procedures and functions in my Aurora PostgreSQL dB instance. I'm following the guide on this page: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.IAMDBAuth.Connecting.NET.html Eventually I want to connect this with Dapper, but for now I'm just trying to get the code from the above example to work. I am using the npgsql package and can successfully retrieve the RDSAuthToken via the RDSAuthTokenGenerator.GenerateAuthToken() function using the appropriate region endpoint, cluster endpoint, port number, and db user. The problem comes when I use the AuthToken I retrieved earlier to create a connection to the server: using NpgsqlConnection connection = new NpgsqlConnection($"Server=Cluster Endpoint;User Id=dB User;Password=AuthToken;Database=dB Instance name"); I am now getting this error: "28000: pg_hba.conf rejects connection for host \"172.31.30.255\", user \"dB User\", database \"dB Instance Name\", SSL off I'm not sure what I need to do to get this to work. As far as I can tell, I've done everything exactly as I was supposed to according to the guide in the documentation. I also created a user role with the specific permission for rds-db:connect for my specific dB user and dB instance id. My only guess is that I have failed to connect that authorization in some way to the actual dB user. I assigned that permission to a role with the same name, and then I created a dB user with that name in the dB and then granted it the rds_iam role, but it's not clear to me that the IAM user and the dB user would be connected yet. And I haven't been able to find examples online for how to connect them. It would be great to get a little help with this one. Thanks! Edit: I realized that my issue might be with the SSL Certificate path that is required at the end of the connection string in the example I linked above. I will keep looking into this, but I'm wondering if this will work to use in a Lambda if I have to reference a path to a certificate that I install on my computer. Although, I might not be understanding how this works.

Currently i have 1 writer and 2 reader instance in AuroraDB. When I was trying to change the instance write instance type to larger instance, aurora automatically promoted a reader instance to writer. But after the upgrade finish, the original writer instance stayed as reader instance. Is there a way to have a automatic failback to the original writer after the upgrade is completed?

Hi - I am having issues with high latency in connecting lambdas and a Postgres Aurora Serverless cluster via the Data API. After some research, it looks like [Connection Pooling is the best solution](https://aws.amazon.com/blogs/database/best-practices-for-working-with-amazon-aurora-serverless/). However, it looks RDS Proxy (the AWS native connection pooling solution) doesn't support Aurora Serverless. Does anyone know a workaround for this (while still using Aurora Serverless)?

HI team, I am working on creating DR strategy for our single AZ Aurora DB. We are planning to take manual snapshot of Aurora DB (postgres) and copy it to different region. Let us know if you have any script/tool to perform the same.

Hi, I have the following scenario: I have an existing aurora-postgres db cluster running in eu-north-1 (Stockholm) region. My usecase is now that I need a second replica cluster in another region (eu-central-1) to be able to use services that are not available in eu-north-1. Setting up a global database is possible in the console, however I want to automate, currently using cloudformation. But when I try to add the resource type AWS::RDS::GlobalCluster to the existing cfn deployed in eu-north-1 it gives me error: "Resource type not found". I have also tried moving out the globalcluster resource to its own cloudformation template, deploying it in eu-central-1 where the resource type seems to exist (no error here). However, when I specify the primary cluster arn it gives me an error "value for engineName should be specified". I've tried to change the primary cluster ARN to the primary cluster identifier instead, but then get "DBCluster xxyyzz not found" - I can imagine this is due to the fact that I deploy in another region than the primary cluster itself. Is it possible to accomplish what I want with cloudformation, or am I forced to do it manually? Thank you

Does AWS Aurora with postgresql engine support XA transactions meaning a transaction manager could do 2 phase commit operations on both the Aurora postgres as well as another messaging queue in a single transaction. Also if it is supported, does the XA transaction feature work with Aurora Global Database across multiple regions or does it work only within one region? Finally, does RDS postgres support the same XA transaction functionality and also if there are any constraints with that XA support between multi-region vs single region if at all it is supported on RDS postgres?

Is there an AWS guide for the best practices on CryptoShredding or secure data deletion? Thanks!

I have a question about how the rds proxy works. If I close the connection to the proxy endpoint from the application (lambda function), does it also close the underlying database connection (proxy to rds) ? Or it just closes the application to proxy connection and return the database connection back to the pool? If the latter is true, can I safely close the connection from the application when the work is done and request it again when necessary?

Hi, I started a DMS task last week, and after letting it run for three days, I stopped adding new tables to the process, and when I tried to activate it again, I've received the message: ``` Last Error Task 'PMZPTPTYYJZGJQLBZCBYC7GSUQMWC2RICPEY6DA' was suspended due to 6 successive unexpected failures Stop Reason FATAL_ERROR Error Level FATAL ``` Looking at the logs, I found this error message 6x consecutively: ``` 2022-02-14T13:57:39 [DATA_STRUCTURE ]E: SQLite general error. Code <19>, Message <UNIQUE constraint failed: events.identifier, events.eventType, events.detailMessage>. [1000505] (at_sqlite.c:525) ``` What does it mean? Which kind of actions should I take?

Trying to upgrade our Aurora Clusters finally. Got them recently updated to 11.13, but every attempt I make to upgrade to 12.8 fails with **"Database cluster is in a state that cannot be upgraded: Postgres cluster is in a state where pg_upgrade can not be completed successfully."** Here are the logs which I think point to the culprit: **2022-02-11 22:37:53.514 GMT [5276] ERROR: could not access file "$libdir/postgis-2.4": No such file or directory 2022-02-11 22:37:53.514 GMT [5276] STATEMENT: LOAD '$libdir/postgis-2.4' 2022-02-11 22:37:53.515 GMT [5276] ERROR: could not access file "$libdir/rtpostgis-2.4": No such file or directory 2022-02-11 22:37:53.515 GMT [5276] STATEMENT: LOAD '$libdir/rtpostgis-2.4'** command: "/rdsdbbin/aurora-12.8.12.8.0.5790.0/bin/pg_ctl" -w -D "/rdsdbdata/db" -o "--config_file=/rdsdbdata/config_new/postgresql.conf --survivable_cache_mode=off" -m fast stop >> "pg_upgrade_server.log" 2>&1 waiting for server to shut down....2022-02-11 22:37:53.541 GMT [5185] LOG: received fast shutdown request 2022-02-11 22:37:53.541 GMT [5185] LOG: aborting any active transactions 2022-02-11 22:37:53.542 GMT [5237] LOG: shutting down ................sh: /rdsdbbin/aurora-12.8.12.8.0.5790.0/bin/curl: /apollo/sbin/envroot: bad interpreter: No such file or directory 2022-02-11 22:38:10.305 GMT [5185] FATAL: Can't handle storage runtime process crash 2022-02-11 22:38:10.305 GMT [5185] LOG: database system is shut down -------- I found several other articles that point to issues with Postgis, so I followed what they suggest, but no luck. First our cluster is running Postgis 2.4.4. So I went ahead and updated this to 3.1.4, tried the approach to restart the instance and validate its really using Postgis 3 and that all looks fine. Nothing helps though. If anyone has suggestions, I am happy to try. Thanks Thomas

hi, I'm trying to use the postgresql "to_timestamp" function inside a RDS Data Service batchExecuteStatement calls of the AWS Javascript SDK. I set the parameterSets to an array. An element of the array is: `{ name: 'received_on', typeHint: 'TIMESTAMP', value: { stringValue: 'to_timestamp(' + record.received_on + ')' } },` where record.received_on is an epoch time like 1644417054153 (I also tested 1644417054.153). The execution of the code throw this error: Cannot parse date or time "to_timestamp(164441704.153)" Where is the problem? Thanks

Hello, We recently upgraded from aurora pg 12.4 to aurora 13.4 and after that, we noticed that this query stopped working, `select sum(allocated) from aurora_stat_memctx_usage()` I did not see anything in the change log about this method going away, but I'm unable to call it on any of our aurora 13.4 instances, but it works fine on our aurora 12.4 instance. Any help would be appreciated.

We are trying to setup logical replication with aurora postgres using different databases on same instance but the instance just hangs. Is this currently supported or is there anything special we need to do for it to work? Thanks!

I am exploring DMS for migrating RDS to Aurora database. Can someone please explain how this migration type works: "Migrate existing data and replicate on-going changes" Time T1 - So, my application is talking Source database Time T2 - I use DMS to migrate data from source to target database. Time T3 - Data migration completes.. Time T4 - I change my application to use target database by changing the endpoint of the database My question is - Is the above correct ? What happens to data writes between Time T3 and T4 ? Does. "replicate on-going changes" , means, it will keep replicating the data from Source to Target till it is not stopped , and will make sure all writes get replicated to Target database ?

For Major version upgrades in Aurora cluster, AWS documentation says: "*To safely upgrade your DB instances, Aurora PostgreSQL uses the pg_upgrade utility described in the PostgreSQL documentation. After the writer upgrade completes, **each reader instance experiences a brief outage** while it's upgraded to the new major version automatically*." Document link: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html If the cluster has 10 replicas is it possible to do the upgrade in batches or one-by-one, so that the clients dependent on the replicas do not experience downtime ?

From January 31, 2023, Amazon Aurora PostgreSQL-compatible edition will no longer support major version 10.x. Per the Aurora Version Policy (1), we are providing 12 months notice to give you time to upgrade your database cluster(s). We recommend that you proactively upgrade your databases running Amazon Aurora PostgreSQL major version 10.x to Amazon Aurora PostgreSQL 11 or higher at your convenience before January 31, 2023. If you do not upgrade your database running Aurora PostgreSQL 10.x before January 31, 2023, Amazon Aurora will upgrade your Amazon Aurora PostgreSQL 10.x databases to the appropriate Amazon Aurora PostgreSQL major version during a scheduled maintenance window on or after January 31, 2023. *How to Determine Which Instances are Running Aurora PostgreSQL 10.x* 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. (2) 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 Upgrade to a New Major Version * You can initiate an upgrade of your database instance — either immediately or during your next maintenance window — to a newer major version of Amazon Aurora PostgreSQL using the AWS Management Console or the AWS Command Line Interface (CLI). The upgrade process will shut down the database instance, perform the upgrade, and restart the database instance. The database instance may be restarted multiple times during the upgrade process. While major version upgrades typically complete within the standard maintenance window, the duration of the upgrade depends on the number of objects within the database. To avoid any unplanned unavailability outside your maintenance window, we recommend that you first take a snapshot (3) or a fast database clone (4) of your database and test the upgrade to get an estimate of the duration. To learn more about upgrading PostgreSQL major versions in Aurora, review the Upgrading Database Versions page (5). Please be aware of the following timeline: • Now through January 31, 2023 - You can initiate upgrades of Amazon Aurora PostgreSQL 10.x instances to Amazon Aurora PostgreSQL 11 or higher at any time. • Starting August 1, 2022, you will no longer be able to create new Aurora clusters or instances with Aurora PostgreSQL major version 10.x from either the AWS Console or the CLI. You can still add read replicas to existing Aurora PostgreSQL 10.x clusters and continue to apply changes to existing Aurora PostgreSQL 10.x instances, such as migrating to a Graviton2 R6g instance or changing instance configuration, until January 31, 2023. • Starting January 31, 2023 Amazon Aurora will upgrade your Amazon Aurora PostgreSQL 10.x databases to the appropriate Amazon Aurora PostgreSQL major version during a scheduled maintenance window on or after January 31, 2023. Restoration of Amazon Aurora PostgreSQL 10.x database snapshots will result in an automatic upgrade of the restored database to a supported version at the time. [1]https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.VersionPolicy.html [2] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/accessing-monitoring.html [3] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_CreateSnapshotCluster.html [4] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Managing.Clone.html [5] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Updates.html

RDS Postgres allows adding up to 5 replicas. But when I create a replica, it creates it as a single instance, not as a cluster. If I want to add RDS Postgres Replicas in a cluster, so that they all can share load from my application, how can I do this ? (so that application can handle more load) I know this is possible with Aurora replicas, as Aurora creates a cluster of replicas which has single endpoint and which can scale in or scale out. But All normal RDS Postgres Replicas are created like single instances with different endpoints. Is it possible to make RDS postgres replicas as a cluster with 1 endpoint?

Good evening ... as the subject states, we are currently running an RDS version of postgres 12.7 and wish to migrate over to Aurora. I would assume the easiest would be to create an aurora read replica, test all functions (including postGIS), and once confirmed things work, promote that and should be good however the create aurora read replica is not showing on the console. I saw a few threads about that being a big but that was months ago. This database gets thousands of updates every minute, so really trying to keep the downtime to nothing or as little as possible, so any guidance or help is appreciated.

I have an RDS Aurora Postgres instance that's been online for a few years. When created, through the console, I turned on automatic backups. On the DB instance page, I can see daily snapshots. I'd now like to practice the "restore at point in time" functionality that is documented at https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html However, there are no "Automated Backups" in the automated backups page for the region. If I open a Snapshot in the db instance page, I see "create a new instance from snapshot," which does not give an option for restoring to a particular point in time, only whenever the snapshot was made. I would post some lightly redacted screenshots, but apparently that's not supported in these forums.

Hi, According to https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Migrating.html#AuroraPostgreSQL.Migrating.RDSPostgreSQL.Import.Console it's possible to migrate PostgreSQL RDS (12.7) to Aurora by adding Aurora Read Replica. But I have no such option for my PostgreSQL RDS. Is it has some problems in this feature? I tried other options "Migrating an RDS for PostgreSQL DB instance using a snapshot", but it also inacitvated button in my dashboard. Is it possible that the feature was announced but isn't available yet?

I'm using RDS Aurora Serverless with PostgreSQL compatibility. I'm trying to write a query that uses the [PostgreSQL LIKE clause](https://www.tutorialspoint.com/postgresql/postgresql_like_clause.htm) with percentage signs as wildcard operators. This is what I'm using right now: ``` SELECT * FROM products WHERE name LIKE '%:productName%' ``` and the productName gets passed into the RDS Data API through a `stringValue` field parameter (since it comes from user input). ``` { name: 'productName', value: { stringValue: variableFromUserInput } } ``` This doesn't seem to be working since my query always succeeds but returns no results. Is this use case supported with the RDS Data API and am I using this correctly?

Hi, My Aurora DB in Serverless mode and on Postgresql engine worked fine in recent months. From begin new year (last two days) DB restarting several times per day with log note "DB instance restarted". DB is not available for few minutes after restart and works fine to next restart. I also noticed that before each restart (~20s) is logged "The DB cluster has scaled from 8 capacity units to 4 capacity units, but scaling wasn't seamless for this reason: timed out finding a scaling point". Sometimes from 8 ACU to 4 ACU, sometimes from 4 ACU to 8 ACU. Anybody know what is reason for this DB behavior?

Hello, I am seeing these errors in my Aurora instance: ``` 1:32:59 PM UTC: The DB cluster has scaled from 2 capacity units to 4 capacity units, but scaling wasn't seamless for this reason: An internal error occurred. 1:33:13 PM UTC: DB instance restarted ``` Applications connected to the database experience errors when it happens: ``` psql: error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. ``` Any ideas on what would be causing these errors and how to prevent them? Thanks.

Hi Team, I have an architecture based question, How Postgre SQL DB log processing can be captured through AWS lambda , aws Kinisis Data streams and finally Data should loads into Time Stream Database. Providing High level scenario: Draft Data flow : **Aurora Postgre DB** ----DB Logs Processing to ---->** Lambda** --->Ingestion to ----> **Kinesis Data Streams ** ---Process and Join context data and insert --- Insert to --------> **Time Stream Database** I believe , we can process / loads the AWS IoT (sensors , device data) to Time Stream Database through Lambda , Kinesis Streams , Kinesis Data analytics and finally Time series Database and we can do analytics on time series data . But i am not sure How the postgre SQL db logs (write ahead logs) process through Lambda and ingest through Kinesis streams and finally load into Time Stream Database . and above flow also required to Joins some tables like Event driven tables with associated Account , Customer tables and then it will load into Time Series Database . would like to know if above flow would be accurate , as we are not processing any sensors / devices data ( where sensors data captures all measures and dimensions data from device and loads into Time Stream DB ) so Time Series database always a primary database . if anyone can through some lights , how postgre sql db logs can be integrated with Time Stream database through Kinesis Data streams , Lambda . Need your help Thanks

Dear Team, We are planning to upload approx 1.5 lakhs documents per day in S3 and planning to store its metadata in the DB(1 row/document). We also have a search requirement on this metadata for which we are planning to use AWS OpenSearch. So, basically the DB is going to be used as System of record only. Wanted to check what will be the better DB to store this huge volume of data. Dynamo DB or Aurora ?

Seeing some errors in the Postgresql log file. But other than these reported errors, the db instance is working fine and applications have no issues connecting, reading and writing to db instance. We want some help from AWS to verify if these errors require any further action. Thanks. 2021-12-07T03:29:32.000Z Copy 2021-12-07 03:29:32 UTC::@:[8182]:WARNING: unrecognized configuration parameter "rds.adaptive_autovacuum" 2021-12-07 03:29:32 UTC::@:[8182]:WARNING: unrecognized configuration parameter "rds.adaptive_autovacuum" 2021-12-07T03:29:32.000Z Copy 2021-12-07 03:29:32 UTC::@:[8182]:WARNING: unrecognized configuration parameter "rds.enable_plan_management" 2021-12-07 03:29:32 UTC::@:[8182]:WARNING: unrecognized configuration parameter "rds.enable_plan_management" 2021-12-07T03:29:32.000Z 2021-12-07 03:29:32 UTC::@:[8182]:LOG: database system is shut down Postgres Shared Memory Value: 51704012800 bytes LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf" 2021-12-07T03:29:35.000Z 2021-12-07 03:29:35 UTC::@:[8776]:WARNING: unrecognized configuration parameter "rds.adaptive_autovacuum" 2021-12-07T03:29:35.000Z 2021-12-07 03:29:35 UTC::@:[8776]:WARNING: unrecognized configuration parameter "rds.enable_plan_management" 2021-12-07T03:29:35.000Z 2021-12-07 03:29:35 UTC::@:[8776]:LOG: redirecting log output to logging collector process 2021-12-07T03:29:35.000Z 2021-12-07 03:29:35 UTC::@:[8776]:HINT: Future log output will appear in directory "/rdsdbdata/log/error". 2021-12-07T03:38:49.000Z 2021-12-07 03:38:49 UTC::@:[8777]:LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf" 2021-12-07T03:51:10.000Z 2021-12-07 03:51:10 UTC::@:[29750]:LOG: setting shmemname /mnt/hugetlbfs/aurora-postgres-buffer-cache 2021-12-07T03:51:10.000Z 2021-12-07 03:51:10 UTC::@:[29743]:LOG: Waiting for runtime initialization complete... 2021-12-07T03:51:11.000Z 2021-12-07 03:51:11 UTC:[local]:rdsadmin@rdsadmin:[30193]:FATAL: the database system is starting up 2021-12-07T03:51:11.000Z 2021-12-07 03:51:11 UTC::@:[30192]:LOG: database system was interrupted; last known up at 2021-12-07 03:50:57 UTC 2021-12-07T03:51:11.000Z 2021-12-07 03:51:11 UTC::@:[30192]:LOG: Outbound recovery is not required 2021-12-07T03:51:12.000Z 2021-12-07 03:51:12 UTC:[local]:rdsadmin@rdsadmin:[30220]:FATAL: the database system is starting up 2021-12-07T03:51:13.000Z 2021-12-07 03:51:13 UTC::@:[29743]:LOG: database system is ready to accept connections 2021-12-07T03:55:10.000Z 2021-12-07 03:55:10 UTC::@:[29743]:LOG: received SIGHUP, reloading configuration files 2021-12-07T03:55:10.000Z 2021-12-07 03:55:10 UTC::@:[29743]:LOG: parameter "unix_socket_permissions" cannot be changed without restarting the server 2021-12-07T03:55:10.000Z 2021-12-07 03:55:10 UTC::@:[29743]:LOG: parameter "apg_critical_insights_enabled" changed to "on" 2021-12-07T03:55:10.000Z 2021-12-07 03:55:10 UTC::@:[29743]:LOG: configuration file "/rdsdbdata/config/postgresql.conf" contains errors; unaffected changes were applied 2021-12-07T09:48:09.000Z 2021-12-07 09:48:09 UTC:10.201.11.180(61119):postgresrds@spam:[17324]:LOG: could not receive data from client: Connection timed out

Hello, I'm following the tutorial for using Postgres CDC using logical replication here - https://aws.amazon.com/blogs/database/stream-changes-from-amazon-rds-for-postgresql-using-amazon-kinesis-data-streams-and-aws-lambda/ The DB cluster parameter group has rds.logical_replication enabled, and I've verified that the user I intend to use is capable of IAM auth, and that logical replication slots can be created and queried: CREATE ROLE replicate LOGIN; GRANT rds_replication, rds_iam TO replicate; GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO replicate; ...snip...IAM authenticate as replicate user... Server: PostgreSQL 13.4 Version: 3.2.0 Home: http://pgcli.com replicate@dev-db-host:mydb> SELECT pg_create_logical_replication_slot('mydb_replication_slot', 'wal2json'); replication slot "mydb_replication_slot" already exists Time: 0.016s replicate@dev-db-host:mydb> SELECT pg_create_logical_replication_slot('test_replication_slot', 'wal2json'); +--------------------------------------+ | pg_create_logical_replication_slot | |--------------------------------------| | (test_replication_slot,0/5005318) | +--------------------------------------+ SELECT 1 Time: 0.044s replicate@dev-db-host:mydb> SELECT * FROM pg_logical_slot_peek_changes('test_replication_slot', null, null); +-------+-------+--------+ | lsn | xid | data | |-------+-------+--------| +-------+-------+--------+ SELECT 0 Time: 0.033s replicate@dev-db-host:mydb> However when I attempt create a replication connection using the python psycopg2 code in the blog post, postgres tells me > FATAL: password authentication failed for user "replicate" Someone else asked the psycopg devs, who've indicated it's an RDS issue: https://github.com/psycopg/psycopg2/issues/1391 Any ideas? Cheers, Jim P.S. I've verified that the postgres logical replication connection (either `replication=database` option on the command line or psycopg2's `LogicalReplicationConnection` type) is possible when using a plain old password instead of RDS IAM.

We currently work with Aurora RDS postgres. Initially our database was small and easy to manage, we sent a dump to S3 and made it available to developers to develop new features and solve bugs. For today our database weighs 700GB, **what are the best strategies to make the data available to developers?** It is impossible to download the entire database, it would be very costly in time. The most important thing is that they have data to develop new features and solve bugs

Hello! Then we try to upgrade 11.13 cluster to 12.8 it fails, in logs I can find only errors like this: 2021-12-08 06:55:26.080 GMT [7934] LOG: background worker "plans update worker" (PID 8004) exited with exit code 1 command: "/rdsdbbin/aurora-11.13.11.13.0.8865.0/bin/pg_ctl" -w -D "/rdsdbdata/db_old" -o "--config_file=/rdsdbdata/config_old/postgresql.conf" -m fast stop >> "pg_upgrade_server.log" 2>&1 waiting for server to shut down....2021-12-08 06:55:31.615 GMT [7934] LOG: received fast shutdown request 2021-12-08 06:55:31.619 GMT [7934] LOG: aborting any active transactions 2021-12-08 06:55:31.622 GMT [7934] LOG: background worker "logical replication launcher" (PID 8005) exited with exit code 1 2021-12-08 06:55:31.623 GMT [8000] LOG: shutting down .............sh: /rdsdbbin/aurora-12.8.12.8.0.5790.0/bin/curl: /apollo/sbin/envroot: bad interpreter: No such file or directory 2021-12-08 06:55:44.671 GMT [7934] FATAL: Can't handle storage runtime process crash 2021-12-08 06:55:44.671 GMT [7934] LOG: database system is shut down done server stopped Please, help me to solve it.

I have been using IAM database authentication on an Aurora for Postgres for many months now and everything worked well. A few days ago I started getting login errors until now it is impossible to login at all. I am not sure about the timeline as we only use these accounts for individual user connections. Only accounts not using IAM can login now. I am not aware of any change but I cannot pinpoint the root cause of the error. The error I am getting in Postgres clients is this: ``` Unable to connect to server: FATAL: PAM authentication failed for user "<REDACTED_USERNAME>" FATAL: pg_hba.conf rejects connection for host "<REDACTED_IP>", user "<REDACTED_USERNAME>", database "postgres", SSL off ``` If I look into the Postgres logs I get a little more details: ``` * Trying <REDACTED_IP>:1108... * Connected to rdsauthproxy (<REDACTED_IP>) port 1108 (#0) > POST /authenticateRequest HTTP/1.1 Host: rdsauthproxy:1108 Accept: */* Content-Length: 753 Content-Type: multipart/form-data; boundary=------------------------1f9a4da08078f511 * We are completely uploaded and fine * Mark bundle as not supporting multiuse < HTTP/1.1 403 Forbidden < Content-Type: text/html;charset=utf-8 < Content-Length: 0 < * Connection #0 to host rdsauthproxy left intact 2021-12-05 14:42:43 UTC:10.4.2.137(32029):<REDACTED_USERNAME>@postgres:[7487]:LOG: pam_authenticate failed: Permission denied 2021-12-05 14:42:43 UTC:10.4.2.137(32029):<REDACTED_USERNAME>@postgres:[7487]:FATAL: PAM authentication failed for user "<REDACTED_USERNAME>" 2021-12-05 14:42:43 UTC:10.4.2.137(32029):<REDACTED_USERNAME>@postgres:[7487]:DETAIL: Connection matched pg_hba.conf line 13: "hostssl all +rds_iam all pam" 2021-12-05 14:42:43 UTC:10.4.2.137(13615):<REDACTED_USERNAME>@postgres:[7488]:FATAL: pg_hba.conf rejects connection for host "<REDACTED_IP>", user "<REDACTED_USERNAME>", database "postgres", SSL off ``` So it seems to be "rdsauthproxy" that rejects the authentication. My understanding is that this proxy is part of the Aurora instance and I did not find a way to get its logs where hopefully I could find any information on why the authentication is rejected. I checked the IAM configuration in case something changed but it seems fine. The users have a policy like this: ``` { "Version": "2012-10-17", "Statement": [ { "Sid": "", "Effect": "Allow", "Action": "rds-db:connect", "Resource": "arn:aws:rds-db:eu-west-3:<REDACTED_ACCOUNT_ID>:dbuser:*/<REDACTED_USERNAME>" } ] } ``` The usernames match exactly between IAM and Postgres. In Postgres they all have the "rds_iam" role. Is there anything I could be missing? At least is there a way to retrieve logs of an Aurora rdsauthproxy instance that maybe could point me in the right direction?

We are considering enabling logical (streaming) replication on our existing Aurora PG 13 cluster for CDC? We want to keep our aurora read replicas as is, but I haven't found good resource that explains how to achieve both, OR what is the implication for existing read replicas. Our current understanding is that we might have to stand up 2x current nodes in our cluster which would be way too expensive for us.

Hello, I have a customer who needs to move their RDS PostgreSQL database to a different account, and would like to migrate to Aurora while doing so. Just wanted to do a quick sanity check + see if there are simpler options available before I shoot over the following options to the customers. With downtime 1. Take snapshot of PostgreSQL database 2. Share snapshot with new account following these steps (https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Migrating.html#AuroraPostgreSQL.Migrating.RDSPostgreSQL.Import.Console) 3. Create Aurora cluster from RDS PostgreSQL snapshot No downtime 1. Create Aurora cluster in new account 2. Use DMS to migrate data from RDS PostgreSQL to Aurora cluster in new account (https://aws.amazon.com/blogs/database/set-up-aws-dms-for-cross-account-migration/) The customer is running RDS PostgreSQL 10.13 at the moment.

I am using *AWS DMS* to perform migration from on premise **SQL Server to Aurora PostgreSQL**. 1/ After performing the data migration task step, I am seeing same table name twice in PostgreSQL database. One table was created during schema conversion tool and 2nd table was created during data migration task. Is there any option in data migration task to prevent creation of new table,if it exists. 2/ I am also facing issues when converting the schema using AWS Schema Conversion Tool.It seems SCT is not supporting auto conversion of computed fields, temp tables and trigger objects to targeted Aurora PostgreSQL database. Does SCT automate part of the process, and not all of the process.

I am currently migrating an Amazon RDS for PostgreSQL database from Amazon EC2 to Amazon Aurora PostgreSQL-Compatible Edition. I am using AWS DMS and have encountered the following issue: One of the columns in a particular table stores the values of water pressure measured within a second. This column is an array of decimal numbers (Ex: {2.44, 5.66, 8.55}). I received the following error message during the migration from AWS DMS: "1 unsupported data type '_float4' on table 'data1', column 'pressure'". Does AWS DMS support ARRAY data type for double or floating point numbers? The AWS documentation indicates that the arrays can't be migrated. However, further down on the same page, it's mentioned that AWS DMS supports arrays from a source RDS for PostgreSQL database and that arrays are mapped to CLOBs in AWS DMS. I'm looking for some guidance on whether ARRAY data type is supported by AWS DMS during migration. The reports return the following error: Note: You can see that the pressure column is indicated with real[] . 1 unsupported data type '_float4' on table 'data1', column 'pressure' pipeminder=# \d data1 Table "public.data1" Column | Type | Modifiers ---------------+--------------------------+----------- device_id | bigint | not null timestamp | timestamp with time zone | not null pressure | real[] | not null pressure_min | real | not null pressure_mean | real | not null pressure_max | real | not null flow | real | not null Indexes: "data1_unique_device_time" UNIQUE CONSTRAINT, btree (device_id, "timestamp")

A customer has setup Aurora PostgreSQL and any time a CDC occurs they need to trigger lambda function. e.g like in this Blog https://aws.amazon.com/blogs/database/capturing-data-changes-in-amazon-aurora-using-aws-lambda/ However there doesnt seem to invoke lambda from PostgreSQL..? If there's none, how else have any of your customers accomplished this requirement?

While migrating from SQL Server or Sybase database to PostgreSQL, how do you convert a stored procedure with return status and check if procedure call is successful. Sample example: EXEC @return_status = sample_proc

Does Aurora Global with PostgreSQL support “Write forwarding” from secondary clusters ? if not how are the customers suppose to achieve this. To put it the other way, how can they direct their application to do writes to the aurora cluster in a different region ? What should they do at networking level to make the write endpoint visible Any guidance would be much appreciated. Thanks FYI.. Customer has got a Primary Aurora Postgres cluster in eu-west-2 They have created a secondary read replica cluster in us-east-2 They can connect to the read only endpoint in us-east-2 from a us-east-2 ec2 instance But cannot connect to the primary eu-west-2 cluster from us-east-2 (They are running an EC2 instance just to test the networking)

Customer who periodically simulates an outage in RDS Aurora PostgreSQL by stopping and starting the entire Aurora cluster and observing the effect on different upstream systems. This is not an ideal situation as it takes some time for the stopping/starting and the environment is offline and unavailable for other testing use during the duration. They were thinking of setting up a reader node for testing and isolate testing to this node. However, they indicated that these reader nodes can only be rebooted and not just stopped, this may not be ideal for them. Is there a way for the customer to have a similar parallel environment for testing purposes for these offline scenarios while maintaining functionality of the database for other testing?

Customer is looking for ways to track changes in their data in Aurora PostgreSQL. Traditionally they could add a date created, date_modified, username etc columns directly into each table and use it as a reference. Any suggestions of a way they could cut down on having to do this for each table and instead consume some audit/WAL logs for each row change? Or ideas on how others are tackling this use case?

A customer wants to know whether there is any native mechanism to parallel upload data to Aurora from multiple csv files in S3. Based on the documentation [1] unlike, DMS there is no supported way of doing it. Secondly, is there a way one can modify aws_s3 extension i.e. wondering if have open-sourced it. DMS is another alternative, but wanted to rule out if there I am missing anything specific here. [1] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Migrating.html#USER_PostgreSQL.S3Import

A customer is using Redshift in eu-west-3 and looking to use Aurora Postgresql as a DataMart downstream. They asked me if Aurora works well for such use cases. Do any of you have any experience or warning for such OLAP uses cases? Thanks

Hello, TL;DR - I have a customer who has a SaaS solution which includes multi-tenant data layers. They want to improve their tenant isolation by creating a logical DB per tenant. Aurora Postgres is a main component of the architecture. In order to create better isolation, they would like to create a database per tenant (to clarify: multiple logical databases within the same cluster). They ask whether a single Aurora PostgreSQL cluster can hold 10K DBs. 1. Is this a reasonable requirement from Aurora Postgres (and from PostgreSQL at all)? 2. In which point (regarding DBs number) solutions like sharding will make sense? (talking only about the number of DBs in a cluster. To clarify - I'm also discussing with them about each DB performance requirements, availability requirements, etc..) 3. In addition, I am discussing with the customer about other considerations, like dividing the DBs in order to create better maintenance / operations abilities and limit the blast radius of one cluster). Many thanks in advance.

Were any changes made to how connections are handled between PostgreSQL and Aurora Postgres? Specifically, there are limitations in Postgres with connections due to memory usage, and a customer wants to know if any improvements were made with Aurora. In addition, if the connection "math" is the same as vanilla Postgres, is the total max connections for a cluster equal to the # of replicas * max connections per, or something else? Thanks for any insights!

Is there an easy way to migrate from Aurora Postgres to RDS Postgres? My customer has several Aurora instances overprovisioned (the smallest available aurora instance type is way bigger than what they need), and wants to migrate them to regular RDS. I can think only of pg_dump or Database Migration Service. Are there any other ways that I'm missing?

Customer would like to move his PostgreSQL to Aurora PostgreSQL with PostGIS. As per [AWS forum][1], this should work as RDS PostgreSQL [documentation][2] is supported and Aurora doesn't list it as [incompatible][3]. I quickly checked I could load the postgis extension in Aurora but I don't have extensive experience with this. Are there any drawbacks, tips with Aurora + PostGIS ? [1]: https://forums.aws.amazon.com/thread.jspa?messageID=811633&tstart=0 [2]: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS [3]: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.Compare.html