BUG #12050: Orphaned base files

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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/main/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.
Are there any other system tables I should be looking at?

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
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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
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/main/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.
Are there any other system tables I should be looking at?

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
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi Michael,

Thanks for the response. Before I remove those files, I had a couple more questions.
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?

i.e PG file -> postgres postgres 1073741824 Nov 11 01:56 2602351127

Is it a correct assumption that filename 2602351127 should equal pg_relation_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 system tables but not the file system.

Thanks!

-----Original Message-----
From: Michael Paquier [mailto:michael.paquier@gmail.com]
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 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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi,

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?

Thanks,
Jamie

-----Original Message-----
From: Michael Paquier [mailto:michael.paquier@gmail.com]
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 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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs