PostgreSQL on Amazon RDS – Configuring the beast

In the last post I looked at how you can strengthen the security for your PostgreSQL instance in Amazon RDS. In this post I’ll look at how you can configure your PostgreSQL instance.

When you login to the Amazon console and select your running instance the screen looks like this:

If you want to modify the instance there is a “modify” entry in the “Instance Actions” menu:

This brings you to the main configuration page which is the same as the one which was displayed when we did the setup of the instance. Notice that you can change the security group here, too. This is what we talked about in the last post.

But how can I change the PostgreSQL parameters? Nothing is offered here to do that. This is what the “Parameter Groups” are for:

It is not possible to change the default parameter group:

But we can create a new one:

Parameter groups created in addition to the default one can be edited:

And voila: The full set of PostgreSQL parameters is available. Although it is not possible to edit every parameter (e.g. archive_command or the ssl stuff) most of them can be adjusted. Lets give it a try and change “work_mem” to 128:

… and then save the parameter group. When I log into my instance and check the current_setting of work_mem it is 4MB:

How can we now apply the parameter group we just created? Quite easy: Go back where you can modify the instance and change the parameter group to the group just created:

… and apply the changes. A summary will be displayed on what will be changed:

Once we proceed the new settings will be applied:

Surprisingly the status changes to “pending reboot” after a few seconds which should not be necessary for modifications of work_mem):

Lets do it:

Once the reboot completed lets check if our new value for “work_mem” is really applied:

It is. So, the way to adjust the PostgreSQL settings is via the parameter groups. If you have many instances which different kind of workloads this simplifies configuration a lot.

In the next post I’ll look at how to add a replica database.