Hot answers tagged

14

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


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


12

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


12

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


10

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)


10

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


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.


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.


8

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

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


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

@Philᵀᴹ's answer is almost there. I just used it on my query and found the fix. I would have commented, but don't have enough points, so here's the answer. You have to use current_timestamp and then convert it to iso8601 format. Like so: WITH events AS ( SELECT event.eventVersion, event.eventID, event.eventTime, event.eventName, event....


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


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

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


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

Got the answer from aws. NCHAR and NVARCHAR types are converted to CHAR and VARCHAR types, respectively, and are stored in the specified number of bytes. A VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters. So, if I am storing double-byte characaters in SQL ...


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

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

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

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

Anthony's answer explains pretty well how RDS use those parameters. I just would like to add more comments to it. You should follow table of contents for RDS. The units may be seemed little bit weird to you if you compare it with postgresql.conf units. I just want to give an example; Let's say you want to set shared_buffers to 1GB. The unit of the ...


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