L
L
Luan2022-02-04 12:48:16
PostgreSQL
Luan, 2022-02-04 12:48:16

How to reduce Wal in postgresql?

There is a server with bases 1s. There are not so many places, 80 gigs of disk and 10 for backup for wal magazines, not big 2 bases are spinning. Everything was fine from the beginning. Then Over time, the pg_wal directory began to grow. But then we figured it out, a log was being accumulated, which was not transferred to the disk for backup. Switched off and on archive_mode and the log began to be transferred to the backup disk. But the logs have accumulated for 40 gigs, and we have only 10, i.e. still not transferred.
The disk is now not enough and the question arose: is it possible to delete what was transferred to the backup and to unload the log so that it does not accumulate?
And you can somehow reduce the number of files for both backup and databases? Doesn't postgres need to keep that many logs?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Melkij, 2022-02-04
@melkij

Doesn't postgres need to keep that many logs?

And the fact that postgresql itself does not need to be transferred somewhere via archive_command at all, and the base does not worry about these files in any way. This is for whoever sets it up. And just this person will be in charge of the question "how many shafts need to be stored."
If the one who configured the archive_command doesn't care about the archive either, then the archive will be infinite (16 EiB, that's not a joke).
The wal archive is needed for pitr (or file-shipment replication). For pitr, absolutely all wal are needed, without exception, from the moment the basebackup was removed to the required recovery point (and the recovery point cannot be earlier than the end time of the basebackup taken as the basis). In what time frame you need pitr - choose accordingly when defining your own backup requirements policy.
(10GB of space for backups sounds frankly amazing, if you have such a small database, then why do you need 80GB of the main disk? And 8 would be a lot)

P
Prog, 2022-02-14
@damprog

It is not clear why you need WAL under 1C "with small databases"?
pg_dump will do you in seconds...

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question