Re: [Beginner Question] Will the backup wal file take too much storage space?

On Tue, 13 Jun 2023 at 10:01, Wen Yi <896634148(at)qq(dot)com> wrote:
> As you can see, base on the wal, we can restore the database to any check point, but sometimes, If we insert a large column to the postgres (Up to 1 GB), the postgres will also create a 1 GB wal to store it.

You normally restore from a checkpoint and keep the logs from there.
Checkpoints are done to avoid the need to keep all the WAL files from
the moment you created the database. This is when you want the normal
things, to be able to restore to a correct state.
Checkpoints indicate all the wal files before them have been applied
and can be discarded.

Checkpoints are not restore points, you restore to a point in time.
With a copy of the data files and all the wals AFTER the copy you can
advance the copy to a correct state after that point in time.

When you insert postgres record it in the wal, once it is there pg
knows it cannot be lost. Then it updates the main data files when it
is more convenient. And then it deletes or recycles old wal files.

But if you want to restore to ANY point in time you would need a copy
of the initial state of the database and ALL the wal files. Normally
you do not want to do this, at most you want to restore to "any point
in the last 3 days", in which case you will need a copy of the
datafiles from 3 days ago plus all the wals from there plus change.

> In this situation, will the backup wal file take too much storage space?
This is confusing. WALs are not backup per se, they are a recovery
mechanism. You do a backup of the main data files and the wal files
because you do not want to stop the world to do the backup. The COPY
of the datafiles and the wals is the backup. If you want to restore to
a very far point in a DB which has very large files it will take a lot
of space. But if you did it that way the copy of the data files will
not be in the initial ( empty ) copy of the datafiles, it will just be
in the wal copy, and it needs to be somewhere if you want to do that.

Francisco Olarte.