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
Date: Mon, 16 Jul 2018 11:18:10 -0400
Message-ID: <22179.1531754290@sss.pgh.pa.us> (raw)
In-Reply-To: <33986830-A802-43DD-AABB-6035B37251EC@gmail.com>
References: <33986830-A802-43DD-AABB-6035B37251EC@gmail.com>
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 under 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 = 123456. It does 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) = 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 your 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, rahul.blooming@gmail.com
Subject: Re: [Admin] Deleting Orphan Relfilenode
In-Reply-To: <22179.1531754290@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox