All Questions
1,006 questions
69
votes
8
answers
188k
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 ...
- 793
39
votes
4
answers
46k
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; ...
36
votes
3
answers
78k
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 ...
- 637
34
votes
3
answers
43k
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;
...
- 441
32
votes
3
answers
65k
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 ...
- 431
32
votes
2
answers
24k
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 ...
- 1,095
26
votes
1
answer
43k
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 ...
- 565
22
votes
2
answers
21k
views
Does amazon rds PostgreSQL require vacuum?
It isn't clear if auto vacuum is running in rds. Thanks!
- 221
22
votes
1
answer
15k
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 ...
- 11.9k
22
votes
2
answers
12k
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 ...
- 351
21
votes
1
answer
10k
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 ...
- 395
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 ...
- 241
18
votes
2
answers
21k
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 ...
- 293
17
votes
1
answer
17k
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 ...
- 581
16
votes
3
answers
33k
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 ...
- 1,232
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 ...
- 825
15
votes
1
answer
26k
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. ...
- 313
15
votes
2
answers
12k
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 ...
- 397
15
votes
2
answers
4k
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!)
...
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 ...
- 251
14
votes
1
answer
8k
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 &...
- 543
14
votes
4
answers
17k
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 ...
- 353
13
votes
2
answers
29k
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 ...
- 233
13
votes
1
answer
8k
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 (...
- 233
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 ...
- 302
12
votes
2
answers
5k
views
Does AWS RDS support MEMORY storage engine?
Title says it all. Strangely can't find a result on this.
- 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 ...
- 43.2k
11
votes
2
answers
11k
views
Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4
I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER ...
11
votes
1
answer
32k
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 ...
- 111
10
votes
1
answer
14k
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 ...
- 211
10
votes
2
answers
7k
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 ...
- 101
10
votes
1
answer
2k
views
How to add index to a big table with 60M records without downtime?
we have been struggling with one issue in the past few days. We want to add an index to a huge table with 60M records. At first we tried adding it with basic mysql syntax. But it clogged our ...
- 53
10
votes
2
answers
33k
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 ...
- 1,334
9
votes
1
answer
754
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 ...
9
votes
2
answers
18k
views
What is default MySQL binlog retention in AWS RDS?
I tried to retrieve my MySQL RDS binlogs in order to run manual "rollback" for some SQLs.
Unfortunately, I noticed that only 2 binlogs for the last 30 minutes are available.
"call mysql....
9
votes
4
answers
12k
views
Cannot revoke permissions or drop user in PgSQL AWS RDS
I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and ...
- 449
9
votes
2
answers
4k
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 ...
- 271
9
votes
1
answer
21k
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 ...
- 211
9
votes
1
answer
4k
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 (...
- 785
9
votes
1
answer
3k
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 ...
9
votes
2
answers
2k
views
Best practice for securely backing up RDS Postgres "offsite"?
We're running Postgres on RDS, which is largely great. The big issue with it, however, is to do with AWS' security model, which allows anyone with various permissions to delete everything - your DBs, ...
- 281
9
votes
1
answer
2k
views
AWS RDS postrgres massive disk use, small tables
I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
- 621
8
votes
2
answers
10k
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 ...
- 195
8
votes
2
answers
6k
views
AWS RDS - Changing Instance Class
So I'm fairly new to AWS and I'm hoping you guys can reassure me as I couldn't find anything in the AWS documentation either way. I actually have two questions, the first:
I have an RDS MySql instance ...
- 183
8
votes
3
answers
11k
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). ...
- 231
8
votes
1
answer
24k
views
Create a read-write user and a read-only user
I'm trying to do what I think is a common setup for a PostgreSQL database on a RDS instance. I want to have:
Either on public schema, or on a custom schema I don't especially care
A read-write user
A ...
- 83
8
votes
3
answers
9k
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. ...
- 181
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 ...
- 181
8
votes
1
answer
8k
views
Why RDS PostgreSQL `MaximumUsedTransactionIDs` not decreasing even after data base seems correctly `VACUUM FREEZE`'d?
My MaximumUsedTransactionIDs seems to keep growing indefinitely, but I have
these evidences hinting me it maybe should not be happening that way:
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD ...
- 96
8
votes
1
answer
1k
views
RDS MYSQL instance rebooted and won't load INNODB table
So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
- 203