How to install PostgreSQL on AWS EC2

hey everyone in this crash course i'll be guiding you through setting up postgres on ec2 before we get started i want to get a few things out of the way in case you're new to databases or have a permissive budget i'd strongly encourage you to use a managed service like rds or heroku postgres these services are designed for production use and feature functionality like failover in case your postgres server actually fails the example used in this video will cost around dollars a month to run and feature no failover or any sort of managed service database access should always be controlled with security groups and whitelisted ips and please don't leave your database server open to the public as always you can find all the commands used in this video along with step-by-step instructions in the blog post linked in the description below okay with that out of the way let's get started first navigate to the c2 console on aws and go ahead and launch an instance here i'm going to use an ubuntu 20.04 lts release so i'm going to scroll down find ubuntu server 20 select it choose the t2 micro instance type hit next next next and once more okay here i'm going to restrict ssh access on port 22 in my public ip address and apply the same rule to port 5432 which is used by the postgres service with the security group configured it's time to create a key pair so we can actually ssh into the server i'm going to give a key pair name and download it and once it's downloaded i'm going to click on launch instances while the instances launch let's jump into the terminal navigate to the downloads directory and enable read only mode on the pem file we just downloaded so change mode does just that we will be able to read the file and use it to authenticate with the server to make sshing a bit more ergonomic i'm going to move the pem file to my ssh directory and open up the ssh config in visual studio code here i'm going to provide the hostname which is the ip address of that server and the username will be ubuntu and the identity file will basically be the pem file that we use to authenticate so with this in place i will be able to connect by simply saying ssh post press on ec2 and it will allow me to connect to this remote machine so it looks like it just worked now here i'm going to update the package list and upgrade the system with the package list updated i'm going to install postgres the postgres installer will go ahead and create a user for us named postgres so this user can be used to communicate with the postgres service so here i'm going to login as postgres and then use the postgres user to create a role named ubuntu so this role will be used to communicate with the database the best practice here is to simply create users with the minimum amount of privileges they need to carry out whatever operations you want them to do in this example i'm going to allow the ubuntu role to log in and create databases in case you want to store multiple databases that will be consumed by different applications please make sure to create one user per application this way you can control access and compartmentalize what databases will be accessible by which user to finish up the user configuration i'm going to assign a password to this ubuntu user i'm just going to say password for now but in production you should use a strong password to make sure that you don't get hacked so right now your postgres server is only accessible from within this machine to make it accessible from the outside we will need to edit the postgres configuration file to do this i'm going to find it and edit the line that says listen addresses so it needs to be changed from localhost to wildcard this will allow postgres to communicate over the public dns name of this server next we need to allow users to actually authenticate with the server so to do that i need to find another file so i'm going to use this handy bash incantation and i'm going to find pghb.conf and open that with nano i'm going to navigate to the bottom of the file and drop in the following code i'm going to save it i'm going to exit out and restart postgres okay at this point the server is ready for use i'm going to use pg admin to test it out to see if everything works and here i'm going to just create a new server i'm going to give it a name and then i'm going to flip over to ec2 copy the public dns name and then provide the username that we configured from earlier along with the password and click save and the connection seems to work so i can see that there's a database i'm going to create a new one i'm going to call this test database and jump into it and here let's go ahead under tables create a users table and here i'm going to name the table users give it a column called name and set the data type to text i'm going to save that and let's see if we can write a little bit of sql and insert some data into it so i'm going to say insert into this table here named users there's going to be a column called name and to this name column the following values so i'm going to say roberta and finish it with the terminator and run it and looks like the insertion worked so let's see if we can actually see the data and we do all right that's it for now i'll see you next time

Channel: Restarone Solutions

Views: 8,302

Keywords: postgres, postgreSQL, amazon, AWS, EC2, ubuntu, server, dev ops, database admin

Id: LV2ooRnZqpg

Length: 5min 54sec (354 seconds)

Published: Sat Jan 02 2021