The third chapter of PostgreSQL Replication Understanding Instant Recovery (1)

So far, you have mastered a certain theory. Because life is not only made up of theories (it can be equally important), it is time to delve into practical work.

The goal of this chapter is to let you know how to recover data to a given point in time. This is important when your system crashes or someone accidentally deletes a table, does not replay the entire transaction log, but replays a small portion of it. Instant recovery (Pitr,point-in-time-recovery) will be a tool for this partial transaction log replay.

In this chapter, you will learn all the information you need to know about instant recovery (PITR), and there will be practical examples to guide you. Therefore, we will apply all the concepts you have learned in chapter two, understand the PostgreSQL transaction log, establish an incremental backup, or set up a simple, basic backup system.

Here is an overview of the topics we'll cover in this chapter:

• Understand the concepts behind PITR

• Configure Postresql PITR

• Run Pg_basebackup

• Restore PostgreSQL to a specific point in time

3.1 Understanding the role of PITR

PostgreSQL provides a tool that can back up a database, called Pg_dump. Basically, Pg_dump will connect to the database, read the transaction isolation level "Serialize" all the data, and return the data as text. Since we are using serialization, the dump is always consistent. So, if your pg_dump starts at midnight, it ends at six o'clock in the morning. You will create a backup that contains all the data as of midnight, but no further data. This snapshot creation is very convenient and completely feasible for data from small to medium volumes.

[dumps always remain consistent.] This means that all foreign keys are intact, and the newly added data is lost after the dump is started. This is probably the most common way to perform standard backups. ]

However, if your data is so valuable and so large that you want to organize it back up, it is obviously not for highly critical data. In addition, the data is not efficient to replay 20TB in text form. An immediate recovery has been designed to solve this problem. How does it work? Based on the snapshot of the database, XLOG will be replayed later. This can be done indefinitely, or to a point in time that you choose. In this way, you can reach any point in time. This method opens the door to many different methods and functions.

• Restore a DB instance to a given point in time

• Create a backup database that contains a copy of the original database

• Create a history of all changes

In this chapter, we will specialize in the functionality of incremental backups. and describes how to change a chosen medium with incremental archiving to make your data more secure.

3.1.1 Moving to an architectural perspective

The following image provides an overview of a common architecture diagram using instant Restore:

We have seen in the previous chapters that PostgreSQL produces a 16MB transaction log segment. Whenever one of these segments is filled and ready, PostgreSQL will invoke the so-called Archive_command. The purpose of the Archive_command is to transfer the Xlog files from the DB instance to the archive. In our image, a small box in the lower right corner of the image is archived.

The beauty of this design is that you can basically use any shell script to archive transaction logs. Here are some ideas:

• Use some simple replication to transfer data to an NFS share

• Run rsync to move files

• Use custom scripts to validate the Xlog file and move it to an FTP server

• Copy XLOG files to tape

The choice of possible management Xlog is limited by imagination.

Restore_command accurately corresponds to Archive_command. Its role is to get the data from the archive and provide it to the instance, which is to replay it (in our picture, this is marked as a restore backup). As you can see, replay may be used to replicate or simply restore a database to a given point in time, as described in this chapter. Again, Restore_command is just a simple script that can do the things you want with a file and a file.

[It is important that you are responsible for archiving as an all-powerful manager.] You must decide how much xlog to keep and when to delete it. The importance of this task cannot be underestimated. ]

Keep in mind that when Archive_command fails for some reason, PostgreSQL will keep the Xlog file for a few seconds and try again. If the archive continues to fail from a specific point, the master may be filled. The order of the Xlog files cannot be interrupted, and if a file is missing, you will not be able to replay the Xlog. All xlog files must exist because PostgreSQL requires a sequence of xlog files. If a file is missing, recovery will stop at the latest location.

The third chapter of PostgreSQL Replication Understanding Instant Recovery (1)