Postgres in Amazon RDS
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Patel, Denish
A compelling option for startups In recent years, Amazon's cloud service is key for deploying and scaling IT infrastructure tiers, and this is not different with Postgres as well. Amazon's relational database service for Postgres RDS - is a new but compelling option for the startups, when the resources are scarce and administrators are hard to find! RDS is a great option for people to achieve in terms of rapid deployment of replicas, ease of failover, and the ability to easily redeploy hosts when failures occur, rather than spending extensive time trying to repair. In the talk, I'm planning to topics related to managing Postgres RDS : Introduction of RDS Benefits of using RDS Challenges with RDS Choosing EC2 vs RDS Security Tuning Scaling Monitoring Backups Cost Generic Recommendations
 I have a lot of stuff to talk and it will finish in 30 minutes. So, I don't see anybody outside waiting for my talk. So, this is the great. So today we are going to talk about Postgres in Amazon RDS. How many of you don't know what is RDS? RDS. Okay. Okay. That's the main stuff. Yes. Okay. So, I will not talk a little bit about what is RDS, but I will dive more into what is Postgres RDS is going to be offered. Okay. So, who am I? First, I am a database architect working with Omniti for more than 7 years. I used to be Oracle DBA about 7 years ago. Then since then I haven't looked back working with Postgres and I am really happy if you are a person who wants to switch from Oracle to Postgres. This is the best database to work on. I am working with MySQL and NoSQL still like I prefer working on Postgres compared to other databases. My contact details, if you have any question after the talk, I am available on Twitter 24 by 7. Blog, I write sometimes, sometimes on my website. It's listed here. Let me give a brief overview of Omniti. Omniti is a consulting service providing solutions to the client to solve the problems that fall into one of this categories, scalability, reliability, HA, consistency, security. This is my first PGCon but I feel like home so which is good and I think I will be coming back next year probably. I visit other conferences in US but this is my first PGCon even though I am involved with Postgres for a number of years. And we are hiring. If you are awesome database guy or operations guy or whatever guy or girl. I can use this. Amazon RDS. What is Amazon RDS? We will talk about what? I am not representing Amazon. So I need to give their overview as well like their side like what they are marketing and what's my review of it. So I am going to talk about the highlights. We will go through like there is a five step process to set up a Postgres instance with the replication setup and everything. So we will go through that. If you want to follow through, if you have an Amazon account you can just spin up your instance too with me. Postgres RDS features that we care about like administration and some limitations and question and answer. I think if you want to hold on your question that might be good because some of the questions you might be answering at the talk progresses. But I will be happy to answer any question anytime if you feel like you don't want to hold on. So what is Amazon RDS? RDS is the full name is relational database services. So it is a managed relational database services. So as you can see here Amazon has started I think with MySQL. They started with MySQL then added Oracle SQL server and they added Postgres in November of last year. So from the maturity perspective MySQL is the most mature RDS right now because that has been in market for longer period of time and that makes sense. Service highlights it's managed. If you don't know what is managed means like you don't have to worry about hardware. You don't have to worry about installing the database. You don't have to worry about setting up the replication. You don't have to worry about failover. What else backups? You have to worry about little bit about monitoring from the application side on little bit of database side but usually they take care of as well. Compatible. So it's a compatible with whatever other services like you don't have to install a specific driver or anything like. You can connect to the RDS instance using basic wall using Java driver or.NET driver. Whatever driver you are working your application in you can able to connect to their database instance. Just wanted to clear this is just a database service. It's not like Heroku like that provides application as well lot of other stuff. But this is just a database. If you want to just bring up a database this is where you go for testing maybe. This is a database in the cloud that's another highlight there like it's in the cloud. Everybody should be in the cloud. Design for use with other Amazon website. So it's if you have Amazon if you are already working with the Amazon like you don't have to do anything extra to work with this service. You can just plug and play. And that's what they advertise is inexpensive because the theory is like managing the database and other those are the cost. Actually Omniti is providing the services like we are doing the managed services as well. So it's I think that makes sense. It's little expensive but it depending on your use cases. So let's get started with the Postgres RDS first. So just wanted to make sure that it is still in beta. So if you are not aware of it like I am I think I don't use for production for sure right now because still in beta and as you know beta is beta. But I will use Postgres 9 for beta 1. So once you have an Amazon account you can just click on the Amazon RDS. This is the first page you will hit and there are as I mentioned earlier there are different databases you can select for and we always select Postgres. So let's select the number 2 in the list. The next screen will be like it will ask you for production. Yes there are 2 options like let me explain the second option first. It's RDS free usage start so that will if you are just playing around like if you want to play right now you can just use like it's free service that you can play with the Amazon RDS. But we will choose like let's assume that we are using for a more important database. It's a multi-AG deployment and provision IOPS. How many of you don't know what is multi-AG? So multi-AG is like let me explain. Amazon has data centers in the different availability zones. So there is a region. Region is like US, Asia those are the regions. Under that they have availability zones like US East it's in Virginia, US West in California somewhere. So that's multi-AG. So if you want to set up that instance with the main instance like the master is in Virginia and set up a slave on the California let's say. So that is multi-AG. Provision IOPS? Provision IOPS? So. I'm actually from that team. Multi-AG is actually in a region not across regions. Right. Right. So it's just in like US East is where one region is multi-agent. Correct. Definitely. So region yeah. So region I think I have in the back in my slide like it's not multi-region. But I just wanted to give the overview of it. Provision IOPS is the IOPS you usually pay for and it's little complicated than you think but let's assume that we know about it. Yes. Next. We will select the DB engine is already select the license model. This is not for us because it's providing other services like ORAC or SQL server. So they need or my SQL licensing DB engine version is 933 right now. So be careful about that. DB instance class is I chose like M DB M1 large like there is a very good documentation. What kind of instance is like Amazon provides different kind of instances large extra large it will depending on the how much RAM and the CPU power your instance will have. So I selected M1 large for now multi-ag deployment. Yes. Auto minor version upgrade. Yes. What does that mean? Yes means like whenever there is a let's say 934 is available now. So whenever it will be available it will upgrade for you. So you don't have to upgrade and there will be window for that. Next story let's say we chose 100 gig use provision IOPS. Yes. And then I said 1000. So there is a ratio that you have to select like you cannot select 100 gig with 3000 IOPS. There is a ratio 3 to 10 ratio. I think we will talk about them in detail. DB instance identifier omni ti master database omni ti password. Done. Number 4. Now we will know we will be selecting port that you want to run the instance like default I use 543 to you have a choice there. Choose a VPC. It's a virtual private network that you want to select but I want don't want to dwell into right now. Well availability zone preference. So there is a preference you can select like if you are if you are a startup starting in California you can choose California as availability zone. Option group is the template it will use for a postgres postgres equal dot com file. So I think I will talk more about it but let's say we are using the default one. Parameter group is it's a I think postgres equal dot com is the parameter group. I think option group is I think by default it's taken you don't have to select in postgres. I think it's for another database. None for postgres. None for postgres. So you have to choose the parameter group default postgres 93 and I chose the default security group by default. Let's say let's do that manage options backup retention period. You can choose up to 35 days if you want to have backups of your database backup window you can select the window. I selected 4 MTC and 4.5 like about 30 minutes they can take a backup. It's usually fast but in like if you are running very high transactional database then backup will take little bit of resources. The maintenance window is really important. So as I mentioned earlier there is upgrade, minor version upgrade, auto minor version upgrade that's the maintenance window. You can select here that on Sunday from 4 to 4.30 I am available for upgrade. So they will I think send out a notification and make sure that you set up outage window for your customers and you can do that. And that's review. I think we are done. So we have a postgres instance up and running with 933 with 100 gig of storage. Maintenance details is there, availability zone details is there and boom. I am connected. Pcql, the satch, hostname, omni ti, username and I am connected right now. So in 6 steps if your developer doesn't know about like how can I set up a backups, how can I set up a multi AZ replication. This is the like they want to just test it out. I think this is the really great way to set up an instance. Recently I have been using this for lot of like when there is a new things I want to test it out I just bring up a new RDS instance like. It's cheaper than like I spent time managing that database. So let's talk about the things that we assume that we knew before like in 6 steps we assume that we knew about the configuration parameters and all. So one of the things it provides is pre configuration parameters. So I am going to talk about some of the parameters that we care about. There is a maximum connection parameters by default. I think it's 7 gig instance. How it calculates is DB instance class memory. So if you have right now I have selected m1 large so I think it's 7 gig of RAM. So it will select take that RAM size divided by that number and put that maximum connection 604. You can change it though. Effective cache size by default it's take similar way memory divided by this number and 3.6 gig for 7 gig of RAM size I think that's good enough. Shared buffers is real same thing. You can change this all these parameters. This work memory by default is 16 MB but you can change it. Work memory by default is 1 MB and can be changed. This is the parameter we cannot change. Logline prefix. So if you are still working on the old Postgres log monitoring tool called PGFuin that might not work. Right now most of the people are working PGBager. Who is still using PGFuin? So that might not work. I haven't tested yet. It might work but PGBager allows you to pass in the logline prefix parameter. Log minimum duration statement disabled by default. So this parameter if you don't know it can log the statement takes longer than that amount of time and you can enable it. So the first thing you can do you can just do the enable and it will it's on off like not on off sorry this is 10 milliseconds or 5 milliseconds depending on your workload. So the process is like it's easy to create parameter groups and so if you think that you have a dev stage those kind of instance you can just create a parameter the template for this Postgres.com file and whenever you can create an instance you can just pre-select whatever template you want to apply. All those things can be done on command line as well. So you don't have to like use the web UI. So and you can automate that as well. So and it's allow dynamic calculation right that's another thing like people it's really important like when you change the if you do the scalability increase your size of the instance that it will change automatically and whenever the if the parameter requires restart the next restart time it will be applied or you can apply right away if you want to restart the dev. The cons is cannot change some of the parameters but most of the parameter I think can be changed one of them is log line prefix but we have a work around for that but usually if you are not doing like there is a other parameters like some libraries and other things if you are using for your database you cannot change. The next thing is automatic software patching. So as of now Postgres 9 3 1 2 and 3 are available we are still waiting on 9 3 4 it's not available I think I will I think I will it's still in beta so they are not fast in upgrade maybe but hopefully that's the thing I have been talking to Amazon guy like we would like to get it faster than it should be. You can control upgrade time you have to wait till the new version like for example if there is a bug or data loss bug in exposed or security bug exposed you cannot upgrade by yourself like you have to wait you have to depend on usually you depend on your DBS now you depend on the Amazon. So Postgres RDS doesn't support anything older than 9 3 they started with 9 3 which is good I think and there is a replication bug in 9 3 so make sure and delay in upgrade those are the things. Point in time restored so this is cool feature so as I you can restore your instance in point in time like there is a in the dashboard you can select restore DB instance and you can restore use custom restore time and it will restore your instance you select the new instance it will create the new instance and restore that snapshot to the new instance so which is really cool. It will apply the x locks val locks point in time restored so this is the new instance we created for restore omni tier restored so it is restored now and you can use that instance as like other instance. Another thing is snapshot you can do the manual or automated snapshot if you think like you are applying schema or schema change you want to take a snapshot you can do manual snapshot that and you can restore if it doesn't go smooth you can just restore to the previous version of your database so it is really good for testing and development purposes like you can just restore it you don't have to wait for your DBS to restore your database. And you can do the automated snapshot to like for example you can automate it every day or every hour or whatever the frequency you want so and you can use the restore you can bring up that instance using the previous facility I mentioned about restoring. Another thing it mentioned DB events like it's still a black box right for devs like they don't know where is my hardware where is my install so it keep tracks of all the events so there is a DB events that you can like starting from the once the instance is started all the major events are locked there like backup the database creating a manual snapshot finished when the backup was finished applying the modification to the storage all kind of events are locked there so which is really good. DB event notification another thing is like if you want to just get notified if something changes on my if the database is restarted or whatever you can set up like there are 13 event categories like backup creation deletion failover maintenance recovery restoration so those kind of events can be notified using email or you want to get page depending on your preference. Here I think my slide is little off like this should be the first slide Amazon Webizer this is like this is what the region and availability zone so region is I think I still have a typo here it should be US and then you have a different region and region do you know which we have the California is considered as a region or. There are two regions in the West there is US West one which is US West two which is Oregon. Ok so you have so then it's right then because US east is a region US west is a region but when you set up availability zone it's actually within the region so it's not from like you are setting up replication from Virginia to California it's from Virginia to somewhere else like New Jersey maybe and then it's not multi region so as you can see on the dashboard it's US east one D US east one E so those are the two availability zone that my instance is running right now and provision IOPS it's I think IOPS is a input output per second so it's usually it gives you from ranges from 1000 to 30,000 IOPS per year instance and you need to select ranges from 100 gig to 3 terabyte if you want to get 30,000 IOPS you need to select 3 terabyte or equivalent to that I'm not sure what's the I think they want to make money or probably but. It is about performance ok. It is about performance ok. Let me see ok there is a ratio between 3 to 1 to 10 to 1 so you have to select into that ratio so as I said like 3 terabyte you can go up to 30,000 IOPS per second for example I think I don't want to dive into this but you can scale up to 2000 3000 depending on your need but that's really cool push button scaling like when you think that I want to increase the IOPS for my instance from 1000 IOPS to 2000 IOPS you can go back to your instance I went back to the dashboard my data base omni TI and use different I wanted to increase the space as well so I just increase the 200 and set the IOPS to 2000 and then continue. Sure yeah storage cannot be because of the reasons like because they don't have I think information about your database size or something like yeah but IOPS I think you can go back and I think it's online too so that's really cool you don't have to restart your database so no. So as you can see here I increase the IOPS like in the in the DB events I saw you can see that there is an increase IOPS so the event will be logged there it took like about an hour for me to so it's not like in five minutes you will get from 1000 to 2000 you still need to still need to what it's called I forgot about but yeah so it took about an hour to increase the IOPS from 1000 to 2000 background there. Okay thank you very much you are here multi easy was quick multi easy whenever I created a multi easy first time it was really quick because it the new instance was started you can see here like 16 April 16 I created a multi easy and in like 4 minutes now it will be it was created another thing is like whenever you are on cloud you care about security I have 5 minutes so isolation and security access from specific like you can see like by default it's doing a great job it doesn't allow anybody to log in your RDS instance so you need to use a set your IP address or your VPC from your EC2 if you are already working with Amazon there is a security group you can define in the Amazon that you can assign to this instance you can use EC2 the another thing is limited default roles like there are RDS super user role and RDS admin role you RDS super user cannot log in so by default omni TI is not a super user it's a super user for a database so if you are creating a database it will be super so you can create anything you want under the for the database you cannot do anything or at the cluster level SSL is on by default so which is good for cloud so you can see so SSL and it's always on turned on so it's from the security perspective that makes sense the good thing I like is like it doesn't allow by default to everybody to connect your database instance you need to define who is who can able to connect to the instance monitoring and matrix so these are the default monitoring and matrix are already separate of whenever you create an instance it's CPU DB connections I think we care about DB connections but most of them are still missing like use you do lot of other monitoring too so if I would still if you are using it as a some right now we are evaluating for one customer to migrating they have only two gig of database and we don't want to manage their Amazon EC to instance maybe and this might be a good option for them to just bring up the RDS and use that so it's a monitoring and matrix like the CPU other utilizations other features automatic how host replacement like if there's hardware failure you don't have to worry about that the it will be failed over automatically and it's using synchro synchronous replication if how many of you don't know difference about synchronous and synchronous everybody knows great that's good it's a really cool feature if you are using it right postgres locks monitoring like that's another thing usually people think like okay the database is running everything is fine when there's a problem how can I dive into the locks so there is a console but it's not pretty but the good thing is you can download that lock file there are commands for all the stuff I talked about like RDS there is a RDS command that you can do the RDS watch DB lock file and provide the log name and you want to just log for error you can provide this lock file name and you can watch the log as it happening you can download the lock file by every day you want to download and run into the PGA that you can do that so I think so you can do some kind of maintenance like analyze of your locks okay the next thing is there's a limitation like you cannot install your what's called customized extensions but by default it's providing about 35 extensions which is like all of the extension some of them are missing but PG crypto is there H store is there post gi is there T search 2 is there PLPG SQL so what's the good one DB link is there so those kind of is all the most of the extensions are there so if you are like for example if you have attended my colleagues Geith stock like PG part man let's say we talked about that with Amazon like okay can you install this can you make a default but no not yet so maybe we will work out something but right now you cannot install your customized extension until Amazon install it for you as we are postgres we need to run the PG bench results provision IOS 1000 number of clients 100 number of threads one duration 600 seconds we did a quick test single AG versus multi AG like what's the difference of the performance it was about I think 5 per like transaction per second why 2 about 200 and 186 about 5% I think 5% difference if you are using multi AG versus single AG instance because it's a synchronous replication that's the reason and depending on your like as you know like a sometimes there is a high load on then you might get a lower performance too so that's a good question like you don't have access to another instance you have you just assume that there is only one instance everything is in background yeah but it's written into two zones but you just write into one you don't know about anything the you can't access the different the slave or streaming applications so for multi AG for single AG like there will be backup like but it will not be the failover like you will be missing the failover part right replay longer time you have to it has to build the instance and then bring up the instance sorry so it will be automated like you will be still listening to the same C name or whatever the IPR whatever the host name you are talking to you don't have to change any application yeah C name flip yeah no but no you cannot disable the synchronous application but but yeah yeah yeah yeah okay that's good to know I didn't know about that limitations I think I think the limitation ofhemia is very stage but most like the client I mentioned they they can able to leverage the outage time then we might be able to again it's a database size is small another scenario we are going to use it for with the Jenkins servers they are bringing up the instance constantly they are right now have four servers for hardware so we want to reduce that hardware cost and we'll just use this RDS and each Jenkins servers will create a new database yeah migration if you are running EC2 instance right now you have only one option right now you need to dump your EC2 instance database and restore into RDS you cannot set up an application I think somebody asked me that question in on the channel but it's not possible yet a PG bouncer cannot be installed on the DB server like the client we are working they have PG bouncer running on the database server we moved out that PG bouncer to running on application server and it's always a good idea to run on the application server so there is no downside for that cannot install the custom extension like Mimio PG part man that we are using for number of clients so that might be a problem pricing okay so there is a pricing model is on demand and reserve pricing on demand is like whenever you want to just test it out you can just bring up the instance or reserve is one year to three years depending if you want to just reserve that instance for one year either pricing will be low I just compared with the single AG versus multi AGs you can see the difference double the because you are paying for the failover as well so it's a 400 versus 800 and for if you go to reserve instances like it will be cheaper because you are making a long-term commitment 10 to 20 percent cheaper and price doesn't include bandwidth right external bandwidth okay yes that's maximum yes yeah no not internally but externally yes and there are some references I used that will be a my slide will be available on PGcon website under my talk further reading my colleague Keith wrote like we both wrote article called postgres accessing postgres from Amazon RDS it's on dbt a dot com if you want to read it's a very detailed article about what's RDS is about another thing I wanted to mention is a search conference omni-ti have a search conference in September 24 to 26 in Washington DC if you want to attend it's more about the scalability it will be all like database application and I think I am open for questions it's everything it's a you can store 100 KG of data are we are we still talking about the storage is free right it 2014 I know I'm just kidding yeah but yeah storage is cheaper than anything everything else but yeah it will be x-locks and data files so maybe if you need a 50 gig of database you probably want to set 100 gig in any case a good recommend is to set the data the this size double than whatever the database size is like for the growth reasons no no yes that's the reason it's a double the price no no I think they are still working on that streaming application at the moment yeah but I think as for the track record like bicycle RDS has that thing so I assume that it will be there will be something it's not that difficult if my sequel can provide why not post so it's just select that free tire instance and you when you select that there will be default like there's an entire postcard of on file will be available so you can just go into the dashboard and see that mostly the default I'm sure the last question. Is the data encrypted as best? No. At the moment, we're getting across all the engines that work. They just, you can find out and figure that at some point. We use DBS, so. Yeah. Okay. Is it open 9.4? No. They are still struggling for 9.3.4 right now. In general, I mean, our plans are to support the major versions when they release, you know, in the short period. And much like our other engines, we find that the major version is just a single upgrade model. Yeah, I think MySQL has four or five versions. Yeah. Yeah. I was a little surprised, you showed a snippet saying that like a few versions of 9.3 were available including 9.3.1, which has this, you know, in bugs like data corruptifications that stand by. But maybe the explanation for that is that they're using their own, you know, replications. Yeah, we don't use that replication. Yeah. So the bugs are, you know, and we do recommend, I mean, the default version of 9.3.3. To some degree, we provide flexibility for the customers because the customer said, look, I'm on 9.3.1, I haven't tested 9.3.3. You know, I need another copy of my database. We don't want to tell the person, no, you can't have it right now. Over time, we will deprecate versions, but it's pretty early in the life cycle so far, so I'm in for further stress. Cool. And I think, I think they are very approachable guys. Like I have been talking to them for back and forth. Okay. Thank you very much.
 I have a lot of stuff to talk and it will finish in 30 minutes.  So, I don't see anybody outside waiting for my talk.  So, this is the great.  So today we are going to talk about Postgres in Amazon RDS.  How many of you don't know what is RDS?  RDS.  Okay.  Okay.  That's the main stuff.  Yes.  Okay.  So, I will not talk a little bit about what is RDS, but I will dive more into what is  Postgres RDS is going to be offered.  Okay.  So, who am I?  First, I am a database architect working with Omniti for more than 7 years.  I used to be Oracle DBA about 7 years ago.  Then since then I haven't looked back working with Postgres and I am really happy if you  are a person who wants to switch from Oracle to Postgres.  This is the best database to work on.  I am working with MySQL and NoSQL still like I prefer working on Postgres compared to other  databases.  My contact details, if you have any question after the talk, I am available on Twitter 24  by 7.  Blog, I write sometimes, sometimes on my website.  It's listed here.  Let me give a brief overview of Omniti.  Omniti is a consulting service providing solutions to the client to solve the problems that  fall into one of this categories, scalability, reliability, HA, consistency, security.  This is my first PGCon but I feel like home so which is good and I think I will be coming  back next year probably.  I visit other conferences in US but this is my first PGCon even though I am involved with  Postgres for a number of years.  And we are hiring.  If you are awesome database guy or operations guy or whatever guy or girl.  I can use this.  Amazon RDS.  What is Amazon RDS?  We will talk about what?  I am not representing Amazon.  So I need to give their overview as well like their side like what they are marketing and  what's my review of it.  So I am going to talk about the highlights.  We will go through like there is a five step process to set up a Postgres instance with  the replication setup and everything.  So we will go through that.  If you want to follow through, if you have an Amazon account you can just spin up your  instance too with me.  Postgres RDS features that we care about like administration and some limitations and question  and answer.  I think if you want to hold on your question that might be good because some of the questions  you might be answering at the talk progresses.  But I will be happy to answer any question anytime if you feel like you don't want to  hold on.  So what is Amazon RDS?  RDS is the full name is relational database services.  So it is a managed relational database services.  So as you can see here Amazon has started I think with MySQL.  They started with MySQL then added Oracle SQL server and they added Postgres in November  of last year.  So from the maturity perspective MySQL is the most mature RDS right now because that  has been in market for longer period of time and that makes sense.  Service highlights it's managed.  If you don't know what is managed means like you don't have to worry about hardware.  You don't have to worry about installing the database.  You don't have to worry about setting up the replication.  You don't have to worry about failover.  What else backups?  You have to worry about little bit about monitoring from the application side on little bit of  database side but usually they take care of as well.  Compatible.  So it's a compatible with whatever other services like you don't have to install a  specific driver or anything like.  You can connect to the RDS instance using basic wall using Java driver or.NET driver.  Whatever driver you are working your application in you can able to connect to their database  instance.  Just wanted to clear this is just a database service.  It's not like Heroku like that provides application as well lot of other stuff.  But this is just a database.  If you want to just bring up a database this is where you go for testing maybe.  This is a database in the cloud that's another highlight there like it's in the cloud.  Everybody should be in the cloud.  Design for use with other Amazon website.  So it's if you have Amazon if you are already working with the Amazon like you don't have  to do anything extra to work with this service.  You can just plug and play.  And that's what they advertise is inexpensive because the theory is like managing the database  and other those are the cost.  Actually Omniti is providing the services like we are doing the managed services as  well.  So it's I think that makes sense.  It's little expensive but it depending on your use cases.  So let's get started with the Postgres RDS first.  So just wanted to make sure that it is still in beta.  So if you are not aware of it like I am I think I don't use for production for sure  right now because still in beta and as you know beta is beta.  But I will use Postgres 9 for beta 1.  So once you have an Amazon account you can just click on the Amazon RDS.  This is the first page you will hit and there are as I mentioned earlier there are different  databases you can select for and we always select Postgres.  So let's select the number 2 in the list.  The next screen will be like it will ask you for production.  Yes there are 2 options like let me explain the second option first.  It's RDS free usage start so that will if you are just playing around like if you want  to play right now you can just use like it's free service that you can play with the Amazon  RDS.  But we will choose like let's assume that we are using for a more important database.  It's a multi-AG deployment and provision IOPS.  How many of you don't know what is multi-AG?  So multi-AG is like let me explain.  Amazon has data centers in the different availability zones.  So there is a region.  Region is like US, Asia those are the regions.  Under that they have availability zones like US East it's in Virginia, US West in California  somewhere.  So that's multi-AG.  So if you want to set up that instance with the main instance like the master is in Virginia  and set up a slave on the California let's say.  So that is multi-AG.  Provision IOPS?  Provision IOPS?  So.  I'm actually from that team.  Multi-AG is actually in a region not across regions.  Right.  Right.  So it's just in like US East is where one region is multi-agent.  Correct.  Definitely.  So region yeah.  So region I think I have in the back in my slide like it's not multi-region.  But I just wanted to give the overview of it.  Provision IOPS is the IOPS you usually pay for and it's little complicated than you think  but let's assume that we know about it.  Yes.  Next.  We will select the DB engine is already select the license model.  This is not for us because it's providing other services like ORAC or SQL server.  So they need or my SQL licensing DB engine version is 933 right now.  So be careful about that.  DB instance class is I chose like M DB M1 large like there is a very good documentation.  What kind of instance is like Amazon provides different kind of instances large extra large  it will depending on the how much RAM and the CPU power your instance will have.  So I selected M1 large for now multi-ag deployment.  Yes.  Auto minor version upgrade.  Yes.  What does that mean?  Yes means like whenever there is a let's say 934 is available now.  So whenever it will be available it will upgrade for you.  So you don't have to upgrade and there will be window for that.  Next story let's say we chose 100 gig use provision IOPS.  Yes.  And then I said 1000.  So there is a ratio that you have to select like you cannot select 100 gig with 3000 IOPS.  There is a ratio 3 to 10 ratio.  I think we will talk about them in detail.  DB instance identifier omni ti master database omni ti password.  Done.  Number 4.  Now we will know we will be selecting port that you want to run the instance like default  I use 543 to you have a choice there.  Choose a VPC.  It's a virtual private network that you want to select but I want don't want to dwell  into right now.  Well availability zone preference.  So there is a preference you can select like if you are if you are a startup starting in  California you can choose California as availability zone.  Option group is the template it will use for a postgres postgres equal dot com file.  So I think I will talk more about it but let's say we are using the default one.  Parameter group is it's a I think postgres equal dot com is the parameter group.  I think option group is I think by default it's taken you don't have to select in postgres.  I think it's for another database.  None for postgres.  None for postgres.  So you have to choose the parameter group default postgres 93 and I chose the default  security group by default.  Let's say let's do that manage options backup retention period.  You can choose up to 35 days if you want to have backups of your database backup window  you can select the window.  I selected 4 MTC and 4.5 like about 30 minutes they can take a backup.  It's usually fast but in like if you are running very high transactional database then backup  will take little bit of resources.  The maintenance window is really important.  So as I mentioned earlier there is upgrade, minor version upgrade, auto minor version  upgrade that's the maintenance window.  You can select here that on Sunday from 4 to 4.30 I am available for upgrade.  So they will I think send out a notification and make sure that you set up outage window  for your customers and you can do that.  And that's review.  I think we are done.  So we have a postgres instance up and running with 933 with 100 gig of storage.  Maintenance details is there, availability zone details is there and boom.  I am connected.  Pcql, the satch, hostname, omni ti, username and I am connected right now.  So in 6 steps if your developer doesn't know about like how can I set up a backups, how  can I set up a multi AZ replication.  This is the like they want to just test it out.  I think this is the really great way to set up an instance.  Recently I have been using this for lot of like when there is a new things I want to  test it out I just bring up a new RDS instance like.  It's cheaper than like I spent time managing that database.  So let's talk about the things that we assume that we knew before like in 6 steps we assume  that we knew about the configuration parameters and all.  So one of the things it provides is pre configuration parameters.  So I am going to talk about some of the parameters that we care about.  There is a maximum connection parameters by default.  I think it's 7 gig instance.  How it calculates is DB instance class memory.  So if you have right now I have selected m1 large so I think it's 7 gig of RAM.  So it will select take that RAM size divided by that number and put that maximum connection  604.  You can change it though.  Effective cache size by default it's take similar way memory divided by this number  and 3.6 gig for 7 gig of RAM size I think that's good enough.  Shared buffers is real same thing.  You can change this all these parameters.  This work memory by default is 16 MB but you can change it.  Work memory by default is 1 MB and can be changed.  This is the parameter we cannot change.  Logline prefix.  So if you are still working on the old Postgres log monitoring tool called PGFuin that might  not work.  Right now most of the people are working PGBager.  Who is still using PGFuin?  So that might not work.  I haven't tested yet.  It might work but PGBager allows you to pass in the logline prefix parameter.  Log minimum duration statement disabled by default.  So this parameter if you don't know it can log the statement takes longer than that amount  of time and you can enable it.  So the first thing you can do you can just do the enable and it will it's on off like  not on off sorry this is 10 milliseconds or 5 milliseconds depending on your workload.  So the process is like it's easy to create parameter groups and so if you think that  you have a dev stage those kind of instance you can just create a parameter the template  for this Postgres.com file and whenever you can create an instance you can just pre-select  whatever template you want to apply.  All those things can be done on command line as well.  So you don't have to like use the web UI.  So and you can automate that as well.  So and it's allow dynamic calculation right that's another thing like people it's really  important like when you change the if you do the scalability increase your size of the  instance that it will change automatically and whenever the if the parameter requires  restart the next restart time it will be applied or you can apply right away if you  want to restart the dev.  The cons is cannot change some of the parameters but most of the parameter I think can be changed  one of them is log line prefix but we have a work around for that but usually if you  are not doing like there is a other parameters like some libraries and other things if you  are using for your database you cannot change.  The next thing is automatic software patching.  So as of now Postgres 9 3 1 2 and 3 are available we are still waiting on 9 3 4 it's not available  I think I will I think I will it's still in beta so they are not fast in upgrade maybe  but hopefully that's the thing I have been talking to Amazon guy like we would like to  get it faster than it should be.  You can control upgrade time you have to wait till the new version like for example if there  is a bug or data loss bug in exposed or security bug exposed you cannot upgrade by yourself  like you have to wait you have to depend on usually you depend on your DBS now you depend  on the Amazon.  So Postgres RDS doesn't support anything older than 9 3 they started with 9 3 which  is good I think and there is a replication bug in 9 3 so make sure and delay in upgrade  those are the things.  Point in time restored so this is cool feature so as I you can restore your instance in point  in time like there is a in the dashboard you can select restore DB instance and you can  restore use custom restore time and it will restore your instance you select the new instance  it will create the new instance and restore that snapshot to the new instance so which  is really cool.  It will apply the x locks val locks point in time restored so this is the new instance  we created for restore omni tier restored so it is restored now and you can use that  instance as like other instance.  Another thing is snapshot you can do the manual or automated snapshot if you think like you  are applying schema or schema change you want to take a snapshot you can do manual snapshot  that and you can restore if it doesn't go smooth you can just restore to the previous  version of your database so it is really good for testing and development purposes like  you can just restore it you don't have to wait for your DBS to restore your database.  And you can do the automated snapshot to like for example you can automate it every day  or every hour or whatever the frequency you want so and you can use the restore you can  bring up that instance using the previous facility I mentioned about restoring.  Another thing it mentioned DB events like it's still a black box right for devs like  they don't know where is my hardware where is my install so it keep tracks of all the  events so there is a DB events that you can like starting from the once the instance  is started all the major events are locked there like backup the database creating a  manual snapshot finished when the backup was finished applying the modification to the  storage all kind of events are locked there so which is really good.  DB event notification another thing is like if you want to just get notified if something  changes on my if the database is restarted or whatever you can set up like there are  13 event categories like backup creation deletion failover maintenance recovery restoration  so those kind of events can be notified using email or you want to get page depending on  your preference.  Here I think my slide is little off like this should be the first slide Amazon Webizer this  is like this is what the region and availability zone so region is I think I still have a typo  here it should be US and then you have a different region and region do you know which we have  the California is considered as a region or.  There are two regions in the West there is US West one which is US West two which is Oregon.  Ok so you have so then it's right then because US east is a region US west is a region but  when you set up availability zone it's actually within the region so it's not from like you  are setting up replication from Virginia to California it's from Virginia to somewhere  else like New Jersey maybe and then it's not multi region so as you can see on the  dashboard it's US east one D US east one E so those are the two availability zone that  my instance is running right now and provision IOPS it's I think IOPS is a input output per  second so it's usually it gives you from ranges from 1000 to 30,000 IOPS per year instance  and you need to select ranges from 100 gig to 3 terabyte if you want to get 30,000 IOPS  you need to select 3 terabyte or equivalent to that I'm not sure what's the I think they  want to make money or probably but.  It is about performance ok.  It is about performance ok.  Let me see ok there is a ratio between 3 to 1 to 10 to 1 so you have to select into that  ratio so as I said like 3 terabyte you can go up to 30,000 IOPS per second for example  I think I don't want to dive into this but you can scale up to 2000 3000 depending on  your need but that's really cool push button scaling like when you think that I want to  increase the IOPS for my instance from 1000 IOPS to 2000 IOPS you can go back to your  instance I went back to the dashboard my data base omni TI and use different I wanted to  increase the space as well so I just increase the 200 and set the IOPS to 2000 and then  continue.  Sure yeah storage cannot be because of the reasons like because they don't have I think  information about your database size or something like yeah but IOPS I think you can go back  and I think it's online too so that's really cool you don't have to restart your database  so no.  So as you can see here I increase the IOPS like in the in the DB events I saw you can  see that there is an increase IOPS so the event will be logged there it took like about  an hour for me to so it's not like in five minutes you will get from 1000 to 2000 you  still need to still need to what it's called I forgot about but yeah so it took about an  hour to increase the IOPS from 1000 to 2000 background there.  Okay thank you very much you are here multi easy was quick multi easy whenever I created  a multi easy first time it was really quick because it the new instance was started you  can see here like 16 April 16 I created a multi easy and in like 4 minutes now it will  be it was created another thing is like whenever you are on cloud you care about security I  have 5 minutes so isolation and security access from specific like you can see like by default  it's doing a great job it doesn't allow anybody to log in your RDS instance so you need to  use a set your IP address or your VPC from your EC2 if you are already working with Amazon  there is a security group you can define in the Amazon that you can assign to this instance  you can use EC2 the another thing is limited default roles like there are RDS super user  role and RDS admin role you RDS super user cannot log in so by default omni TI is not  a super user it's a super user for a database so if you are creating a database it will  be super so you can create anything you want under the for the database you cannot do anything  or at the cluster level SSL is on by default so which is good for cloud so you can see  so SSL and it's always on turned on so it's from the security perspective that makes sense  the good thing I like is like it doesn't allow by default to everybody to connect your database  instance you need to define who is who can able to connect to the instance monitoring  and matrix so these are the default monitoring and matrix are already separate of whenever  you create an instance it's CPU DB connections I think we care about DB connections but most  of them are still missing like use you do lot of other monitoring too so if I would still  if you are using it as a some right now we are evaluating for one customer to migrating  they have only two gig of database and we don't want to manage their Amazon EC to instance  maybe and this might be a good option for them to just bring up the RDS and use that  so it's a monitoring and matrix like the CPU other utilizations other features automatic  how host replacement like if there's hardware failure you don't have to worry about that  the it will be failed over automatically and it's using synchro synchronous replication  if how many of you don't know difference about synchronous and synchronous everybody  knows great that's good it's a really cool feature if you are using it right postgres  locks monitoring like that's another thing usually people think like okay the database  is running everything is fine when there's a problem how can I dive into the locks so  there is a console but it's not pretty but the good thing is you can download that lock  file there are commands for all the stuff I talked about like RDS there is a RDS command  that you can do the RDS watch DB lock file and provide the log name and you want to just  log for error you can provide this lock file name and you can watch the log as it happening  you can download the lock file by every day you want to download and run into the PGA  that you can do that so I think so you can do some kind of maintenance like analyze of  your locks okay the next thing is there's a limitation like you cannot install your  what's called customized extensions but by default it's providing about 35 extensions  which is like all of the extension some of them are missing but PG crypto is there H  store is there post gi is there T search 2 is there PLPG SQL so what's the good one  DB link is there so those kind of is all the most of the extensions are there so if you  are like for example if you have attended my colleagues Geith stock like PG part man  let's say we talked about that with Amazon like okay can you install this can you make  a default but no not yet so maybe we will work out something but right now you cannot  install your customized extension until Amazon install it for you as we are postgres we need  to run the PG bench results provision IOS 1000 number of clients 100 number of threads  one duration 600 seconds we did a quick test single AG versus multi AG like what's the  difference of the performance it was about I think 5 per like transaction per second  why 2 about 200 and 186 about 5% I think 5% difference if you are using multi AG versus  single AG instance because it's a synchronous replication that's the reason and depending  on your like as you know like a sometimes there is a high load on then you might get  a lower performance too so that's a good question like you don't have access to another instance  you have you just assume that there is only one instance everything is in background  yeah but it's written into two zones but you just write into one you don't know about  anything the you can't access the different the slave or streaming applications so for  multi AG for single AG like there will be backup like but it will not be the failover  like you will be missing the failover part right replay longer time you have to it has  to build the instance and then bring up the instance sorry so it will be automated like  you will be still listening to the same C name or whatever the IPR whatever the host  name you are talking to you don't have to change any application yeah C name flip yeah  no but no you cannot disable the synchronous application but but  yeah yeah yeah yeah okay that's good to know I didn't know about that limitations I think  I think the limitation ofhemia is very  stage but most like the client I mentioned they they can able to leverage the  outage time then we might be able to again it's a database size is small  another scenario we are going to use it for with the Jenkins servers they are  bringing up the instance constantly they are right now have four servers for  hardware so we want to reduce that hardware cost and we'll just use this  RDS and each Jenkins servers will create a new database yeah migration if you are  running EC2 instance right now you have only one option right now you need to  dump your EC2 instance database and restore into RDS you cannot set up an  application I think somebody asked me that question in on the channel but it's  not possible yet a PG bouncer cannot be installed on the DB server like the  client we are working they have PG bouncer running on the database server we  moved out that PG bouncer to running on application server and it's always a  good idea to run on the application server so there is no downside for that  cannot install the custom extension like Mimio PG part man that we are using for  number of clients so that might be a problem pricing okay so there is a  pricing model is on demand and reserve pricing on demand is like whenever you  want to just test it out you can just bring up the instance or reserve is one  year to three years depending if you want to just reserve that instance for one  year either pricing will be low I just compared with the single AG versus multi  AGs you can see the difference double the because you are paying for the failover  as well so it's a 400 versus 800 and for if you go to reserve instances like it  will be cheaper because you are making a long-term commitment 10 to 20 percent  cheaper and price doesn't include bandwidth right external bandwidth okay  yes that's maximum yes yeah no not internally but externally yes and there  are some references I used that will be a my slide will be available on PGcon  website under my talk further reading my colleague Keith wrote like we both wrote  article called postgres accessing postgres from Amazon RDS it's on dbt a  dot com if you want to read it's a very detailed article about what's RDS is  about another thing I wanted to mention is a search conference omni-ti have a  search conference in September 24 to 26 in Washington DC if you want to attend  it's more about the scalability it will be all like database application and I  think I am open for questions it's everything  it's a you can store 100 KG of data  are we are we still talking about the storage is free right it 2014 I know I'm  just kidding yeah but yeah storage is cheaper than anything everything else  but yeah it will be x-locks and data files so maybe if you need a 50 gig of  database you probably want to set 100 gig in any case a good recommend is to  set the data the this size double than whatever the database size is like for  the growth reasons no no yes that's the reason it's a double the price  no no I think they are still working on that streaming  application at the moment yeah but I think as for the track record like bicycle  RDS has that thing so I assume that it will be there will be something it's  not that difficult if my sequel can provide why not post  so it's just select that free tire instance and you when you select that  there will be default like there's an entire postcard of on file will be  available so you can just go into the dashboard and see that mostly the  default  I'm sure the last question.  Is the data encrypted as best?  No.  At the moment, we're getting across all the engines that work.  They just, you can find out and figure that at some point.  We use DBS, so.  Yeah.  Okay.  Is it open 9.4?  No.  They are still struggling for 9.3.4 right now.  In general, I mean, our plans are to support the major versions when they release,  you know, in the short period.  And much like our other engines, we find that the major version is just a single upgrade model.  Yeah, I think MySQL has four or five versions.  Yeah.  Yeah.  I was a little surprised, you showed a snippet saying that like a few versions of 9.3 were available including 9.3.1, which has this, you know, in bugs like data corruptifications that stand by.  But maybe the explanation for that is that they're using their own, you know, replications.  Yeah, we don't use that replication.  Yeah.  So the bugs are, you know, and we do recommend, I mean, the default version of 9.3.3.  To some degree, we provide flexibility for the customers because the customer said, look, I'm on 9.3.1, I haven't tested 9.3.3.  You know, I need another copy of my database.  We don't want to tell the person, no, you can't have it right now.  Over time, we will deprecate versions, but it's pretty early in the life cycle so far, so I'm in for further stress.  Cool.  And I think, I think they are very approachable guys.  Like I have been talking to them for back and forth.  Okay. Thank you very much.
Postgres_in_Amazon_RDS_2_0_131.png Postgres_in_Amazon_RDS_2_132_623.png Postgres_in_Amazon_RDS_2_624_755.png Postgres_in_Amazon_RDS_2_756_875.png Postgres_in_Amazon_RDS_2_876_959.png Postgres_in_Amazon_RDS_2_960_1019.png Postgres_in_Amazon_RDS_2_1020_1751.png Postgres_in_Amazon_RDS_2_1752_2207.png Postgres_in_Amazon_RDS_2_2208_3419.png Postgres_in_Amazon_RDS_2_3420_3491.png Postgres_in_Amazon_RDS_2_3492_3539.png Postgres_in_Amazon_RDS_2_3540_6107.png Postgres_in_Amazon_RDS_2_6108_6167.png Postgres_in_Amazon_RDS_2_6168_7775.png Postgres_in_Amazon_RDS_2_7776_9107.png Postgres_in_Amazon_RDS_2_9108_13175.png Postgres_in_Amazon_RDS_2_13176_15347.png Postgres_in_Amazon_RDS_2_15348_18743.png Postgres_in_Amazon_RDS_2_18744_19139.png Postgres_in_Amazon_RDS_2_19140_20339.png Postgres_in_Amazon_RDS_2_20340_20435.png Postgres_in_Amazon_RDS_2_20436_21587.png Postgres_in_Amazon_RDS_2_21588_22451.png Postgres_in_Amazon_RDS_2_22452_24059.png Postgres_in_Amazon_RDS_2_24060_26207.png Postgres_in_Amazon_RDS_2_26208_26747.png Postgres_in_Amazon_RDS_2_26748_27491.png Postgres_in_Amazon_RDS_2_27492_28895.png Postgres_in_Amazon_RDS_2_28896_32267.png Postgres_in_Amazon_RDS_2_32268_34175.png Postgres_in_Amazon_RDS_2_34176_34223.png Postgres_in_Amazon_RDS_2_34224_35147.png Postgres_in_Amazon_RDS_2_35148_35531.png Postgres_in_Amazon_RDS_2_35532_36539.png Postgres_in_Amazon_RDS_2_36540_37511.png Postgres_in_Amazon_RDS_2_37512_39587.png Postgres_in_Amazon_RDS_2_39588_39635.png Postgres_in_Amazon_RDS_2_39636_39863.png Postgres_in_Amazon_RDS_2_39864_40067.png Postgres_in_Amazon_RDS_2_40068_40115.png Postgres_in_Amazon_RDS_2_40116_43079.png Postgres_in_Amazon_RDS_2_43080_43811.png Postgres_in_Amazon_RDS_2_43812_43883.png Postgres_in_Amazon_RDS_2_43884_44087.png Postgres_in_Amazon_RDS_2_44088_44183.png Postgres_in_Amazon_RDS_2_44184_45119.png Postgres_in_Amazon_RDS_2_45120_46751.png Postgres_in_Amazon_RDS_2_46752_46895.png Postgres_in_Amazon_RDS_2_46896_47243.png Postgres_in_Amazon_RDS_2_47244_51299.png Postgres_in_Amazon_RDS_2_51300_51383.png Postgres_in_Amazon_RDS_2_51384_51599.png Postgres_in_Amazon_RDS_2_51600_53999.png Postgres_in_Amazon_RDS_2_54000_56375.png Postgres_in_Amazon_RDS_2_56376_56843.png Postgres_in_Amazon_RDS_2_56844_57227.png Postgres_in_Amazon_RDS_2_57228_66029.png