Removing files under pg_clog

Steven Harms escribi�:

I ran into a script today that was removing files under
/var/lib/pgsql/data/pg_clog today because they were too large
(Postgresql 7.4). My initial thought was this could cause data loss
or corruption, can someone provide insight as to if that is correct?

Yeah. How large?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

I don't have stats on how big they were getting, but they are running
this every night, which I suspect causes issues (and I suspect the
reason their logs were getting big is because they programmed a bunch
of locked transactions):

find /pgsql/data/pg_xlog -type f -mtime +1 | xargs rm -f
find /pgsql/data/pg_clog -type f -mtime +1 | xargs rm -f

On Thu, Apr 8, 2010 at 4:06 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

Steven Harms escribió:

I ran into a script today that was removing files under
/var/lib/pgsql/data/pg_clog today because they were too large
(Postgresql 7.4).  My initial thought was this could cause data loss
or corruption, can someone provide insight as to if that is correct?

Yeah.  How large?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
GPG Key ID: C92EF367 / 1428 FE8E 1E07 DDA8 EFD7 195F DCCD F5B3 C92E F367

WWW: http://www.sharms.org/blog

Steven Harms escribi�:

I don't have stats on how big they were getting, but they are running
this every night, which I suspect causes issues (and I suspect the
reason their logs were getting big is because they programmed a bunch
of locked transactions):

find /pgsql/data/pg_xlog -type f -mtime +1 | xargs rm -f
find /pgsql/data/pg_clog -type f -mtime +1 | xargs rm -f

Oh, the *directories* were getting big, not the files? (Normally
pg_clog files do not grow beyond a certain, rather small size, which is
why I was asking) Yeah, they are bound to lose or corrupt data sooner
rather than later. If they ever see their system crash, it won't be
able to recover due to pg_xlog deletion. (Note that pg_xlog is quite
different from pg_clog). pg_clog deletion guarantees that they will
have problem vacuuming or something.

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"And as an added bonus, now my computer goes to the toilet for me, leaving me
free to spend time on more useful activities! yay slug codefests!" (C. Parker)

Alvaro Herrera wrote:

Steven Harms escribi?:

I don't have stats on how big they were getting, but they are running
this every night, which I suspect causes issues (and I suspect the
reason their logs were getting big is because they programmed a bunch
of locked transactions):

find /pgsql/data/pg_xlog -type f -mtime +1 | xargs rm -f
find /pgsql/data/pg_clog -type f -mtime +1 | xargs rm -f

Oh, the *directories* were getting big, not the files? (Normally
pg_clog files do not grow beyond a certain, rather small size, which is
why I was asking) Yeah, they are bound to lose or corrupt data sooner
rather than later. If they ever see their system crash, it won't be
able to recover due to pg_xlog deletion. (Note that pg_xlog is quite
different from pg_clog). pg_clog deletion guarantees that they will
have problem vacuuming or something.

My guess is that they are not vacuuming all databases so old clog files
cannot be removed. That, combined with the fact they are runing 7.4
would incline me to get as far away from that system as possible so as
not be blamed for the predictable problems.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com