PostgreSQL #6 - Backup & Restore

PostgreSQL #6- Backup and Restore

What do you do if your data is lost ? Are you ready for this kind of scenario ? Every database that exists and even PostgreSQL in sepecific might crush(power outage,physical damage to the server and so on.). As a result of that you might lose crucial/non crucial data. In both cases, if you dont have any backup of your database you have a BIG problem.

If your PostgreSQL cluster is after a crush and you start the instance it will try to recover the data with the transaction log or with the wal files. The wal files are the files that contains all the changes since the last checkpoint and they are saved in the pg_xlog directory(pg_wals in PostgreSQL 10). The instance will try to roll forward the wals in order to save/restore the data that was lost. When the database start rolling forward the wals it start point is the last checkpoint. I'm pretty sure that I talked about it in the last posts but think about it as the last saving point that you had in your game.

Backups & Restore

In postgreSQL we can divide the backups to 2 types : 

1)Logical Backip - Creating an sql dump that includes all the sql commands that are required to generate the object that we decided to backup. The tools that we use in this kind of backup are pg_dump and pg_dumpall.

Advantages : 

-You can select what to backup in the cluster.

-Can be restored in parallel.

-The backup is consist from just one file.

-The backup can be restored to a different release.

Disadvantages : 

-Doesnt support recovery to point in time.

-The databases arent backed up at the same time.

-Doesnt support incremental backup.

-Slower than the physical backup.

2)Physical Backup - Backup the data directory itself, every file, every byte. 

Advantages : 

-Faster than the logical backup.

-Supports recovery to point in time.

-Supports incremental backup.

-Supports backup in parallel

-All the databases are backed up at the same time

Disadvantages : 

-The backup consist from many crucial files.

-You backup/restore the whole cluster and dont have the option to select what to backup.

If you are familiar with oracle then the logical backup reminds the impdp/expdp utility while the physical backup reminds full rman backup. Both of those approaches have some advantages and disadvantages. I recommend  to use both of them so that you can enjoy from the benefits of both worlds.

Hands on : 

For the examples in this post I created a new database called mcdb and a few objects : 

Lets start from the logical backup :

As I said earlier in the post, in this type of backup we have the option to select what we want to backup.In general, before you take a logical backup you need to thing what do you need to backup. Is it a specific object ?  is it a database ? Or do you need to backup all the cluster ?

 I recommend to check the syntax of the commands via pg_dump --help before you start the examples :

Example #1 : Backup the metadata of a specific schema : 

In my example I created all the objects in the schema public So I'll backup the metadata of schema public in the database mcdb : 

-s -> metadata only

-n -> specific schema, in this case its the public schema

-f -> the name of the output file

-d -> what database to backup

Now, lets drop the schema public in our database and restore it : 

Now lets restore the schema via psql. When you create a backup in plain text format the restore is done with the psql utility (yes the one we use to login). In the next example I'll use a different format and you'll see what our other utility for restoring.

If we didnt mention the -c flag during the backup, before restoring the backup we need to create the main object (in this case the schema public). The backup includes everything but the creation command of the main object.  : 

-d -> what database connect to

-f -> run the script mcdb_public_schema.dump (this is the backup that we created).

and the result is : 

For the next example please insert a record into the table mc_table1 : 

Example #2- Backup a specific database : 

Lets backup the whole database that I created : mcdb. It is a very common action to backup the whole database before a maintenance work on that database or in case of any other work.

-d -> what database to backup

-F > in what format the backup file will be (c=compressed custom,d=directory,t=tar,p=plain text which is the default one)

Why would I use one of d/t/c formats ? Well those formats add more functions when we will need to restore our database with that backup. Moreover the backup file is comppreseed and support parallel mode during the restore process. Restoring the database with a backup in one of those format is used with the tool pg_restore. With pg_restore we can restore a specific object that is part of the backup

Now, lets delete the view mc_view and then with the backup that we created we will restore only that view: 

You can check what your dump (if its in special format and not plain text) contains with the commamd pg_restore -l dump_file and the output you will get (for my example ) : 

the restore command : 

-d -> to which database connect

-t -> restore the relation mc_vw (which is our view). You can choose other relations like materialized views,views,tables,sequences,foreign tables.

*The flag -j num (for example -j 5) will create 5 jobs that will work on the restore process. In other words, the j flag is for parallel restore.

* There is another backup tool that I mentioned but I didnt expand on - pg_dumpall. This utility backs up all your cluster. I dont recommend using pg_dumpall because it doesnt support the compressed formats(t/d/c) and it doesnt support parallel restore / backup. If you want to backup all your database, run the pg_dump command for each database individual. Just generate a script that will do it for you (basic loop).

*Sometimes there is a limit on the file size in linux. Therefore I recommnd you to add a gzip command in the end of the backup command, for example : pg_dump -d mcdb | gzip > mcdb.dump.gz

Now lets talk abit about phyiscal backup & restore. In this type of backup there are also 3 approaches :

1)Cold FS backup - just copy the data directory of your database. To do so you must shutdown your cluster in order to have a consistent backup.

2)Snapshot of your server - just take a snapshot of your virtual machine, in this case you dont have to shutdown your cluster.

3)Continues archiving - In this type of backup we can take a live fs backup. Moreover, we also backup our wals files and that allows us to do point in time recovery if needed. I'll focus on this backup because it is considered the most complicated one of the there : 

Requirements : 

you need to change a few parameters in the postgresql.conf : 

-wal level need to be set to at least replica.

-archive_mode need to be set to on

-archive_command need to be set 

1)Create a directory on the local/remote machine or on a nfs mount to save the wals that are created. In this example we will create the directory /PostgreSQL/9.6.3/archives :

2)Now change the parameters that I mentioned in the requirements. We set the archive command to : 

archive_command='cp -i %p /PostgreSQL/9.6.3/%f''

Inthe postgresql.conf file you can see the meaning of %f and %p : 

%p - path of file to archive

%f - file name only

3)Restart the cluster to apply those changes

4)lets switch xlog(wal) and by that we will force a checkpoint : select pg_switch_wal() (in pg10) and select pg_switch_xlog in the previous verisons : 

5)connect via psql to the cluster with superuser and run : select pg_start_backup('write your label');

6)Copy the data directory and save it in a safe place. If you have tablespaces that arent default make sure to copy those files also !

    

7)Now connect to the cluster with a superuser and run : select pg_stop_backup() : 

Now,after we have a copy of the data directory if you'll check its content you will see that there is a new  file called back_label in that directory. This file contains metadata about the backup we took:

(dont mind about the dates in the picture, my server doesnt have a synchronized clock).

Now we have a hot backup of the database. All we need in order to do PITR(Point in time recovery) are the wals that were generated since the moment this backup was taken. Lets do a live example, 

Now lets insert a few records into our table : 

lets switch an xlog : 

Now lets shutdown our cluster and delete our data directory. This kind of event is one of the worse things that can happen to us. 

Now, we have the backup of the data directory(that we took with the cp command) and we have all the wals that were generated and were coppied with the archive_command. Therefore, we can restore the database to any point in time.

Restore Point in time

1)Lets place the backup of the data directory that we have as our new data directory. If you have tablespaces that located out of the data directory place them in the right place aswell. Make sure that all the soft link in pg_tablespace are exist.

2)Now you need to delete all the content of the pg_wal / pg_xlog directory :

*Sometimes you might get errors that another postgres instance is up, so delete the postmaster.pid .

3)Lets create a new file in the data directory that is called recovery.conf. Inside we need to specify the restore_command. When the cluster starts and it recognize that there is a recovery.conf file in the data directory it realizes that it needs to start recovering itself. Once it is done recovering it changes the name of the file to recovery.done. Inside the recovery conf place the command :
 restore_command='cp /PostgreSQL/9.6.3/archives/%f %p'
*Make sure that the first path is the path you mentioned in the archive_command(where the wals were coppied to).

4)Now is time to start our cluster with pg_ctl start, and the result : 

Now lets focus abit on the output. As you can see, the cluster is starting to search for the wals in order to recover them. If you'll focus on their name you will realize that they are in hexa-decimal base. So the cluster succeeded to restore 3 wals and then it failed on the fourth. Why then ? Well, its is simple it failed because the last wal that was generated is the one with the A in the end and B wasnt created. In other words, we restored the database to the last point in time it was available.

Lets check that the table mc_table1 has all the data that we inserted into it after we took the hot backup of the data directory : 

Finished.

Summary

To sum things up, there are 2 main type of backups : physical / logical. Both of them are easy to do once you try them a few times. There are many other open source tools that can help you manage your backups. One that I can recommend is pg_barman which reminds the oracle`s rman utility very much. Moreover, I didnt specify in this post the postgres utility pg_basebackup but it is very similar to the concept of pg_start/stop_backup. I know that this post was very long but I hope that it was helpfull. Feel free to contact me of leave any reply. See you in the next post :)