Обсуждение: OID and filesystem issues

Hi All,

I have a couple of questions hopefully someone can help me with.

1) Does every relation exist in pg_class? Can we assume that if there is a file with OID which is not present in
pg_class,this file can be deleted? (we have a few such files, some of them take more than 2G of space in total) 

2) If we remove such file and run vaccum or pg_dump will we get into troubles ? Where does vaccum take information
aboutrelations from? pg_class or filesystem ? 

3) We have a relation which is unaccessible (permission denied), ls -l also prints this message so it's something with
filesystem.What would be the best procedure to solve this issue ? 

Thanks
Lee. *****************************************************
Lee Fedden
Software Developer
CONTEXT
Tel:+44 (0)20 8394 7724
Fax:+44 (0)20 8394 7701
Email Address: lfedden@contextworld.com
Web Site: http://www.contextworld.com
*****************************************************
This message and the information contained therein is intended for the use
of the person(s) ("the intended  recipient(s)" to whom it is addressed. It
may contain information that is privileged and confidential within the
meaning of applicable law. If you are not the intended recipient, please
contact the sender as soon possible. The views expressed in this
communication may not necessarily  be the views held by Context Business
Limited or its subsidiaries. The contents of an attachment to this e-mail
may contain viruses that could damage your own computer system. While every
reasonable precaution has been taken to minimise this risk, Context Business
Limited and its subsidiaries cannot  accept liability for any damage which
you sustain  as a result of software viruses. You should carry out your own
virus checks
before opening the attachment. Please notice that Context Business Limited
monitors e-mails sent or received. Further communication will signify your
consent to this.

Context Business Limited, a Company registered in England under number
06298773. Registered Directors Jeremy Davies and Howard Davies.
Registered office address: 10-18 Putney Hill, London, SW15 6AA
VAT 919 7799 50

lfedden@contextworld.com writes:
> 1) Does every relation exist in pg_class? Can we assume that if there is a file with OID which is not present in
pg_class,this file can be deleted? (we have a few such files, some of them take more than 2G of space in total) 

You do know it's relfilenode that counts, not OID?

As long as you are paying attention to the right column, yes, you could
remove any file that doesn't correspond to a live pg_class row.  It's
not a common situation though so I'd definitely triple-check first.

> 3) We have a relation which is unaccessible (permission denied), ls -l also prints this message so it's something
withfilesystem. What would be the best procedure to solve this issue ? 

Fix the ownership/permissions with chown/chmod, as needed.  Pretty much
all files in a database should be alike in this regard (postgres-owned,
mode 0600).

            regards, tom lane

Hi Tom,
Thanks for your reply.

I did mean the data/OIDs from the relfilenode column in pg_class.

Even when logged in as root, a 'ls -l' command returns:

mm2:/var/lib/pgsql82/data/base/16673 # ls -lash | more
/bin/ls: 19357877: Permission denied
/bin/ls: 19569529: Permission denied
/bin/ls: 19569531: Permission denied
/bin/ls: 19569533: Permission denied
/bin/ls: 19614956: Permission denied
/bin/ls: 19716858: Permission denied
total 124G
  68K drwx------ 2 postgres postgres   68K 2008-04-15 15:52 .
    0 drwx------ 6 postgres postgres   144 2007-06-22 17:20 ..
 7.0M -rw------- 1 postgres postgres  7.0M 2008-04-12 19:51 10163089
    0 -rw------- 1 postgres postgres     0 2007-10-24 10:45 10163091
  24K -rw------- 1 postgres postgres   24K 2008-02-28 10:19 10264430
etc...

And when I tried to change the owner or file permissions (still as root):

mm2:/var/lib/pgsql82/data/base/16673 # chown postgres.postgres 19716858
chown: cannot access `19716858': Permission denied

mm2:/var/lib/pgsql82/data/base/16673 # chmod u+rw 19716858
chmod: cannot access `19716858': Permission denied

So can we assume it is safe to remove these none accessible files and also the couple of orphaned OID files from
pg_classwe can still access? 

Are there any known situations that could cause such issues or anyway we could track back and see how this happened?
I'mkeen to prevent anything like this happening again in the future. 

Thanks
Lee.


 -----Original Message-----
From: tgl@sss.pgh.pa.us
Sent: 15 April 2008 15:44
To: Lee Fedden
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] OID and filesystem issues




 --------------------------------------------------------------------------  --
lfedden@contextworld.com writes:
> 1) Does every relation exist in pg_class? Can we assume that if there
is a file with OID which is not present in pg_class, this file can be
deleted? (we have a few such files, some of them take more than 2G of
space in total)

You do know it's relfilenode that counts, not OID?

As long as you are paying attention to the right column, yes, you could
remove any file that doesn't correspond to a live pg_class row.  It's
not a common situation though so I'd definitely triple-check first.

> 3) We have a relation which is unaccessible (permission denied), ls -l
also prints this message so it's something with filesystem. What would be

the best procedure to solve this issue ?

Fix the ownership/permissions with chown/chmod, as needed.  Pretty much
all files in a database should be alike in this regard (postgres-owned,
mode 0600).

   regards, tom lane

 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
*****************************************************
Lee Fedden
Software Developer
CONTEXT
Tel:+44 (0)20 8394 7724
Fax:+44 (0)20 8394 7701
Email Address: lfedden@contextworld.com
Web Site: http://www.contextworld.com
*****************************************************
This message and the information contained therein is intended for the use
of the person(s) ("the intended  recipient(s)" to whom it is addressed. It
may contain information that is privileged and confidential within the
meaning of applicable law. If you are not the intended recipient, please
contact the sender as soon possible. The views expressed in this
communication may not necessarily  be the views held by Context Business
Limited or its subsidiaries. The contents of an attachment to this e-mail
may contain viruses that could damage your own computer system. While every
reasonable precaution has been taken to minimise this risk, Context Business
Limited and its subsidiaries cannot  accept liability for any damage which
you sustain  as a result of software viruses. You should carry out your own
virus checks
before opening the attachment. Please notice that Context Business Limited
monitors e-mails sent or received. Further communication will signify your
consent to this.

Context Business Limited, a Company registered in England under number
06298773. Registered Directors Jeremy Davies and Howard Davies.
Registered office address: 10-18 Putney Hill, London, SW15 6AA
VAT 919 7799 50

lfedden@contextworld.com writes:
> Even when logged in as root, a 'ls -l' command returns:

> mm2:/var/lib/pgsql82/data/base/16673 # ls -lash | more
> /bin/ls: 19357877: Permission denied
> /bin/ls: 19569529: Permission denied

You get that as root?  I think you've got filesystem problems that are
beyond the ken of us mere database weenies.  Better ask some kernel
hackers.

(If you're on a SELinux-enabled system it might have to do with SELinux
permissions, although I thought that those typically weren't enforced
against interactive shells.  It might be useful to look in the kernel
log and see if anything comes out when you do this.)

> So can we assume it is safe to remove these none accessible files and also the couple of orphaned OID files from
pg_classwe can still access? 

If it won't let you chown it, it likely won't let you rm it, either...

            regards, tom lane