Thread: pg_log
Question: what is logged in pg_log table and how I can cleanup it? (And is this possible at all?) PostgreSQL version: 7.0.3 -- Andrew W. Nosenko (awn@bcs.zp.ua)
"Andrew W. Nosenko" wrote: >Question: what is logged in pg_log table and how I can cleanup it? (And >is this possible at all?) Don't touch it !!! It records the status of all your tuples. If you wipe it, you destroy your data. Developers: I've seen this question several times; wouldn't it be better to rename pg_log to pg_tuplestatus? Calling something ...log is asking for it to be wiped. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Jesus said unto her, I am the resurrection, and the life; he that believeth in me, though he were dead, yet shall he live" John 11:25
"Andrew W. Nosenko" <awn@bcs.zp.ua> writes:
> Question: what is logged in pg_log table and how I can cleanup it?
Transaction commit-status bits, and don't even *think* of touching it.
Easiest way to hose your database that I know of :-(
If it gets big enough to be really annoying, you can
dumpall/initdb/reload. There is no shortcut.
regards, tom lane
"Oliver Elphick" <olly@lfix.co.uk> writes:
> Developers:
> I've seen this question several times; wouldn't it be better to rename
> pg_log to pg_tuplestatus? Calling something ...log is asking for it
> to be wiped.
In 7.1 it'll have a numeric filename ($PGDATA/global/1269, actually)
which should reduce the tendency for DBAs to assume there's nothing
important in it. I regard this as one of the major advantages of
having switched to numeric filenames ;-)
regards, tom lane
Tom Lane wrote: > > "Oliver Elphick" <olly@lfix.co.uk> writes: > > Developers: > > I've seen this question several times; wouldn't it be better to rename > > pg_log to pg_tuplestatus? Calling something ...log is asking for it > > to be wiped. > > In 7.1 it'll have a numeric filename ($PGDATA/global/1269, actually) > which should reduce the tendency for DBAs to assume there's nothing > important in it. I regard this as one of the major advantages of > having switched to numeric filenames ;-) > What are the others? Is the point of moving to numeric filenames to discourage people mucking with the files? With the current system for example if a backend coredumps I know to look for it in the directory of the database. But how will I know what that is in the future? -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
> > In 7.1 it'll have a numeric filename ($PGDATA/global/1269, actually) > > which should reduce the tendency for DBAs to assume there's nothing > > important in it. I regard this as one of the major advantages of > > having switched to numeric filenames ;-) > > > What are the others? Is the point of moving to numeric filenames to > discourage people mucking with the files? With the current system for > example if a backend coredumps I know to look for it in the directory of > the database. But how will I know what that is in the future? I am planning to write a utility to map file name/number to database objects, hopefully for 7.1. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Joseph Shraibman <jks@selectacast.net> writes:
> Tom Lane wrote:
>> In 7.1 it'll have a numeric filename ($PGDATA/global/1269, actually)
>> which should reduce the tendency for DBAs to assume there's nothing
>> important in it. I regard this as one of the major advantages of
>> having switched to numeric filenames ;-)
>>
> What are the others? Is the point of moving to numeric filenames to
> discourage people mucking with the files?
Er, you did recognize that as a joke, right son?
The point of using OID-based filenames is to support rollback of
create/drop/rename table commands. Consider
create table foo (f1 int);
<< load foo with a bunch o' data >>
begin;
drop table foo;
create table foo (bar text, baz numeric);
<< load foo with a bunch o' data >>
rollback;
Under the 7.0-and-before file naming convention, it's almost impossible
to cope with this. 7.0 deletes the file $PGDATA/base/yourdb/foo as soon
as it sees the DROP TABLE, and at that point you're already screwed for
rollback, never mind the subsequent CREATE of a completely different
table also named foo. You could try postponing the physical delete of
foo until commit, but that still fails if the transaction rewrites foo
with different data as above.
If the physical file names are based on unique, never-reassigned OIDs,
then all these problems go away at a stroke. 7.1 does indeed support
rollback of drop and rename table, and the reason is that it doesn't
have to rename the physical files under the hood.
> With the current system for
> example if a backend coredumps I know to look for it in the directory of
> the database. But how will I know what that is in the future?
'core' is still 'core' ;-). Beyond that, the OID and name columns of
pg_database and each database's pg_class give you the mapping. Bruce
is threatening to write some utilities to make the mapping more readily
available to a DBA, but that's not done yet.
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [010116 16:38] wrote: > "Andrew W. Nosenko" <awn@bcs.zp.ua> writes: > > Question: what is logged in pg_log table and how I can cleanup it? > > Transaction commit-status bits, and don't even *think* of touching it. > Easiest way to hose your database that I know of :-( > > If it gets big enough to be really annoying, you can > dumpall/initdb/reload. There is no shortcut. My pg_log file is about 3MB on one server and 5MB on another. If 3-6MB is a "pretty large" size for pg_log and people are deleting it, perhaps a name change is in order? pg_commitstatus? or something? I think pg_'log' screams 'delete me please' to um... "power users" :) -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Alfred Perlstein wrote: : * Tom Lane <tgl@sss.pgh.pa.us> [010116 16:38] wrote: : > "Andrew W. Nosenko" <awn@bcs.zp.ua> writes: : > > Question: what is logged in pg_log table and how I can cleanup it? : > : > Transaction commit-status bits, and don't even *think* of touching it. : > Easiest way to hose your database that I know of :-( : > : > If it gets big enough to be really annoying, you can : > dumpall/initdb/reload. There is no shortcut. Thanks to Tom for answer, this is exactly what I want to know. : : My pg_log file is about 3MB on one server and 5MB on another. If : 3-6MB is a "pretty large" size for pg_log and people are deleting : it, perhaps a name change is in order? : : pg_commitstatus? or something? : : I think pg_'log' screams 'delete me please' to um... "power users" : : :) : Sorry for my English, when I say `cleanup' I don't say about deleting file nor about any other changes directly/"in evade"/without PostrgreSQL backend. But simple about `is this file/table/etc have data what never be used anymore (like deleted tuples in regular tables)?' and if this sort of data exist then `is exist some analog of VACUUM that can return this space into filesystem (decrease size of file)?' -- Andrew W. Nosenko (awn@bcs.zp.ua)
* Andrew W. Nosenko <awn@bcs.zp.ua> [010117 01:16] wrote: > Alfred Perlstein wrote: > : * Tom Lane <tgl@sss.pgh.pa.us> [010116 16:38] wrote: > : > "Andrew W. Nosenko" <awn@bcs.zp.ua> writes: > : > > Question: what is logged in pg_log table and how I can cleanup it? > : > > : > Transaction commit-status bits, and don't even *think* of touching it. > : > Easiest way to hose your database that I know of :-( > : > > : > If it gets big enough to be really annoying, you can > : > dumpall/initdb/reload. There is no shortcut. > > Thanks to Tom for answer, this is exactly what I want to know. > > : > : My pg_log file is about 3MB on one server and 5MB on another. If > : 3-6MB is a "pretty large" size for pg_log and people are deleting > : it, perhaps a name change is in order? > : > : pg_commitstatus? or something? > : > : I think pg_'log' screams 'delete me please' to um... "power users" > : > : :) > : > > Sorry for my English, when I say `cleanup' I don't say about deleting > file nor about any other changes directly/"in evade"/without PostrgreSQL > backend. But simple about `is this file/table/etc have data what never > be used anymore (like deleted tuples in regular tables)?' and if this > sort of data exist then `is exist some analog of VACUUM that can return > this space into filesystem (decrease size of file)?' We get a lot of "I deleted pg_log and now everything is broke" emails on this list. Just curious, how large is this file on your system? -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Tom Lane wrote: > > 'core' is still 'core' ;-). Beyond that, the OID and name columns of > pg_database and each database's pg_class give you the mapping. Bruce > is threatening to write some utilities to make the mapping more readily > available to a DBA, but that's not done yet. > > regards, tom lane Simple: symlink made out a table name pointing to a file with a number name. Won't work with windows, but simple on unix. -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com