AWS RDS has a pretty good web UI in their console. It’s even got a log file watcher built in. But sometimes you just want to download your PostgreSQL log files from your command line, maybe because you’re looking for slow queries and want to run pgBadger. Or you want to script grabbing the last log file after you run a batch data load. Or you just like the command line better.

You should already know that AWS had a command line that uses the same APIs their web console is built with and has extensive documentation. Make sure you have that installed and configured to access your AWS account. Since most of the CLI output is in JSON, we’re going to us the JQ tool to pick apart the responses and get the bits we want.

Step 1: Find your instance identifier

Hopefully you gave your RDS instance a nice easy to remember name when you created it. To find out, list all of your instances, filter for postgres engines (in case you also have some “other” databases for some reason) and show it’s status. You use the describe-db-instances API for this:

aws rds describe-db-instances 

And this will give you a big blob of JSON that makes your eyes bleed trying to find the information you want. Like this: AWS JSON

To make this usable, fire up JQ, filter down to postgres engines (let’s pretend we don’t need MySQL for WordPress or anything), and check the availability status:

aws rds describe-db-instances | jq ' .DBInstances[] | select( .Engine | contains("postgres")) | .DBInstanceIdentifier + ": " + .DBInstanceStatus' 
"bigsql-on-rds: available" 
"openwatch: available"

Much better!

Step 2: Find the most recent log file

Now, you want to find the most recent log file for your instance. Use the describe-db-log-files command and the instance identifier for your db, find the one that was most recently written:

aws rds describe-db-log-files --db-instance-identifier bigsql-on-rds | jq ' .DescribeDBLogFiles | max_by( .LastWritten ) | .LogFileName'
"error/postgresql.log.2017-03-22-14"

Step 3: Retrieve the log file

Download it! Use the text output if you want it to look like the PostgreSQL log file you know and love. If you had previously set log_min_duration_statement to a value, you’ll see the SQL statements that took longer than your value to execute.

aws rds download-db-log-file-portion --db-instance-identifier bigsql-on-rds --log-file-name "error/postgresql.log.2017-03-22-13" --output text
2017-03-22 13:13:43 UTC:172.30.1.60(37924):xxxxxx@xxxxxxx:[22791]:LOG:  duration: 3.244 ms  parse <unnamed>: SET extra_float_digits = 3
2017-03-22 13:13:43 UTC:172.30.1.60(37924):xxxxxx@xxxxxxx:[22791]:LOG:  duration: 1.902 ms  execute <unnamed>: SET extra_float_digits = 3
2017-03-22 13:13:43 UTC:172.30.1.60(37924):xxxxxx@xxxxxxx::[22791]:LOG:  duration: 14.961 ms  parse <unnamed>: INSERT INTO some_table (column_one, column_two, column_three, column_four) values ($1,$2,$3,'now()')
2017-03-22 13:13:43 UTC:172.30.1.60(37924):xxxxxx@xxxxxxx:[22791]:LOG:  duration: 1.678 ms  bind <unnamed>: INSERT INTO some_table (column_one, column_two, column_three, column_four) values ($1,$2,$3,'now()')
2017-03-22 13:13:43 UTC:172.30.1.60(37924):xxxxxx@xxxxxxx:[22791]:DETAIL:  parameters: $1 = 'a big long string', $2 = '', $3 = 'short string, or so'
2017-03-22 13:13:43 UTC:172.30.1.60(37924):xxxxxx@xxxxxxx:[22791]:LOG:  duration: 8.089 ms  execute <unnamed>: INSERT INTO some_table (column_one, column_two, column_three, column_four) values ($1,$2,$3,'now()')
2017-03-22 13:13:43 UTC:172.30.1.60(37924):xxxxxx@xxxxxxx:[22791]:DETAIL:  parameters: $1 = 'an even longer and bigger string than the first time', $2 = '', $3 = 'short string, or so'
2017-03-22 13:15:04 UTC::@:[22496]:LOG:  received SIGHUP, reloading configuration files
2017-03-22 13:15:04 UTC::@:[22496]:LOG:  parameter "log_min_duration_statement" changed to "-1"
2017-03-22 13:15:23 UTC::@:[22496]:LOG:  received fast shutdown request
2017-03-22 13:15:23 UTC::@:[22496]:LOG:  aborting any active transactions

Step 4: Script it (Left as exercise for reader)