agora inbox for postgres@postgres.berkeley.edu
help / color / mirror / Atom feedhow to clean up pg_log, pg_time and pg_variable?
6+ messages / 2 participants
[nested] [flat]
* how to clean up pg_log, pg_time and pg_variable?
@ 1994-06-06 09:39 shlam@ie.cuhk.hk
1994-06-06 09:57 ` Re: how to clean up pg_log, pg_time and pg_variable? Paul M. Aoki <aoki@postgres.Berkeley.EDU>
0 siblings, 1 reply; 6+ messages in thread
From: shlam@ie.cuhk.hk @ 1994-06-06 09:39 UTC (permalink / raw)
To: legacy
I discover that the size of $postgres/data/pg_log, pg_time and
pg_variable grow very large. Is there any command to clean up or reset these files ?
Thanks.
Alan S. H. Lam
Department of Information Engineering, CUHK, Hong Kong
E-mail: shlam@ie.cuhk.hk
Tel: (852) 609 8364 Fax: (852) 603 5032
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to clean up pg_log, pg_time and pg_variable?
1994-06-06 09:39 how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
@ 1994-06-06 09:57 ` Paul M. Aoki <aoki@postgres.Berkeley.EDU>
1994-06-06 10:14 ` Re: how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
0 siblings, 1 reply; 6+ messages in thread
From: Paul M. Aoki @ 1994-06-06 09:57 UTC (permalink / raw)
To: shlam@ie.cuhk.hk; +Cc: legacy
shlam@ie.cuhk.hk writes:
> I discover that the size of $postgres/data/pg_log, pg_time and
> pg_variable grow very large.
pg_variable should never grow beyond one disk page (8k).
with respect to the other two, pg_log grows at 2 bits (.25 bytes) per
transaction and pg_time grows at 4 bytes per transaction.
> Is there any command to clean up or reset these files ?
what pg_log and pg_time store are the commit status and commit time of
each transaction. in theory, this could be compressed by something
like run-length encoding (since most transactions commit) or truncated
after vacuuming (if you could somehow arrange to vacuum the entire
installation..). in fact, they just grow.
it would be a bad idea to remove them or truncate them.
--
Paul M. Aoki | CS Div., Dept. of EECS, UCB | aoki@postgres.Berkeley.EDU
| Berkeley, CA 94720 | ...!uunet!ucbvax!aoki
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to clean up pg_log, pg_time and pg_variable?
1994-06-06 09:39 how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
1994-06-06 09:57 ` Re: how to clean up pg_log, pg_time and pg_variable? Paul M. Aoki <aoki@postgres.Berkeley.EDU>
@ 1994-06-06 10:14 ` shlam@ie.cuhk.hk
1994-06-06 10:28 ` Re: how to clean up pg_log, pg_time and pg_variable? Paul M. Aoki <aoki@postgres.Berkeley.EDU>
0 siblings, 1 reply; 6+ messages in thread
From: shlam@ie.cuhk.hk @ 1994-06-06 10:14 UTC (permalink / raw)
To: Paul M. Aoki <aoki@postgres.Berkeley.EDU>; +Cc: shlam@ie.cuhk.hk; legacy; shlam@ie.cuhk.hk
>> I discover that the size of $postgres/data/pg_log, pg_time and
>> pg_variable grow very large.
>pg_variable should never grow beyond one disk page (8k).
>with respect to the other two, pg_log grows at 2 bits (.25 bytes) per
>transaction and pg_time grows at 4 bytes per transaction.
>> Is there any command to clean up or reset these files ?
>what pg_log and pg_time store are the commit status and commit time of
>each transaction. in theory, this could be compressed by something
>like run-length encoding (since most transactions commit) or truncated
>after vacuuming (if you could somehow arrange to vacuum the entire
>installation..). in fact, they just grow.
>it would be a bad idea to remove them or truncate them.
However, the pg_time eat up 40M disk space and pg_log also ~20M
something. Is there any tools to compress this files or expire some of
the information in these log files?
Thanks.
Alan S. H. Lam
Department of Information Engineering, CUHK, Hong Kong
E-mail: shlam@ie.cuhk.hk
Tel: (852) 609 8364 Fax: (852) 603 5032
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to clean up pg_log, pg_time and pg_variable?
1994-06-06 09:39 how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
1994-06-06 09:57 ` Re: how to clean up pg_log, pg_time and pg_variable? Paul M. Aoki <aoki@postgres.Berkeley.EDU>
1994-06-06 10:14 ` Re: how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
@ 1994-06-06 10:28 ` Paul M. Aoki <aoki@postgres.Berkeley.EDU>
1994-06-07 02:09 ` Re: how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
0 siblings, 1 reply; 6+ messages in thread
From: Paul M. Aoki @ 1994-06-06 10:28 UTC (permalink / raw)
To: shlam@ie.cuhk.hk; +Cc: legacy
shlam@ie.cuhk.hk writes:
> >what pg_log and pg_time store are the commit status and commit time of
> >each transaction. in theory, this could be compressed by something
> >like run-length encoding (since most transactions commit) or truncated
> >after vacuuming (if you could somehow arrange to vacuum the entire
> >installation..). in fact, they just grow.
> Is there any tools to compress this files or expire some of
> the information in these log files?
sorry for being long-winded :-) no.
--
Paul M. Aoki | CS Div., Dept. of EECS, UCB | aoki@postgres.Berkeley.EDU
| Berkeley, CA 94720 | ...!uunet!ucbvax!aoki
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to clean up pg_log, pg_time and pg_variable?
1994-06-06 09:39 how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
1994-06-06 09:57 ` Re: how to clean up pg_log, pg_time and pg_variable? Paul M. Aoki <aoki@postgres.Berkeley.EDU>
1994-06-06 10:14 ` Re: how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
1994-06-06 10:28 ` Re: how to clean up pg_log, pg_time and pg_variable? Paul M. Aoki <aoki@postgres.Berkeley.EDU>
@ 1994-06-07 02:09 ` shlam@ie.cuhk.hk
1994-06-07 02:21 ` Re: how to clean up pg_log, pg_time and pg_variable? Paul M. Aoki <aoki@postgres.Berkeley.EDU>
0 siblings, 1 reply; 6+ messages in thread
From: shlam@ie.cuhk.hk @ 1994-06-07 02:09 UTC (permalink / raw)
To: Paul M. Aoki <aoki@postgres.Berkeley.EDU>; +Cc: legacy
>> Is there any tools to compress this files or expire some of
>> the information in these log files?
> sorry for being long-winded :-) no.
Then I want to do what is the consequence if I accidentally delete these files?
Alan S. H. Lam
Department of Information Engineering, CUHK, Hong Kong
E-mail: shlam@ie.cuhk.hk
Tel: (852) 609 8364 Fax: (852) 603 5032
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to clean up pg_log, pg_time and pg_variable?
1994-06-06 09:39 how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
1994-06-06 09:57 ` Re: how to clean up pg_log, pg_time and pg_variable? Paul M. Aoki <aoki@postgres.Berkeley.EDU>
1994-06-06 10:14 ` Re: how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
1994-06-06 10:28 ` Re: how to clean up pg_log, pg_time and pg_variable? Paul M. Aoki <aoki@postgres.Berkeley.EDU>
1994-06-07 02:09 ` Re: how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
@ 1994-06-07 02:21 ` Paul M. Aoki <aoki@postgres.Berkeley.EDU>
0 siblings, 0 replies; 6+ messages in thread
From: Paul M. Aoki @ 1994-06-07 02:21 UTC (permalink / raw)
To: shlam@ie.cuhk.hk; +Cc: legacy
shlam@ie.cuhk.hk writes:
> Then I want to do what is the consequence if I accidentally delete these files?
any tuples that have not been processed by the "vacuum" command become
invisible/"uncommitted", as if they had been rolled back.
the reason is that until the vacuum cleaner processes a newly-
inserted tuple, the system has to consult pg_time and pg_log to see
if/when its transaction committed. if a tuple has not been vacuumed
*and* there is no commit info (because the files were deleted) then
the system assumes the transaction aborted and that the tuple should
be ignored.
an anonymous hacker points out that if you are super-crafty you can
replace log/time pages that correspond to transactions for which *all*
tuples in *all* databases are guaranteed to have been vacuumed with
zero-filled pages (i.e., create a file with "holes," like those
created by dbm) since these do not take up actual file system space.
this is a nifty, tricky way to do the truncation i was talking about
before. we don't currently have a tool to do this.
--
Paul M. Aoki | CS Div., Dept. of EECS, UCB | aoki@postgres.Berkeley.EDU
| Berkeley, CA 94720 | ...!uunet!ucbvax!aoki
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1994-06-07 02:21 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1994-06-06 09:39 how to clean up pg_log, pg_time and pg_variable? shlam@ie.cuhk.hk
1994-06-06 09:57 ` Paul M. Aoki <aoki@postgres.Berkeley.EDU>
1994-06-06 10:14 ` shlam@ie.cuhk.hk
1994-06-06 10:28 ` Paul M. Aoki <aoki@postgres.Berkeley.EDU>
1994-06-07 02:09 ` shlam@ie.cuhk.hk
1994-06-07 02:21 ` Paul M. Aoki <aoki@postgres.Berkeley.EDU>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox