PostgreSQL 8.4 Tablespace Inconsistency
Hello,
We operate a PostgreSQL 8.4.22 database on RHEL 6.10. Over the years,
we've attempted to intelligently distribute tables and indexes across
multiple tablespaces. After introducing a new tablespace, we attempted to
inventory which tables and indexes were on which tablespaces only to find
that pg_class indicated something inconsistent with our change records, as
well as with what we found on our filesystems.
As an example of the seeming inconsistency, pg_class indicated that certain
tables and indexes were on reltablespace 0, which, as we understood, refers
to pg_default, which, coincidentally is listed with pg_tablespace with oid
1663. That same pg_class object is clearly present on the filesystem for a
non-default tablespace.
As another example, pg_class lists no tables or indexes with one of our
non-default tablespace; though, that filesystem has a tablespace-like path
with many open files (lsof) listed whenever the database is running.
Our tablespaces were setup with CREATE TABLESPACE, ALTER TABLE ... SET
TABLESPACE, and ALTER INDEX ... SET TABLESPACE.
Thank you in advance for any insights and/or assistance you can provide,
Harold Falkmeyer
Harold Falkmeyer <hfalkmeyer@gmail.com> writes:
As an example of the seeming inconsistency, pg_class indicated that certain
tables and indexes were on reltablespace 0, which, as we understood, refers
to pg_default, which, coincidentally is listed with pg_tablespace with oid
1663. That same pg_class object is clearly present on the filesystem for a
non-default tablespace.
No, reltablespace = 0 means that the relation is in its database's default
tablespace; that's whatever pg_database.dattablespace says, not
necessarily pg_default. The reason for this is basically to allow a
database to be moved en-masse to another tablespace without having
to update its pg_class.
As another example, pg_class lists no tables or indexes with one of our
non-default tablespace; though, that filesystem has a tablespace-like path
with many open files (lsof) listed whenever the database is running.
Maybe those objects are in a different database of the cluster?
Another thought is to take a close look at the symlinks in
$PGDATA/pg_tblspc to verify that your tablespaces are pointing
where you think they are. Note that pg_tablespace.spclocation
is not authoritative on this; the symlinks are.
regards, tom lane
On Fri, Aug 9, 2019 at 7:15 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Harold Falkmeyer <hfalkmeyer@gmail.com> writes:
As an example of the seeming inconsistency, pg_class indicated that
certain
tables and indexes were on reltablespace 0, which, as we understood,
refers
to pg_default, which, coincidentally is listed with pg_tablespace with
oid
1663. That same pg_class object is clearly present on the filesystem
for a
non-default tablespace.
No, reltablespace = 0 means that the relation is in its database's default
tablespace; that's whatever pg_database.dattablespace says, not
necessarily pg_default. The reason for this is basically to allow a
database to be moved en-masse to another tablespace without having
to update its pg_class.
Thank you. This clarification is extremely helpful.
As such, the following SQL now seems to produce results consistent with
what we would have expected:
SELECT
n.nspname||'.'||c.relname AS _relfqn,
c.oid,
c.relfilenode,
c.relkind,
t.spcname,
case when coalesce(t.spclocation,'') != '' then t.spclocation else
current_setting('data_directory') end AS _spclocation,
pg_relation_size(c.oid) AS _size
FROM
pg_class c
LEFT JOIN pg_database d ON ( d.datname = current_database() )
LEFT JOIN pg_namespace n ON ( c.relnamespace = n.oid )
LEFT JOIN pg_tablespace t ON ( case when
coalesce(c.reltablespace,0) != 0 then c.reltablespace else d.dattablespace
end = t.oid )
ORDER BY 1 ASC;
Also, it seems that \d examinations only show the specific tablespace when
not that of d.dattablespace!?
As another example, pg_class lists no tables or indexes with one of our
non-default tablespace; though, that filesystem has a tablespace-like
path
with many open files (lsof) listed whenever the database is running.
Maybe those objects are in a different database of the cluster?
The objects were on the same cluster. We just had an inaccurate
understanding of pg_class.reltablespace and tablespace presentment with \d.
Another thought is to take a close look at the symlinks in
$PGDATA/pg_tblspc to verify that your tablespaces are pointing
where you think they are. Note that pg_tablespace.spclocation
is not authoritative on this; the symlinks are.
Another great point. We actually had done this and found that spclocation
was consistent with the symbolic links $PGDATA/pg_tblspc.
regards, tom lane
Thank you very much for your reply!
Appreciatively,
Harold