Create a new postgreSQL RDS instance

<yambe:breadcrumb self="Create a new postgreSQL RDS instance">Amazon web services|Amazon web services</yambe:breadcrumb>

Create a new postgreSQL RDS instance

To create a new postgreSQL RDS instance using AWS management console use following steps:

1. Log into AWS management console in web browser
2. Go to RDS service
3. Click on Create database
4. Select options as follows

   Database creation method

   Standard create

   Engine type

   postgreSQL

   Version

   Select as per requirement

   Template

   Production

   DB-Instance identifier

   Enter name for the RDS instance

   Master username

   postgres

   Master password

   Enter suitable password. Ideally long alpanumeric passwords without special characters

   DB Instance class

   Based on requirement select DB class. You can optionally enable option for previous generation classes, but there may not be any advantage in going with older generation.

   Storage type

   Select based on requirement. For this example we selected General purpose SSD

   Allocate storage

   Enter storage required in DB. This can be increased later on.

   Enable storage auto-scaling

   Enter a maximum limit till which database should auto-scale

   Multi-AZ

   "Do not create a standby instance" makes sense unless your application is itself multi-AZ with instances across multiple AZ behind an Elastic Load Balancer (ELB)

   VPC and Subnet group

   Select based on requirement.

   Public access

   Mostly no. Yes would make DB accessible over Internet. In case you want to choose yes, make sure password configured is very complex / large

   Security group

   Select desired security group. It makes sense to have VM based security group where only the related application/Web VMs can connect to this DB.

   Availability zone

   Within the region select the AZ for the DB. Note that there might be considerable data transfer between application and DB. Hence it makes sense for both to be in same AZ for lower costs and higher performance

   Database authentication

   Password authentication makes most sense

   Additional configuration -> Database name

   Enter appropriate as per requirement

   More databases can be created within the same postgreSQL instance later using psql as explained in other sections below

   DB Backup

   Enable automatic backup. Note that backups of up to DB allocated storage are free. Hence for 20GB Database backups up to 20GB are free.

   Retention period

   Choose retention period (Eg 7 days)

   Backup window

   If you have preferred backup window select it or else go with no preference.

   Copy tags to snapshots

   Enable

   Backup replication to anothe region

   Not required. Only makes sense for multi-AZ deployment.

   Performance insights

   Enable and select retention period eg 7 days

   Enhanced onitoring

   Disable unless really needed

   Log exports

   Not required

   Enable auto minor version upgrade

   Leave it enabled

   Maintenance window

   Again if there is preference for when the version upgrade should happen select that or else leave to default 'no preference'

   Deletion protection

   Select based on what organization typically does for other instances (Eg EC2).

5. Look at the estimated monthly costs and if it is acceptable, create database
6. Wait for database status to change from "Creating" to "Available"
7. Click on the database name and note the value for Endpoint. We need to use this value to connect to DB from EC2 instances.

Connect to postgreSQL database

Based on whether database is public or private it can be connected only from EC2 instances or from anywhere. To connect to database copy its endpoint FQDN and use it as parameter for host. The DB username is typically postgres for first time connection. You can create additonal users later. The password for postgres user is defined while creating the RDS instance. During DB creation we might have also created a database and hence database name might also be known. Hence for connecting from Linux the command could be

psql -h <endpoint-fqdn> -U postgres -d <db-name>

If datbase name is not known connect to postgres database using:

psql -h <endpoint-fqdn> -U postgres 

Create additional database

Once we are connect to postgreSQL we can create additional database using:

\c postgres
CREATE USER <username> WITH LOGIN ENCRYPTED PASSWORD '<desired-password>';
GRANT <username> to postgres;
\du
CREATE DATABASE <dbname> WITH OWNER="<username>";
\l

Take manual backup

If it is required to take manual backup either use pg_dump on application / web side or at database level you can go to Database -> Maintenance and Backups. Click on "Create snapshot".

Wait for snapshot status to change from "Creating" to

Anytime we can go to RDS service and choose snapshots from left side for manual snapshots

Restore backup

1. Go to RDS instance and go to "Maintenance and Backups". Select appropriate snapshot and click on "Restore". During restore

   DB instance class

   Select as per requirement

   Multi AZ-Deployment

   No

   If it is test restore then select no. If this is restore for a new production to replace old production, and old production is multi-AZ then perhaps select yes

   Storage type

   General purpose SSD

   DB instance identifier

   Name for the instance where DB would be restored

   VPC and Subnet group

   Select as per requirement. Perhaps same as original DB being restored

   Public accessibility

   No

   Availability zone

   As per requirement. Perhaps same as original DB being restored

   Security group

   Select as per requirement. Perhaps same as original DB being restored

2. Leave most other things to default and restore DB
3. Wait for status to change from "Creating" to "Available"
4. You can now connect to backup instance as per requirement. Once task is complete delete either restored instance or original production instance.

Deleting database

Select database and from Actions menu select Delete. Based on the criticality of database create final snapshot before deleting and choose whether to retain existing automated snapshots (backups).

If any manual snapshots were created for database, they would still be present. We need to delete them manually before/after deleting the database based on requirement.

psql tips

For psql please note

\h

Can be used to get help. For example '\h CREATE DATABASE' will show syntax for CREATE DATABASE command

\?

Lists various options