Hot answers tagged

13

SQL Server does not automatically change the recovery model. If you set it to simple, then see later it is set to full, either: Someone else changed it back to full The database has been restored from a backup where the database is set to full recovery There may be a scheduled agent job that is changing the recovery model. From a comment on this answer: ...


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 ...


10

For Aurora Postgres, there's two relevant cluster-level parameters (note they're not instance-level parameters): rds.force_ssl and ssl. I haven't tested this myself but you should be able to modify them in the usual way using DB Parameter Groups.


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

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)


9

I had the same issue with our staging RDS. After quite a while I found a blog post that says: In a very busy database, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the "replication_delay" will continually grow because the last replayed transaction timestamp isn't increasing. To ...


8

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.


7

If DMS is told to create the table on the destination (the default option) it only creates the most basic structure needed to move the raw data over. So you shouldn't trust that other metadata like default values are moved over either. You can either migrate all your changes after moving the data, or create the schema first on your destination and tell DMS ...


7

Yes, it does. How come ? Run this query in the RDS Instance: mysql> select * from information_schema.engines where engine='MEMORY'\G *************************** 1. row *************************** ENGINE: MEMORY SUPPORT: YES COMMENT: Hash based, stored in memory, useful for temporary tables TRANSACTIONS: NO XA: NO SAVEPOINTS: ...


7

My guess is that you're running PostgreSQL on AWS. If so, you need to identify this in the question and the tags. The last process mentioned in the kernel dump is in fact aws so it seems like a safe bet Jun 1 13:49:04 ip-10-1-0-9 kernel: [260956.963722] [16368] 1000 16368 7725 1044 19 0 0 aws With regard to PostgreSQL, ...


7

To find out the wal_level you can use PSQL and execute: show wal_level; You need to set rds.logical_replication parameter to 1. It will automatically change wal_level to logical. I found some details here


6

It seems that I had to re-run the AWS RDS PostGIS instructions: CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$; SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser') FROM ( SELECT nspname, relname FROM pg_class c JOIN ...


6

If you only have 40% of 15 GB = 6 GB free, you aren't going to be able to have two copies of a 7 GB table on the instance at the same time, whether you use ALTER TABLE (which usually creates an entire copy of the table and then replaces the existing table with it, as RolandoMySQLDBA explained) or create another table and insert the data. It sounds like ...


6

A pg_dump output with the -Fc (custom) is not a SQL script, it's a compressed archive that only pg_restore is able to handle. You may either pass the dump file to pg_restore and pipe its output to psql (often done that way and close to what you tried): pg_restore /home/jboss/proc_dump_jan15.dump | psql --host=blahrds [other options]... ...or use the -d,-...


6

Yes. Think of EC2 as a machine. It has a disk attached - a EBS (Elastic Block Store), and the disk size is your limit. Lets say your ec2 instance has a 20gig disk. If you database grow over 20gig, you will have to resize your attached disk. I am not aware of a auto-scalling method for EC2 disks. But you can simply make a snapshot of a EBS, create another ...


6

Changing Instance Class: From the documentation "Modifying a DB Instance and Using the Apply Immediately Parameter", changing the instance class and setting "Apply Immediately" to false will result in: The change being applied during the next maintenance window. The change causing an outage to occur. Since the instance is Multi-AZ and you're changing the ...


6

Amazon RDS documentation provides a table of units for each parameter under the "Working with PostgreSQL Parameters" topic. Units are also usually included in the description of each parameter in the parameter group edit UI from the Amazon RDS web console. min_wal_size (Postgres 9.5 Parameter Group Family) Default: 16 Units: MB Allowed Values: 2-201326592 ...


6

VACUUM processes launched by autovacuum can be safely killed with: SELECT pg_terminate_backend(PID_of_backend); Actually, any client processes in Postgresql can be terminated this way. Uncommitted work by this backend will be simply discarded. You could then re-run VACUUM manually at a low-traffic time: VACUUM VERBOSE users; Check whether Cost-based ...


6

In AWS EC2, you don't get the ability to add additional network ADAPTERS to your VM. You can add additional network INTERFACES, which are just different IPs/subnets/routes/etc, but they're using the same underlying network adapters (cards). You can choose to use instance types with faster networking ports, though. The EC2 instance configuration page lists ...


6

The RDS Postgres instance has a parameter group. Check in RDS colsole what parameter group is defined: RDS -> Databases -> choose your db -> Configuration -> parameter group. If it's the default one, create a new one and change track_activity_query_size to the value you need. Apply the new parameter group or change the actual one. The parameter is static so ...


5

Here is the Problem: Whenever you run ALTER TABLE, there must be a temp table used. The temp table is not being written to your data folder. It is most likely written to /rdsdbdata/tmp. Please this query SHOW VARIABLES LIKE 'tmpdir'; This will give you an idea where that folder is. Again, by default, it is /rdsdbdata/tmp. Why would that halt things for ...


5

Other than outdated techniques like obtaining exclusive locks on tables and tactics like SELECT HIGH_PRIORITY and INSERT DELAYED -- which are either deprecated or otherwise not useful (especially with InnoDB) -- MySQL (including RDS) does not have any mechanisms for prioritization like this. Depending on the nature of your "longer running background queries"...


5

You can do the following: UNLOAD your data to S3 (http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html) Run your deployment scripts against the new database COPY the data into the new database (http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html) This (UNLOAD/COPY) will be fairly quick for a development size dataset.


5

The "geospatial feature" of dynamodb isn't really anything else then calculating a geohash from the coordinates, storing the hash in the same row, create an index on the hash, and when querying based on a location, calculate the hash of that location and compare to the indexed hashes in the table. The algorithm of geohash is really simple and with a ...


5

For a single instance comparison, not much, but RDS has many advantages, maintenance of a multi-AZ setup with read replicas in different regions, etc. is more than I'd prefer to deal with in EC2. RDS does a tremendous amount of tooling you'd otherwise have to do yourself; this is always a plus. RDS also presents a uniform interface to your databases, in my ...


5

There is no limitation of 64KB on the size of a MySQL row. There are limitations, but almost never does anyone hit them. Can you point to where you got that limit? There are a couple of obscure limits in InnoDB, such as having dozens of big VARCHARs, but they are easily worked around. Here are the main limits for MySQL: http://mysql.rjweb.org/doc.php/...


5

T2 instances are given cpu time based on a credit system, and t2.micros are have the least overall cpu capability in the instance class. These credits are accumulated at a rate of 6 per hour on a t2.micro, which gives you the ability to burst to 100% of the cpu core for 6 minutes. All other times you are only granted 10% of the core. Furthermore, you will ...


5

The issue related to JSONB columns is a known limitation of DMS. In particular, if you're using DMS to migrate from your EC2 instance to RDS Postgres, it will convert the type to varchar(765) and truncate any JSONB column longer than 756 chars. https://forums.aws.amazon.com/message.jspa?messageID=735961 We heard during AWS Summit - San Francisco that the ...


5

What you are trying to do won't work because pgcrypto's decrypt function is not going to receive the KMS key you encrypted the data with. Essentially, when you set up KMS it generates a key pair consisting of a public key and a private key. These are generated on a Hardware Security Module (HSM) and the public key is something you can query the KMS api for. ...


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