Thread: 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,
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;
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