38

Upgrading an instance in RDS means RDS will be physically migrating the database to a new instance, likely on a different physical host, so downtime would not be avoidable. Migrating to provisioned IOPS would likely mean your data would be migrated to a new EBS volume (and the server might be migrated to a new instance as well with this change, depending on ...


37

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with: revoke usage on schema public from public; This is required when it's not desirable that people peek into other people schemas, ...


21

You have at least two options. The first one makes use of a small query and a text editor. We have to collect the schemata of our interest: SELECT nspname FROM pg_namespace; You can add a WHERE clause if you want to limit the scope. Copy the output and amend it, so you get a number of GRANT USAGE ON SCHEMA ... TO your_role; commands. Then just feed ...


20

I'm a big AWS fan in general... but RDS, not so much. @RolandoMySQLDBA has pointed out are some pretty good points against it. The only advantage I see in RDS compared to MySQL on EC2 are the ability to do point and click snapshots, clones, and point-in-time recovery, but these are not nearly sufficient to make up for the loss of control and flexibility ...


20

You probably don't want to hear this, but the best option to speed up SELECT DISTINCT is to avoid DISTINCT to begin with. In many cases (not all!) it can be avoided with better database-design or better queries. Sometimes, GROUP BY is faster, because it takes a different code path. In your particular case, it doesn't seem like you can get rid of DISTINCT. ...


17

Each connection carries the load of per-connection buffers as set by these parameters join_buffer_size sort_buffer_size read_buffer_size read_rnd buffer_size Changing the number of connections increases the amount of memory each connection can demand to this : (join_buffer_size+sort_buffer_size+read_buffer_size +read_rnd buffer_size) X max_connections I ...


16

You can't add your own extensions to RDS, at least not ones that require superuser rights (like anything with C code). This is one of the downsides you accept in exchange for convenient management. If the extension only includes simple plpgsql and sql functions you can add the functions manually. That is not possible with anything requiring superuser ...


16

You are correct: your admin account is not sysadmin, but a less powerful account. This is necessary to ensure that AWS is able to limit you from doing EVERYTHING to this instance, and escaping security boundaries. Setting a database online As part of your admin account, you have access to set databases offline, but bringing a database back online requires ...


15

Run this query to show if/when your tables were last vacuumed. SELECT * FROM pg_stat_all_tables; A table won't be vacuumed until the insert/update/delete threshold is reached, which is 20%. You can tweak the setting in RDS by creating a new DB Parameter Group and change the setting you want.


13

Currently there are a few available extensions in Amazon RDS -- detailed in the "Database Engine Features" chapter. You can issue the following command to get the available extensions supported: SHOW rds.extensions;


12

You'll have to test it. You could do some back of the envelope calculations to approximate the number of I/Os per insert, multiply it by the number of transactions per second, add in some buffer room etc, but it's much easier to just test it. The easiest thing to do is to allocate a best guess, then go back and increase or decrease it to match you're real ...


12

session_replication_role I found an alternative way of disabling foreign keys -- https://stackoverflow.com/a/18709987 set session_replication_role = replica; And re-enabling them with set session_replication_role = default; This works on RDS but still requires unusual privileges (i.e. not granted by default). dropping and recreating FKs Alternative ...


11

If scaling out DB Servers is not your cup of tea, then Amazon RDS is OK to use because all bells and whistles come with it. Those who simply want moderate HA, backups, and scaling out benefit a great deal. On the flip side, if you want to scale up hardware, that is out of the question for RDS. What if you want to scale up MySQL's capabilities? Unfortunately,...


11

This answer applies to AWS Aurora not straight MySQL RDS. Just because SHOW ENGINES says the database has the MEMORY storage engine doesn't mean you can actually use it. We have found that in AWS Aurora the SHOW ENGINES; call will tell you that the MEMORY engine is supported. However, if you try to change the engine to MEMORY or create a non-temporary table ...


11

Here's the trick, at least with pgAdmin v4: Leave the tablespace blank. It will default to "pg_default" when creating the database.


10

You should fully tune the MySQL Environment, particularly your InnoDB settings. (See the bottom of my Answer for tuning tips). This would be much better than fighting Amazon for elbow room in RAM/Disk. Why did I say fight? If you just spun up an Amazon RDS instance of MySQL, you would subject yourself to whatever constraints are given. All models of MySQL ...


10

AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html In order to do that in a way that's completely transparent to the application, you would need an intermediate proxy. Your application would then always connect to ...


10

You can directly upload the (gzipped) pg_dump results to s3 using the aws cli: pg_dump -Z 9 -v -h ${DATABASE_HOST} -U ${DATABASE_USER} -d ${DATABASE_NAME} | aws s3 cp --storage-class STANDARD_IA --sse aws:kms - s3://my-bucket/dump.sql.gz At work we wrapped this in a script which additionally fetches the required credentials from parameter store and then ...


9

RDS for MySQL 5.6 supports "in" and "out" migration strategies of live workloads using MySQL's native replication, but RDS for MySQL 5.5, they only support "in" migration... which is unfortunate, because essentially every possible combination except the one that you need is available. That would have been nice. :( If your dump download took 3 hours and ...


9

You want ALTER DEFAULT PRIVILEGES. Give the rds_superuser default access rights to all new tables. This only affects tables created after the ALTER. For existing tables you must GRANT rights.


9

Yes, it is safe. Vacuum full will recreate the tables as new objects and only when they're done they will be put to use. If you cancel it, the new files not yet put to use will be removed and old ones will be kept. Of course if you would cancel it by killing the process handling the vacuum there would be files left, but still the database would be intact.


9

I just wanted to point out that AWS has updated and now have a cluster Read endpoint that does load balancing in case anyone runs into this from Google. https://aws.amazon.com/blogs/aws/new-reader-endpoint-for-amazon-aurora-load-balancing-higher-availability/


9

As a_horse_with_no_name originally suggested in question comments: --assuming we have tables: a, b, ... CREATE TABLE a ( a_id integer NOT NULL PRIMARY KEY, a_name text ) ; CREATE TABLE b ( b_id integer NOT NULL PRIMARY KEY, b_name text ) ; First we create a global sequence and add a column in each table: -- create one global sequence to be used ...


8

You can't download a snapshot from RDS you have to use a tool like pg_dump This has already been answered multiple times on regular Stack Overflow: https://stackoverflow.com/questions/14916899/download-rds-snapshot


8

Like most things, it's a cost vs effort/complexity trade-off. RDS is easier, but less flexible, and costs more for the same level of performance. (In particular, note that there's no way to replicate into or out of RDS)


8

You must be running in a Security Group firewall problem. Go to your RDS Dashboard, select Instances and open the instance you want to connect to. Look for a line like this : Security Groups rds-launch-wizard (sg-3e9axxx) ( active ). You should be able to click on rds-launch-wizard (sg-3e9axxx) which leads you to the EC2 Dashboard in the Security Groups ...


7

Just a guess as there isn't enough information to really figure this out but it could be network latency. You're saying that local connection to local DB is fast but local to remote DB is slow. All other things being equal the only difference is the network connection between you (local) and the remote DB. Generally AWS is pretty fast and has a stable ...


7

I used the basic iostat (iostat -h) tool to get an idea of how many iops I was currently using. From that I extrapolated how much I'd use if I was under 4 times that amount of load and went with that amount. For me that worked out to 780 IOPS so I went with 800 IOPS.


7

I am 99% sure that Amazon RDS does not give you a 'root' access. i.e. You can't create a user as 'root'@'localhost' or 'root'@''. Can you confirm user you have used while launching Amazon RDS instance? and make sure that Password matches with user created.


Only top voted, non community-wiki answers of a minimum length are eligible