Category: AWS

Turning on query logging for PostgreSQL in Amazon RDS

Source: https://www.postgresql.org/docs/9.4/runtime-config-logging.html

This is a very specific topic, but I needed to do it to track down performance of a slow web service call.

First, go to your Amazon RDS instances tab and select the database that needs logging turned on, then go to “See details” under instance actions.

Find the Parameter Group attached to the database. Click on it to edit it, or go to “Parameter groups” in the sidebar and find the corresponding parameter group to edit.

There are two parameters you want to look for: log_statement and log_min_duration_statement (see update below). log_statement accepts 3 values: none (default), ddl, mod, and all. For nearly all purposes, you want to change this to all. log_min_duration_statement simply sets a filter (in milliseconds) for when to log a SQL query – this lets you specifically target slow queries. The default value is -1 (disabled). In my case, most queries were in the 3-5ms range, so I set it to 10.

Once you save the parameters, it’ll take about a minute or so for them to be applied to your database(s) and logging to begin. Once they are done applying, go back to “See details” for your database instance, and scroll down to the “Logs” section. Logs (postgres.log files) are displayed from oldest to newest, so you actually have to click through to the last page (for me, page 15) to see the newest log files.

It turns out we had very few queries that were over 10ms, and the slowest one was 68ms, so the bottleneck was elsewhere.

UPDATE 2019-03-17

The original entry was written for Postgres 9.4 in 2018. It looks like I was out of my mind at the time, and this setting is called “log_min_duration_statement“. As a helpful person pointed out in the comments, log_statement = all will log all statements (schema updates and data modifications).

Here’s a summary of what the Postgres 9.4 documentation on these variables:

log_min_duration_statement will log the statement duration if it takes longer than the given time span; 0 will log all statement durations. This SO link indicates that the log_statement setting has no effect on which statements are logged by log_min_duration_statement.

While we’re at it, let’s talk about log_duration, too. This boolean value causes every duration to be logged, but differs from log_min_duration_statement in that log_duration does not log query text, while log_min_duration_statement will log query text of statements that exceed the duration.

Source: https://www.postgresql.org/docs/9.4/runtime-config-logging.html

Receiving and processing emails in Azure vs. AWS

One big difference I noticed between Amazon Web Services and Windows Azure is that Azure doesn’t have built-in support for sending and receiving emails, while AWS has Simple Email Service (SES) (Aside: of my friends at Amazon says they’re running out of words to put between “simple” and “service”). This is probably because the power of the cloud has been harnessed to send spam emails in the past, and indeed, Azure compute IP blocks have been added to spam blacklists. To send emails from Azure, Microsoft recommends using an external service like SendGrid.

However, what if you want to receive emails in Azure? I did some digging and there isn’t really a well-defined way. The only plausible results I found were setting up a compute role with a third-party SMTP server, or using an external service like Postmark, which cleans up email for you before calling a user-provided webhook. Postmark is nice because you can configure a spam filter before your webhook is called, which you would have to do manually if you were to run your own SMTP server.

Now let’s take a look at Amazon SES. SES has been around for a couple years now (the documentation was released in 2011). You can create a ruleset to chain multiple actions together when an email is trigger. For example, the first action can copy the email to an S3 (Simple Storage Service) bucket, followed by triggering a Lambda function (which is a serverless piece of code) to process the email, and the final action can be to make an SNS (Simple Notification Service) call to send a push notification to a mobile app, all from within AWS.

Amazon can even register your domain name using Amazon Route 53, and you probably should use that as well, because transferring an existing domain is a 7-step hassle. Also, if you registered or transferred your domain recently, ICANN rules prohibit you from transferring again for 60 days. Don’t shoot yourself in the foot by registering a domain name with another registrar within 60 days of using SNS, or you won’t be able to use that domain for receiving email until the prohibition expires.

So if you’re trying to send and receive emails in Windows Azure, you’re pretty much forced to use a third party service, whereas if you’re with Amazon, you can do everything from within AWS.