Questions tagged with PostgreSQL
Content language: English
Sort by most recent
I've been leveraging the pg_cron extension to run scheduled precompute jobs within the Postgres DB for over a year now, and have never had this issue.
This extension comes with a table that logs the jobs that need to be run, as well as a function that allows you to easily create new jobs and insert them into the table. Today, when trying to schedule a new cron job in the Postgres DB, the DB crashed - terminating all existing connection to the DB.
We haven't changed anything in the DB since yesterday, so it is confusing as to why this extension may be related to the crash. I'm hoping someone can help provide some insight.
I have a PostgreSQL RDS database in AWS which is in a free-tier period
I have launched the database for free-tier but still I am getting charged for the RDS every month, which is of the type "$0.131 per GB-month of provisioned gp2 storage running PostgreSQL", why I am getting charged for this, like this charge for the inbound/outbound transfer, database backups or basically what I am exceeding above free-tier limits for which I am charged
Kindly provide me a solution for how I can avoid these charges, I should change the RDS configurations, change the storage type, or any other step I can take so, that I can avoid these charges
Hello,
I have a RDS Postgres used by a lambda. This lambda is used on a front end experience.
Recenlty I reached the limit of connections on the rds.
My concern is that the logs where showing this messages like a 100 times:
2023-02-27 11:00:22 UTC:3.0.139.252(47570):********@********:[23428]:FATAL: password authentication failed for user "*********"
2023-02-27 11:00:22 UTC:3.0.139.252(47570):********@*********:[23428]:DETAIL: Role "********" does not exist.
Connection matched pg_hba.conf line 13: "host all all all md5"
2023-02-27 11:00:45 UTC:3.0.139.252(52788):********@*********:[23453]:FATAL: password authentication failed for user "********"
2023-02-27 11:00:45 UTC:3.0.139.252(52788):*******@********:[23453]:DETAIL: Role "******" does not exist.
Connection matched pg_hba.conf line 13: "host all all all md5"
2023-02-27 11:01:26 UTC:3.0.139.252(58080):********@**********:[23498]:FATAL: password authentication failed for user "**********"
2023-02-27 11:01:26 UTC:3.0.139.252(58080):********@**********:[23498]:DETAIL: Role "*****" does not exist.
Connection matched pg_hba.conf line 13: "host all all all md5"
2023-02-27 11:01:33 UTC:3.0.139.252(39560):********@*******:[23539]:FATAL: password authentication failed for user "**********"
2023-02-27 11:01:33 UTC:3.0.139.252(39560):*********@*******:[23539]:DETAIL: Role "*******" does not exist.
Connection matched pg_hba.conf line 13: "host all all all md5"
This is easier a log issue because the connection limit was reached or either someone trying to bruteforce the database.
Can you confirm that this is link to the connection limit being reached ?
Also now when I look at my connections limit I see pikes like bellow:
But when I look my trafic analytics I don't have huge pikes of views at the same time.
What can this be related to ? What exatcly count as a connection ?
Thank you in advance,
Can we create multiple databases in one instance of Postgres RDS Multi AZ DB Cluster. AWS Console does not allow specifying the initial DB name for this option. If yes, possible, Please share the reference document or CL details
Hi,
we have a small db.t4g.micro RDS (PostgreSQL) instance as a development database and an RDS proxy in front of it.
We started sending traffic constantly to it during the last 12 hours or so and we noticed that the instance restarted on its own twice.
Looking at the monitoring tab in RDS console, we observed that the restart times coincide exactly with the times that EBS Bytes Balance dropped to zero. (CPU credit balance, Burst Balance and EBS IO Balance were all > 0).
Excerpt from the error/postgresql.log:
```
2023-03-01 04:51:55 UTC::@:[375]:LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, total=1.754 s; sync files=0, longest=0.000 s, average=0.000 s; distance=65535 kB, estimate=65535 kB
2023-03-01 04:56:54 UTC::@:[375]:LOG: checkpoint starting: time
2023-03-01 04:56:54 UTC::@:[375]:LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, total=0.639 s; sync files=0, longest=0.000 s, average=0.000 s; distance=65535 kB, estimate=65535 kB
2023-03-01 04:57:21 UTC:172.31.5.233(18341):postgres@dns:[384]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:21 UTC:172.31.5.233(64393):postgres@dns:[386]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:21 UTC:172.31.45.245(10055):rdsproxyadmin@postgres:[31769]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:21 UTC::@:[372]:LOG: received SIGHUP, reloading configuration files
2023-03-01 04:57:21 UTC:172.31.45.245(19305):postgres@dns:[388]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:21 UTC:172.31.5.233(19611):rdsproxyadmin@postgres:[31768]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:21 UTC::@:[372]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2023-03-01 04:57:21 UTC::@:[375]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2023-03-01 04:57:24 UTC::@:[373]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2023-03-01 04:57:24 UTC::@:[373]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2023-03-01 04:57:21 UTC:172.31.5.233(61767):postgres@dns:[383]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:22 UTC::@:[31837]:FATAL: terminating autovacuum process due to administrator command
2023-03-01 04:57:23 UTC::@:[379]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2023-03-01 04:57:21 UTC:172.31.45.245(10079):postgres@dns:[401]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:23 UTC::@:[375]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2023-03-01 04:57:23 UTC::@:[379]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2023-03-01 04:57:21 UTC:172.31.45.245(51481):postgres@dns:[389]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:22 UTC:172.31.5.233(52633):postgres@dns:[385]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:23 UTC:172.31.5.233(17531):postgres@dns:[382]:FATAL: terminating connection due to administrator command
2023-03-01 04:57:24 UTC::@:[377]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2023-03-01 04:57:25 UTC::@:[372]:LOG: received smart shutdown request
2023-03-01 04:57:29 UTC::@:[372]:LOG: background worker "logical replication launcher" (PID 380) exited with exit code 1
2023-03-01 04:57:29 UTC::@:[375]:LOG: shutting down
2023-03-01 04:57:29 UTC::@:[375]:LOG: checkpoint starting: shutdown immediate
2023-03-01 04:57:29 UTC::@:[375]:LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, total=0.308 s; sync files=0, longest=0.000 s, average=0.000 s; distance=65535 kB, estimate=65535 kB
2023-03-01 04:57:31 UTC:127.0.0.1(49550):rdsadmin@rdsadmin:[31839]:FATAL: the database system is shutting down
2023-03-01 04:57:32 UTC::@:[372]:LOG: database system is shut down
```
Should EBS Bytes Balance exhaustion really lead to the database spontaneously restarting?
Thanks,
Nikos
In the CLI doc for [creating db instance](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html) and [creating db cluster ](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-cluster.html), it is mentioned that if publicly accessible flag is not specified explicitly, then the behavior is dependent on DBSubnetGroupName being specified or not. In my case, I specified the DBSubnetGroup which consists of public subnets and yet, the db instances created were not publicly accessible.
Couldnt execute this query without superuser permission
ALTER ROLE user WITH REPLICATION;
Im running a postgres db even I have enabled RDS_SUPERUSER role to that user.
Hello , Im trying to create an index and getting this error .Can't find any solution yet how to rewrite the index creation
msdp_central=> CREATE INDEX i_account_id_search ON t_subscriber USING btree (upper(unaccent((f_account_id)::text)) varchar_pattern_ops, f_unit_pk);
ERROR: functions in index expression must be marked IMMUTABLE
Please help
I followed these steps: [https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-connect-using-iam/]() and was able to login into the RDS postgreSQL instance with a created user and its token. My question is: Is there a way to change that token into a password? I am creating users for that RDS...and would like to provide them passwords. I hope my question make sense. Or if you have suggestions on how to go about this.
I got an email from AWS that my RDS DB instance that was using deprecated PostgreSQL version 9.3.25 and deprecated instance class db.m1.small has now been put in a "failed" state. The email suggests that I should restore it from a snapshot using a new instance class. However, none of the new instance classes seem to allow engine version 9.3.25 and there doesn't seem to be any option to simultaneously upgrade the engine version while restoring.
Is there any way to do this?
Hello,
We plan to, but have not yet migrated to a supported major version of Aurora PostgreSQL since major version 10 was deprecated on January 31. In the [announcement](https://repost.aws/questions/QU0ligxMlJSJWhzzgKbKgytQ/announcement-amazon-aurora-postgre-sql-10-x-end-of-support-is-january-31-2023), it was stated that forced major version upgrades would only be performed during a maintenance window.
If our cluster is marked for automatic upgrade at some point, would this be visible like the usual [pending maintenance actions](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Maintenance.html#USER_UpgradeDBInstance.Maintenance.Viewing)? Would AWS notify us through some other channel? While we are actively preparing to perform this upgrade, we would appreciate knowing when/if it's going to be applied during the maintenance window on any given week.
Thanks!
Hi everyone.
I get a problem with RDS postgresql. It stucks on Security Group (screenshot). Now I cannot change Security Group. May I get help?