Events Add an event Speakers Talks Collections
 
Duration 36:38
16+
Play
Video

PGConf India 2020 - Understanding Backup - Amit Sharma - EDB

Amit Sharma
Senior Manager at EnterpriseDB
  • Video
  • Table of contents
  • Video
PGConf India, 2020
February 27, 2020, Bengaluru, India
PGConf India, 2020
Request Q&A
PGConf India, 2020
From the conference
PGConf India, 2020
Request Q&A
Video
PGConf India 2020 - Understanding Backup - Amit Sharma - EDB
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Add to favorites
141
I like 0
I dislike 0
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
  • Description
  • Transcript
  • Discussion

About speaker

Amit Sharma
Senior Manager at EnterpriseDB

Learning new technologies has always fascinated me. Implementing them in my current organization has helped me stay involved with project management across teams. Through discussions with my peers and the guidance of my seniors I have been able to stay ahead. I have been involved with various projects during my current and previous jobs. I have worked with my Manager to implement the "Software Testing Lab", which has helped me in understanding the developments in the virtualization. We have evaluated and implemented VMWare, Virtual Box and Azure platform. I have successfully installed and managed Oracle, Postgres and SQL Servers over various big houses using our proprietary softwarea. I have been instrumental in testing databases technologies for customer requirements which include HSQLDB, Postgres and MYSQL as well migrations from one database to another. My core strengths include quick learning adaptability and self-learning in order to make sure I am moving ahead of the crowd.

View the profile

About the talk

In order to get the maximum reliability, backups play a vital role to ensure that you have all the data required to recover from any eventuality. There are multiple methods available with which you can take a backup and one needs to plan and understand which method suits them the best. We will be discussing various environments depending on size and recovery methods to give you a clear view of the factors which can help you define a clear policy on backups in your organization.

Share

There are two kinds of devious one who have who are taking the back of the other ones who are planning to take the backups and those are the ones that always face issues when the actual situation occurs. So, me being apart of support, we do have several customers reaching out to us that I have a kind of backup, but I want to go back and restore some tables, are some, you know, corrupted the data. And I want to restore from backup but he doesn't have the right kind of backup soon just having a backup, doesn't help. You also need to understand what kind of backup will help you

restore what kind of data, right? So you might need to restore multiple things from your cluster and what kind of back of his help to restore what kind of data is very important. So backups are important because there are several application issues that might happen. So we have several users to connect directly to the database cluster in order and separate several queries issues. Can have fun with that. And then there are several viable technology. Is that happen. We try to keep our service

animals safe as possible but then there are situations and losing all the servers highly unlikely. But I have seen that happen when the servers are in a certain building that catches fire or they got submerged in water that has happened. Has mustard face these issues so that's possible. So what do we expect to recover from backups, right? So that that's the key factor, which we need to understand before we start planning for taking a backup or we might need to restore store just a table, we might destroy need to restore a database, and we might need to restore a complete

to the store. So in postgres, a cluster can have multiple databases, right? So you might need to restore individual database. All you might need to restore the complete to the store. So these are the two things, which you can be expected to recover from any situation. So we have had several customers who have come to us, that they do have an application servers, right? So they don't need any backups to have multiple notes, they have the database in the cloud, that's highly, it's

highly possible for that to fail, right? So don't dismiss who faced situations because the previous flight, user accidentally drunk, the table, right? That replicates, as fast as you can blink, your eyes, you might have six different, you know, roughly cost, but the table is gone from all those necklace. So that's why you need to understand that that you need to have both the backup as well as the replicas. Replication is usually for a high-reliability where your Hardware feels, right? It

doesn't seem guard against loss of data from a user error, or from the corruption perception. Also, replicate from one Tower to another. So you have to understand that's why having a backup is very is very much important. Little getting replicated. How hot is a replicate as soon as something written to the master, right? Any action that happens on the master is replicated. Write a user accidentally runs a command to translate a particular record or a particular table. What will

happen? It will replicate Right. So, you will need to have some kind of backup in order to recover from that, right? So if you delete a table, right? So you have, you have no backup. If you have a backup from yesterday, you might be able to recover at least a thousand records off, out of 1010 record, you might just have to recreate those 10 records. States with having a backup is very important. So, you cannot recover from any kind of data loss from a user error or form of corruption

so that I cannot take care of those stupid little things. So the options that are dealer in postgres to take the backups, The Logical back of which is basically the data structure, you have PGW to start around with a dress for physical back up, so you can have PT base back up with shakes. The back of the entire tree or you can have five system-level backups like need backup or anything else that you might be using, right? So you can have a complete snapshot of the disk itself

instead of taking everything separately. So I guess it depends on the cost. Effective depends on what you might need to recover and how fast do you might need to recover. So we'll have a look at it in the next few slides. We'll have a look at what our options are available which you can recover from PT dump and which options you can recover from his backup or backup and how fast it can be right? So that will depend and again your business required requirement club with that will help you understand which one will be

cost-effective for you. So that's another planning podcast soon. So you need to have all the options that need you to plan for the cost of each option. The cost is not only the hardware cost, it's also the cost for the time lost in recovering, right? So you have to take into account that as well. Cakes. So pretty. That's the primary tool for taking the logical backup. Right. It runs like a regular cycle session is connect to the database server and then you can get a consistent snapshot of that particular database for the time, you started the backup

right thumb. So you can detect can take care of multiple tables to 1 Tradeport. Able is what motivated mean in case you have a huge table and the rest of the time tables that Place Mall, do Santa Claus, might compete in a couple of minutes, but if you have a huge table of a couple of hundred. We'll take it on time. So you need to understand how much time and attention that will be your actual time of taking a dump. Drunk and being plain text file and it can be in compressed formats as well as custom, right? So there are multiple options available from which you can recover. So,

will understand. How will the plaintext health right in case? It's just a table or structural table. You need to recover a plain text. You can just do like a script or do I search mentally and recover. But from a customs form that, you can extract the complete data of a huge table recovery. so, You can dump individual objects, so you so you can have the dump of the schema dump off, just the table or just a database. So, you can have different kinds of dumb stupid or dumb. It's so taking dumps into Julie is not that much helpful,

right? So having a full backup always helps, so you can in certain situations where you want to dump and Destroy to AccuWeather. Forecast, set up individual dumps, might help. But I do want to do some testing on a particular position with just a single table that might help, but having a full backup, definitely value our performance in Texas, it's a coffee, and accidents in the background with the PC dump. It doesn't have that much performance on the server to this one single back and table. As I told you,

it wasn't you supposed to schedule because it reads from the disc, right? So that's why in the negatives, the Isle of the disc increases in case your application or your database is also intensive. Then it might just hamper your operations number of objects with also Be a consultation on 85th and it will do you have 10 small tables and one table is pretty huge table will impact the performance of the PG, dump itself. And the time, it will take all the Iowa clinic on the considerations need to be taken while using Fiji time.

Happy to dump all the first it takes the global objects. That's the catalogs of the post office, and then a little of the table, then. It takes the dump of the data separately, this house, in case of collectible stores, right? And the sequel sequel file, so you can have a table definitions in case you have made people's faces as well. Try to take care of that as well book sad. Ew, Scrabble, not at the place to level. It also allows unlimited of tables and schemers, right? So in case you want to extract a

particular schema, there are options available for that again because you just copy to extract that if you do the same, again, Call paid back to the database server and then on top of that particular table or dirty. It's a huge table or since it's a copy, and you can tune it up to some level. Just like you tune the SQL queries, but it's pretty slow. In case you have to restore a computer or a complete use database, So that's where the physical backups coming, right? So in case you have a couple of hundred, DVDs of databases, Rite Theater director, isn't a couple

of hundred DVDs and you dial us. All your translations are pretty high or a daily level. That's where the physical backups come in. They don't involve any Curry on the server, reporting back to your operations, but the challenge with them is that you need to, you need to have the same kind of architecture. So if you're taking from 10 to 6 at St, Joseph's, the position has to be seeing the doctor structures. Including the table says, we have to be a sale. Then the company Flags in case you have compared to phone The Source Code,

the post office, then it needs to be the same Flags in order to use that particular back up. And again It's not logging into the database or running any SQL queries. It allows only a full cluster level back up. So you have the back of all the episodes in one go. Also two types of backups, you can use the offline so you can shut down your database. You can you copy a folder or even take the Snapchat of the whole disk itself. If you have it in Cloud, do they have multiple

options to take the disc level? Snapshot so that helps, and that's much faster than copying the data folder to another disk write Pizza. Lenny face copy Mackenzie select backup and couple of other celebrities are available for taking the back up off the disks so you can use any of those and they come with any compression levels are we can help you compress a snapshot that can be used as well to snapshot so you can also take the snapshot of a running cluster. You need to put it in a

backup mode, right? So we can start back up and your backup label is how you put it in the back up more. Continue your operations. And then you can take the Snapchat, a snapshot of will be from the time. You have started the PD PD start back up, right? So that's not starting because it's turned up to that point after that, you will need to include the Wall Files to make sure you are able to recover up to that point, right? So you start the backup, then you stopped the backup. You have that you a club that you archived on low price between these two will have a complete

complete information to restore the cluster, right? We have her. So I guess you do not put it in the back up mode says the tables will get modified at the back end. You will not have a consistent Snapchat of the tree and you might have issues restoring Furniture so customers do miss doing a start back up and start back up. So make sure you plan for that. You have a script or you don't you have a charter to Steps to make sure. Yes. I'm missing. But, yes, you can do it. I think, in PG version 10 or 11, there's an auction, I can check and get back.

So incremental backup. So there are three seats available so incremental backup. Basically you can take care of her, take a back up off the wall files, right? So you have a full backup on Monday for the rest of the video, take up back up off the wall files and then you have a fan club it and have it one full backup of the week, right? But there are so there was bought and I believe Batman also have the option of taking incremental backups. So you'll have to use some kind of fraternity. Need a second.

Soap, having a backup is pretty good, right? But again, as we discussed what we need to recover is more important. That's what we need to plan planning a backup. Only does not help sorry backup does, how does it until consistency? I mean, what happened to the lights? Are they just invited Love and Rockets into database in the wall pipe so long, because he might have said, two men tablespaces, then that probably not be a very good idea. Large, right? So you need to understand how much time it will take, you can do it until you can have that little thread for doing the tables places,

along with your directory. Right. So so, so that's kind of business account but you have to understand analyzed, which plant is best for you. And since then, we take a file from Snapchat and then we restart replica from the archive. But again, that's something that expensive because they have to have a look at this. It's not just for backup. You can use it for as you don't lie, but sometimes enough is being taken to go to that that time, right? So, thank you.

So the whole recovery box is what we do, understand right, restore and Recovery works on the same principal Venable posters starts on the store starts with a consistent checkpoint, and then it starts hurting from the wall files from the point. That finds a concession checkpoint. The files need to be returned to the pg wall. A PDF at 3 depending on the version. Eat change. Change mean to impose gas is supposed to go into a wall file that are required. Minimum settings that are required or archive mode and archives commission, to ensure that you will, if I get it. And then

each one file once it's completed, is checked for Consistency consistency. So once a wall file is return, we can use it to to apply from the checkpoint read. All the changes that have been made and then applied to recover or restore both work on the same principles. So even if you have a replica it works on the same principal at use the wall pipes to make sure that your application and then come up to the latest point. So, the options available to recover table cluster or

I'll text him right as well. As a previous backup data can be recovered from account custom format of the dump or a plane from text backup, as well as a b dump all option than a plaster or at least 11 restore backup or PG. Base pack up. A whole thing's work so recovering a table. So if you have a piece with a custom format, you can extract the table that has been dropped while using the PGA store, utility-scale file. Then, you can simply Connect using the psql and the store that

you have to make choices. You're trying to restore the table. Does not exist in the database. From a physical back up again. This is costly because you have to restore the whole cluster. So, used to start a new cluster, use a regular table, then you can use paddle jobs to restore that particular drop table. So, in this case, the starting of his flock is growing from a physical backup is much more costly because you will need to start an entire cluster. So you need to have

a complete server available, the same damn thing, and then you can just order, you can extract the table and then the storage. So in this case, you're pretty dumb backup would help. They are recovering or did episode lost or gained from a custom dump, you can extract the data base that you won't be sick with you, so you can feel your database for Recovery. You can increase the required memory spell, parameters to speed up recovery, and then in the cluster, still needs to be recovered separately. So, in case of a, in case you need to install, the whole cluster, the

fastest way to lose from the five second devil, backup reitzel, copy when you copy the dump directly to a new folder, simply modify the permissions. And if you have all the batteries which was the same has compiled using the same flag so you can see please start the server and you'll be up and running in no time. So in this case are recovering recovering, in this case, for a complete letter will be much faster than recovering using the PG dump. Walkman shouldn't be quite. So simple way of saying that you would you need for your Quarries in post, so you can use the same

kind of unique animals called that because your friends, the sequence copy command in the past, to load the data, we have to do the same to. You will be in the office at School chorus. I suppose you're using a bar manager, searching for a backup option of restoring parlor table using Batman. I have no juice. Another important point is to do the point in time recovery, right? Every time somebody accidentally drops a table, right? You already have a little dog cable from wall, your application, you will need to

go back and recover that particular table. So that helped ensure from the beginning that you have the Orthodox on the wall pipe, then you have a full database backup back. So you cannot go. So you cannot say that. I just dropped the table and I think about to go back up right now and I will be able to restore a previous point and of all sizes, right? So you can only move forward from your check point to and of start applying the wall file. You cannot go back in time. So we have to make sure you have a backup

off the previous day. Then you can understand what time the table was dropped. From the low price and then you can do a recovery, an hour or 30 minutes before that So you have to make sure that you're prepared for that. So what you have to do, you have to extract all files from the backup restore it on off. How to start on a separate server, use the PDX lock folder and then in the recovery., filed, besides the rest of the parameters and you have, you can use the recovery Target time. So, usually, in order to cover to the latest point, it's used

as latest. But in case you want to recover to a point where the table and before that, you can use that particular timestamp to recover. Greenwood right. White count is a separate file on the slave server. I'm getting removed, right? Right. Yes, yes. Yes. So once you have the recovery.com for ready, you can simply run it, it will cover up to that particular time. It will have the table. If you have set the air to set up on a test machine, you can dump the table restaurant to your production backup, or you can use this in case, there's a huge gap in between

the production and production and back up that you had So pros and cons of The Logical backup. So logical backup single database backup is pretty easy. If it's ready, Foster do the copied all the data No separate files are required. So nothing else is required. Its gross margin compatible. So you can dump from 9 to 3 or so reminder for 9 to 3 and then you can just order to any version with you want. You have to make sure that the PG dump utilities that he used to dump

the data is for the version that you're going to the store. So if you want to restore from 9:30 to 10, make sure you use the Pee Dee Dum from version 10. So this way you can also do the upgrades All the cons no. Here because it's a steel file. So it's a Snapchat of the current status of the server of the time, you ran around a piece of dump. So in case, something had one. Draw, something has accepted as possible. In case of a big, a big database or a big table Hooks and weights while taking a dump. It's possible that you are pretty. Abel is busy. So it still needs a

soft lock. So it might keep on waiting for that extra effort is required to the store in the inspector. You still have to extract the relevant data. You want to restore in case you want to restore a table. Still have to extract it from WWE and then destroy it too. Do you want? another thing is that you have to back up the global objects and everything separately, and you have to take the database Need a dump separate data from the table, separately. SQL statements are executed

very slow because of deck. Pros and cons of physical backups. New locking your rate since it's a v system level back up. It doesn't hamper any operations of the database server unless you are stopping it and then restarting it and backed up at one point of time. It's an almost instantaneous store in the sense that you can simply ensured that you have the right kind of benefit buying. It is available on a secondary server and you can start those binders and it'll read that it with the correct form. Of course

and you only have the same data available to you with a combination of the backup. And the Wall Files is only possible. If you have the right size available video, So, it has our dependency on the binary. So you cannot have a backup from a version 9.6 and The Story 2 version 10. Very large backups because you have to make sure that you have the Wall Files as well. So having just a PG, best backup or PT or a Snapchat doesn't help you have to make sure that you have all the wallpaper available in order to make sure you reach the latest latest point of vegetable server.

So you will have to just not backup. You will also have, you will also need space to store all of all files from the point that you have a speck of was taken and white rice or at least from the disk and it has to write from the desk. So, no pocket. Option is no Internet. It's all the food. I was pretty high. So what if we have a lot to go back up, we have a PG dumb and when starting the sub, Do you have a New York on that. Get that was on the show. Me my

sequel files. So you will have to first restore it to a server, right? And in that case, the timeline will change the world, 5 will change. So, we will not be able to fly the world 5. Shoe size does matter. This is just a basic guidelines. This is not hard and fast so small. It's advisable to keep what kind of Black Ops because you will not have a, you not use that much space, right? So anything up, 250GB should take that as well as the fight, play fight on firestick level back up, make sure that you have all the options available to you.

At the end of the day, in case you need to restore an 1800 depending on your cost and the resources available both kinds of backups. If not, then physical backup is much preferred to take the beer. You can just order to connect, check the tables, you can extract the data. So you have all these options in case of a physical backup for Open you dumped will simply not finish the fight system level backups. Hello, I have one question actually currently using V mode for taking the trailer back up.

So I would like to know, is it possible to take a backup for instrument for daily back up and what is the difference between the first modern, Streamwood Streamwood and beautiful ReStore in future. So basically the difference between faced more than I do in my face, I don't think that there's any difference. It's just that in the back, end of it's over, we'll get more busy people depend on. Anybody else can get in the world. 5, Okay, so that's really much difference. Prime minister was pretty busy effect mod house because in this, in fact, mode your

slaves will be requesting for the five right in between most of the operations, in case of trimming. The primary server will have to stream the 5. So that's where it will make the difference. Backup time. I don't I don't think so. I'm not aware of any difference. Right. It will definitely be so it will apply the wall face. Flea market is actually afraid of outfit. Yeah, I understand that's about the streaming application. So frequency once a week, at least again, depending on how much rights happened to your muscles over. So in case

you how you get a huge audience in a single day, then you change it to a day again, you have to think how much far back you want to go, right? How much time it takes to backup and Recovery Unit to plant that as well. If you're taking a dump, it might take you $67 to take the backup, right? So that might not be possible everyday because it would do to your muscles over. So you have to take doctor consultation into learning as we said that there are a couple of

backups. Have a look at that as well. But again, I can also act to space once you start having incremental backups, Can you restore a backup? You can have the backup any which way you want. Make sure you plan to restore, you should test it because unless or until you are able to restore in our time, which is feasible or conducive to your business plan. On having a back-up doesn't have, this will not help your business. So make sure you have you are able to check the time, it takes to restore again. That don't think should be acceptable

to your business requirements. How far you might need to restore again? You cannot go. No, cannot go back. You can only go forward. So how much times did I might need to go back? To is the time you need to keep the Wall Files? I need to plan for that. So you make sure that you have that planning done. Always test your backups. All of us take back up and hardly anybody of us take some time to check those backups. There is no way in postgres to check the consistency of a backup unless or until you restore

it. There is no available to check whether your backup is valid or not unless it starts up and you're able to connect to a server. You need to find the recovery time as well, make sure you practice some stores. Right there to understand how much time increases, as you're dating quizzes and everything. So that if you are not there, the other TV, I can understand where the backup is, how much time it will take to the store and what all of our missions are other changes that he needs to be in his

environment. And I think they should help. So, everything else? Just thank you. Thank you.

Cackle comments for the website

Buy this talk

Access to the talk “PGConf India 2020 - Understanding Backup - Amit Sharma - EDB”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Standart

Get access to all videos “PGConf India, 2020”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Ticket

Interested in topic “IT & Technology”?

You might be interested in videos from this event

September 28, 2018
Moscow
16
179
app store, apps, development, google play, mobile, soft

Similar talks

Marc Linster
Chief Technology Officer at EDB
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Robert Haas
VP, Chief Database Scientist at EnterpriseDB
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Jim Mlodgenski
Principal Engineer at Amazon Web Services
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Buy this video

Video

Access to the talk “PGConf India 2020 - Understanding Backup - Amit Sharma - EDB”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Conference Cast

With ConferenceCast.tv, you get access to our library of the world's best conference talks.

Conference Cast
816 conferences
32658 speakers
12329 hours of content