Обсуждение: WAL logs clog-up disk space
Hi there, I'm currently using a PostgreSQL 9.0 installation on a CentOS 5 linux machine. I'm not the administrator of the machine, however the person that is doesn't know very much about PostgreSQL unfortunately. So I'm trying to work with them to sort out a problem I'm having. What seems to be the problem, is that periodically, a folder where some WAL logs are stored gets full i.e. the disk partition runs out of space. I've looked into the pg_conf file and have found this: archive_mode = on archive_command = 'cp %p /var/lib/pgsql/9.0/data/pg_wal/%f' The folder 'var' is on a partition with about 20GB of space. Once every few weeks or so, this gets full, and I can't use PostgreSQL. The administrator then runs something like the below to clean the folder out: pg_archivecleanup -d /var/lib/pgsql/9.0/data/pg_wal 000000010000001600000016.00000017.backup Could someone suggest a more permanent solution instead please? Is there a better way to configure this? In the meantime we have changed the code in the pg_conf to below: archive_mode = on archive_command = 'cp %p /home/userdb/pg_wal/%f' This saves the files to a much larger area of the disk. But it's more of a band-aid than a solution clearly. Thanks James
James David Smith wrote: > I'm currently using a PostgreSQL 9.0 installation on a CentOS 5 linux > machine. I'm not the administrator of the machine, however the person > that is doesn't know very much about PostgreSQL unfortunately. So I'm > trying to work with them to sort out a problem I'm having. What seems > to be the problem, is that periodically, a folder where some WAL logs > are stored gets full i.e. the disk partition runs out of space. I've > looked into the pg_conf file and have found this: > > archive_mode = on > archive_command = 'cp %p /var/lib/pgsql/9.0/data/pg_wal/%f' > > The folder 'var' is on a partition with about 20GB of space. Once > every few weeks or so, this gets full, and I can't use PostgreSQL. The > administrator then runs something like the below to clean the folder > out: > > pg_archivecleanup -d /var/lib/pgsql/9.0/data/pg_wal > 000000010000001600000016.00000017.backup > > Could someone suggest a more permanent solution instead please? Is > there a better way to configure this? Yes, there are several ways. First, do you need to be able to restore the database to any given point in the past? If not, and you do your backups with pg_dump, you can simply set archive_mode=off and restart the database. Otherwise you should make sure that all archived WALs older than your oldest backup are deleted. You can use cron jobs or something else for that. Maybe you want to have a look at http://www.pgbarman.org/ Yours, Laurenz Albe
Dear Laurenz, Thanks for the reply. Answers below On 27 November 2013 15:18, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > James David Smith wrote: >> I'm currently using a PostgreSQL 9.0 installation on a CentOS 5 linux >> machine. I'm not the administrator of the machine, however the person >> that is doesn't know very much about PostgreSQL unfortunately. So I'm >> trying to work with them to sort out a problem I'm having. What seems >> to be the problem, is that periodically, a folder where some WAL logs >> are stored gets full i.e. the disk partition runs out of space. I've >> looked into the pg_conf file and have found this: >> >> archive_mode = on >> archive_command = 'cp %p /var/lib/pgsql/9.0/data/pg_wal/%f' >> >> The folder 'var' is on a partition with about 20GB of space. Once >> every few weeks or so, this gets full, and I can't use PostgreSQL. The >> administrator then runs something like the below to clean the folder >> out: >> >> pg_archivecleanup -d /var/lib/pgsql/9.0/data/pg_wal >> 000000010000001600000016.00000017.backup >> >> Could someone suggest a more permanent solution instead please? Is >> there a better way to configure this? > > Yes, there are several ways. > > First, do you need to be able to restore the database to any given > point in the past? No. Although a certain amount of time in the past would be useful. Maybe 2-3 days if that is possible? > If not, and you do your backups with pg_dump, you can simply > set archive_mode=off and restart the database. Yes, we could do this. I know how to use pg_dump so we could automate that I guess. > Otherwise you should make sure that all archived WALs older > than your oldest backup are deleted. > > You can use cron jobs or something else for that. > > Maybe you want to have a look at http://www.pgbarman.org/ I've not seen this before. I'll check it out. > Yours, > Laurenz Albe
James David Smith wrote: >>> What seems >>> to be the problem, is that periodically, a folder where some WAL logs >>> are stored gets full i.e. the disk partition runs out of space. I've >>> looked into the pg_conf file and have found this: >>> >>> archive_mode = on >>> archive_command = 'cp %p /var/lib/pgsql/9.0/data/pg_wal/%f' [...] >>> Could someone suggest a more permanent solution instead please? Is >>> there a better way to configure this? >> Yes, there are several ways. >> >> First, do you need to be able to restore the database to any given >> point in the past? > No. Although a certain amount of time in the past would be useful. > Maybe 2-3 days if that is possible? I am afraid that I have been imprecise in my reply. I meant "any given point in time after the end of your oldest backup". You see, if you do your backups with pg_dump or pg_dumpall, you can restore the database only to the exact points in time when you took these backups. If you need to be able to restore your database to a state between two backups, you will have to use a different technology (http://www.postgresql.org/docs/9.3/static/continuous-archiving.html). >> If not, and you do your backups with pg_dump, you can simply >> set archive_mode=off and restart the database. > Yes, we could do this. I know how to use pg_dump so we could automate > that I guess. I suggest that you go with the simplest solution that fulfills your requirements, so if you don't need point-in-time-recovery and your databases are not large, pg_dump might be the best for you. Yours, Laurenz Albe