# Introduction
Introduction to using AWS EC2 to host PostgreSQL server
Imagine the following scenario:
1. You have several files of data that are interconnected with each other
2. You want to create data tables combining information from separate data files
3. You want your project team to access the data and tables all from one location
A SQL server allows you to input your data into a database and create tables from different files using SQL commands, and you can also create users and allow them access to different databases in your server.
There's a big remaining question: how do you create a server for everyone in your project team to access? Several cloud vendors offer solutions, but most of the pre-configured SQL database cloud services can become expensive (if you'd like to investigate for yourself, [Amazon Relational Database Services (RDS)](https://aws.amazon.com/rds/) lists their pricing for PostgreSQL [here](https://aws.amazon.com/rds/postgresql/pricing/?pg=pr\&loc=3)).
A cheaper solution is to install and create your own PostgreSQL server on an Amazon EC2 instance and having your teammates access the server through pgAdmin4, PostgreSQL's most popular GUI.
In this documentation, we will
1. Configure an AWS EC2 Ubuntu machine for PostgreSQL server
* Configure instance to scale up or down depending on CPU demand using EC2 auto-scale feature
2. Set-up PostgreSQL server with users in our Ubuntu instance
3. Access PostgreSQL server on our remote computers using pgAdmin4
4. Backing up your PostgreSQL EC2 server in AWS
The goal is to review every step and give clear rationale so that you not only know what to do but also why you're doing it. Additionally, I list all of the website resources referenced in each section.
### About PostgreSQL
If you'd like to learn how to use PostgreSQL, here is a comprehensive guide I found incredibly useful:
### Contact Me
If you have any questions or find any issues with this documentation, feel free to contact me!
:envelope:
:bird: @beliciataylor
For more information about me, visit my website:
{% embed url="" %}
# Overview
Configuring AWS EC2 auto scaling group for PostgreSQL
This section reviews how to configure an AWS EC2 instance for a PostgreSQL server. We will
1. Create a security group that allows SSH and PostgreSQL server connections
2. Create a launch template
3. Create server using auto scaling group (recommended) OR single instance
4. Attach an elastic IP address to our instance
Each of these subsections contains a video that walks through the instructions.
### Why use AWS EC2 Auto Scaling over a single instance?
Auto-scaling adds or removes EC2 instances depending on changes in demand on the server. By using an auto scaling group, we can keep costs down by utilizing a smaller-sized instance (i.e 2 vCPUs) and adding instances when CPU usage increases.
AWS EC2 auto scaling allows you to
* Assign a launch template to your auto scale group
* A launch template is a saved pre-configuration of an instance that you can use to launch instances that have the same parameters
* AWS uses your launch template to add instances you've previously configured when scaling out your application
* Note: scaling out is when AWS **adds** instances when demand increases
* Configure the minimum, maximum, and desired number of instances for your application
* Prevent certain instances from being automatically terminated when scaling in
* Note: scaling in is when AWS **removes** instances when demand decreases
* Set a scaling policy that defines when instances should be added or removed from the group
### References
*Guides*
(1) Research Documentation for Creating Jupyter Hub Server Using AWS,
(2) Better Programming (Medium), *How to Provision a Cheap PostgreSQL Database in AWS EC2*,
*Documentations*
(3) Amazon EC2 Instance Types,
(4) Amazon EC2 On-Demand Instance Pricing,[ https://aws.amazon.com/ec2/pricing/on-demand/](https://aws.amazon.com/ec2/pricing/on-demand/)
(5) Amazon Elastic Compute Cloud, *Amazon EBS-optimized instances*,
(6) AWS EC2 Auto Scaling User Guide, *What is Amazon EC2 Auto Scaling?,*[ https://docs.aws.amazon.com/autoscaling/ec2/userguide/what-is-amazon-ec2-auto-scaling.html](https://docs.aws.amazon.com/autoscaling/ec2/userguide/what-is-amazon-ec2-auto-scaling.html)
(7) Amazon EC2 Auto Scaling, *Creating a launch template for an Auto Scaling Group*,
(8) Amazon EC2 Auto Scaling, *Health checks for Auto Scaling,* [*https://docs.aws.amazon.com/autoscaling/ec2/userguide/healthcheck.html*](https://docs.aws.amazon.com/autoscaling/ec2/userguide/healthcheck.html)
(9) Amazon Elastic Compute Cloud, *Status checks for your instance*,
(10) Amazon EC2 Auto Scaling, *Controlling which Auto Scaling instances terminate during scale in*,
(11) Amazon EC2 Auto Scaling, *Target tracking scaling policies*,
*Forums*
(12) StackOverflow, *What is the smallest AWS EC2 Instance I can run Postgres DB on?*,[ https://stackoverflow.com/questions/61718959/what-is-the-smallest-aws-ec2-instance-i-can-run-a-postgres-db-on](https://stackoverflow.com/questions/61718959/what-is-the-smallest-aws-ec2-instance-i-can-run-a-postgres-db-on)
(13) StackOverflow, *How does the ARM architecture differ from x86?*,
# Create Security Group
Configure security group for launch template
You need to allow the following network traffic to your PostgreSQL instance.
* SSH: Allows you to access Linux instance through your terminal
* PostgreSQL: Allows incoming requests for PostgreSQL server
To create a security group for your instance in the launch template,
1. Navigate to your AWS EC2 dashboard.
2. Under "Network & Security", select "Security Groups"
3. Select "Create security group"
4. Security group name (Example): PostgreSQL Server Security
5. Description (Example): Allow PostgreSQL
6. VPC: Select VPC for your instance
7. Security group rules (Inbound): Select add rule and add the following:
1. SSH: For source, choose "Anywhere"
2. PostgreSQL: For source, choose "Anywhere"
8. You do not need to change the outbound settings.
9. Tags: Skip tags
10. Select "Create Security Group"
Your security group for your PostgreSQL server is ready to be added to your launch template.
{% embed url="" %}
# Create Launch Template
Configurations for launch template
If we want to create an auto scale group, we first have to create the launch template for our instance. The launch template will be used by our auto scale group to add instances that all have the same configuration.
To open the launch template configurations,
1. Navigate to your AWS EC2 dashboard.
2. Under "Instances", select "Launch Templates".
3. Select "Create launch template".
### Launch template name and description
These settings can be customized for your server, but I've provided examples for you to use during your set-up.
1. Launch template name (Example): PostgreSQL-Ubuntu
2. Template version description (Example): PostgreSQL server instance template for project.
3. Auto Scaling guidance (optional): Check box if you'd like although I did not see a need for it
### Amazon machine image (AMI)
For this documentation, we will use an Ubuntu 20.04 machine. This was the preferred machine for our set of tasks, but another machine could serve as an acceptable substitute.
1. In the search box, type "Ubuntu Server 20.04 LTS".
2. Select the option where "architecture: 64-bit (x86)"
**Note about AMI:** The instructions in this document are catered towards this AMI; choosing a different machine may require following a different set of instructions.
**Note about AMI Architecture:** When selecting the machine, you will note that two Ubuntu 20.04 options will appear. They differ in their architecture: (Intel) x86 and ARM. To quote this [StackOverflow](https://stackoverflow.com/questions/14794460/how-does-the-arm-architecture-differ-from-x86) post discussing their difference, ARM "is a simpler architecture, leading to small silicon area and lots of power save features", whereas Intel x86 is "a power beast in terms of both power consumption and production."
As I am no expert in computer architecture, and I am furthermore assuming they can both serve our purposes, I decided that the "power beast" option sounded much more attractive.
### Instance type
Since this instance will be launched in our auto scale group, we will select an instance type with 2 vCPUs.
1. In the search box, type and select "m4.large".
**Note about Auto-Scale:** If the auto scale sees a rise in demand, it will add another instance with 2vCPUs, resulting in our server having 4 vCPUs at its disposal.
**Note about Instance Type:** We'll be selecting an "M4" instance.
* According to [AWS EC2 Instance Types](https://aws.amazon.com/ec2/instance-types/) page, an M4 instance is best used for "small and mid-size databases."
* The [EC2 on-demand instance price](https://aws.amazon.com/ec2/pricing/on-demand/) for an "m4.large" instance (2 vCPUs) is $0.10 per hour, which works well for our budget.
### Key pair (login)
I would recommend adding a key pair in order to SSH into your machine. However, this is up to you.
As of writing this documentation, AWS has a feature called "EC2 Instance Connector" that allows you to enter your machine through AWS on your browser, and you do not require a key pair.
However, if you want other members of your project team to access your machine through SSH without a key pair, I will refer you to this Stack Overflow forum:
{% embed url="" %}
Either way, here are your options:
* If you do not want to associate a key pair, leave the default setting: "Don't include in launch template"
* Recommended: If you do want to associate a key pair, select a key pair you've previously made from the drop down menu, or select "Create new key pair" to create a new one.
### Network settings
For our case, we'll be using our Virtual Private Cloud (VPC). Also, we'll select our security group that we made in the previous section to attach to our instances.
1. If not already highlighted, select Virtual Private Cloud (VPC).
2. Security group: Select the security group created in previous section
### Storage (volumes)
I increased the volume size from 4 to 100 GiB. You may choose whatever storage volume fits your needs.
1. Select "Volume 1"
2. Change size (GiB) (Example): Type "100"
### Resource tags
Adding tags to all our launch template instances will be important when we want to backup our EC2 instance in a later section.
1. Create a name for your key and value
* EX. PostgreSQL (Key) AND Database (Value)
2. From Resource types, select "Instances" and "Volumes"
Feel free to further customize your launch template for your needs. Once you've finished, select "create launch template". Your template is ready to be used!
{% embed url="" %}
# Option 1: Create Auto Scaling Group
Configurations for auto scaling group
Once our launch template is ready, we can create our auto scaling group. Our auto scaling group will be able to increase or decrease the number of instances for our server based on changes in CPU usage, and this will help keep the cost of our server down.
To open the launch template configurations,
1. Under "Auto Scaling", select "Auto Scaling Groups".
2. Select "Create Auto Scaling Group".
#### Step 1: Choose launch template or configuration
Name your auto scaling group here, and select the launch template configured in the last section.
1. Name, Auto Scaling group name (Example): PostgreSQL Ubuntu
2. Launch template (Example): PostgreSQL-Ubuntu
1. Version: Leave as Default (1)
#### Step 2: Configure settings
1. Under "Instance purchase options", leave as the default option: "Adhere to launch template"
2. Under "Network", select your VPC and select your subnets
1. In my case, I selected the first three subnets in my region.
#### Step 3: Configure advanced options
Leave options as defaults.
#### Step 4: Configure group size and scaling policies
The goal for our auto scaling group is to scale up when there is an increase in CPU usage on our server.
To configure group size and scaling policies,
1. Under "Group size", set the following:
* Desired capacity: 1
* Minimum capacity: 0
* Maximum capacity: 2
2. Under "Scaling policy", select "Target tracking scaling policy" and set the following:
* Scaling policy name (Example): PostgreSQL Server Average CPU
* Metric type: Average CPU utilization
* Target value (Example): 75
* Disable scale in to create only a scale-out policy: Leave unchecked
3. Under "Instance scale-in protection", leave the box unchecked.
**Note on size and scaling policies:**
* Change the minimum group size to 0
* This will prevent the launch of an instance when we want to stop the server
* *Why?* If the minimum group size capacity is "1" and the instance is stopped, your auto scaling group will launch another instance to keep the minimum group size as 1.
* Change the maximum capacity from 1 to 2
* Auto scaling group can then scale out when there's an increase in CPU utilization.
* Set predefined metric type "Average CPU utilization" in dynamic scaling policy
* For our instance to scale in and out depending on CPU utilization, we will set the (dynamic) scaling policy
* Selecting "Average CPU utilization" will keep the average CPU utilization at our defined target value by scaling in or out.
* Ensure that auto scale group can scale in and scale out
* Scale-out: If we "disable scale in to create only a scale-out policy," then our auto scale group will only add instances and not remove them.
* We want our auto scale group to remove instances. If not, our costs would add up because we would have extras instances that we do not need for our CPU utilization.
* Scale-In: If we enable "instance scale-in protection" for the entire auto scale group, then the auto scale group could not terminate the instances and scale in.
* "Instance scale-in protection" is a crucial setting to add for our "master" instance, but we do not want to set it for all of our instances.
#### Step 5: Add Notifications
I did not add notifications, but if you'd like to be notified of when your serve instances launch, terminate, fail to launch, and/or fail to terminate, then "add notification" and add your email address.
#### Step 6: Add tags
We are not going to add tags to our auto-scale group because we already added tags in the launch template.
The launch template, which is how auto scale will be creating instances, will already tag all our resources. Adding tags here would be redundant.
#### Step 7: Review
Check your settings. Once you're satisfied, select "Create Auto Scaling group".
Your Auto scaling group is now working, and you should see that an instance has launched under "Instances".
### Master Instance Settings
We want to ensure that our master instance with all our PostgreSQL configurations, which we will configure in the next chapter, will not terminate if
1. the auto scale group decides to scale-in,
2. the instance is accidentally terminated, or
3. the instance is stopped
In order to prevent our master instance from being terminated in any of the above scenarios, we will
1. enable scale-in protection,
2. suspend the "Replace Unhealthy" process, and
3. change termination protection
#### Enable Instance Scale-in Protection
To set scale-in protection,
1. In the EC2 menu, under "Auto Scaling", select "Auto Scaling Groups"
2. Select the auto scaling group you created
3. Select "Instance management"
4. Under "Instances", select your master instance (it should be the only instance present in the auto scale group)
5. Under "Actions", and under "Instance scale-in protection", select "Set scale-in protection"
**Suspend the "ReplaceUnhealthy" process**
To suspend "ReplaceUnhealthy",
1. In the EC2 menu, under "Auto Scaling Groups", select "Auto Scaling Groups"
2. Select your auto scaling group
3. Under details, go to "Advanced configurations" and select "edit"
4. Under "Suspended processes", select "ReplaceUnhealthy"
5. Select "Update"
**Note on suspending "ReplaceUnhealthy":** The job of the "ReplaceUnhealthy" process is to terminate instances that are marked as unhealthy by the "HealthChecks" process and launch a new instance to replace them. Instances that are stopped (either due to instance failure or being stopped) will activate "ReplaceUnhealthy". Therefore, this is the process that would cause our instance to be terminated and replaced if we stopped our instance.
The reason we are choosing "ReplaceUnhealthy" and not the "Terminate" process is because "Terminate" does not allow any instance to be terminated in the auto scale group. However, we do want instances that are no longer necessary (i.e our server does not demand as much CPU to stay running) to be terminated. Therefore, we want "Terminate" to continue operating.
#### Change Termination Protection
To enable termination protection,
1. In the EC2 menu, select "Instances"
2. Select your master auto scaling instance
3. Select "Actions"
4. Select "Instance Settings"
5. Select "Change Termination Protection"
6. Select "Yes, Enable"
{% embed url="" %}
# Option 2: Create a Single Instance
Create a single instance from your launch template
If you would prefer to not create an auto scale group for your server, then feel free to launch a single EC2 instance instead! In this section, we will walk through how to spin up a EC2 instance from our launch template.
1. In the EC2 menu, select "Launch Templates"
2. Select your launch template
3. Under "Actions", select "Launch instance from template"
4. Your launch template settings will appear. You have two options here:
* Option 1: You can change any of the settings from the launch template
* For example, you may want to choose an instance type that has more CPUs (EX. m4.xlarge) since you will not be using an auto group to scale for more CPU power.
* Option 2: Keep the settings from your launch template
5. After you are satisfied with your settings, select "Launch instance from template"
You've now launch an instance based on your launch template! If you go to "Instances", you should see your new instance being created.
{% embed url="" %}
# Attach Elastic IP Address
Attach an elastic IP address to your PostgreSQL server instance
If our master instance is stopped, the instance will be assigned a new public IP address. This can be very inconvenient because we would constantly have to check what our new IP address is in order to access our server.
To avoid this problem, we'll assign an elastic IP address to our master instance in the auto scale group.
To create an elastic IP address,
1. Navigate to your AWS EC2 dashboard.
2. Under "Network & Security", select "Elastic IPs".
3. Under "Elastic IP Address settings" and "Public IPv4 address pool", leave the default option "Amazon's pool of IPv4 addresses"
4. Tags (Optional): If you'd like, you can associate your PostgreSQL tag with your Elastic IP address.
5. Select "Allocate"
After being brought back to the "Elastic IPs" page,
1. In the green box that says "Elastic IP address allocated successfully", select "Associate this Elastic IP address"
2. Under "Resource type", leave the default option: "Instance"
3. Under "Instance", select the master instance of your auto scale group
4. Under "Private IP address", select an IP address
5. Under reassociation, leave the box unchecked
**Note about reassociation**: Having the elastic IP be reassociated means that if the instance with the elastic IP address in your auto group fails, the IP address would be reassociated with the instance that replaces the failed one.
For our case, if our master instance fails, it would be best if the elastic IP address is simply dissociated. Then, we can use a backup (discussed in a later chapter) to recreate our master instance and reattach the elastic IP address to the backup.
{% embed url="" %}
# Overview
Install and configure PostgreSQL on our Ubuntu instance
Now that our Ubuntu instance is running on AWS EC2, we will now SSH into our instance and configure PostgreSQL (Version 12.4).
In this chapter, I will give instructions and rationale on how to do the following:
1. Install PostgreSQL
2. Create roles for PostgreSQL
3. Allow for remote connections
Be sure to SSH into your instance before following the instructions.
### References
*Guides*
(1) TecMint, *How to Install PostgreSQL and pgAdmin4 in Ubuntu 20.04*,
(2) DigitalOcean, *How To Install and Use PostgreSQL on Ubuntu 20.04*,
(3) Better Programming (Medium), *How to Provision a Cheap PostgreSQL Database in AWS EC2*,
(4) The Geek Stuff, *How to Allow Remote Connection to PostgreSQL Database using psql*,
*Documentations*
(5) PostgreSQL 12 Documentation, *Chapter 20. Client Authentication*,
(6) PostgreSQL 12 Documentation, *20.1. The pg\_hba.conf File*,
(7) PostgreSQL 12 Documentation, *20.5. Password Authentication*,
(8) PostgreSQL 12 Documentation, *19.3 Connections and Authentications*,
*Forums*
(9) StackExchange, *Difference between sudo user and root user*,
(10) StackOverflow, *How do I exit the vim editor?*,
(11) AskUbuntu, *How to install updates via command line?*,
(12) AskUbuntu, *Is there a command to list all users? Also to add, delete, modify users, in the terminal?*,
(13) Unix StackExchange, *What is the difference between su username and su - username?*,
(14) AskUbuntu, *'sudo su -' vs 'sudo -i' vs 'sudo /bin/bash' - when does it matter which is used, or does it matter at all?,*
(15) Stack Overflow, *How to configure PostgresSQL postgresql.conf listen\_addresses for multiple ip addresses*
(16) AskUbuntu, *What does -y mean in apt get -y install command,*
# Install PostgreSQL
Install PostgreSQL onto your Ubuntu instance
In this section, we will be walk through setting up PostgreSQL on your Ubuntu instance.
### Update Ubuntu Machine
Before we install PostgreSQL into our instance, we must first update and upgrade our packages in the instance.
```
sudo apt-get update -y && sudo apt-get upgrade -y
```
{% hint style="info" %}
The **-y** is a Linux flag that means to response "Yes" to any questions or prompts that appear in these commands.
{% endhint %}
### Install PostgreSQL
To install PostgreSQL, type the following line of code:
```bash
sudo apt install postgresql -y
```
That's it! PostgreSQL is now installed onto your machine. When you installed PostgreSQL, you also created a "postgres" user account.
# Add Role to PostgreSQL Server
Add role to login and create databases in your postgres server
To start using PostgreSQL, we will need a user and a database.
First, we'll switch to the postgres user account on our instance.
```
sudo su postgres
```
To run PostgresSQL, type
```
psql
```
In the next two lines, we'll create a user role who will be able to login and create databases ([3](https://medium.com/better-programming/how-to-provision-a-cheap-postgresql-database-in-aws-ec2-9984ff3ddaea)). Replace `username` and `'password'` with your own:
```sql
CREATE ROLE username
WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'password';
```
To stop running PostgresSQL, type
```sql
\q
```
To log out of the postgres account, type
```sql
exit
```
# Allow Remote Connection
Configure client authentication and listen address for remote connection to PostgreSQL server
In order to access our PostgreSQL server hosted on AWS from our local computers, we must configure two different files to allow connection from any IP address to our server.
First, we will edit the "pg\_hba.conf" to configure client authentication. Then, we will edit the "postgresql.conf" file to allow any IP address to connect to our server.
### Configure Client Authentication
In this section, we will
1. define what client authentication is
2. use an example to understand its purpose
3. review different methods for implementation
At the end, we'll configure the client authentication file to use one of the securest methods for our server.
**Note: Understanding Client Authentication**
Imagine the following scenario: you have a PostgreSQL server with two databases (db1, db2) and three users (user1, user2, user3).
1. Bob is user1, who has access to d1
2. Dave is user2, who has access to db2
3. Sally is user3, who has access to d1 and db2
Bob wants to connect to the PostgreSQL server through their pgAdmin4 (i.e a client application). In order to connect, Bob has to tell the server that they want to connect as "user1" to "db1".
PostgreSQL server determines the identity of the client (i.e Bob through pgAdmin4) in a process called *Authentication* ([9](https://www.postgresql.org/docs/8.3/client-authentication.html)). Through authentication, the server also determines whether Bob is allowed to connect to db1 as user1.
PostgreSQL has different methods of client authentication which are further explained in these two documentations:
* [pg\_hba.conf File Section Documentation](https://www.postgresql.org/docs/12/auth-pg-hba-conf.html)
* [Password Authentication Documentation](https://www.postgresql.org/docs/12/auth-password.html)
#### Editing pg\_hba.conf file
For this example, we'll be setting our authentication method as md5. To do this, we must edit the client authentication configuration file titled "pg\_hba.conf" using a text editor.
To edit "pg\_hba.conf" using the "vim" text editor,
* Type the following line of code in your terminal
```
sudo vim /etc/postgresql/12/main/pg_hba.conf
```
* Using your keyboard arrow keys, scroll through the file until you've reached the portion of the file that looks like a table, where the last column is labeled "METHOD"
* Under IPv4 and IPv6 local connections, change the method to "md5"
* If the method is already set to "md5", then you do not need to do anything.
* Additionally, in the "ADDRESS" column, change the IP addresses to be "0.0.0.0/0" for IPv4 and "::/0" for IPv6
{% hint style="info" %}
To type in the vim editor, press "i" to be in insert mode, type as you normally would, then press ESC to be in command mode. To exit and save, type ":wq".
{% endhint %}
Here are the before and after pictures for your IPv4 and IPv6 connection configurations:
### Change Listening Address
In "postgresql.conf", the variable "listening\_addresses" is set to "localhost". We will change "localhost" to "\*" in order for the server to listen for any IP connection. ([8](https://www.postgresql.org/docs/12/runtime-config-connection.html))
* Open the file in vim by pasting the result in the last code here
```
sudo vim /etc/postgresql/12/main/postgresql.conf
```
* Change "listening\_addresses = localhost" to "listening\_addresses = \*"
{% hint style="info" %}
Make sure `listen_addresses = "*"` does not have a `#` in front of it or else the statement will be commented out.
{% endhint %}
* Now that you've changed these files, restart your PostgreSQL server using the following line of code:
```
sudo systemctl restart postgresql
```
You are all set to connect remotely to your server!
# Overview
Remotely connect to your PostgreSQL server using pgAdmin4
Now that we have spent a lot of time configuring our server, it's time to use it! We will be reviewing two different ways to connect to our server remotely:
1. Using pgAdmin4, PostgreSQL's most popular GUI
2. Using Python API
### Database Server Host Name/Address
Your server host name/address can be found on the "Instances" section of AWS. You will need this address to connect to your server.
1. Click on your PostgreSQL instance
* Information about your instance should appear below
2. Copy and save your "Public IPv4 DNS", your server's host name and address
* Your "Public IPv4 DNS" should be written in the following format: ec2-\[your-public-IPv4-address].\[region].compute.amazonaws.com"
### References
*Guides*
(1) Better Programming (Medium), *How to Provision a Cheap PostgreSQL Database in AWS EC2*,
{% embed url="" %}
(2) PostgreSQL Tutorial, *PostgreSQL Python: Connect to PostgreSQL Database Server*,
{% embed url="" %}
(3) Towards Data Science (Medium), *Python and PostgreSQL: How to Access A PostgreSQL Database Like a Data Scientist*,
{% embed url="" %}
# Connect with pgAdmin4
Connecting to your PostgreSQL server hosted on AWS on your local pgAdmin4
### Install pgAdmin4
To install pgAdmin4 on your local computer, follow the directions here:
{% embed url="" %}
### Connecting to Server
* Open pgadmin4 on your local computer
* Select "Object"
* Under "Objects", go to "Create" and then select "Server"
* OPTION 2: You can also click "Add New Server" in the "Quick Links" menu in the Dashboard
* Under "General"
* Name: Write any name you'd like for your server to be on your local computer: EX. PostgreSQL AWS
* Under "Connection"
* Host name/address: Paste your host name we copied from our AWS instance (discussed further in the Overview section of this chapter)
* Port: 5432 (standard for PostgreSQL)
* Username: your "username" created in the "Add Role to PostgreSQL Server" section in previous chapter
* Password: type "password" for your "username"
* Save password?: Select if you prefer not to type your password every time you log into the server locally
* Press the "Save" icon at the bottom right corner of the dialog box
You've now connected to your server!
{% embed url="" %}
# Connect with Python API
Connect to your server using python scripts
You can also connect to your database and execute queries through PostgreSQL's Python API. We'll be using the python package *psycopg2* to connect to our database in the following steps:
1. Create a `database.ini` file containing sensitive information needed to connect to our database
2. Create a `config.py` script containing a config function
3. Create and run a `connect_to_db.py` script to connect to the database
I relied heavily on this website for the information on this page:
{% embed url="" %}
### Create "database.ini"
To connect to your database, you first need to provide Python your credentials. If you're sharing project code with team, or if your code is in a public repository, you don't want to share your host name, username, and (most importantly) password with everyone who sees the Python scripts that run queries.
Instead, we can write all of our login credentials in a file titled `database.ini`and refer to it in our `config.py` script. Additionally, if you're using a GitHub repository to share project code, we can add `database.ini` to our .gitignore file so that our personal login information is not contained in the script.
In your folder where you will place all your Python scripts with queries (meaning the script will require you to connect to your database), write a `database.ini` file that contains the following information:
```
[postgresql]
host=ec2-[your-public-IPv4-address].[region].compute.amazonaws.com"
database=db
user=username
password=password
```
### Create config.py script
To set your configurations for connecting to your database in Python, create a `config.py` file with a function called config, which you will be using whenever you want to connect to your database in your scripts. ([2](https://www.postgresqltutorial.com/postgresql-python/connect/))
Place this code in a script titled `config.py`:
```
from configparser import ConfigParser
def config(filename='database.ini', section='postgresql'):
# create a parser
parser = ConfigParser()
# read config file
parser.read(filename)
# get section, default to postgresql
db = {}
if parser.has_section(section):
params = parser.items(section)
for param in params:
db[param[0]] = param[1]
else:
raise Exception(
'Section {0} not found in the {1} file'.format(section, filename)
)
return db
```
### Connect to your Database
Now, we will run a function called "connect()" to test whether we can connect to our database.
Place the following code in a script called `connect_to_db.py`:
```
import psycopg2
from config import config
def connect():
""" Connect to the PostgreSQL database server """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# execute a statement
print('PostgreSQL database version:')
cur.execute('SELECT version()')
# display the PostgreSQL database server version
db_version = cur.fetchone()
print(db_version)
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
if __name__ == '__main__':
connect()
```
Run `connect_to_db.py` in your terminal:
```
python connect_to_db.py
```
Your output should be a variation of the following
```
Connecting to the PostgreSQL database...
PostgreSQL database version:
()
Database connection closed.
```
If you see the above output, then you've successfully connected to your database!
### Additional Resources
If you want to learn more about creating table and executing other queries using the Python API, the following documentation, which I also referenced throughout this section, has tutorials and examples on creating table and executing other queries using psycopg2.
{% embed url="" %}
Also, the following Medium post also discusses how to use pandas to create dataframes from tables in your database (towards the end of the post).
{% embed url="" %}
# Overview
Backup the server to ensure your server and data will not be lost in the event of an instance failure
Now that your server is configured and running, we now want our server to be backed up on a regular basis to ensure your server and data will not be lost in the event of an instance failure. We can use AWS Data Lifecycle Manager to regularly back up our master EC2 instance and all of the resources with our PostgreSQL/Database tag.
During backups with this method, the instance will be stopped for a couple of seconds during your designated backup period ([1](https://medium.com/better-programming/how-to-provision-a-cheap-postgresql-database-in-aws-ec2-9984ff3ddaea)). Recall that when we set up our auto scale group we suspended the "ReplaceUnhealthy" process, so our master instance will not be terminated by this backup. Also, we attached an elastic IP address to our instance, so our public IP address will not change every time we do a backup.
### References
(1) Better Programming (Medium), *How to Provision a Cheap PostgreSQL Database in AWS EC2*,
{% embed url="" %}
(2) Better Programming (Medium), *Create Easy Disk Snapshots with AWS Data Lifecycle Manager*,
{% embed url="" %}
# Configuring Automatic Backups
Set up backups every
To configure automatic backups using AWS Data Lifecycle Manager,
1. In the EC2 menu, under "Elastic Block Store", select "Lifecycle Manager"
2. Select "Create Lifecycle Policy"
3. In the main (i.e first) section,
1. Under "Policy type", leave the default: EBS snapshot policy
2. Under "Select resource type", select "Instance"
3. Description (Example): Backup PostgreSQL server
4. Under "Target with these tags", select the tag you created during your Launch Template Setup. EX. PostgreSQL (Key), Database (Value
4. In "IAM Role" section,
1. Leave as defaults
5. In "Policy Schedule 1" section,
1. Schedule name (Example): Server Daily Backup
2. Frequency (Example): Daily
3. Every (Example): 24 hours
4. Starting at (Example): 9:00 UTC (4 AM EST)
5. Retention type (Example): Count
6. Interval (Example): 5
6. Leave the other sections as their defaults
7. Select "Create Policy"
Now every resource that has our PostgreSQL tag, including our instance, will be backed up daily at 4AM EST, and we will only keep five of our backups at a time.
{% embed url="" %}
# Next Steps
Things to do after setting up PostgreSQL server
You now have a brand new PostgreSQL server! Some things to do next would be to...
(1) create users to access the server and databases,
(2) create tables and insert data into your databases, either using Python's API or pgadmin4 interface,
(3) increase your server's security by specifying which IP addresses have access to the server,
... and much more!
As I said in the introduction, feel free to reach out to me with any questions or concerns about the documentation!
Email:
Twitter: @beliciataylor