Difference between revisions of "Create a new postgreSQL RDS instance"

From Notes_Wiki
(Created page with "<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 ne...")
 
m
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
<yambe:breadcrumb self="Create a new postgreSQL RDS instance">Amazon web services|Amazon web services</yambe:breadcrumb>
[[Main_Page|Home]] > [[Amazon web services]] > [[Create a new postgreSQL RDS instance]]
=Create a new postgreSQL RDS instance=


To create a new postgreSQL RDS instance using AWS management console use following steps:
To create a new postgreSQL RDS instance using AWS management console use following steps:
Line 103: Line 102:
;\h : Can be used to get help.  For example '\h CREATE DATABASE' will show syntax for CREATE DATABASE command
;\h : Can be used to get help.  For example '\h CREATE DATABASE' will show syntax for CREATE DATABASE command
;\? : Lists various options
;\? : Lists various options
[[Main_Page|Home]] > [[Amazon web services]] > [[Create a new postgreSQL RDS instance]]

Latest revision as of 05:23, 18 April 2022

Home > Amazon web services > 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



Home > Amazon web services > Create a new postgreSQL RDS instance