Thread: BUG #12050: Orphaned base files
The following bug has been logged on the website: Bug reference: 12050 Logged by: Jamie K Email address: jkoceniak@mediamath.com PostgreSQL version: 9.1.14 Operating system: Debian GNU/Linux 7 Description: I have identified a number of tables that were dropped in the db but the files still remain under /9.1/main/base/. How do I go about verifying this is true and how do I clean this up so I can reclaim the disk space? I checked in the pg_class table but none of the objects exist. For example, # SELECT pg_relation_filepath(oid) FROM pg_class WHERE pg_relation_filepath(oid) like '%2601708994%'; pg_relation_filepath ---------------------- (0 rows) Here is an example table under /9.1/main/base/ but not in pg_class anymore: -rw------- 1 postgres postgres 1073741824 Nov 10 22:51 2601708994 -rw------- 1 postgres postgres 1073741824 Nov 10 22:51 2601708994.1 -rw------- 1 postgres postgres 1073741824 Nov 10 22:52 2601708994.10 -rw------- 1 postgres postgres 231424000 Nov 10 22:52 2601708994.11 -rw------- 1 postgres postgres 1073741824 Nov 10 22:51 2601708994.2 -rw------- 1 postgres postgres 1073741824 Nov 10 22:51 2601708994.3 -rw------- 1 postgres postgres 1073741824 Nov 10 22:52 2601708994.4 -rw------- 1 postgres postgres 1073741824 Nov 10 22:52 2601708994.5 -rw------- 1 postgres postgres 1073741824 Nov 10 22:52 2601708994.6 -rw------- 1 postgres postgres 1073741824 Nov 10 22:52 2601708994.7 -rw------- 1 postgres postgres 1073741824 Nov 10 22:52 2601708994.8 -rw------- 1 postgres postgres 1073741824 Nov 10 22:52 2601708994.9 -rw------- 1 postgres postgres 2981888 Nov 10 21:17 2601708994_fsm
jkoceniak@mediamath.com writes:
> I have identified a number of tables that were dropped in the db but the
> files still remain under /9.1/main/base/.
There are no (or at least should be no) data files directly under
$PGDATA/base --- they all live one level further down under a per-database
subdirectory. The fact that you haven't addressed that point makes me
wonder whether you are checking the right database.
regards, tom lane
Hi Tom,
We only have one database and all the orphaned files are located under:
/postgresql/9.1/main/base/31858
It looks like we have over 100G of orphaned files in the /postgresql/9.1/ma=
in/base/31858 directory.
For example, I am unable to locate 2600102740 in the master pg_class system=
table but these files exist in the data directory.=20
Are there any other system tables I should be looking at?=20
Example orphaned files:
-rw------- 1 postgres postgres 1073741824 Nov 10 14:56 2600102740
-rw------- 1 postgres postgres 1073741824 Nov 10 14:56 2600102740.1
-rw------- 1 postgres postgres 1073741824 Nov 10 15:06 2600102740.10
-rw------- 1 postgres postgres 116482048 Nov 10 15:06 2600102740.11
-rw------- 1 postgres postgres 1073741824 Nov 10 14:56 2600102740.2
-rw------- 1 postgres postgres 1073741824 Nov 10 14:56 2600102740.3
-rw------- 1 postgres postgres 1073741824 Nov 10 14:57 2600102740.4
-rw------- 1 postgres postgres 1073741824 Nov 10 14:57 2600102740.5
-rw------- 1 postgres postgres 1073741824 Nov 10 14:57 2600102740.6
-rw------- 1 postgres postgres 1073741824 Nov 10 14:57 2600102740.7
-rw------- 1 postgres postgres 1073741824 Nov 10 15:03 2600102740.8
-rw------- 1 postgres postgres 1073741824 Nov 10 15:04 2600102740.9
-rw------- 1 postgres postgres 2949120 Nov 10 13:27 2600102740_fsm
What is the process for cleaning up files in a data directory when you can'=
t locate the tables in the system tables like pg_class?
Thanks!
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Monday, November 24, 2014 7:09 PM
To: Jamie Koceniak
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #12050: Orphaned base files
jkoceniak@mediamath.com writes:
> I have identified a number of tables that were dropped in the db but=20
> the files still remain under /9.1/main/base/.
There are no (or at least should be no) data files directly under $PGDATA/b=
ase --- they all live one level further down under a per-database subdirect=
ory. The fact that you haven't addressed that point makes me wonder whethe=
r you are checking the right database.
regards, tom lane
Hi Tom,
I haven't heard anything back on this issue for some time.
Is it safe to remove the files /postgresql/9.1/main/base/31858 directory?
Thanks,
Jamie
-----Original Message-----
From: Jamie Koceniak=20
Sent: Monday, November 24, 2014 7:40 PM
To: 'Tom Lane'
Cc: pgsql-bugs@postgresql.org
Subject: RE: [BUGS] BUG #12050: Orphaned base files
Hi Tom,
We only have one database and all the orphaned files are located under:
/postgresql/9.1/main/base/31858
It looks like we have over 100G of orphaned files in the /postgresql/9.1/ma=
in/base/31858 directory.
For example, I am unable to locate 2600102740 in the master pg_class system=
table but these files exist in the data directory.=20
Are there any other system tables I should be looking at?=20
Example orphaned files:
-rw------- 1 postgres postgres 1073741824 Nov 10 14:56 2600102740
-rw------- 1 postgres postgres 1073741824 Nov 10 14:56 2600102740.1
-rw------- 1 postgres postgres 1073741824 Nov 10 15:06 2600102740.10
-rw------- 1 postgres postgres 116482048 Nov 10 15:06 2600102740.11
-rw------- 1 postgres postgres 1073741824 Nov 10 14:56 2600102740.2
-rw------- 1 postgres postgres 1073741824 Nov 10 14:56 2600102740.3
-rw------- 1 postgres postgres 1073741824 Nov 10 14:57 2600102740.4
-rw------- 1 postgres postgres 1073741824 Nov 10 14:57 2600102740.5
-rw------- 1 postgres postgres 1073741824 Nov 10 14:57 2600102740.6
-rw------- 1 postgres postgres 1073741824 Nov 10 14:57 2600102740.7
-rw------- 1 postgres postgres 1073741824 Nov 10 15:03 2600102740.8
-rw------- 1 postgres postgres 1073741824 Nov 10 15:04 2600102740.9
-rw------- 1 postgres postgres 2949120 Nov 10 13:27 2600102740_fsm
What is the process for cleaning up files in a data directory when you can'=
t locate the tables in the system tables like pg_class?
Thanks!
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, November 24, 2014 7:09 PM
To: Jamie Koceniak
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #12050: Orphaned base files
jkoceniak@mediamath.com writes:
> I have identified a number of tables that were dropped in the db but=20
> the files still remain under /9.1/main/base/.
There are no (or at least should be no) data files directly under $PGDATA/b=
ase --- they all live one level further down under a per-database subdirect=
ory. The fact that you haven't addressed that point makes me wonder whethe=
r you are checking the right database.
regards, tom lane
On Tue, Jan 20, 2015 at 11:55 AM, Jamie Koceniak <jkoceniak@mediamath.com> wrote: > Is it safe to remove the files /postgresql/9.1/main/base/31858 directory? The general rule is usually the following if you do not want to corrupt your system: do not mess up manually with those files and let the system manage it. Be sure to at least take file-level backup if you go down this dangerous road. -- Michael
Hi Michael, Thanks for the response. Before I remove those files, I had a couple more q= uestions. Does a Postgres system file map to a single pg_class record and represent = a single table in Postgres? In other words, can a PG system file share data across more than 1 table?=20 i.e PG file -> postgres postgres 1073741824 Nov 11 01:56 2602351127 Is it a correct assumption that filename 2602351127 should equal pg_relatio= n_filenode(oid) within pg_class? I know there can be 2602351127.1, 2602351127.2 files as well. 2602351127 is missing in pg_class in my case. It would be a disaster to remove the file thinking it was a table that was = dropped only to find out some other table has data in that file. What I am guessing happened here is the table was somehow dropped from syst= em tables but not the file system. Thanks!=20 -----Original Message----- From: Michael Paquier [mailto:michael.paquier@gmail.com]=20 Sent: Tuesday, January 20, 2015 6:59 PM To: Jamie Koceniak Cc: Tom Lane; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #12050: Orphaned base files On Tue, Jan 20, 2015 at 11:55 AM, Jamie Koceniak <jkoceniak@mediamath.com> = wrote: > Is it safe to remove the files /postgresql/9.1/main/base/31858 directory? The general rule is usually the following if you do not want to corrupt you= r system: do not mess up manually with those files and let the system manag= e it. Be sure to at least take file-level backup if you go down this danger= ous road. -- Michael
Hi, If I don't remove the orphaned files, is there any change that Postgres cou= ld use the same file node (oid) again? For example, say file node 2602351127 doesn't exist anywhere in pg_class, c= ould Postgres use that same file node id again? What would happen in that case? Thanks, Jamie -----Original Message----- From: Michael Paquier [mailto:michael.paquier@gmail.com]=20 Sent: Tuesday, January 20, 2015 6:59 PM To: Jamie Koceniak Cc: Tom Lane; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #12050: Orphaned base files On Tue, Jan 20, 2015 at 11:55 AM, Jamie Koceniak <jkoceniak@mediamath.com> = wrote: > Is it safe to remove the files /postgresql/9.1/main/base/31858 directory? The general rule is usually the following if you do not want to corrupt you= r system: do not mess up manually with those files and let the system manag= e it. Be sure to at least take file-level backup if you go down this danger= ous road. -- Michael
Jamie Koceniak <jkoceniak@mediamath.com> writes:
> If I don't remove the orphaned files, is there any change that Postgres could use the same file node (oid) again?
> For example, say file node 2602351127 doesn't exist anywhere in pg_class, could Postgres use that same file node id
again?
> What would happen in that case?
It will not overwrite an existing file when selecting a new relfilenode.
regards, tom lane