Questions tagged with PostgreSQL

Trying to create a Read Replica for my PG 10 DB fails with `undefined is not an object (evaluating 'S.DBSubnetGroup.VpcId')`. What to do?lg...

Hi, One of my SingleDB instance which is currently working and connected with application. I convert SingleInstance to MultiAZ-Cluster. After that instance is completly disappear from my RDS Console, like i doesn't have any instance. But my application is still working, and all crud operation working fine. Could any one please tell, is it usual, should i wait?lg...

I created a postgres database instance and to connect to it from my terminal, I followed this formula: `psql -h <REMOTE HOST> -U <DB_USER> <DB_NAME>` I inserted my specific info and pressed enter but nothing happened. I got no prompt, error or feedback.lg...

What version of the PostGIS extension is available on the RDS Preview Postgresql 15 database.lg...

Newer versions of Amazon Aurora PostgreSQL-compatible edition are now available and database cluster(s) running Aurora PostgreSQL minor versions 11.12, 12.7, and 13.3 need to be upgraded by March 15, 2023. These newer minor versions include important updates that will improve the operations of your Aurora PostgreSQL instances and workloads. We strongly encourage you to upgrade to at least a recommended minimum minor version at your earliest convenience. * For PostgreSQL Minor Version 11.12, the recommended minimum minor version is 11.17. * For PostgreSQL Minor Version 12.7, the recommended minimum minor version is 12.12. * For PostgreSQL Minor Version 13.3, the recommended minimum minor version is 13.8. Starting on or after 12:00 PM PDT on March 15, 2023, if your Amazon Aurora PostgreSQL cluster has not been upgraded to a newer minor version, we will schedule the relevant recommended minimum minor version to be automatically applied during your next maintenance window. Changes will apply to your cluster during your next maintenance window even if auto minor version upgrade is disabled. Restoration of Amazon Aurora PostgreSQL 11.12, 12.7, and 13.3 database snapshots after March 15, 2023 will result in an automatic upgrade of the restored database to a supported version at the time. How to Determine Which Instances are Running These Minor Versions? * In the Amazon RDS console, you can see details about a database cluster, including the Aurora PostgreSQL version of instances in the cluster, by choosing Databases from the console's navigation pane. * To view DB cluster information by using the AWS CLI, use the describe-db-clusters command. * To view DB cluster information using the Amazon RDS API, use the DescribeDBClusters operation. You can also query a database directly to get the version number by querying the aurora_version() system function i.e., "SELECT * FROM aurora_version();". How to Apply a New Minor Version You can apply a new minor version in the AWS Management Console, via the AWS CLI, or via the RDS API. Customers using CloudFormation are advised to apply updates in CloudFormation. We advise you to take a manual snapshot before upgrading. For detailed upgrade procedures, please see the available User Guide [1]. Please be aware that your cluster will experience a short period of downtime when the update is applied. Visit the Aurora Version Policy [2] and the documentation [3] for more information and detailed release notes about minor versions, including existing supported versions. If you have any questions or concerns, the AWS Support Team is available on AWS re:Post and via Premium Support [4]. [1] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html [2] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.VersionPolicy.html [3] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Updates.20180305.html [4] https://aws.amazon.com/supportlg...

Hi everybody, We are running a postgresql 14.4 with RDS. We want to use the **postgres_fdw** extension which is a supported and available extension according to the AWS documentation (https://docs.amazonaws.cn/en_us/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-14x). But `CREATE EXTENSION postgres_fdw` returns: *SQL Error [42501]: ERROR: permission denied to create extension "postgres_fdw" Hint: This extension is not specified in "rds.allowed_extensions".* What's going on ? How can we use that extension ? best,lg...

Amazon RDS will start deprecating M1, M2, M3, R3, and T1 instances for your PostgreSQL, MySQL, and MariaDB databases from February 6, 2023. All PostgreSQL, MySQL, or MariaDB databases running in Amazon RDS on M1, M2, M3, R3, or T1 instances will be automatically upgraded as per the upgrade path outlined below by RDS, no earlier than February 6, 2023 starting 12 AM UTC: |Existing Instance | Size,Existing Specs | New Instance Size | New Instance Specs | | --- | --- | --- | --- | |db.m1.small | 1 vCPU, 1.70 GB | db.t3.small | 2 vCPU, 2.00 GB | |db.m1.medium | 1 vCPU, 3.75 GB | db.t3.medium | 2 vCPU, 4.00 GB | |db.m1.large | 2 vCPU, 7.50 GB | db.m5.large | 2 vCPU, 8.00 GB | |db.m1.xlarge | 4 vCPU, 15.00 GB | db.m5.xlarge | 4 vCPU, 16.00 GB | |db.m2.2xlarge | 4 vCPU, 34.20 GB | db.r5.xlarge | 4 vCPU, 32 GB | |db.m2.4xlarge | 8 vCPU, 68.40 GB | db.r5.2xlarge | 8 vCPU, 64 GB | |db.m3.medium | 1 vCPU, 3.75GB | db.t3.medium | 2vCPU, 4GB | |db.m3.large | 2 vCPU, 7.5 GB | db.m5.large | 2 vCPU, 8 GB | |db.m3.xlarge | 4 vCPU, 15GB | db.m5.xlarge | 4 vCPU, 16 GB | |db.m3.2xlarge | 8 vCPU, 30GB | db.m5.2xlarge | 8 vCPU, 32 GB | |db.r3.large | 2 vCPU 15.25 GB | db.r5.large | 2 vCPU, 16 GB | |db.r3.xlarge | 4 vCPU, 30.5GB | db.r5.xlarge | 4 vCPU, 32 GB | |db.r3.2xlarge | 8 vCPU, 61GB | db.r5.2xlarge | 8 vCPU, 64 GB | |db.r3.4xlarge | 16 vCPU, 122GB | db.r5.4xlarge | 16 vCPU, 128 GB | |db.r3.8xlarge | 32 vCPU, 244GB | db.r5.8xlarge | 32 vCPU, 256 GB| Migration will be done in the maintenance window of your RDS instance. During this migration, your database may be shutdown and restarted multiple times. If you choose the “Apply Immediately” option, the migration will be initiated immediately after clicking on the “Modify DB Instance” button. If you choose not to apply the change immediately, the migration will be performed during your next maintenance window. Your PostgreSQL, MySQL, or MariaDB databases in Amazon RDS running on M1, M2, M3, R3, or T1 instances will be automatically migrated to an equivalent M5, R5, or T3 instance by RDS, no earlier than February 6th, 2023 starting 12 AM UTC, as per the migration path. To avoid any unplanned disruptions, we recommend that you migrate your PostgreSQL, MySQL, and MariaDB databases in Amazon RDS running on M1, M2, M3, R3, or T1 instances to an equivalent M5, R5, or T3 instance before February 3, 2023. To learn more about the latest instance types supported on RDS, refer to the Amazon RDS user guide [1]. For guidance on modifying your instance class, see the Amazon RDS User Guide [2]. If you have any questions or concerns, the AWS Support Team is available via re:Post [3] and via AWS Premium Support [4]. [1] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html#Concepts.DBInstanceClass.Types [2] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.DBInstance.Modifying.html [3] https://repost.aws [4] https://aws.amazon.com/supportlg...

Hi all, I have setup AWS Athena to query PostgreSQL DB data. However, I encountered **GENERIC_USER_ERROR** when trying to query some data, with following messages:  Full Error Messages: ``` GENERIC_USER_ERROR: Encountered an exception[com.amazonaws.SdkClientException] from your LambdaFunction[arn:aws:lambda:eu-west-1:XXXXXXXXXXXX:function:jdbc_postgres] executed in context[retrieving meta-data] with message[Unable to execute HTTP request: Connect to s3.eu-west-1.amazonaws.com:443 [s3.eu-west-1.amazonaws.com/XX.XXX.XXX.XXX, s3.eu-west-1.amazonaws.com/XX.XXX.XXX.XXX, s3.eu-west-1.amazonaws.com/XX.XXX.XXX.XXX, s3.eu-west-1.amazonaws.com/XX.XXX.XXX.XXX, s3.eu-west-1.amazonaws.com/XX.XXX.XXX.XXX, s3.eu-west-1.amazonaws.com/XX.XXX.XXX.XXX, s3.eu-west-1.amazonaws.com/XX.XXX.XXX.XXX, s3.eu-west-1.amazonaws.com/XX.XXX.XXX.XXX] failed: connect timed out] This query ran against the "XXXXX" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: a01d4534-faad-4f4c-80b6-492c44fac1c4 ``` Since it mentioned about to post this error message, hence posted this error message in AWS re:Post forum. Does anyone have any ideas on how to resolve this issue? Thanks in advance! Best Regards, Jerry Chonglg...

We came across a weird scenario in which the replication for one of our RDS Postgres replicas is terminated per below screenshot. We could not find any root cause for this issue and we were not able to fix the issue yet either.  Here is only log that might be related which we could find within our RDS read replica logs: ``` 000000010002F94800000019 archive /rdsdbdata/log/restore/pg-wal-archive.12472857.* is not yet downloaded, exiting restore script for now 2022-12-11 09:59:59 UTC::@:[29307]:LOG: started streaming WAL from primary at 2F948/64000000 on timeline 1 2022-12-11 09:59:59 UTC::@:[29307]:FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010002F94800000019 has already been removed recovering 00000002.history ``` I was wondering if anyone came across this issue and know how to fix the replication? Thanks in advance! Mehdilg...

I created an Appflow and use RDS Postgres as destination. When doing the fields mapping after setting set the connection to DB, only 4 fields can be selected from RDS Postgres, but there are 20 fields created in that table. I tried to use the different tables but still got this same situation. What possible issue this could be? Thank you!lg...

I have a Lambda Python function connecting via psycopg2 to a PostgreSQL db instance running RDS. The Lambda connects absolutely fine (Lambda and RDS both in EU-West-2 region) I can also connect to the PostgreSQL via PgAdmin4 from a local development system and other developers can also access from other locations/IPs via PGAdmin with no problem. I can also connect a simple psycopg2 connect and query script from my local desktop here. Therefore I know RDS is accepting and responding to externally-sourced psycopg2 connections and queries. HOWEVER, when I upload the same simple connect script to my web server (OVH - based in France if of any relevance), running equivalent Python and psycopg2 etc., the connection fails with the standard psycopg2 error response from the Python: `Error raised: connection to server at "xxxxxxxx.yyyyyyyyyy.eu-west-2.rds.amazonaws.com" (ppp.qqq.rrr.ssss), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections?` I've tweaked the Security Group settings to permit anything from anywhere etc and still no joy. PostgreSQL in the RDS seems to have listening on * which seems necessary to permit connections under certain circumstances. What is the subtlety in the differing sources that means such a connection from the OVH web server won't work; I can't find anything in the docs that seems to link to this issue and there's nothing obvious mis-configured on the server-side.. Any responses gratefully received.lg...

I am seeing my Aurora Postgres sequence values skip by 33 on a semi-consistent basis. I am aware of a thread from the PG mailing list saying that when PG recovers it can cause a sequence skip. Also if a large transaction rolls back then any sequence values updated during that transaction will remain at their new value and won't be rolled back. I get that - its the whole point of a sequence. But in my case nothing is happening and boom tomorrow morning the sequences have skipped ahead by 33. This article discusses other reasons that can cause a sequence skip: https://www.cybertec-postgresql.com/en/gaps-in-sequences-postgresql/ But I am not seeing any of those events. This appears to happen randomly. Anyone else seeing this? I migrated from RDS/Postgres and never experienced this. This just started on the migration to Aurora Postgres.lg...