From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: size of table + toasted tables + indexes != pg_total_relation_size
Date: Sun, 04 Nov 2018 16:47:06 +0000
Message-ID: <87d0rk3itx.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <CA+t6e1nF76vnRO9YV2m97-1bSKj0QnivXQWbXAT5xBaggjaDug@mail.gmail.com>
References: <CA+t6e1mtdVct+Cn=qs=q=LLL_yKSssO6dxiZk+b16xq4ccvWvw@mail.gmail.com>
<87pnvl2gki.fsf@news-spur.riddles.org.uk>
<CA+t6e1nF76vnRO9YV2m97-1bSKj0QnivXQWbXAT5xBaggjaDug@mail.gmail.com>
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
>> You should probably check whether there are files in the db's
>> directory that do not correspond to the filenode of any table (use
>> select pg_relation_filenode(oid) from pg_class; to get a list of
>> filenodes)
Mariel> * and If i'll find some, is it safe just to delete them ?*
You need to check that they're not recent (i.e. could not belong to
table creations/rewrites that are still in progress, since those would
not be visible in pg_class).
Also make sure you're looking in the right database (using
pg_relation_filepath instead may make this clearer).
Remember that a given filenode/filepath also generates names with
suffixes for forks (e.g. _vm) and segments (.1, .2 etc) - those need to
be kept for valid filenodes and deleted along with orphaned ones.
Naturally, if you get this wrong you will break your database beyond
easy recovery, so BE CAREFUL. As an extra check, you could look at the
times of the files and check against your server logs to see if there
were indeed any crashes or unclean restarts of pg around those times; if
you have a lot of apparently orphaned files you should investigate why.
--
Andrew (irc:RhodiumToad)
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: andrew@tao11.riddles.org.uk, mariel.cherkassky@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: size of table + toasted tables + indexes != pg_total_relation_size
In-Reply-To: <87d0rk3itx.fsf@news-spur.riddles.org.uk>
* 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