How to configure a PostgreSQL database on RDS

Configuring the creation method

The first step in provisioning a new database on RDS is to choose the creation method:

While the Easy create method can help you choose some reasonable defaults, it doesn't provide the level of flexibility that you probably want when configuring an important database for your environment. The Standard create option offers additional options and is still a relatively short process.

Choose the Standard create so that you have full control over the creation process.

Selecting the PostgreSQL engine and version

Next, choose the Database Engine:

For this guide, we will be configuring a PostgreSQL database, so we will choose the PostgreSQL option. Amazon's Aurora is an alternative PostgreSQL-compatible option that provides Amazon's own scaling and management features. For this guide, however, we will focus on a PostgreSQL native deployment.

Choose the version of PostgreSQL that you wish to use. The default selected version is typically a safe choice, but you may also want to select the latest release for newer features or an older release depending on compatibility with your application libraries.

Choosing the production template

Next, you will be asked to choose a Template:

Templates are meant to provide preselected options that match typical deployment scenarios. Since we are provisioning a production database, select the Production template.

Setting the database availability

The next section includes the first major decision you'll need to make in regards to your deployment: the database's availability and durability:

This section determines exactly how your database will be deployed within AWS's environment. This has implications on how resistant your setup is to failure and outages, both of your specific instance and of regional disruptions in Amazon's infrastructure.

Let's take a look at the various options we have, what they provide, and what scenarios they are useful for. For this guide, we will be recommending a Single DB instance, so feel free to skip ahead if you aren't interested in the other options.

Configuring the basic settings

The next section allows you to configure your deployment's basic settings:

First, you have the opportunity to name your database instance by setting the DB instance identifier. Select something unique that will help you understand the role and purpose of the database when displayed within the AWS console.

Next, configure the credentials for your database instance. Start by setting the master username for your PostgreSQL database. By default, most PostgreSQL client tools assume that the master user is called postgres, so you can keep that username if appropriate.

Note: The master username is the main administrative account within the PostgreSQL database instance. You should always create and configure additional application-specific usernames with limited privileges after provisioning your database instance. Configure your database clients and applications to use those less privileged identities for their daily operations. Only use the master username for your initial configuration and to create more limited and narrowly scoped avenues of access.

You then have the option to set and confirm a password for the master username. You can either choose a strong, preferably machine-generated, password for the account or you can check the Auto generate a password box to have AWS automatically generate a secure password for you.

If you select your own password, save your password somewhere secure as you will be unable to retrieve it later. Likewise, if you auto generate a password, you will only have access to the password the first time you access the credentials in the AWS console after creation. Be sure to record the password securely as you won't be able to retrieve it again at a later time.

General suggestions for choosing DB instance classes

In general, RAM tends to be a more important factor than CPU with most relational database workloads. Because of this, you shouldn't necessarily discount the burstable classes out of fear of low performance. They offer some of the best value while still being able to reasonable performance.

Specifically, instances like the db.t3.medium, which provides 2 vCPUs and 4 GiB RAM, is a good baseline selection for many use cases if you are just starting out. The CPU should be able to handle a medium level of activity while accommodating request spikes. Assuming the hardware performance is suitable for your project, one of the first limitations you might run into is the network cap of 2,086 Mbps, so keep that in mind as you decide.

If you want a higher level of CPU performance for a standard database with a decent amount of traffic, you may want to consider a standard class instance with 2-4 vCPUs and 8-16 GiB of RAM. The m6g instances are advertised at having up to a 40% better price-to-performance ratio than the older m5 instances, so they are often the better choice when looking at standard instances. With that in mind, the db.m6g.large and db.m6g.xlarge instances are good starting selections for this instance class type.

Memory optimized database instances are primarily useful if you know that you will have to load large data sets into memory. This might be because your tables hold many records or because you frequently query large numbers columns. If you make heavy use of PostgreSQL's JSON columns, this might be something to consider. That being said, it's often better to start with a more standard configuration and change the instance class if you find those resources are not sufficient.

Choosing the storage type

RDS offers three different types of storage that are appropriate for different scenarios:

• General Purpose SSD (gp2): This is a standard solid-state drive selection. Your IOPS (input/output operations per second) available for your storage device is tied directly to the size of space you allocate. The baseline IOPS is set at 3 IOPS/GiB allocated, with the ability to burst to 3,000 IOPS for short periods of time.
• Provisioned IOPS SSD (io1): This option allows you to decouple the amount of storage space from the IOPS allocated to your database.
• Magnetic: Magnetic storage uses traditional spinning disks instead of SSDs.

In general, it is almost always best to start with the general purpose SSD option. This allows you to set the amount of space that you need and deploy your database on SSDs. The magnetic option is rarely a good choice as it has slower speed and is limited to 1000 IOPS.

The provisioned IOPS SSDs are a good choice if you have performed fairly robust application profiling and have a good idea of the amount of IOPS your environment generates with various workloads. This also tends to be a lower number than anticipated due to caching and other mechanisms that can lower the amount of operations that actually hit the drive. Generally, think of this as an advanced choice that might become appropriate once you have experience running your exact configuration over time.

Selecting the initial storage space

After selecting your storage type, choose the amount of storage to allocate. This is also highly dependent on your application. In general, however, people tend to overestimate the amount of space they'll need at the beginning. Start low and remember that you can always scale up.

Configure autoscaling

Next, select whether to enable autoscaling. This option allows your storage space to automatically scale with usage. This is generally a good idea as it allows you to start with a smaller initial allocation for a more affordable price and then automatically increase the storage space as your data increases. You can configure a maximum scaling amount to make sure that you end up scaling more than you're comfortable with.

In general, it is often enough to start with anywhere from 20 to 100 GiB as your initial allocation (often depending on what provides you with enough IOPS) and then set your maximum storage threshold to the maximum size you are willing to pay for.

Configuring the VPC

When asked to select the Virtual Private Cloud (VPC) where your database will be deployed, it is usually a good idea to deploy it to a new VPC or the VPC containing applications that will access it. Deploying to the default VPC is usually not recommended as it means that your database will be subject to any adjustments you make to the default network environment.

Choosing the Subnet group

Next, you need to select the Subnet group. This defines the IP range within the VPC where your database may be deployed. Generally, it's a good idea to create a new group here as well.

Deciding whether to allow public access

One of the most important choices you'll have to make is whether to allow public access to your database instance. This is largely a function of your security requirements and your ability to configure special access to your database.

The most secure approach is to turn off public access. This will only allow your database to be accessed by (or through) instances deployed within the same network environment as your database. To access your database externally, you'll need to configure a jump host or an internet gateway with external access to authenticate and route external connections to your database. This can be a significant undertaking if you're not familiar with configuring infrastructure or managing network rules.

The other approach is to allow for public access. This will make your database instance accessible from the public internet. It is less secure as anyone can attempt to connect to your database, but it does allow for a simpler access model from external connections. If you choose this option, ensure that all of your database passwords are very strong and rotated frequently. You will likely see external attempts to access your service in the logs, so password strength is vitally important.

The choice of whether to enable public access or not is more complicated than it initially appears. If you enable public access, you can still lock down access to a specific set of external IP addresses and provide other points of configuration to filter out illegitimate connection attempts. In general, it is best to go with the most secure configuration you'll be likely able to support over the long term.

Choosing the VPC security group

VPC security groups control the access rules used to connect to the instances deployed within. These are basically firewall-like access control policies that help limit the types of connections that can be made to your database instances.

Create a new VPC security group unless you have already configured one for production-database purposes. Choose a name that will help you remember the focus and role of the security group when displayed in the AWS console.

Choose the availability zone and port

You can choose the availability zone within your current region where you want to deploy your database instance to. If you have no preference, feel free to leave "No preference" selected.

If you open the "Additional configuration" options, you can optionally change the port where PostgreSQL will listen. Changing this can help alleviate some level of chatter in the connection logs of an open instance, but it does not offer any significant security improvements and can make connecting more tedious.

Database options

The first additional section allows you to configure some additional options for PostgreSQL:

If you'd like, you can choose to have RDS automatically create an initial database for your deployment. This is usually unnecessary as any migrations you run or other setup scripts you might have typically create the necessary database structures within the instance.

You can also sometimes choose a DB parameter group for your instance. Multiple selections here might not be available depending on the selections you made earlier and whether you've configured parameter groups outside of the creation process. When present, they allow you to alter the PostgreSQL parameters applied to this instance.

PostgreSQL does not support option groups, so that drop down will not be active.

Backup

Next you can configure the options associated with automatic database backups:

If you want to enable automatic backups, select the enable automatic backups box. This will tell AWS to automatically take daily point-in-time snapshots of your database instances that you can use to restore from. It is worth noting that, while not mentioned on this page, backups will be stored in AWS's Simple Storage Service (S3) and that you will be charged for the space they consume.

By default, backups are set with a retention window of 7 days, meaning that you will be able to restore snapshots of your database taken within the last 7 days. If you are using this feature, you might want to increase the retention period to 14 days to give yourself more time to catch unwanted changes. Keep in mind however that increasing the retention period will incur greater costs for the additional storage.

If you have any preferences around when backups occur, you can select an appropriate window of time, otherwise, it's safe to leave as "No preference".

Keep "Copy tags to snapshots" selected to make it easier to find the appropriate snapshots within S3.

If you want additional protection, you can also select Backup replication to replicate your changes to a different AWS region automatically as well. This will allow you to quickly restore from a different region, but will increase your S3 storage costs and data transfer costs.

Encryption

Next, you can configure the on-disk encryption for your database instance:

It is almost always a good idea to enable encryption for your instance for additional security.

In general, you can use the default AWS KMS Key for this unless you have additional requirements such as encrypting to a key used by a different AWS account.

Performance insights

Next, configure the performance insights for your instance:

Performance insights are a feature that helps you debug performance issues in your RDS instances. By default, the insight data will be retained for 7 days for free. You can alternatively choose long term storage of 2 years for an additional cost depending on your CPU instance types.

Again, you can leave the default AWS KMS Key selected for this encryption unless you have special requirements.

Monitoring and Logs

Next, you can configure the monitoring and logs for the database:

In general, it is helpful to enable monitoring to give you better visibility into your database instance and how it is performing.

As for exporting logs, it is typically safe to disable this by default unless you need it. Your application logs will usually bubble up database-specific information as needed, but you can always enable PostgreSQL log exporting if you find it helpful. Logs will be exported to AWS CloudWatch Logs.

Maintenance and deletion protection

The maintenance and deletion protection sections finish off the additional configuration:

It is typically considered safe to automatically upgrade minor versions of PostgreSQL automatically. Minor versions should have no breaking changes and upgrades should be performed relatively seamlessly. Select an upgrade window if you have a preference.

It is always a good idea to enable deletion protection for your production database. This prevents you from accidentally deleting your database instance by requiring you to revert this option before a deletion will be successfully processed. There is no reasonable reason to opt out of this option for most use cases.

Viewing auto generated password

If you configured AWS to auto generate a password, you will see a notice like this at the top of the screen:

Click View credential details to get see the password:

Note that this is the only time you'll have access to this password and you'll have to regenerate it if you lose it.

Viewing other connection information

For the rest of the connection information, click on the instance name.

In the Connectivity & security tab, you can find your database's endpoint and port number on the left-hand side:

In this example, our endpoint name (URL for the host) is production-db1.cddm7tgh3j5j.us-east-1.rds.amazonaws.com and our database is listening on PostgreSQL's standard port: 5432.

To find the master username that you configured (this will be postgres if you didn't modify it), visit the Configuration tab and look for the Master username field under the Availability heading:

You can also find the name of the initial database on the left-hand side of this same tab, if you configured one.

With this information, you can connect to your PostgreSQL instance. For an example, to connect to the instance displayed here, you could use the following connection URI:

postgresql://postgres:Pa38iSJWm8qtq90LnmZ8@production-db1.cddm7tgh3j5j.us-east-1.rds.amazonaws.com:5432/postgres

You can learn more about how to construct connection URIs from connection details in our guide on PostgreSQL connection URIs.