Basic Postgresql Backup and Restore

hi I'm Kristin and in this video I'm going to be covering a basic PostgreSQL backup and restore so in terms of basic I'm going to be focusing on sequel dumps so sequel dumps basically dump the database to a text file and all it contains are sequel commands to rebuild your database in the data now a sequel dump is typically run as the Postgres user because you want essentially someone with super user privileges to be able to backup all the data that you have and the objects as well now the sequel dump in Postgres is case it is essentially a snapshot in time from the point at which you start it so it is a consistent snapshot of the database one advantage of taking backups using a sequel dump is that it can be restored in a newer version of Postgres if you want to do upgrade the other advantage is it's mostly non-blocking so it can happen or you can take backups while the system is online serving applications now we're going to be talking about the two command line utilities that you use to create a sequel dump one is called a PD dump and that's for done creating a single dump for individual databases and one is pg dump all which dumps all the databases for the cluster as well as all the global objects such these are users groups table spaces etc okay so let's take a look at some backup and restore commands so the backup commands you can use PG dump this happens on the command line usually running as the post res user so PG dump specify your database name that you want to do the dump for and direct it to the output file now you can give it whatever extension you want but when I do what I tend to do dot sequel because it's essentially outputting SQL now if you want to dump the entire database cluster so all the databases on a given post gross cluster you would do PG underscore dump all and direct it to a different backup file now to do the restore because these are just sequel files you can treat it as an input file into ap SQL command so essentially this command can next to the database using psql for the particular database name and then runs this as an input file to load all the data back into the database and similarly if you want to restore a cluster backup you would use P SQL and direct the input file with the F option and then specify the database to be actually the Postgres database because typically if you're installing the whole cluster there's no other database other it's there it's a fresh cluster now something else you may want to consider is using compression so when doing a backup if you wanted to use compression you could do PG dump your database name and pipe it to a command like gzip and what I tend to do is use the extension GZ when I export it to something like that similarly you can do something for PG dump all so you could pipe it to gzip and then specify your file in terms of restoring when you're using compression you would use something like a gun zip and specify the file of interest and the see option specifies to send it to standard out so you can pipe the decompressed output of this file pipe it into the PS QL command and specifying the database that you want to directed towards so this will restore the database with this particular name and then this is what the command looks like for doing a PG dump all so essentially you're doing the same thing in piping the Pope to P SQL and specifying postgrads is the database okay so let's take a break here and let's look at my local system so this is I'm using PG admin which is a GUI - GUI tool for Postgres that allows me to look at the different databases and tables etc on my system and you can see I have other than the Postgres database I have three other databases here and the X just means I'm not connected to them in PG admin yet so one of my applications tests tab development is actually a rails app and it is running right here if I hit refresh so this is data that is in that database so now what I want to do is I want to take a - a database dump of this particular database my test app database so I'm going to go to the command line and again this is my local system this actually is a Ubuntu system 1404 and the first thing I'm going to do because I like to do the backups as postgrads is assume the post cries user and then now I'm going to paste this command so I'm going to do a PG dump the tests tap a development database again piping its output to gzip and then it's going to direct it to this file so I have a backup directory under my post-grad user and it's going to put a test app underscore backup GZ into that directory so I hit if I hit enter that should be done if I do a list I can see that there is a I'll dua I can see that there is a backup directory here and if I look in it I can see my test step underscore backup GZ which I just created now I'm going to go in and stop my web application that's running so I've stopped my test app and then I'm going to drop the database so I'm running a POS QL command and I'm passing this command so this will run this run the sequel command as the post-crisis user so I'm saying drop the database test app underscore development okay so that database is dropped and I can actually confirm it if I go back into PG Ataman and do a refresh so test app is no more now before I can restore it I'm going to need to just create the database before I can restore all the objects in it because with the I'm not doing a PG dump all I'm doing just a standard PG dump so it's going to want to restore into a database so I need to go ahead and create it now so I've created the database now just to see what it looks like let's I'm going to go ahead and start up the rails app so it starts fine but if I go back here and hit refresh there's migrations pending so the state of the database is definitely not where it needs to be so let's go back to the command line and I'm going to paste in the restore command then I will go ahead and stop the connections to the database okay so I'm going to run the restore command gun zip again send it the this extracted file to standard out so it can be piped into the P SQL command and we're restoring it to the test step development database okay so that ran and don't say any particular error so it looks like everything worked so now I'll go back start up my database I'm sorry start at the rails application and if I go back here and hit refresh there we go we see the same two entries that were here previously so we've successfully dumped out our database and destroyed it and and then restored it back to the time it was at the point where we backed it up so what I've just shown you is the PG dump command example I didn't want to blow away my Postgres database cluster but I could have done it PG all back up and then a PG all restore or use the methods to do a full cluster restore and now I'm going to show you a script that I use in my work at times when I just want a very basic backup so this is just a shell script what it does when it starts running is it grabs what the data is and puts it in a particular format then it goes through it will delete any old backups in that exists in the directory older than a day then it does a full Postgres cluster cluster backup so PG dump ball to gzip and it uses the pins the date at the end additionally if you've connected up with a file storing service like s3 you could put in a command here that will sync your backup directory with s3 in addition you will probably want to synchronize your postgrads configuration files as well so if you've made any changes to PostgreSQL kampf or your connection information with HPA you want to backup those as well and on Ubuntu at least there under the EDC postcards SQL directory and then under the particular cluster of interest so this is a 9.3 in the main cluster so that's where those configuration files are stored and that just sends them to whatever particular bucket you set here so to run a command like this I typically typically enable crontab and paste a command similar to the following so at 4:00 a.m. UTC every day run the backup script dot shell and send output what's going on with this script in any errors to the backup script log in the same directory now if I wanted to run that script right now it is in the current directory so I could just do dot slash backup script to run it and it would help to actually spell it correctly okay so it just ran through and it gives you a output with what's going on and if we do an LS backup to look in the back directory we can see our database cluster dump with the date appended was created so thus far I've shown you how to backup a database and go ahead and delete that database and restore it as well as shown you a backup script that you could use to backup your own systems and put it on a regular recurring basis in cron to run but what you'll definitely want to do is whatever backup scheme you come up with and bring up a second server or second location and restore the full database to it to make sure that your restore procedures work correctly so there's just a few more things I wanted to mention what about restoring individual tables can that be done using sequel dumps so by default no in order to do it you must use a custom PG dump format because PG dump just helps put outputs sequel commands in a text file and it reads them in with P SQL and it doesn't know how to do individual tables however you can use a custom dump format by using the syntax so doing a PG dump FC which defines the file format as being custom doing your particular database and sending the output to DB backup dump again the extension doesn't matter I'm just using dump for clarity purposes now for doing the restore you you don't use P SQL you use another command called PG restore so you could restore a database on by doing PG PG underscore restore the d option specify the database and then the file you want to restore now there are other options to be able to restore individual tables now you may be wondering can you use PG dump all to dump to a custom format and you cannot but there is a way to get around it so you can use PT dump ball and use a Global's only option that allows you to backup information such as the tablespace information users groups so you could backup that and not the databases then you could backup each database using the custom format with PG dump so that is one way to get around it essentially to have your cake and eat it too ok let's talk just briefly about restore errors so you can specify to halt on any errors when you're doing a psql load if you set on error stop equals on so how this would look with your standard psql restore command is this the standard command and then you would just add this to the end of it for example now you want to be aware using this with a full cluster dump because when you're doing a full cluster dump you're backing up absolutely everything and normally you still have things like the Postgres user or other things in the database when you create a new cluster and you're going to be doing a restoring a full cluster so one alternative to this is restore a Global's only cluster dump without on air stop then restore each database with on air stop enabled so restoring just the Global's again the users groups table spaces should happen very fast and you should be able to identify any errors by eye but then as you're restoring each database with all the data you could use the on air stop just to ensure that the restore happened with it out any errors occurring okay so after the restore one thing you want to consider doing is doing a vacuum DB - a - Z so what this does is it vacuums which is essentially a garbage collection and analyzes statistics for all databases so a means all databases in Z means to go ahead and analyze the statistics so generally you generally you will want to run this after you do a restore operation so in terms of references to find out more information about this and there are the postcards SQL Docs and this particular link talks a lot about what I've presented here you can also just look at the man pages if you're on your server or if you're using Ubuntu or other distribution you can look at that PG dump PG dump all and the vacuum DB so if you're interested in learning more about systems administration database administration application management in general and be sure to subscribe on youtube or join our newsletter at Ruby tree software comm slash newsletter thanks

Channel: Ruby Tree Software, Inc.

Views: 89,101

Keywords: PostgreSQL (Software), pg_dump, sql dump, pg_dumpall, postgres, backup, restore

Id: RDBia4-kHoM

Length: 18min 0sec (1080 seconds)

Published: Tue Feb 03 2015