Questions tagged with 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 ```

Autovacuum does not clear the database. Databases middle-db Postgres 10.18, AWS RDS(vCPU 2, RAM 8Gb, SSD(gp2) 1100Gib) Table "spree_datafeed_products" ``` relid | 16556 schemaname | public relname | spree_datafeed_products seq_scan | 20 seq_tup_read | 365522436 idx_scan | 962072108 idx_tup_fetch | 9929276855 n_tup_ins | 2846455 n_tup_upd | 35778058 n_tup_del | 284291955 n_tup_hot_upd | 0 n_live_tup | 3546840 n_dead_tup | 338790851 n_mod_since_analyze | 307930753 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | 2022-04-29 13:01:43.985749+00 vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 1 ``` Table and indexes sizes: ``` indexname | size index_spree_datafeed_products_on_updated_at | 48 GB index_spree_datafeed_products_on_state | 35 GB index_spree_datafeed_products_on_size_variant_field | 40 GB index_spree_datafeed_products_on_product_id | 32 GB index_spree_datafeed_products_on_original_id | 31 GB index_spree_datafeed_products_on_datafeed_id | 42 GB index_spree_datafeed_products_on_datafeed_id_and_original_id | 31 GB index_spree_datafeed_products_on_data_hash | 39 GB spree_datafeed_products_pkey | 18 GB pg_size_pretty - 419 GB ``` Worker: ``` datid | 16404 datname | milanstyle_production pid | 2274 backend_start | 2022-05-01 19:52:00.066097+00 xact_start | 2022-05-01 19:52:00.23692+00 query_start | 2022-05-01 19:52:00.23692+00 state_change | 2022-05-01 19:52:00.236921+00 wait_event_type | wait_event | state | active backend_xid | backend_xmin | 1301636863 query | autovacuum: VACUUM ANALYZE public.spree_datafeed_products backend_type | autovacuum worker ``` Settings: ``` autovacuum on autovacuum_analyze_scale_factor 0.05 autovacuum_analyze_threshold 50 autovacuum_freeze_max_age 200000000 autovacuum_max_workers 3 autovacuum_multixact_freeze_max_age 400000000 autovacuum_naptime 30 autovacuum_vacuum_cost_delay 20 autovacuum_vacuum_cost_limit -1 autovacuum_vacuum_scale_factor 0.1 autovacuum_vacuum_threshold 50 ``` The garbage cleaning script has accumulated a lot of deleted entries. We have been waiting for more than a week (autoclearance). What is the problem? Why is the database failing?

I'm performance testing some tables in both RDS Postgres and MySQL with the following configurations: * Tested each on `db.m6g.4xlarge` and `db.m6g.xlarge` instance type * 1024 GiB storage each * Running a `SELECT * FROM very_large_table`, all tables tested are ~80gb **MySQL:** For the `db.m6g.4xlarge` instance type, I was getting variable results on 2 similar tables (exact same schema + same row count + ~same size), one table could be read at >10k iops consistently and the other table lagged behind at <3k iops, this variability was very confusing. Since switching to the db.m6g.xlarge instance type, I've been consistently capped at 1.8k read iops and a max read throughput of 26Mbs. [This article](https://aws.amazon.com/rds/instance-types/) says that I should be getting up to 4750 Mbs but I'm getting no where near that. **Postgres:** Testing on the `db.m6g.4xlarge` instance type, reading from a table would give me just over 3k iops, 140Mbs throughput. On a `db.m6g.xlarge`, Im getting at most 2k iops, 100Mbs throughput. Is there a way to guarantee a set number of iops per `SELECT *` query? It feels like my databases aren't being utilized properly with such intensive workloads. I've tried setting up an RDS MySQL cluster with 10k provisioned iops but my read queries were still capping at ~2k iops.

What are the backup and recovery options available for Babelfish for Aurora PostgreSQL?

I am trying to create Aurora readreplica from RDS Postgressql 11.13 but I couldn't see "Create Aurora readreplica" button from RDS Postrgres earlier I have seen this button before but now it's not showing I don't know what was changed? I followed this document. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Migrating.html#AuroraPostgreSQL.Migrating.RDSPostgreSQL.Replica

This announcement is for customers that are running one or more Amazon RDS DB instances with a version of PostgreSQL, that has been deprecated by Amazon RDS and requires attention. The RDS PostgreSQL minor versions that are listed in the table below are supported, and any DB instances running earlier versions will be automatically upgraded to the version marked as "preferred" by RDS, no earlier than July 15, 2022 starting 12 AM PDT: | Major Versions Supported | Minor Versions Supported | | --- | --- | | 14 | 14.1 and later | | 13 |13.3 and later | | 12 | 12.7 and later | | 11 |11.12 and later | | 10 |10.17 and later| | 9 |none | Amazon RDS supports DB instances running the PostgreSQL minor versions listed above. Minor versions not included above do not meet our high quality, performance, and security bar. In the PostgreSQL versioning policy [1] the PostgreSQL community recommends that you always run the latest available minor release for whatever major version is in use. Additionally, we recommend that you monitor the PostgreSQL security page for documented vulnerabilities [2]. If you have automatic minor version upgrade enabled as a part of your configuration settings, you will be automatically upgraded. Alternatively, you can take action yourselves by performing the upgrade earlier. You can initiate an upgrade by going to the Modify DB Instance page in the AWS Management Console and change the database version setting to a newer minor/major version of PostgreSQL. Alternatively, you can also use the AWS CLI to perform the upgrade. To learn more about upgrading PostgreSQL minor versions in RDS, review the 'Upgrading Database Versions' page [3]. The upgrade process will shutdown the database instance, perform the upgrade, and restart the database instance. The DB instance may restart multiple times during the process. If you choose the "Apply Immediately" option, the upgrade will be initiated immediately after clicking on the "Modify DB Instance" button. If you choose not to apply the change immediately, the upgrade will be performed during your next maintenance window. Starting no earlier than July 15, 2022 12 AM PDT, we will automatically upgrade the DB instances running deprecated minor version to the preferred minor version of the specific major version of your RDS PostgreSQL database. (For example, instances running RDS PostgreSQL 10.1 will be automatically upgraded to 10.17 starting no earlier than July 15, 2022 12 AM PDT) Should you need to create new instances using the deprecated version(s) of the database, we recommend that you restore from a recent DB snapshot [4]. You can continue to run and modify existing instances/clusters using these versions until July 14, 2022 11:59 PM PDT, after which your DB instance will automatically be upgraded to the preferred minor version of the specific major version of your RDS PostgreSQL database. Starting no earlier than July 15, 2022 12 AM PDT, restoring the snapshot of a deprecated RDS PostgreSQL database instance will result in an automatic version upgrade of the restored database instance using the same upgrade process as described above. Should you have any questions or concerns, please see the RDS FAQs [5] or you can contact the AWS Support Team on the community forums and via AWS Support [6]. Sincerely, Amazon RDS [1] https://www.postgresql.org/support/versioning/ [2] https://www.postgresql.org/support/security/ [3] http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html [4] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html [5] https://aws.amazon.com/rds/faqs/ [search for "guidelines for deprecating database engine versions"] [6] https://aws.amazon.com/support

Hello! I am trying to deploy a rails 7 app to elastic beanstalk, but my deploy keeps failing. In the logs I see: `An error occurred while installing pg (1.3.5), and Bundler cannot continue` (installing the postgres gem is failing) I SSHed onto my instance and ran bundle manually, and see `Your PostgreSQL is too old. Either install an older version of this gem or upgrade your database to at least PostgreSQL-9.3.` In my .elasticbeanstalk.packages.config file I have: ``` packages: yum: postgresql14-devel: [] ``` But this seems to have no effect on the version of Postgres on my instance. Creating an RDS instance associated to my beanstalk environment with any version of PostgreSQL does not seem to solve the problem. `postgres -V` is not a command on my beanstalk instance if I ssh onto my instance and cat `usr/bin/pg_config` I think it may be set to version 9.2, but this file doesn't look to me like something I should be editing via SSH and I don't see any references to manipulating how it gets generated. Any assistance would be greatly appreciated! Update1: .elasticbeanstalk.packages.config should be within `.ebextensions` instead. I made this change, still had this error. Found another thread on stack overflow that described the packages.config file to look like ``` packages: yum: amazon-linux-extras: [] commands: 01_postgres_activate: command: sudo amazon-linux-extras enable postgresql10 02_postgres_install: command: sudo yum install -y postgresql-devel ``` I updated the file, still had no luck. I terminated and rebuild my app to try again, and manually ran `sudo amazon-linux-extras enable postgresql10` and `sudo yum install -y postgresql-devel` via ssh, which finally let me successfully bundle install. Still working on making this work via EB deploy instead of manually messing with the boxes. Update 2: After making the above changes and creating a new application + environment I am able to consistently get past the above issue.

Hi, Chr(0) is present in the source data and dms is not migrating this character to target. DMS transfers all non-printable characters except chr(0). Due to this DMS reports these records as mismatched records. Is there any workaround for this in DMS to retain chr(0) in target. Source DB:rds oracle Target :AUrora postgresql 13.X Sample insert query: insert into non_ascii_test values (1,'This string doesnt contain non-printable ascii characters'); insert into non_ascii_test values (2,'This string contains non-printable ascii characters'||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)); insert into non_ascii_test values (3,'This string contains non-printable ascii characters'||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)); insert into non_ascii_test values (6,chr(0)||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)||chr(10)||chr(11)||chr(12)||chr(13)||chr(14)||chr(15)||chr(16)||chr(17)||chr(18)||chr(19)||chr(20)||chr(21)||chr(22)||chr(23)||chr(24)||chr(25)||chr(26)||chr(27)||chr(28)||chr(29)||chr(30)||chr(31)||'test non-ascii print'||chr(0)||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)); Thanks Bharath

Hi, As mentioned in the question, my ECS tasks cannot connect to my RDS. The ECS tasks try to resolve the rds by name, and it resolves to the RDS public IP (RDS has public and private IPs). However, the security group on RDS doesn't allow open access from all IPs so the connection fails. I temporarily allowed all connections and could see that the ECS tasks are routing through the open internet to access the RDS. Reachability Analyzer checking specific tasks' Elastic Network Interface to the RDI ENI is successful, using internal routing through the peering connection. At the same time I have another server on VPC C that can connect to the RDS. All the config is similar between these two apps, including the peering connection, security group policies and routing tables. Any help is appreciated Here are some details about the VPCs VPC A - 15.2.0.0/16 [three subnets] VPC B - 111.30.0.0/16 [three subnets] VPC C - 15.0.0.0/16 [three subnets] Peering Connection 1 between A and B Peering Connection 2 between C and B Route table for VPC A: 111.30.0.0/16 : Peering Connection 1 15.2.0.0/16: Local 0.0.0.0/0: Internet Gateway Route table for VPC C: 111.30.0.0/16: Peering Connection 2 15.2.0.0/16: Local 0.0.0.0/0: Internet Gateway Security groups allow traffic to RDS: Ingress: 15.0.0.0/16: Allow DB Port 15.2.0.0/16: Allow DB Port Egress: 0.0.0.0/0: Allow all ports When I add the rule: 0.0.0.0/0 Allow DB Port to the RDS, then ECS can connect to my RDS through its public IP.

Is there a way to set autocommit off at the RDS level. Need to do that to make all the Java code written for the Oracle database to work with out major modifications. There are triggers that are fired based on the transaction ID generated at the beginning of the transaction. Since autocommit is on (as a default) triggers are not able to find the transaction.

I'm looking into Cloudtrail Lake and need tips/help on regarding queries. The given Query returns records as expected, however I need to queries where todays date is taken into consideratio,. This without having to rewrite the eventTimes dates every time. Is there a function like **now()**, **current_date()** etc? I see that supported date and time functions are using Presto 0.266 syntax, but are not able to find a solution to this issue https://docs.aws.amazon.com/awscloudtrail/latest/userguide/query-limitations.html ``` SELECT awsRegion, recipientAccountId, count(* ) as numRec FROM $EDS_ID WHERE eventTime >= '2022-05-01 00:00:00' #I want something like now() - 7 days and eventSource = 'states.amazonaws.com' ```

Hi, today is 05/02/2022 this document : https://aws.amazon.com/rds/faqs/?nc1=h_ls says: Q: What happens when an Amazon RDS DB engine version is deprecated? When a minor version of a database engine is deprecated in Amazon RDS, we will provide a three (3) month period after the announcement before beginning automatic upgrades. At the end of this period, all instances still running the deprecated minor version will be scheduled for automatic upgrade to the latest supported minor version during their scheduled maintenance windows. We are using PostgreSQL 10.14 (deprecated). run upon some db.m5.2xlarge and db.t2.micro instances. Does the above statement mean AWS have a process in place to automatic upgrade our PostgreSQL from 10.14 to 10.17 without human interaction? I just ran a select version() and the output still says 10/14 : PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit Will AWS upgrade from deprecated minor version to highest non-deprecated minor it could find (which is 10/20) or the lowest non-deprecated minor version (which is 10.17)? When was the exact announced date for 10.14 deprecation? Is there any web page to show exact deprecated date for all deprecation version? Or how could I find such information?

Hi, We are not using RDS Servless but dedicated instance: We are deploying a new environment with PostgreSQL 10.14 on db.m5.xlarge with multi-zone and got the following error. We are using terraform to deploy all our resources: creating DB Instance: InvalidParameterCombination: Cannot find version 10.14 for postgres status code: 400, request id: 5c429ea1-4668-4b15-a43f-cd3e07ed09e3 I double checked here : https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html and it said db.m5.xlarge does support Postgres 10.4 above. However, 10.14 is deprecated. Is that the reason we got "cannot find version 10.14 for Postgres"? this command : aws rds describe-db-engine-versions --default-only --engine postgres only shows version 13 and 14. Are these the only 2 supported by AWS currently? I am actually curious about the most effortless upgrade path if we need to upgrade. this page : https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-versions.html#postgresql-versions-version10 seems indicating we can upgrade from 10.14 to 10.20. Is Amazon going to support 10.x for long time? Or it's wiser to upgrade to 14.x? Thank you!

I was trying to perform an upgrade to a snapshot-restored instance (dry run) of a client's RDS Postgres 12.8 database, using the RDS console. The upgrade process failed, during `pg_dumpall`, with the following error: ``` error: schema with OID 12245845 does not exist ``` I did some investigation -- first I ran `select oid, nspname from pg_namespace where oid= 12245845` and got 0 rows returned. Then I started going through the list of pg_catalog tables, finally getting a hit when I ran `select * from pg_proc where pronamespace = 12245845`. So there's a row in `pg_catalog.pg_proc` referencing a non-existent namespace. If this was a non-RDS instance, I could delete the row in `pg_catalog.pg_pg_proc`, but I can't seem to do that in RDS using the master user. I tried manually dumping the database (using `pg_dumpall` and `pg_dump`) but I can't seem to get around this non-existing schema error. Is there any hope, or am I looking at a complete re-install and restoration of data from another source? Many thanks for any ideas or pointers, --Gary

How can I get started with migrating to Babelfish for PostgreSQL from SQL Server, and obtain the training, tips and tricks needed to be successful with my migration?

Trying to migrate existing data from AWS RDS Postgres to AWS managed OpenSearch, but it is not working, no rows were migrated to opensearch, When checking the Cloudwatch log getting below error Bulk request failed. no retry. TotalRecordCount 4080, FailedRecordCount 4080 [1026400] (elasticsearch_bulk_utils.c:181) DMS has the following configuration: { "TargetMetadata": { "TargetSchema": "", "SupportLobs": false, "FullLobMode": false, "LobChunkSize": 0, "LimitedSizeLobMode": false, "LobMaxSize": 0, "InlineLobMaxSize": 0, "LoadMaxFileSize": 0, "ParallelLoadThreads": 5, "ParallelLoadBufferSize": 100, "BatchApplyEnabled": false, "TaskRecoveryTableEnabled": false, "ParallelLoadQueuesPerThread": 0, "ParallelApplyThreads": 0, "ParallelApplyBufferSize": 100, "ParallelApplyQueuesPerThread": 0 }, "FullLoadSettings": { "TargetTablePrepMode": "DO_NOTHING", "CreatePkAfterFullLoad": false, "StopTaskCachedChangesApplied": false, "StopTaskCachedChangesNotApplied": false, "MaxFullLoadSubTasks": 8, "TransactionConsistencyTimeout": 600, "CommitRate": 50000 }, "Logging": { "EnableLogging": true, "LogComponents": [ { "Id": "TRANSFORMATION", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "SOURCE_UNLOAD", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "IO", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "TARGET_LOAD", "Severity": "LOGGER_SEVERITY_DETAILED_DEBUG" }, { "Id": "PERFORMANCE", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "SOURCE_CAPTURE", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "SORTER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "REST_SERVER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "VALIDATOR_EXT", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "TARGET_APPLY", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "TASK_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "TABLES_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "METADATA_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "FILE_FACTORY", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "COMMON", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "ADDONS", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "DATA_STRUCTURE", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "COMMUNICATION", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "FILE_TRANSFER", "Severity": "LOGGER_SEVERITY_DEFAULT" } ], "CloudWatchLogGroup": null, "CloudWatchLogStream": null }, "ControlTablesSettings": { "historyTimeslotInMinutes": 5, "ControlSchema": "", "HistoryTimeslotInMinutes": 5, "HistoryTableEnabled": true, "SuspendedTablesTableEnabled": false, "StatusTableEnabled": true, "FullLoadExceptionTableEnabled": false }, "StreamBufferSettings": { "StreamBufferCount": 3, "StreamBufferSizeInMB": 8, "CtrlStreamBufferSizeInMB": 5 }, "ChangeProcessingDdlHandlingPolicy": { "HandleSourceTableDropped": true, "HandleSourceTableTruncated": true, "HandleSourceTableAltered": true }, "ErrorBehavior": { "DataErrorPolicy": "LOG_ERROR", "EventErrorPolicy": null, "DataTruncationErrorPolicy": "LOG_ERROR", "DataErrorEscalationPolicy": "SUSPEND_TABLE", "DataErrorEscalationCount": 0, "TableErrorPolicy": "SUSPEND_TABLE", "TableErrorEscalationPolicy": "STOP_TASK", "TableErrorEscalationCount": 0, "RecoverableErrorCount": -1, "RecoverableErrorInterval": 5, "RecoverableErrorThrottling": true, "RecoverableErrorThrottlingMax": 1800, "RecoverableErrorStopRetryAfterThrottlingMax": true, "ApplyErrorDeletePolicy": "IGNORE_RECORD", "ApplyErrorInsertPolicy": "LOG_ERROR", "ApplyErrorUpdatePolicy": "LOG_ERROR", "ApplyErrorEscalationPolicy": "LOG_ERROR", "ApplyErrorEscalationCount": 0, "ApplyErrorFailOnTruncationDdl": false, "FullLoadIgnoreConflicts": true, "FailOnTransactionConsistencyBreached": false, "FailOnNoTablesCaptured": true }, "ChangeProcessingTuning": { "BatchApplyPreserveTransaction": true, "BatchApplyTimeoutMin": 1, "BatchApplyTimeoutMax": 30, "BatchApplyMemoryLimit": 500, "BatchSplitSize": 0, "MinTransactionSize": 1000, "CommitTimeout": 1, "MemoryLimitTotal": 1024, "MemoryKeepTime": 60, "StatementCacheSize": 50 }, "PostProcessingRules": null, "CharacterSetSettings": null, "LoopbackPreventionSettings": null, "BeforeImageSettings": null, "FailTaskWhenCleanTaskResourceFailed": false, "TTSettings": null } Opensearch have index with following settings { "settings": { "index.max_ngram_diff" :8, "analysis": { "analyzer": { "my_ngram_analyzer": { "type": "custom", "tokenizer": "standard", "filter": [ "lowercase", "mynGram" ] } }, "filter": { "mynGram": { "type": "nGram", "min_gram": 6, "max_gram": 14, "token_chars": [ "letter", "digit", "whitespace", "symbol" ] } } }, "number_of_shards": 6, "number_of_replicas": 1 }, "mappings" : { "properties" : { "created_at" : { "type" : "date" }, "id" : { "type" : "long" }, "name" : { "type" : "text", "analyzer":"my_ngram_analyzer" , "search_analyzer": "my_ngram_analyzer" }, "phone" : { "type" : "text", "analyzer":"my_ngram_analyzer" , "search_analyzer": "my_ngram_analyzer" }, "updated_at" : { "type" : "date" } } } } I have tried to insert a sample document using _bulk API on opensearch console and it worked, below is the thing I had tried over opensearch, which worked POST _bulk {"index":{"_index":"contacts"}} {"name": "name","phone" : "11111111","created_at" : "2021-12-21T12:12:59","updated_at" : "2021-12-21T12:12:59","id": 101}

I have created a job to migrate my **Postgres data to S3**, I am implementing full load right now, the table consists of a ** lot of records**(count-17496724), that is why I added 10 workers along with **auto scaling** option checked, but i keep getting this error. The job continues to run for long and does not generate any output. Have tried other combination of worker numbers too like 5,10, but same error. Below are the errors from the Logs: 1. ERROR [dispatcher-CoarseGrainedScheduler] scheduler.TaskSchedulerImpl (Logging.scala:logError(73)): Lost executor 1 on 10.0.4.209: Remote RPC client disassociated. Likely due to containers exceeding thresholds, or network issues. Check driver logs for WARN messages. 2. ERROR [dispatcher-CoarseGrainedScheduler] scheduler.TaskSchedulerImpl (Logging.scala:logError(73)): Lost executor 2 on 10.0.4.209: Remote RPC client disassociated. Likely due to containers exceeding thresholds, or network issues. Check driver logs for WARN messages. **About the job:** Creating JDBC connection to the Postgres database and writing the data into S3 as parquet files How do I perform the load, I need to add the incremental logic later.

Can I enable the [Babelfish ](https://aws.amazon.com/rds/aurora/babelfish/) capability in Aurora Serverless? (Babelfish allows support for the SQL Server dialect.)

We use terraform for creating the infra in AWS. We want to create RDS Aurora Postgres instance. Terraform has support to create it. But I am stuck at table creation. There is a line of thought that the Table creation is not part of infra, but for me it looks like part of infra (dynamo db tables are created in terraform). We used terraform local exec command as explained in this [link](https://stackoverflow.com/questions/49563301/terraform-local-exec-command-for-executing-mysql-script) . But the concern is that RDS instances are kept in private subnet, not able to connect directly. Doing SSH tunnelling to execute the table creation script has security issues. Would like to understand from the community how does it is handled ? what are the guidance on this issue ? or or Any leads and help on this will be super helpful.

I am trying to load data from an EC2 instance into Redshift tables but cannot figure out how to do this using the copy command. I have tried the following to create the sql queries: ``` def copy_query_creator(table_name, schema): copy_sql_template = sql.SQL("COPY {table_name} from stdin iam_role 'iam_role' DATEFORMAT 'MM-DD-YYYY' TIMEFORMAT 'MM-DD-YYYY HH12:MI:SS AM' ACCEPTINVCHARS fixedwidth {schema}").format(table_name = sql.Identifier(table_name),schema = schema) return copy_sql_template ``` and ``` def copy_query_creator_2(table_name, iam_role, schema): copy_sql_base = """ COPY {} FROM STDIN iam_role {} DATEFORMAT 'MM-DD-YYYY' TIMEFORMAT 'MM-DD-YYYY HH12:MI:SS AM' ACCEPTINVCHARS fixedwidth {}""".format(table_name, iam_role, schema) print(copy_sql_base) return copy_sql_base ``` where schema is the fixedwidth_spec in the example snippet below: ``` copy table_name from 's3://mybucket/prefix' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' fixedwidth 'fixedwidth_spec'; ``` The function that uses the query created looks like so: ``` def copy_query(self, filepath): schema = Query.create_schema() #returns the formatted fixedwidth_spec table_name = Query.get_table_def() #returns the table_name print(copy_query_creator_2(table_name, iam_role, schema)) self.connect() with self.connection.cursor() as cursor: try: with open(filepath) as f: cursor.copy_expert(copy_query_creator_2(table_name, iam_role, schema), f) print('copy worked') logging.info(f'{copy_query_creator_2(table_name, iam_role, schema)} ran; {cursor.rowcount} records copied.') except (Exception, psycopg2.Error) as error: logging.error(error) print(error) ``` The two attempts return errors. The first returns 'Composed elements must be Composable, got %r instead' while the latter returns 'error at or near STDIN'. Please help.

Hi, I have a manual snapshot created from a DB instance running on PostgreSQL 10.20 engine. I want to use this snapshot for creating a new DB instance that will run on PostgreSQL 14.2 through Cloud Formation. Since this snapshot is created from a different PostgreSQL version, I need to upgrade it first to 14.2 before I initiate the create operation (the stack creation fails otherwise). The snapshot upgrade fails as well with: ``` errorCode: InternalFailure errorMessage: An unknown error occurred ``` I was upgrading the snapshot through AWS CLI (``aws rds modify-db-snapshot`` command) and AWS console, but in both cases, it fails the same way.

Is it possible to force PostGres Database from 9.6.23 to 10.x even though receiving a must be upgraded to 12 or higher? Message: "The RDS for PostgreSQL 9.6 end-of-life date is approaching. Your database is using a version that must be upgraded to 12 or higher as soon as possible. We plan to automatically upgrade RDS for PostgreSQL 9.6 databases to 12 starting April 26, 2022."

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?

I have QuickSight and an RDS(postgres) instance, both in the same region (US-West-2). I also have a valid VPC Connection (checked in the "Reachability Analyzer") between my QuickSight ENI and the RDS ENI. My Goal is to connect QuickSight to the RDS DB, so I can use the DB as a QuickSight Data Source. However, when I go to create a dataset in QuickSight, I am unable to validate the connection. The error I get back is a Postgres authentication error (copied below). I've verified that: - The VPC Connection is valid - The DB username/password is correct - Security Groups are configured as specified in AWS Documentation (gone over these multiple times) - I can interact with the DB otherwise (via application) Error message after failed connection validation: ``` The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver. ```

I have a QuickSight Enterprise instance that produces a timeout error when I try to validate the connection to both RDS(Postgresql) and OpenSearch instances on my vpc. I have setup the sg inbound and outbound rules, the role policies, and am able to auto discover both the RDS and OpenSearch instances. So, what am I missing?

I would like to connect to the RDS postgres database with Glue script written in scala, I have a postgres driver located in S3 and added in script in Dependent JARs path. After running the script it fails with the message `Glue ETL Marketplace - Retrieved no ETL connector jars, this may be due to no marketplace/custom connection attached to the job or failure of downloading them, please scroll back to the previous logs to find out the root cause. ` I cannot find any related logs helping me to understand why there is a problem and I receive a Compilation error.

When will AWS enable creating Database Migration Service (DMS) endpoint for PostgreSQL version 14.0 and 14.1?

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.

I would like to transfer data from postgres RDS database tables to a new reporting database, also created as a postgres RDS. I created data catalogs and created a script that should join two tables together and then save data to a reporting database. It is working as intended only when run for the first time - it saves current data to new database. I need to update the reporting database daily with newly added records to a basic database, but it is not saving new data for me. Is there any way to insert only new data into a db with AWS Glue?

Saw that a PostgreSQL RDS is now in 10.18 version. It was in 10.14 version. Do not know how to track when this upgrade occurred.

Hi, I am forwarding below the error presented when uploading the power bi via directquery with the PostrqSQL database stored in RDS. Error: An error happened while reading data from provider: 'A connection attmpt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond'

Our application stores metadata in postgres database. We are using AWS ebs volumes for postgres data for persistent storage. We attach and detach this EBS volumes to AWS instance when needed. AWS instance is destroyed when not needed and will create new one when needed. EBS volume will be attached when new instance is created. The problem is, when ebs volume is detached postgres version is 9.x. While attaching EBS volume to new instance, postgres version in the new instance is 11.x. How to migrate the postgres data from 9.x to 11.x in the ebs volumes where new instance has 11.x version postgres version installed.

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

We have a scenario where we have multiple database instances and we need to add read replica and failover. We understand that it will be easy if we can have a New Cluster created and databases (writer and reader) within the newly created cluster and configure the failover priorities. Then take the snapshot of the old database and restore it to the newly created database with the cluster Is there any way to avoid the above steps? Can we link the existing database instance to the new cluster?

Hello, I would like to reduce the threshold from 0.3 to 0.1. The parameter pg_trgm.similarity_threshold doesn't exist in any parameter group and due to superuser issues I can't alter the database and change this parameter permanently. I can see that the pg_trgm extension is installed but the parameter is missing.

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 a begginer in AWS and I'm using the free tier to learn AWS, and I´ve tried with one instance database. A few days ago, there are some little costs with GB/month gp2 storage running PostgreSQL. What's the difference between free tier GP2 storage and gp2 storage running PostgreSQL?

Hi, I´m doing some reasearch to find the best place to centralize lots of data logs generated by my application considering pricing ,performance and scalabilty. Today all my application data including logs are stored on an Oracle database, but I´m thinking to move all the LOG related data outside it to reduce it´s size and not to worry about storage performance etc... Just put everything on a "infinite" storage apart from my actual database using CDC or a regular batch process **Below are some needs:** - Only inserts are necessary (no updates or deletes) - Customers will need access to this historic data - Well defined pattern of access (one or two indexes at maximum) - Latencies of few seconds is ok - Avoid infrastrucure, DBA, perfomance bottleneck log term... - Infinite Retentiton period (means I don´t want to worry about performance issues, storage size in long term. But something that can handle a few terabytes of data ) **Use case example: ** Historical Sales order by items ( id_item | id_customer | qty_sold | date_inserted ... ), aprox 50 millions records per day Where I would need to see the historical data by item, and by customer for example (two dimensions) I´ve done some research with the options below **S3 + Athena **-> Put everthing on s3, no worries about infrastructure perfomance issues, however as I need query by item and customer, probably it´would be necessary to break files by item or customer , generate millions of partitions to avoid high costs searching on every file etc.. **Postgre** -> Not sure if could be performance bottleneck once tables gets too big even with partition strategies **DynamoDB **-> Not sure if it´s a good alternative to historical data regarding pricing once seconds latency is ok **MongoDB/ DocumentDB **-> Not very familiar with it (I´d prefer SQL language type) but I know it´s has a good scalability **Cassandra**-> dont´know very much **Timeseries db as influxDB, timestream etc..**-> dont´know very much, but it seems appropriate for timeseries What option would you choose ? Sorry in advance if I saying something wrong or impossible :) Thank you!

Hello, What is the recommended way to create a snapshot (for backup) of an RDS Postgres instance to apply schema changes? We want to maintain both replicas in sync, minimize downtime and keep the new replica up to date. We want to create and keep a snapshot in case we need to rollback these schema changes. I have made research and found https://aws.amazon.com/getting-started/hands-on/move-to-managed/migrate-postgresql-to-amazon-rds/ not sure if this feature can be used for this purpose. Will live schema changes break the migration? Our schema changes are simple, we are adding nullable new columns to existing tables and we are also creating new tables. Thank you in advance.

I have a Postgres 12.7 instance with IOPS SSD (io1) with 6500 IOPS provisioned but my instance can only reach 3500 ( aside from burst ). I have another instance that can reach 6500. Is there an account/region limit on IOPS?

When we are attempting to upgrade the storage on our RDS instance from 2200GB to 2400GB we are met with the following error. This is strange as I thought there were no storage limitations in an upgrade for non-autoscaling storage. ``` InvalidParameterCombination: Invalid storage size for engine name postgres and storage type gp2: 2400 ``` What are the minimum values we can upgrade an RDS instances storage by?

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 ```

Hi Hoping to get some assistance. I'm new to AWS and Redshift, and having some trouble connecting to Redshift using Python. I've been using the below code (I've added X's to things that I think should be private) and running it in Python. ``` import psycopg2 con = psycopg2.connect( dbname = "dev", host= 'reddit-redshift-cluster.XXXXXXXX.eu-west-2.redshift.amazonaws.com', port= "5439", user= "awsuser", password= "XXXXXXX") ``` This is the error I'm getting outputted: ``` psycopg2.OperationalError: connection to server at "reddit-redshift-cluster.XXXXXXXXX.eu-west-2.redshift.amazonaws.com" (X.X.X.X), port 5439 failed: Operation timed out Is the server running on that host and accepting TCP/IP connections? ``` I've tried suggestions online. For example, I've modified publicly accessible setting to Enabled. I've also played around with the Inbound and Outbound rules for the VPC security group (although truthfully, I'm not sure how all this works). Nothing seems to work. Should also note that I set up my cluster with the free trial option, if that changes anything.

I'm trying to create an RDS proxy for my publicly accessible RDS Postgres (db.t4g.micro) instance. When I try to select my DB instance in the "Choose the RDS DB instance ..." input, there are no selectable options (see [screenshot](https://i.imgur.com/126125h.png)). Would anyone know why this might be?

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?

When trying to create a docker i get the following errors. Is there any way to fix this? An error occurred (InvalidParameterCombination) when calling the CreateDBInstance operation: RDS does not support creating a DB instance with the following combination: DBInstanceClass=db.t2.micro, Engine=postgres, EngineVersion=13.4, LicenseModel=postgresql-license. For supported combinations of instance class and database engine version, see the documentation.

Hi Hivemind, I am running a data-crunching workload on DB.t3. medium RDS instance (with a 600GiB gp2 volume and PostGres Engine). I have observed that the EBS byte balance quickly reaches zero and remains at zero throughout the workload execution. Could someone let me know the effect of this on the workload? Will it degrade the rate of the read /write operations and eventually slow down the DB queries? Please note that IOPS(at 4000), EBS IO Balance (at 100), and CPU credit balance (at 600) remain healthy throughout the execution.

As we are forced to upgrade PostgreSQL 9.5 to 12.5, I have activated the maintenance upgrade which has started to work and also took the instance down for a while. After finishing it change status to Available however PostgreSQL version was still 9.5. There's no way for seeing what went wrong. What should we do now?

Hello, I inherited a QuickSight Dashboard, Analysis and Dataset from a colleague who is no longer with the organization. The dataset is based on a **custom SQL** pointing to a PostgreSQL database. I was granted ownership of the dataset. However, I am unable to refresh it because I receive the following error: **"You can't execute the custom SQL query because you don't have sufficient permissions to connect to the underlying data source."** I am perplexed, I do have access to the data source and it is a shared data source. What do I need to do to get the dataset to refresh? Thank you.

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?

Hello everyone, I discovered a problem during a Redshift Spectrum `SELECT` query that works as expected when it's ran inside the RDS (Postgres) DB. The query is like the following: ``` SELECT MIN(column) FROM table; ``` When ran inside the RDS, the result is correct, when ran via Redshift Spectrum it raises the following exception: ``` SQL Error [XX000]: ERROR: Decimal input parse error Detail: ----------------------------------------------- error: Decimal input parse error code: 25101 context: str:0.5507626528024723909068 query: 10256 location: federation_fetchers.cpp:194 process: query0_121_10256 [pid=8648] ----------------------------------------------- ``` The data type is set to `NUMERIC` in the RDS DB. Please, notice there is no `0.5507626528024723909068` value in the table. I can't find any similar question anywhere and I would be really grateful if anyone had any advice on this. Thank you in advance!

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

We have a stored procedure that has been running without issue for months in PG12.7 on RDS. The procedure is shown below. Last night around 6PM MT this procedure stopped working. The error throw is ERROR: invalid transaction termination. Nothing around our database configuration or schema has changed. Our RDS instance is no longer capable of running any commits inside a procedure, even from known good code. We've even scraped the web to find examples of simple commit procedures that work and none do. Is it possible some patch was applied behind the scenes that incapacitated our ability to run these procedures? Seems like a stretch but it's all ive got. ``` CREATE OR REPLACE PROCEDURE public.me_cpro_session_insert(integer) LANGUAGE plpgsql AS $body$ begin insert into me_cpro_session(patient_guid,last_name,first_name,id_session,start_date,end_date,is_complete,instrument,question,position,choice,range_choice,score,instrument_score,appointment_guid) select p.id_external as patient_guid, p.last_name, p.first_name, s.id as id_session, s.start_date, s.end_date - interval '5 hours' as end_date, s.is_complete, qs.name as instrument, t1.text as question, lq.position, t2.text as choice, r.range_choice, c.score, sc.score as instrument_score, s.id_appointment from response r join session s on r.id_session = s.id join patient p on p.id = s.id_patient join question_set qs on qs.id = r.id_question_set join link_question lq on lq.id_question = r.id_question and lq.id_question_set = r.id_question_set join question q on r.id_question = q.id left join choice c on r.id_choice = c.id join translation t1 on t1.id_expression = q.id_expression and t1.id_language = 2 left join translation t2 on t2.id_expression = c.id_expression and t2.id_language = 2 left join score sc on sc.id_session = s.id and sc.id_question_set = r.id_question_set where s.id = $1 order by qs.id asc, lq.position asc; commit; END; $body$ ; ```

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.

Does Postgres 14 on RDS support TOAST compression using LZ4? https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION

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 ?

Hi all, I have a stored procedure in aurora MySQL, this stored procedure is called from my aws glue job. Is there any way to generate a notification if the stored procedure fails? So i want to be notified if the invoked stored procedure fails Thanks.

We have an rds postgres instance and a remote postgres instance(on premise) we can only access via SSL connection. To create an SSL dblink connection in postgres one has to define SSL cert file locations on on the client. I haven't been able to find a way to upload SSL cert files to our RDS postgres instance in order to establish a dblink from our RDS postgres instance to remote postgres instance(on premise). Would it be possible to upload SSL cert files from remote instance somehow so that rds instance could have access to them? Please tell me if this is possible or if there are any alternative options. We would rather use a RDS instance than to manage our own on a EC2 instance.

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?

Hello, I ran this query > select name from stv_tbl_perm where temp = 1 and db_id=101618 And I got the following results : > 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@4872747@9932208@ 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@4870525@9932568@ 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@29321685@9570081@ 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@29323365@9570716@ 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@29324913@9571277@ 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@26377403@8951653@ 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@29323903@9571016@ 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@25415007@8951653@ 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@29326447@9571976@ 271020298241:9e77a3c0-58a7-4b33-97da-85bf6241bc48@29309803@9877604@ These table names are not familiar to me, what do they mean? They are temp table created by users?, they are temp tables created by redshift? this format number:text@number@number@ means something?

I have upgraded my Postgresql RDS from 12.7 to 13.5 and I'm able to upgrade the available extensions like **pg_stat_statements**, **ltree**, etc expect for **pg_repack**. **Default version**: 1.4.6 **Installed version**: 1.4.5 When I try to upgrade pg_repack extensions using sql statement > ALTER EXTENSION "pg_repack" UPDATE TO '1.4.6'; **Error message**: > [22023] ERROR: extension "pg_repack" has no update path from version "1.4.5" to version "1.4.6" I'm not sure how to solve this issue with RDS.

Hi All, I had 2 instances of postgresSQL running as part of AWS RDS. I had missed the payment for the last billing cycle n the services got suspended. I paid the bills right now and am able to access other services like s3. However not able to find the db instances in the RDS dashboard. Able to see the last backup for the database was done yesterday. Will the instance be restored or should I create a new one ? Any idea how to go about it ?

I have an RDS instance (postgres, non-aurora) that someone must have set enhanced monitoring for. Now it still works, but it is stuck in a state of "Configuring-enhanced-monitoring" and won't let me modify it to increase the disk space, upgrade the version, or anything. It has been this way for weeks and is not an enormous DB (like maybe 60GB). I can't even reboot it. It just says it can't do that because the instance is not in an available state. Is there any way to get it out of that state?

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 got sudden spike in AWS RDS postgresql db.t3.small instances, it goes through 3000 Read IOPS, chugging all my IOPS Burst Balance until it empty and still stay on 300 IOPS, i've tried rebooting, increasing the gp2 size but no luck, upgrading the instance however partially mitigated the issue, as it doesn't chug all the way through burst balance. Symptoms: - Spike in read IOPS, read throughput, queue depth every 15 minute - Started only 3 days ago - When the spike occur, it also spike the cpu I've tried: - disabling autovacuum, but it only reduce write iops - upgrading, to no avail, I am still on postgresql 10.17 System: - AWS RDS with PostgreSQL 10.17 - some 2000-3000 databases ( we have database for each user ) - 170 GB gp2 usage Monitoring: https://imgur.com/a/tPCDBUC

We have an RDS PostgreSQL read replica which every 15 minutes exactly spikes to 3000 IOPS for 1-2 minutes. It mysteriously started overnight yesterday with no previous indication. There does not seem to be any corresponding queries or jobs touching the primary database nor the read replica which would be causing this. Manual backups happen once nightly. Replication does not show any current lag. This condition does tend to run us out of EBS burst credits eventually. Rebuilding the read replica results in the same condition. We're at a loss as to what we should look at in order to ascertain what's causing the issue. `pg_stat_activity` doesn't show any queries running at that time.

Source: postgres Target: OpenSearch I have a `text` column called `description` in one of my postgres tables. Per the documentation, this data type is mapped to a `NCLOB`. Since OpenSearch does not not offer LOB support, my `description` is missing in my OpenSearch documents. I tried using the mapping rule bellow, but does not seem to be doing anything ``` { "rule-type": "transformation", "rule-id": "3", "rule-name": "3", "rule-target": "column", "object-locator": { "schema-name": "public", "table-name": "jobs", "column-name": "description" }, "rule-action": "change-data-type", "data-type": { "type": "string", "length": 500 } } ``` When i check the logs i see the following ``` Column 'description' is unsupported in table def 'public.jobs' since the LOB support is disabled ``` However, i do have LOB enabled under task settings: ``` "TargetMetadata": { "ParallelApplyBufferSize": 0, "ParallelApplyQueuesPerThread": 0, "ParallelApplyThreads": 0, "TargetSchema": "", "InlineLobMaxSize": 0, "ParallelLoadQueuesPerThread": 0, "SupportLobs": true, "LobChunkSize": 10, "TaskRecoveryTableEnabled": false, "ParallelLoadThreads": 0, "BatchApplyEnabled": false, "FullLobMode": true, "LimitedSizeLobMode": false, "LoadMaxFileSize": 0, "ParallelLoadBufferSize": 0 }, ``` Is that transformation rule supposed to work? Or will any LOB column be skipped because OpenSearch does not have LOB support? Any way to make this work? Thanks!

There is a postgis function called [ST_Transform](https://postgis.net/docs/ST_Transform.html) to transform the coordinates to a different spatial reference system. This function receives the original geometry (from a table), a set of proj parameters and a srid value. Among the proj parameters, there is a parameter which is a file which is used by proj. For instance: SELECT id, ST_Transform( geom, '+proj=tmerc +lat_0=0 +lon_0=15 +k=0.9996 +x_0=2520000 +y_0=0 +ellps=intl +nadgrids="/tmp/example.gsb" +units=m +no_defs', 6708) as geom FROM test.Test The file is /tmp/example.gsb, and the file can be anywhere in the system but it should be accessible by the program. Is there a way to download a file from a S3 bucket locally, even to a temp folder or a specific folder. I saw that is [possible for SQLServer](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/User.SQLServer.Options.S3-integration.html): > The following example shows the stored procedure to download files from S3. `exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::bucket_name/bulk_data.csv', @rds_file_path='D:\S3\seed_data\data.csv', @overwrite_file=1; `

Twice now in the last week I have restored an automated snapshot for a database (Postgres) and chosen "Single Instance" instead of "Multi-AZ" and it has ignored my choice and instead set up the instance as Multi-AZ. Please fix this annoying and costly bug.

When I try to connect to my RDS Postgresql DB I get the following output { "errorMessage": "2022-01-07T13:28:35.428Z 975a92cd-936c-4d1c-8c23-6318cd609bff Task timed out after 10.01 seconds" } The DB is set to public access **Lambda psycopg2 connection code** connection = psycopg2.connect(user=user, password=password, host=host, port=port, database=database) **print(connection)** <connection object at 0x7ff7eb854b90; dsn: 'user=db_user password=db_password dbname=db_name host=rds_host port=5432', closed: 0> **RDS_LAMBDA_SECURITY_GROUP Inbound** [![Here][1]][1] **VPC Route Table Routes** [![Here][2]][2] Also all the subnets are associated to the route table **Lambda_Role permissions** [![Here][3]][3] **VPC Logs** Lots of REJECTED connections, not sure if it is safe to post a print here. Sometimes the connection to the DB is status ACCEPTED but there are a few other with REJECTED status Any idea on why I still can't connect to my DB? [1]: https://i.stack.imgur.com/b84gl.png [2]: https://i.stack.imgur.com/4oIr4.png [3]: https://i.stack.imgur.com/UB3uN.png

I recently upgraded the PostGIS extension on my Postgres RDS instance. The upgrade went well, but the GEOS library is still on an old version. This means that some of the newer features are not available (e.g. ST_ReducePrecision). Output of `SELECT postgis_full_version();` ``` POSTGIS="3.1.4 ded6c34" [EXTENSION] PGSQL="120" GEOS="3.8.2-CAPI-1.13.4" PROJ="6.2.1" GDAL="GDAL 2.3.1, released 2018/06/22" LIBXML="2.9.9" LIBJSON="0.12.99" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER ``` Error message when using one of the newer features: ``` ERROR: Precision reduction requires GEOS-3.9 or higher ``` Is there a way to update the GEOS library?

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?

We have a PostgreSQL DB with single-tenant structure that include around 15k schemas. The data size is around 3 TB. We want to replicate the data of those schemas into one big MySQL multi-tenant DB. Note: all schemas have the same tables' structure, and the same for the target DB. Can DMS help us achieving that?

I am getting the below error when trying to change a Postgres RDS `db.t2.small` instance class in the `us-east-1e` availability zone to ANY/EVERY other instance class. I need to do this before PG 9.6 retires on 1/18/22 as current instance class is not supported for required upgrade. I have read in post below that this could be a misconfiguration on AWS end. If not, when will capacity be added to us-east-1e? https://repost.aws/questions/QUYNaeNfHOTOKZCSopE5IdnA/rds-r-6-g-no-instances-of-the-requested-class-available > We're sorry, your request to modify DB instance <INSTANCE NAME> has failed. Cannot modify the instance class because there are no instances of the requested class available in the current instance's availability zone. Please try your request again at a later time.

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. I do not have even any postgis extension in use, I was wondering if the problem is having other database instances?. I first upgraded from 9.x to 10.18 and everything worked fine, But now I do not know what else to try. The other instances remain in 9.x. Thanks in advance

So, I spun up a RDS PostgreSQL database instance, and chose the 'Not Public' option. The instance was created with DNS name: pg-rds-db-instance-1.caqxluy95kpl.us-east-1.rds.amazonaws.com Since this is not a public instance, I would expect that the DNS name is not publicly resolvable. However this is not the case. I can do a DNS lookup using multiple public websites such as MXTOOLBOX or https://toolbox.googleapps.com/apps/dig/#A/, and the DNS name resolves to an IP in my private subnet (172.16.4.87). This seems odd to me. Is this expected behavior?

We have two separate accounts in different regions. My team works with the destination account and we want a database from source account which has PostgreSQL 11.5 standard Linux Ubuntu installation on EC2. In the destination we are RDS, so we want to essentially migrate from EC2 to RDS cross region and cross account. I know you can create a database in RDS from snapshot. I like that idea but can we create a database from the Postgres EC2 snapshot/data dump? If we can’t then what’s the best solution, the data size is 15 TB. And I have seen the migration from EC2 postgres dump to RDS postgres but I'm not sure if this can be done cross. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.EC2.html

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.

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?

Hi, I am new to Amazon Sagemaker. I noticed that available options to import datasets are through local files, S3, Redshift, Snowflake and Athena only. Is there a way to import PostgreSQL or MySQL or any other NoSQL datasets?

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.

I attempted to configure my RDS postgres instance for CDC using [Setting up an Amazon RDS PostgreSQL DB instance as a source][1] I configured the DMS source endpoint to use a specific slotName. First using a slot that already existed in the DB and then using a slot I created using pg_create_logical_replication_slot. After successfully testing the endpoint to ensure connectivity, I started the replication task with was to load existing data followed by on-going changes. Both times, the replication failed with a fatal error such as the following and no data (even the existing data that should have loaded) gets replicated. Last Error Stream Component Fatal error. Task error notification received from subtask 0, thread 0 [reptask/replicationtask.c:2822] [1020101] Error executing source loop; Stream component failed at subtask 0, component st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y ; Stream component 'st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y' terminated [reptask/replicationtask.c:2829] [1020101] Stop Reason FATAL_ERROR Error Level FATAL What is the configuration I'm missing to enable CDC from RDS Postgres v11.8? I'm configuring DMS to use the RDS master user. A migration existing data only DMS task succeeds on this instance (if the slotName configuration is removed). The relevant CloudWatch log entries appear to be these 2021-02-03T22:03:05 [SOURCE_CAPTURE ]I: Slot has plugin 'test_decoding' (postgres_test_decoding.c:233) 2021-02-03T22:03:05 [SOURCE_CAPTURE ]I: Initial positioning requested is 'now' (postgres_endpoint_capture.c:511) 2021-02-03T22:03:05 [SOURCE_CAPTURE ]E: When working with Configured Slotname, user must specify LSN [1020101] (postgres_endpoint_capture.c:517) 2021-02-03T22:03:05 [TASK_MANAGER ]I: Task - W6AUC5OI3DNFMFUQ6ZDEYYNZ3NBSABQK3HFD2WQ is in ERROR state, updating starting status to AR_NOT_APPLICABLE (repository.c:5101) 2021-02-03T22:03:05 [TASK_MANAGER ]E: Task error notification received from subtask 0, thread 0 [1020101] (replicationtask.c:2822) 2021-02-03T22:03:05 [TASK_MANAGER ]E: Error executing source loop; Stream component failed at subtask 0, component st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y ; Stream component 'st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y' terminated [1020101] (replicationtask.c:2829) 2021-02-03T22:03:05 [TASK_MANAGER ]E: Task 'W6AUC5OI3DNFMFUQ6ZDEYYNZ3NBSABQK3HFD2WQ' encountered a fatal error (repository.c:5194) 2021-02-03T22:03:05 [SORTER ]I: Final saved task state. Stream position , Source id 0, next Target id 1, confirmed Target id 0, last source timestamp 0 (sorter.c:803) Other items in the log appear to be informational to track progress. [1]: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.RDSPostgreSQL.CDC

I found the [link ](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postgresql-s3-export.html#aws_s3.export_query_to_s3) for Postgres to s3 query export, and I wanted to check if I can use this to perform change data capture from RDS to S3.

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.

Does AWS RDS support heterogeneous database links? Examples: 1. Oracle running on RDS with a [database link](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-DATABASE-LINK.html) to Postgres 2. ~~Postgres running on RDS using the [oracle_fwd](https://github.com/laurenz/oracle_fdw) extension~~ Not possible with RDS

I have 2 RDS instances (one mysql and one postgres) and I need to enable encryption after they were already created. The DBs are large, and I am concerned about potential downtime required to create a snapshot, restore the DB, and then complete the warming process. I estimate it would take ~5 hours to create the snapshot (Postgres ~1.1TB) and ~24 hours for the mysql (~4.5 TB). Then there is time to restore from snapshot and warm the DBs, and then they'd need to (I'm assuming) catch up transactions that may be happening during that gap of time. I'm thinking I would need to make sure they have a multi-AZ DB. Then create a snapshot for the DBs, encrypt the snapshot and restore to a new RDS instance. Then, could I use AWS DMS do on-going replication for the DB to catch up the transactions and then flip over to the new DB when there is an available small window for downtime? Is there a better approach or recommendation?

A customer is using RDS PostgreSQL to store their data for a regulated business. The customer is now being asked by the regulator 1) For AWS RDS db roles, are rdsadmin, rdsrepladmin, root DEFAULT roles created by AWS and what are the purposes of these roles? 2) Running `\du` in `psql` shows “Cannot login” in the `Attributes` column for those user `rds_ad`,`rds_iam`,`rds_password`,`rds_replication`,`rds_superuser`, what does it mean? Highly appreciated if anyone could shed us some light how to answer these questions? Are we supposed to disclose these details with regulator/customer? Thanks a lot!