Skip to main content
27 votes
Accepted

How to set the wal_level in AWS RDS Postgresql?

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 ...
er_the_red's user avatar
22 votes

Best way to pg_dump PostgreSQL on RDS to S3

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 --...
Birne94's user avatar
  • 371
13 votes

Does AWS RDS support MEMORY storage engine?

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 ...
Barry Coleman's user avatar
13 votes

Recovery mode keeps reverting to full

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 ...
Hannah Vernon's user avatar
  • 70.2k
12 votes

How to alter system on AWS RDS?

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, ...
detzu's user avatar
  • 396
10 votes
Accepted

AWS DMS creates tables with no auto_increment

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 ...
Elliot Chance's user avatar
10 votes

How to enable force ssl on AWS Aurora Postgres?

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 ...
Simon Righarts's user avatar
9 votes

How to get the records from Amazon Athena for past week only

@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 ...
zaros's user avatar
  • 91
7 votes
Accepted

Always On Availability Groups Network Configuration in AWS

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 ...
Brent Ozar's user avatar
7 votes
Accepted

Does AWS RDS support MEMORY storage engine?

Yes, it does. How come ? Run this query in the RDS Instance: mysql> select * from information_schema.engines where engine='MEMORY'\G *************************** 1. row *************************** ...
RolandoMySQLDBA's user avatar
7 votes

PostgreSQL: SSL SYSCALL error: EOF detected

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 ...
Evan Carroll's user avatar
  • 63.6k
7 votes

Can AWS services replace SQL Server Agent Jobs?

Can AWS services replace SQL Server Agent Jobs? Sure, in the same sense that Walmart can replace McDonald's. Both sell food, but one of them requires you to cook the food yourself and it may not come ...
J.D.'s user avatar
  • 38k
6 votes

How to set up Amazon RDS parameter group for Postgres?

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 ...
orhankutlu's user avatar
6 votes
Accepted

How to set up Amazon RDS parameter group for Postgres?

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 ...
Anthony Neace's user avatar
6 votes

Why use RDS over EC2 for Postgres?

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 ...
figtrap's user avatar
  • 171
6 votes

Postgres long autovacuum halting database

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 ...
filiprem's user avatar
  • 6,579
6 votes
Accepted

NVARCHAR in SQLServer to VARCHAR in AWS Redshift

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 ...
Pravs_thedataguy's user avatar
6 votes

Stale table lock in AWS Aurora?

I can't tell if this involves Aurora, but here is something worth noting: There are occasions when InnoDB will leave lock lying around. How does this happen ? About six years ago, I answer this ...
RolandoMySQLDBA's user avatar
5 votes

In RDS digest function is undefined after creating pgcrypto extension

My coworker discovered the issue, in case anyone is having a similar problem, the issue was that pgcrypto had been installed into a schema that was not public. So the query for reported installed ...
chris's user avatar
  • 201
5 votes

Why did the data type of my DB change?

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 ...
user126643's user avatar
5 votes
Accepted

What's the default password for SYSTEM in Amazon Oracle RDS?

The SYS user, SYSTEM user, and other administrative accounts are locked and cannot be used More The Amazon documentation for Oracle on Amazon RDS says you have to use master user which has the DBA ...
atokpas's user avatar
  • 8,642
5 votes
Accepted

searching against decrypted column

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 ...
Larry's user avatar
  • 377
5 votes

Import data into AWS RDS sql server through SQL Server management studio

There are stored procedures that you can do on Rds EXEC rds_backup_database 'DBName','.bak file path','','0' EXEC rds_restore_database 'DBName','.bak file path','' File path use S3. Just to add ...
Yunus UYANIK's user avatar
  • 1,099
5 votes

Cluster network name resource 'Cluster Name' failed registration

I have come across this issue with my dev environment usually when during the setup of the cluster, i skip the warning for network binding. When you run a cluster validation, do you receive any ...
samosql's user avatar
  • 530
5 votes

Spike in AWS RDS Oldest Replication Slot lag

The lagging size of the replica lagging the most in terms of WAL data received. Applies to PostgreSQL. If you’re using logical replication you might consider setting up some monitoring around this ...
Tony Schneider's user avatar
5 votes
Accepted

Why is PostgreSQL's auto-explain not working in AWS RDS?

auto_explain only logs a statement upon successful completion. If it gets cancelled, it does not get logged by auto_explain.
jjanes's user avatar
  • 40.1k
5 votes
Accepted

How to install cstore_fdw inside RDS postgres 9.6

No. It's not supported on RDS at this point. See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions. If you really need ...
John Douglass's user avatar
5 votes
Accepted

SQL Server RDS Database Restore from S3 stuck in Restore Mode

It's not stuck, it's just still in restore mode because you used @with_norecovery = 1. You need to restore database with recovery to finish the process.
NikitaSerbskiy's user avatar
4 votes

AWS DMS creates tables with no auto_increment

I faced the same problem and since I have too many tables to go one by one, I created a SQL stored procedure to do the work: for all the tables, get the primary keys, and if the primary key is a ...
Carlos Robles's user avatar

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