All Questions
Tagged with amazonrds or amazon-rds
985 questions
3
votes
2
answers
4k
views
What's the advantage of sharding in AWS Aurora?
AWS' RDS docs on multi-master Aurora state the following:
In an Aurora multi-master cluster, each shard is managed by a specific DB instance, and a DB instance can be responsible for multiple shards.
...
- 447
5
votes
2
answers
6k
views
What is difference between session and connection in Postgres
I use Postgres in Aws of db.m4.4xlarge instance which has threshold of 16 sessions. Does that mean Postgres only accepts 16 concurrent connections? Is my understanding correct?
How can db.m4.4xlarge ...
- 151
0
votes
1
answer
888
views
Unable to change password in postgres even though I'm logged in as that user [duplicate]
Postgres running in RDS accessible only through an EKS cluster...
thus I have a (generic) pod in our cluster which allows me to access our postgres instance. I need to alter the password of the role I'...
- 103
0
votes
0
answers
522
views
AWS RDS: How do I enable connection for specific IP address?
I've been managing a site for over four years, and initially what I did to allow access to the database for some IP address was just editing an existing Security Group called specificip
This is what I ...
- 101
5
votes
0
answers
570
views
Error with pg_trgm indexes while upgrading postgres on RDS
I want to upgrade the major version of my prod Postgres RDS database, from 11.16 to 12.11.
I restored a snapshot to do a dry run.
When trying to modify the version on AWS, I have the following error:
...
- 101
1
vote
0
answers
162
views
aws documentation for DMS CDC is confusing
I am following this documentation https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Security to enable CDC using logical replication.
I have pglogical ...
- 101
0
votes
2
answers
184
views
postgres 11.3 checksum validation post migration
I am in the process of migrating our AWS RDS postgresql from one account to another with zero downtime. I am going with pg_dump/pg_restore + AWS CDC for ongoing replication.
I am looking for a way to ...
- 101
0
votes
0
answers
1k
views
Evaluating AWS RDS Proxy latency overhead
Evaluating some benchmark tests I noticed that there is latency overhead in the transactions to the database using the service provided by AWS: RDS Proxy.
The tests were performed against a Postgres ...
- 101
0
votes
2
answers
1k
views
Is it possible to fetch AWS RDS MySQL transaction logs?
I have AWS RDS MySQL instance, without general query logs.
Is it possible to get all transaction logs? I understand that MySQL binlog retention time is very short, but obviously, AWS RDS keeps all ...
0
votes
2
answers
2k
views
MariaDB 10.6 issue with blocked queries that can't be killed
We have AWS RDS database, MariaDB 10.6.8, and we are facing some issues with queries being blocked, the main issue is that queries would lock the table and this would cause more blocked queries:
show ...
1
vote
1
answer
144
views
Match binlog position in 2 different MySQL replicas
I'm trying implement MySQL upgrade approach that would require either no downtime at all or it would be counted in seconds. One of the problems is that these databases are RDS MySQL databases so there ...
- 181
0
votes
0
answers
476
views
Fastest way to migrate largish (15 TB) PostgreSQL 11 instance to Aurora 14 in AWS
We have an AWS RDS PostgreSQL 11 cluster (currently version 11.13), in which we have a few databases and some 1500 tables. The RDS cluster is of size "db.r5.xlarge", with 4 vCPUs, 32 GB RAM ...
- 125
0
votes
1
answer
319
views
Why RDS MySQL on 100% CPU is still responding?
I sent an ALTER statement that took a long time and I killed some minutes after and then I noticed that CPU usage is almost 100% (98%, 99%, etc).
The process is still being listed with "Killed&...
- 133
0
votes
1
answer
184
views
AWS RDS MySQL limit connection
We have multiple databases in a single RDS instance. Because of any reason if we have load in 1 database it stops all the databases.
I'm not able to move any of the database because of following ...
- 115
0
votes
0
answers
57
views
Why is a user randomly created in AWS RDS even though the permissions from any host (%) exist?
I have a MySQL database in RDS. There are separate users which are allowed to connect from anywhere. To be more precise, using MySQL Workbench there are different users listed in "Users and ...
- 1
0
votes
1
answer
207
views
About partition tables with Mysql 5.7 in RDS Amazon
I haven't found anything in the official amazon documentation that tells me at least the pros and cons of partitioning tables in non-IAAS environments.
Has anyone of you had this experience, were ...
1
vote
1
answer
2k
views
What is MySQL wait state 'wait/synch/mutex/innodb/lock_mutex'
We have an application (on Amazon RDS MySQL 5.7; db.m5.24xlarge; 96 cores) that takes in a lot of data, in parallel processors+queues. It mostly goes to one table, with primary keys not spreading over ...
- 191
0
votes
1
answer
2k
views
AWS RDS Postgresql and max_worker_processes
AWS RDS Postgresql 12.10
According to https://www.enterprisedb.com/postgres-tutorials/comprehensive-guide-how-tune-database-parameters-and-configuration-postgresql,
max_worker_processes Set this to ...
- 658
0
votes
1
answer
1k
views
Amazon RDS change collate for mysql database in production without downtime
I saw a solution like this below:
create a new table like your source table.
alter that new table the way you want.
insert your data into the new table.
create indexes etc. as needed on the new table....
- 101
-2
votes
1
answer
106
views
AWS TO OCI GOLDENGATE REPLICATION
I want to use oracle Goldengate to my Multi cloud setup database, I'm just wondering if Im required to down my primary database to avoid data discrepancy? or goldengate can sync the data even if my ...
2
votes
1
answer
2k
views
How to exceed max_execution_time when running a mysqldump?
Is there any way to exceed max_execution_time when running mysqldump, without changing global max_execution_time? My database is in AWS RDS and I can't change these parameters for the sake of the ...
0
votes
0
answers
249
views
What are space requirements for the Postgres database major version upgrade?
I'm using the RDS Postgres database and the current version is 11.13 and want to upgrade it to 12.10. My instance storage is 5000 GB and almost all storage is occupied. Now only ~500 GB are available.
...
- 109
1
vote
1
answer
2k
views
Is there a way to set innodb_strict_mode on AWS RDS?
Moving an old app that was set up 8 years ago. I've run mysqldump and now I'm trying to load it into a new MySQL database running on AWS RDS.
There was a mismatch in types. There was a text field that ...
- 113
1
vote
1
answer
812
views
How to Restore to AWS RDS SQL Server from TDE Enabled SQL DB(on-premises) backup file stored in S3
I have a on-premises TDE enabled SQL DB backup file available in S3 along with Cert and Pvt Key files.
How to restore in AWS RDS SQL DB by utilizing the same master key password provided during source ...
- 13
1
vote
1
answer
896
views
AWS RDS Serverless-V2 Postgres restart issues
We have an RDS Serverless-V2 Postgres cluster setup with two instances (one read/write, and the other a read). Both instances are in the same AZ for now, although we plan on moving one to a different ...
- 121
-1
votes
1
answer
101
views
Design database for history data
I want to create a database with history data score. For example in June 2011 the keyword had score 102. In July 2020 the same word had score 203. What would be the best database design for this. I ...
- 115
0
votes
2
answers
1k
views
AWS RDS Sql Server READ_COMMITTED_SNAPSHOT
We're currently running Multi-AZ SQL SERVER (Mirroring) and would like to turn READ_COMMITTED_SNAPSHOT on. It doesn't look like this option is available in the parameter group or rdsadmin stored ...
- 13
0
votes
0
answers
31
views
What issue will i run into for real time data writing and reading with lots of table using RDS PSQL
I have a solution that writes sensor data to a database every minute but I do not think the way we have our database is scalable. We are using one database and each sensor has its own table. We have ...
4
votes
1
answer
4k
views
Why AWS RDS PostgreSQL suddenly consumed all free storage
I've got a AWS RDS PostgreSQL (13.x). The DB had 100 GB storage of which around 30 GB was used. Suddenly, during one day all the free storage was eaten by PostgreSQL (so it seems) and even after ...
- 141
2
votes
0
answers
619
views
Why is this DELETE causing my Aurora MySQL 5.7 instance to crash?
I have a large table (2.3TB of data, 1.9TB index, 33 billion rows). A user recently executed a DELETE statement (32k rows) against the table, which caused the server to eat up all memory and then fail ...
- 143
0
votes
0
answers
682
views
How to reduce disk usage on a RDS Postgres instance after a significant increase following a VACUUM(FULL, ANALYZE, VERBOSE)?
We have a Postgres 13 database that has been slowly but steadily increasing its disk usage on Amazon RDS. Right before a VACUUM(FULL, ANALYZE, VERBOSE) command it was ~300 GB, but afterwards it jumped ...
1
vote
0
answers
3k
views
Dbeaver restore database Error 1277 you need the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege for this operation
I'm attempting to import a .sql file to restore a database(hosted on AWS RDS) however this error keeps coming up.
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, ...
1
vote
1
answer
712
views
How to clear AWS RDS Aurora MySQL local storage?
I got a warning message about running out of AWS RDS local storage.
The free storage capacity for DB Instance: {MY INSTANCE NAME} is low
at 2% of the provisioned storage [Provisioned Storage: 157.36 ...
- 111
4
votes
2
answers
3k
views
MySQL RDS: Drop formerly huge table without freezing DB?
I am using MySQL 5.6 in AWS RDS. I want to drop a table that used to have 8 billion rows.
Hearing that dropping a large table can freeze the database, I instead chose to delete the rows using a loop. ...
- 151
0
votes
1
answer
69
views
php mysql aggregating/ combining queries to reduce db connections
I have a use case where a large number of clients are reporting their operational states every minute using an HTTP connection to a php script.
For each client, I have a row in a mysql table with ...
- 101
1
vote
1
answer
576
views
fivetran replication from PostgreSQL (connector Broken with WAL log error)
PostgreSQL RDS instance having physical replication to multi-AZ, same instance also enabled for Fivetran replication copying data to BigQuery using WAL logical test-decoding.
Multiple issues here:
...
- 21
0
votes
0
answers
1k
views
How to understand and decrease Innodb_rows_read.avg (RDS INNODB)?
I am looking to understand what's behind Innodb_rows_read value and how to decrease it. For some unknown reasons my RDS server has been over 90% of CPU for more than 5 days and after investigation, ...
- 109
3
votes
2
answers
5k
views
How to restore a RDS Postgres instance from a snapshot exported to S3?
Amazon RDS Postgres database are backed up as snapshots automatically. Because of the high storage costs ($0.095 per GB-Month), I want to move them to S3 (Storage Class: Glacier Deep Archive: 0.00099 ...
0
votes
0
answers
576
views
LOAD DATA LOCAL INFILE - server's temporary file storage
While reading docs, I came across following:
When LOCAL is used, the client program reads the file and sends its
contents to the server. The server creates a copy of the file in the
directory where ...
- 121
1
vote
0
answers
633
views
Refresh materialized view on Postgresql 11 on RDS
We are currently on Postgres 11.13 on AWS RDS. I am trying to create a materialized view that takes about 6-7 minutes to run. What is the best way to keep this MV mostly up to date? I was thinking of ...
- 111
1
vote
1
answer
662
views
Is rdstopmgr an AWS RDS user?
I am running postgres on AWS RDS. RDS creates some users on its own, such as rdsadmin and rdsrepladmin. I am familiar with these
However, I also see rdstopmgr, when I query postgres for roles. Google ...
0
votes
0
answers
610
views
How to prevent sequences from getting out of sequence?
I have several sequences for tables (dynamic information and crosswalks) that get out of sync from the table primary keys during testing. While I can fix them, what can I do to prevent the sequences ...
1
vote
0
answers
112
views
Delete Temporary Tables from Abandoned ALTER Query
I had some big tables, where the running ALTER query timed out.
Is there a way to remove the temporary tables created by this ALTER query, as they are taking up a huge amount of storage.
I am on RDS.
...
- 123
1
vote
1
answer
2k
views
Row size too large (> 8126) - 5.7.mysql_aurora.2.10.0
CreateTableConstraintStep ddlList =
(CreateTableConstraintStep) dslContext
.createTableIfNotExists(getDSLTableName(List))
.columns(fields)
...
- 11
1
vote
0
answers
2k
views
AWS Lambda to RDS PostgreSQL connection
Hello fellow AWS contributors, I’m currently working on a project to set up an example of connecting a Lambda function to our PostgreSQL database hosted on RDS. I tested my Python + SQL code locally (...
- 11
3
votes
1
answer
378
views
Using innodb_flush_log_at_trx_commit = 2 with RDS MultiAZ - Safe?
I understand the downsides of using the parameter setting
innodb_flush_log_at_trx_commit = 2
If you want to know more, look at another answer, for example Is it safe to use ...
- 163
5
votes
1
answer
5k
views
How to await the execution of the rds_backup_database stored procedure?
Our team is migrating one of our databases to Amazon RDS MS SQL Server. As part of that, we have to rewrite some of our stored procedures.
We have a stored procedure that performs a database backup to ...
3
votes
2
answers
786
views
How to resolve The instance could not be upgraded because the 'reg*' data type is used in user tables?
I tried to upgrade a Postgres DB Engine from 11.12 to 13.3 on AWS via the admin console in the browser. But the upgrade could not be performed, the error/pg_upgrade_precheck.log shows this message.
...
1
vote
1
answer
4k
views
BACKUP DATABASE permission denied when using AWS RDS
Our team is migrating to the cloud and we have chosen RDS For MSSQL to host our current db server. As part of that, we have a lot of stored procedures that need to be modified. Some of those are ...
0
votes
0
answers
149
views
Unable to create Read replica - AWS RDS SQL Server 2016
I am trying to create a read replica for a Sql Server enterprise Edition (2016 SP2 CU16) but no such option is presented in the drop down. I have gone through all the documentation provided and the ...
