Infrastructure at your Service

Daniel Westermann

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

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

az41

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.

az43

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

It is not possible to change the default parameter group:
az45

But we can create a new one:
az47
az48

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

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

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

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

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

Once we proceed the new settings will be applied:
az54

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

Lets do it:
az56
az57
az58

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

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.

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure