How to create PostgreSQL database on AWS RDS and connect using DBeaver from local

hello everyone in this video we're going to see how to create a postgress database on Amazon ideas and then how to connect to that database from our local uh using any of the database uh clients okay I'm going to use dbv to uh connect to that postest database on ads from my local laptop okay so the first step is uh let's try to create a database on Amazon IDs so for that I'm already in my Amazon IDs console I'm just going to click on create database here and so this uh takes you to this page where you need to fill a few details to create the database okay I'm going to select uh standard create and then these are the data type I mean database engine types uh options that the ads offers so we are going to create a postgress database here okay I'm going to select postgress and then these are the engine versions I'm just going to select the latest version and there the templates okay whether it is a production database or Dev test database I'm just going to select a free tire like I mean option because uh this is just for some demo purposes and and uh sample testing so this should be fine for us okay so I'm just going to select free tire and as soon as you select free tire uh these options get disabled by default because the free tire instances are not eligible for multi availability Zone clusters and uh couple of things like that okay so we should be fine I mean we don't care about all those things uh in this case Okay so moving on uh this is your database instance identifier like I mean this is the name of your instance you can call it whatever you want I'm just going to call it my test database and then here is your like credential settings so this is your master username which will be used to log to the database instance okay so you can also autogenerate a password or you can select I mean set the password yourself manually so we are going to set the password ourselves okay I'm just going to give some password here and make sure that you make a note of this password just enter the same password here as well okay so just make sure you remember this master username and password so this will be used to log into that uh database at a later Point okay and uh this is the DB instance class so since we have selected uh you know free tire instance so I mean couple of options are eliminated for us so this should be fine I mean this is a micro instance and that should be fine for us okay and this are the storage uh settings so you can select like I mean the what is the type of storage that you want uh if you want like I mean uh high like I mean uh input output performance you can select this provision iops but I think this uh like I mean are charged so I mean for our use case it should be fine okay and uh you can disable this Auto I mean Auto scaling for storage as well okay so that's it about the storage and then uh coming to the connectivity so we don't want to attach any uh Hy to Compu in I mean resources and the network type we'll keep it as uh ipv4 and VPC we are going to uh create this in our default VPC so if you want to create it like I mean you can select any VPC that you want to create this instance in okay I'm just going to create it in my default VPC and uh then there is a subnet group okay here also if you want to put it in a certain subnet group you can select that certain group group I'm just going to leave it as default and uh next one is a public access so this is a bit important one because we will be trying to access this database from outside of our VPC so that is when we will have that is why we will have to enable this public access okay so if you are like I mean if you don't need don't have a need to connect to your database from outside your VPC you can just leave it as empty so when you I mean uh you can select no when you select no no one from outside your VPC can connect to your database okay uh so I'm just going to select yes because I'm going to try and connect to this database from outside my VPC okay so make sure you select whichever is appropriate for you and then like I mean if you want to collect from your local laptop which is not in your VPC right so you need to select yes here and then the VPC security Fire Group okay so if you have a certain like I mean security groups which have all the rules defined like I mean you can just select that or you can create a new VPC VPC Security Group as well this actually plays a bit important role uh I'll show you in the later part of this video why this is important okay so for now I'm just going to uh select my default Security Group okay and all those things I'm just going to leave it as empty I mean default and then database authentication how you are going to login into your database so I'm just going to select password authentication and monitoring turn on performance insights I don't need this I'm just disabling all those things okay so all these things I'm just going to leave it as uh default and then click on create database okay so yeah I think it it takes a couple of minutes to create the database so if you see here uh it is in creating state so let's wait for the database to get created and then once it is created we will try and connect to the database from our local DB so uh now our database is created it's a successfully created database and uh the status is available now let's Tre uh let's let's try and connect uh to this database from our DB viewer okay so I'm just going to click on this database and uh these are the details of the database okay so to connect to this database we need this endpoint so I'm just going to copy this endpoint and open my DB okay so in my DB I'm just going to click on new database connection and select PR SQL here then click on next and in the host I'm just going to paste the link that I just copied and the database I'm just going to leave it postgress the username is postgress and the password is basically whatever ever you had given while you creating the database okay so that is a password and I'm just going to click on test connection here okay so let's see if it is able to connect to the database okay so if you see we are getting an error saying that connection attempt timed out okay so this is very a common uh error and let's see how to fix this and connect to the database okay so for that let's go back to our AWS console and so the database that you created here click on this in the security click on VP security groups okay so this is the security group in which I had deployed my uh instance like attached say so inside the security group there is something called as inbound rules okay so inbound rules is basically uh your like I mean definition of what traffic you can I mean you're allowing inside this okay so in the inbound roles if you see it is only allowing the traffic from the the security group itself okay so what we need to do is we need to click on this edit in Bound rules and allow the traffic to come from our like I mean local okay so for that click on uh add rule here and uh like you can just say all TCP so or you can like I mean if you want to just allow from one of the port you can just give like I mean since this uh post on Port 5432 you can just give that uh Port here okay and here in the source you can like I mean so this is very important okay so if you want to allow the traffic from any ipv4 address you can just select any ipv4 and basically so anyone from the internet like I mean who has your database uh endpoint and username and password can connect to your database okay this is of course like I mean not very secure because you are allowing everyone like I mean to connect to your database so so if you want to just allow from a certain IPS right like I mean custom you can just select custom and then give your like I mean give the IP ranges here whatever like I mean uh C block you can just give that custom IP here okay or if you just want to uh allow from a particular IP like your IP you can just select this one okay so for now I'm just going to select anywhere but uh like like I said this is not recommended because anyone with your uh database endpoint and username and password will be able to connect to your database okay and you even if you're doing this it's always suggested that you create a separate Security Group and then attach that Security Group to your database um because if there are any other uh instances that are using the security group then they will also be exposed to like I mean uh all these IPS that you're allowing here okay so yeah with that disclaimer so let's just give for this use case I'm just going to give anywhere IP before uh so what I'm essentially saying here is that allow the TCP connections on this port from anywhere in the internet to the security group okay so yeah of course it's again it's saying the same uh disclaimer here here as well because you're allowing all the IP addresses so yep I'm just going to click on Save rules here and yep so that rule is saved now so now let's go back to the DB viewer and okay and then try this connection again so I'm just going to click on test connection and wait for it to get connected okay so if you see now that uh I mean our connection is successful and we are now connected to the database okay so let me do finish so if you see here now you are able to see like I mean all the databases that are there here so this is of course the default database set was created so you can like I mean run your queries you like do whatever you want to do here this database so that was a short demo on showing you how to create a postest database and uh establish connection to your database from your local laptop okay I hope you found the video helpful and I'll see you in the next video thank you

Channel: AWS Made Easy

Views: 6,191

Keywords: AWS, Cloud, Python, Boto3, AWS-API

Id: _Yzr7yBGWQI

Length: 10min 16sec (616 seconds)

Published: Thu Oct 19 2023