On Tue, 13 Jun 2023 at 10:01, Wen Yi <896634148@xxxxxx> 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.
