From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rahul Saha <rahul.blooming@gmail.com>
cc: "pgsql-admin@postgresql.org pgsql-admin@postgresql.org"
 <pgsql-admin@postgresql.org>
Subject: Re: [Admin] Deleting Orphan Relfilenode
In-reply-to: <33986830-A802-43DD-AABB-6035B37251EC@gmail.com>
References: <33986830-A802-43DD-AABB-6035B37251EC@gmail.com>
Comments: In-reply-to Rahul Saha <rahul.blooming@gmail.com>
	message dated "Mon, 16 Jul 2018 17:40:55 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <22178.1531754290.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 16 Jul 2018 11:18:10 -0400
Message-ID: <22179.1531754290@sss.pgh.pa.us>
Precedence: bulk

Rahul Saha <rahul.blooming@gmail.com> writes:
> We have a scenario where we ran Vacuum Full (2 TB Table)but due to some =
reason Vacuum Full failed. =

> These have let almost 1 TB orphan Relfilenodes inside base directory und=
er the concern database which does not map with pg_class. =


Hm, by "fail" do you mean a database crash?  Ordinary errors should have
rolled back the file creation successfully.

> For example - Select * from pg_class where relfilenode =3D 123456. It do=
es not return any row but those relfilenode exist inside base. =


I would not trust that query if I were you --- it will not find mapped
system catalogs.  Maybe you are certain that this orphaned file wasn't
for a system catalog, but I'd still recommend using

select * from pg_class where pg_relation_filenode(oid) =3D 123456;

to be sure.  (See also contrib/oid2name.)

> It would be great if you can suggest how to remove those orphan files. I=
 know definitely manually deleting is not suggested so thought to know you=
r suggestions. =


If they're really orphaned, as proven by a pg_relation_filenode() check,
then manual "rm" is the thing to do.  Postgres doesn't know they are
there and will not remove them.

			regards, tom lane