Questions tagged [amazon-rds]

Amazon Relational Database Service is a web service that makes it easy to set up, operate, and scale a relational database in the cloud.

Filter by
Sorted by
Tagged with
56 votes
5 answers
117k views

ERROR: no schema has been selected to create in

I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not ...
user avatar
39 votes
4 answers
42k views

What is the optimal way to upgrade production RDS instance?

I have MySQL small RDS instance as part of my production system and I want to upgrade it to medium instance with provided IOPS. As old-school DBA I'm aware about "add slave; promote to master; ...
user avatar
32 votes
3 answers
53k views

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it takes ...
user avatar
  • 587
30 votes
2 answers
23k views

Amazon RDS for MySQL vs installing MySQL on an Amazon EC2 instance

At work, we host all our webservers on Amazon EC2 and usually have used MySQL databases installed on the same box as our Apache webserver, and communicated with them on localhost. We now face a need ...
user avatar
25 votes
1 answer
20k views

Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?

When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user): postgres=> create database rich1; ...
user avatar
25 votes
3 answers
37k views

GRANT USAGE on all schemas in a database?

I want to GRANT USAGE to a user/role for a given database. The database has many schemas. I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but ...
user avatar
  • 361
21 votes
2 answers
14k views

Does amazon rds PostgreSQL require vacuum?

It isn't clear if auto vacuum is running in rds. Thanks!
user avatar
21 votes
1 answer
10k views

Get postgres snapshot from Amazon RDS

I can not find a simple way to get a copy of the nightly backup I take of my Amazon RDS postgres instance so I can restore it on my local machine to play with. Right now I'm doing a pg_dump when I ...
user avatar
  • 341
19 votes
1 answer
32k views

Multiple databases in same AWS RDS PostgreSQL instance

I have an existing database in a RDS AWS PostgreSQL instance. I need to create one more database in this instance as spinning up a separate cluster will be expensive. There doesn't seem to be any ...
user avatar
18 votes
2 answers
28k views

Should I increase max_connections in AWS RDS t1-micro for MySQL?

I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
user avatar
  • 241
17 votes
2 answers
18k views

Privileges for database owner; application user

Quick version: What command should I issue to enable a database owner to allow it to access tables in this database and can this be done from that owner's account? Longer Version: I am creating a ...
user avatar
16 votes
1 answer
12k views

How to temporarily disable foreign keys in Amazon RDS PostgreSQL?

I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes ...
user avatar
16 votes
3 answers
30k views

How can I determine how many IOPs I need for my AWS RDS database?

I am migrating part of a mysql database into AWS. The data in question is 99% write, and each row has about 1k of varchar fields, a datetime and 4 ints. I estimate that we will need between 20-25k ...
user avatar
  • 1,072
16 votes
3 answers
9k views

Replicate RDS-MySQL to a non-amazon host?

I have a fairly large MySQL (5.1) database running in Amazon as an RDS instance. I'm working on migrating it out of Amazon. To do that smoothly I'd like to set up the new hardware as a read-only ...
user avatar
  • 785
15 votes
2 answers
3k views

Neither DBCC FREEPROCCACHE nor DBCC FREESYSTEMCACHE('SQL Plans') do anything to free CACHESTORE_SQLCP memory

CACHESTORE_SQLCP Sql Plans takes up > 38 GB after a few days. We are already running with "optimize for ad hoc workloads" option on. (Entity Framework and custom reporting creates a lot of ad hocs!) ...
user avatar
15 votes
2 answers
6k views

How can I seamlessly upgrade the major version of an AWS RDS postgres database?

This morning I was involved in upgrading a PostgreSQL database on AWS RDS. We wanted to move from version 9.3.3 to version 9.4.4. We had "tested" the upgrade on a staging database, but the staging ...
user avatar
14 votes
1 answer
6k views

CPU usage constantly high after upgrading from Postgres 9.5 to 9.6 to 10.6

I upgraded my RDS instance (db.t2.medium) from Postgres 9.5 to 9.6 couple of days back. Since the upgrade, the CPU usage was almost 100% all the time. This brought down my application. Presuming that ...
user avatar
14 votes
2 answers
11k views

Why use RDS over EC2 for Postgres?

I have a database currently running on EC2. As I have to move it to a bigger machine the question of using RDS came up. Pricing: I can get an EC2 c3.large instance with two SSDs (16GB each), two ...
user avatar
  • 365
14 votes
1 answer
10k views

PostgeSQL 9.3: Is it safe to stop VACUUM FULL?

I'm using PostgreSQL 9.3 on RDS. Once in a while, I run a VACUUM FULL operation on the database. However, such operation can take quite a while and it blocks other tables, so the need to stop the ...
user avatar
  • 10.5k
13 votes
3 answers
11k views

AWS RDS MYSQL How do I reduce the DB size? [duplicate]

Possible Duplicate: How do I reduce the database size on AWS RDS MySQL? I have a mysql DB on Amazon. It is bigger than I need and I don't want to pay so much. How do I make it smaller? Their ...
user avatar
  • 302
12 votes
2 answers
22k views

Amazon RDS Postgresql adding new extensions

I want to download jsonbx to RDS instance. RDS PostgreSQL Features Supported shows only built-in features. Does this mean there is no way we can install an extension to RDS which is not in the Feature ...
user avatar
12 votes
2 answers
4k views

Does AWS RDS support MEMORY storage engine?

Title says it all. Strangely can't find a result on this.
user avatar
  • 223
12 votes
2 answers
2k views

UPDATE STATISTICS options fail in Amazon RDS SQL Server

In Amazon RDS SQL Server (any version/edition), create a temp table and update statistics on it. This works fine: IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t; GO CREATE TABLE #t (id INT ...
user avatar
  • 41.4k
11 votes
1 answer
15k views

Best way to pg_dump PostgreSQL on RDS to S3

I need to dump my PostgreSQL on RDS to a file on S3 periodically (to use it elsewhere than AWS). Is there a better way than running a script that runs pg_dump and uploads results to S3 using e.g. ...
user avatar
  • 273
10 votes
3 answers
7k views

Why am I getting permission denied on an ownership change?

My current user is pronto mediapop_staging=> select current_user; current_user -------------- pronto (1 row) This user a standard super user created on AWS RDS. mediapop_staging=> \du ...
user avatar
  • 295
10 votes
1 answer
28k views

Too Many database connections on Amazon RDS

We are having problems with users running queries/views in Drupal that occasionally cause our site to freeze. The freeze occurs because the query causes the number of database connections to go up to ...
user avatar
10 votes
1 answer
4k views

Debug query on big table that is sometimes slow

I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries (...
user avatar
9 votes
1 answer
622 views

MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can't Bring It Online

I can take databases offline (via GUI) but can't bring them back online. The server details are as follow: RDBMS: MS SQL 2016 Express Host: Amazon AWS/RDS Free Tier Details/History of the Problem A ...
user avatar
9 votes
2 answers
6k views

Amazon RDS I/O requests - 1 query = 1 billable I/O?

I have an InnoDB database that I'd like to migrate onto Amazon RDS. My current implementation, hosted on my own server, shows roughly 8M queries per month. The RDS site say that I/O Rate $0.10 ...
user avatar
  • 91
9 votes
1 answer
7k views

How to grant set the replication attribute on RDS PostgreSQL?

On the AWS RDS documentation I see that rds_superuser can: Grant and revoke the replication attribute onto all roles that are not the rds_superuser role. For more information, see this section in ...
user avatar
9 votes
2 answers
3k views

Postgres 9.4 wal_level=logical increases disk space

I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance. Database Migration Service requires the wal_level to be set as logical ...
user avatar
9 votes
1 answer
19k views

Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
user avatar
  • 211
9 votes
1 answer
2k views

Why does RDS PostgreSQL always recover on restart?

I am running an RDS instance of PostgreSQL 9.6.3: select version(); returns PostgreSQL 9.6.3 on x86_64-pc-linux-gnu [...]. I have found that after issuing a stop from the RDS console followed by a ...
user avatar
8 votes
2 answers
9k views

MySQL commits taking time

We are facing an issue, where at certain times there are number of commits that takes a more than a second. The time interval for this is not fixed. Our application sends thousands of commits but at ...
user avatar
  • 195
8 votes
2 answers
21k views

Does ALTER TABLE - ADD COLUMN lock the table?

I have a question about the ALTER TABLE ... ADD COLUMN DDL statement. On a Amazon RDS instance with MariaDB v10.2, I've noticed that INSERT statements complete and the rows are correctly inserted in ...
user avatar
  • 1,225
8 votes
3 answers
7k views

What can cause a rapid drop in RDS MySQL Database free storage space?

How could my MySQL database on Amazon RDS have recently gone from 10.5 GB free to "storage-full" status within about 1.5 hours? It's a 15GB MySQL 5.6.27 database running on a db.t2.micro instance. ...
user avatar
8 votes
2 answers
6k views

MySQL on RDS, data transfer from one instance to another as a production job

I have a MySQL RDS instance ("production") from which I need to copy incremental data to another MySQL RDS instance ("reporting"). “Incremental data” being the new rows in certain tables. Currently ...
user avatar
  • 181
8 votes
1 answer
3k views

Refresh Materialized View impact on the DB

Hi we are running an PostgreSQL 9.6 database in Amazon RDS under a m4.large(2cpu 8gb) and a provisioned IOPS of 1000. The use case is the following: We have a table with several million of registries (...
user avatar
  • 767
7 votes
2 answers
6k views

AWS DMS creates tables with no auto_increment

I am migrating my databases to Amazon's RDS using their DMS tool. Pretty much everything looks great except that columns that were marked as auto_increment on the source database did not get marked ...
user avatar
  • 103
7 votes
1 answer
2k views

What does KEY SHARE OF in a PostgreSQL statement mean?

Given the following SQL, what does "ONLY", "OPERATOR(pg_catalog.=)" and "FOR KEY SHARE" do? SELECT 1 FROM ONLY "public"."acmeinstanceinfo" x WHERE &...
user avatar
  • 463
7 votes
3 answers
7k views

Is there any way to replace sql server file_guid with new GUID?

I have two databases with same file_guid. Getting following error when trying to restore second database in Amazon RDS. Aborted the task because of a task failure or a concurrent RESTORE_DB ...
user avatar
  • 213
7 votes
1 answer
3k views

Diagnosing high write throughput on RDS Postgres

I'm using Postgres 9.6.1 on RDS, and seeing orders-of-magnitude higher write throughput than I expect to see. I'm seeing a WriteThroughput around 40,000,000 Bytes/Second. I've looked at the ...
user avatar
  • 271
7 votes
3 answers
7k views

MySQL in AWS RDS 100% CPU on some queries

I have a managed MySQL (5.7.19) instance on AWS. In general, things work pretty well. I'm consistently around 4% CPU usage, and well below my IOPS limits for the burstable instance (t2.micro). ...
user avatar
  • 221
7 votes
1 answer
4k views

Slower queries after upgrade from mysql-5.5 to mysql-5.6

There is lots written about how MySql 5.6 is faster than MySql 5.5 (e.g. http://dev.mysql.com/tech-resources/articles/mysql-5.6.html) but we upgraded our Amazon RDS instance (using the new upgrade ...
user avatar
  • 241
7 votes
2 answers
19k views

Finding reason behind high write IOPS on MySQL (RDS) server

I am having performance issues with an AWS RDS instance (running MySQL 5.6). This is the backend db to an XMPP server called Tigase. According to Tigase Inc. this type of db instance should be able ...
user avatar
  • 173
7 votes
2 answers
8k views

How to maintain Index and Statistics on AWS RDS?

In the process of setting up the SQL Server Index and Statistics Maintenance code on our AWS RDS instance, I've managed to set up the scripts and can run it from SSMS. Please advice on how to setup ...
user avatar
7 votes
1 answer
5k views

Amazon RDS t2.small as a production server

I currently have a m3.medium as production PostgreSQL server but I want to move it to RDS with the Multi-AZ feature. Since the CPU usage of our current server is never higher than 5% and the RAM ...
user avatar
7 votes
2 answers
2k views

How to create INOUT assignment cast on Amazon RDS PostgreSQL? If not possible, then why?

I'm migrating existing application to Amazon RDS PostgreSQL. The application has cross-database support and for compatibility reasons it includes the following SQL: CREATE CAST (varchar AS numeric) ...
user avatar
7 votes
1 answer
12k views

Amazon RDS mysqbinlog "Could not find first log file name in binary log index file"

Preamble: We use RDS at AWS to manage our MySQL database. I know that RDS creates binary logs used for replication.. I want to harvest these binary logs and store them on S3, so that at any time, i ...
user avatar
7 votes
1 answer
996 views

What is the apgcc schema in Amazon RDS Postgresql?

Recently a mysterious new schema called apgcc appeared in every database in our testing Aurora Postgresql cluster (eu-west region). It is owned by the rdsadmin account. What is it, and where did it ...
user avatar

1
2 3 4 5
18