Обсуждение: how to cleanup archive based on datetime

Поиск
Список
Период
Сортировка

how to cleanup archive based on datetime

От
magodo
Дата:
Hello,

I know there is a tool `pg_archivecleanup`, but it only accepts wal
filename. What if I want to clean up all archived wal before a
specified datetime?

Though I could check the ctime fo each wal and figure out which to
remove, but it looks like not a elegent way to do this.

Also I tried to parse each wal and compare with the specified datetime
in a binary-search way, but it is based on the fact that every COMMIT
time in wal is sequential, but it looks like not the case..

Hence I wonder what is the best practice for this task?

Thank you in advance!
---
Magodo




Re: how to cleanup archive based on datetime

От
Adrian Klaver
Дата:
On 10/10/18 12:00 AM, magodo wrote:
> 
> Hello,
> 
> I know there is a tool `pg_archivecleanup`, but it only accepts wal
> filename. What if I want to clean up all archived wal before a
> specified datetime?


That is dangerous, given that a WAL is important not based on its age 
but on whether it's content is still needed.

It would help to know what it is you are trying to achieve?

> 
> Though I could check the ctime fo each wal and figure out which to
> remove, but it looks like not a elegent way to do this.
> 
> Also I tried to parse each wal and compare with the specified datetime
> in a binary-search way, but it is based on the fact that every COMMIT
> time in wal is sequential, but it looks like not the case..
> 
> Hence I wonder what is the best practice for this task?
> 
> Thank you in advance!
> ---
> Magodo
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: how to cleanup archive based on datetime

От
Martin Marques
Дата:
El 10/10/18 a las 04:00, magodo escribió:
> 
> Hello,
> 
> I know there is a tool `pg_archivecleanup`, but it only accepts wal
> filename. What if I want to clean up all archived wal before a
> specified datetime?
> 
> Though I could check the ctime fo each wal and figure out which to
> remove, but it looks like not a elegent way to do this.
> 
> Also I tried to parse each wal and compare with the specified datetime
> in a binary-search way, but it is based on the fact that every COMMIT
> time in wal is sequential, but it looks like not the case..
> 
> Hence I wonder what is the best practice for this task?

Don't do that manually. Install `barman` and let it take care of
removing old WALs which are not needed anyore.

Regards,

-- 
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services