0

I was following this tutorial about point in time recovery in postgres and come up with the following questions. Can anybody help me.

  • Is it necessary to use pg_basebackup every time I want to back up the database or the postgres backs up the database and stores somewhere considering its configurations?
  • What does the psql -c "select pg_switch_wal();"do? and is it necessary to run every time I want to back up the database?

I use postgres 11 on ubuntu 18.04.3 if that helps.

EDIT

Let me be more specific in my question. As I've understood by searching on the internet, we should have two files to back up the database: one base which contains the general files such as table attributes and a WAL file which contain the log files showing instances in the database. And I have seen that pg_basebackup backs up whole database cluster not only WAL files, so there should be a function which only backs up WAL files.

Thanks

0

In theory, you can run "pg_basebackup" just once (or once per major version upgrade) but then it will take longer and longer to do a restore, as it has to run through all the accumulated WAL since that backup. So it usually makes sense to do a new pg_basebackup periodically.

"The database" won't do it for you. There are a variety of backup tools out there that probably will (pg_barman, pgbackrest, pg_probackup, etc.). But you didn't mention using any of them.

pg_switch_wal() is not necessary as pg_basebackup does it for you.

pg_basebackup will archive the wal files if you set it to, but will only do so for WAL files which are generated during the duration of the base backup itself. This is suitable for a snapshot backup, but not for PITR. In other words, it will let you restore to the exact moment the backup command finished, but is not enough to let you restore to some point after that time. To backup WAL files at other times, you need to set the "archive_command" setting to something that will copy the files for you. (Also, if you have a valid "archive_command" in place before you start the basebackup, then the basebackup itself doesn't really need to backup the WAL in the first place. Then the base backup will not be self contained, you will have to pair it with the WAL archive in order to restore.)

The tutorial you refer to shows a setting for "archive_command". However, the setting it shows is not bullet-proof. As the docs say, "this is an example, not a recommendation". It has two main problems. Once is that it does not fsync the copy before declaring success. That means the system can remove/recycle the WAL file from pg_wal, before the copy of it has been flushed to the disk drive; and so a crash at the wrong time can leave you with zero valid copies. The other problem is that it seems to be copying the file locally. That means that if your computer is destroyed, you will lose your backup at the same time as you lose your master copy. (It is possible that it is not copying locally, because /var/lib/postgresql/pg_log_archive might be a network mounted file-system; but if so then that exacerbates the fsync problem, how do you know the copy on the remote file system is truly safe?)

If you don't want to use "archive_command", then another tool is "pg_receivewal". You would run this on another server, and it will connect to your Postgresql instance and "pull" the wal files, rather than the "push" that "archive_command" does.

Yet another option is to set up a streaming standby on another server, set "archive_mode" to "always", and then have "archive_command" run on the standby, rather than on the master, to save your WAL files.

  • thanks for reply, Let me be more specific in question, Is pg_basebackup backs up all the data or just the base files? If pg_basebackup backs up only base or backs up data that are not necessary ( I mean that as its name suggests that this tool backs up the base files), So how could I backup only wal files so that my back up would have much less volume? – eng.mrgh Oct 10 at 18:38
  • If you want to expand on your question could you put the extra detail in the question (edit) and then let the person who answered know by putting @ in front of their handle (no space). p.s. welcome to the forum! :-) – Vérace Oct 10 at 19:26
  • The behavior of pg_basebackup has changed from version to version. In 11, it will automatically backup the wal files it needs in order to make a self contained base backup, unless you go out of your way to tell it not to. Of course it will not back up future wal files. That is what "archive_command" is for. – jjanes Oct 10 at 20:06
  • Is there a way to back up only wal files so that the backup process finishes fast? Is it applicable that once we back up base files and then only back up wal files?@jjanes – eng.mrgh Oct 10 at 20:19
  • If you only have WAL files, then you can't restore. You need at least one base backup. Again, backing up only WAL files is what archive_command is for. There is no "mode" of pg_basebackup that tells it not to do a base backup. My screwdriver does not have a setting for baking bread. – jjanes Oct 10 at 20:25
0

pg_basebackup creates a complete copy of the database. You need transaction log (WAL) archives to make the backup consistent during restore and to continue replaying all thr data modifications until the point to which you want to recover.

You have to schedule regular base backups as starting points for recovery, otherwise recovery will take too long.

In the event of an outage, you can recover the database using a base backup and WAL archives. What you cannot recover are the last data modifications that are not archived yet, because they are in the currently active WAL file. I guess the page you were reading uses pg_switch_wal() to switch to a new WAL file and archive the current one so that they can recover the last fee transactions.

Normally, if you cannot lose any data even in the event of a catastrophe, you archive WAL with pg_receivewal rather than with archive_command.

  • Could you explain the second paragraph and the fourth one a bit more? – eng.mrgh Oct 12 at 13:54
  • Second paragraph: you can keep replaying transaction logs, but if you replay too many, it will take too long. So you perform regular backups to keep recovery time moderate. Fourth paragraph: That builds on the third one. It mentions pg_receivewal as a tool to overcome the limitation described in the third paragraph. Read the documentation for more about pg_receivewal. – Laurenz Albe Oct 12 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.