Thread: BUG #6267: Wrong results in pg_database_size

The following bug has been logged online:

Bug reference:      6267
Logged by:          Maksym Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.0.4
Operating system:   Linux 2.6.32-5
Description:        Wrong results in pg_database_size
Details:

Somehow pg_database_size producing results which are 2х more then reality.

Here is details:
postgres=# SELECT
oid,datname,dattablespace,pg_size_pretty(pg_database_size(datname)) from
pg_database where datname IN  ('plus1', 'stinger_3');
  oid  |  datname  | dattablespace | pg_size_pretty
-------+-----------+---------------+----------------
 16453 | plus1     |         16443 | 103 GB
 16458 | stinger_3 |         16443 | 114 GB


In reality they are 2x smaller:
postgres@shalyapin:/home/mboguk$ du --max-depth=1 -h
/db/base/main/PG_9.0_201008051/16453
52G     /db/base/main/PG_9.0_201008051/16453
postgres@shalyapin:/home/mboguk$ du --max-depth=1 -h
/db/base/main/PG_9.0_201008051/16458
58G     /db/base/main/PG_9.0_201008051/16458

pg_tablespace_size producing correct results
postgres=# SELECT
oid,spcname,spclocation,pg_size_pretty(pg_tablespace_size(spcname))  from
pg_tablespace;
  oid  |  spcname   |  spclocation  | pg_size_pretty
-------+------------+---------------+----------------
  1663 | pg_default |               | 21 MB
  1664 | pg_global  |               | 1092 kB
 16443 | main       | /db/base/main | 124 GB


May be database located outside of the pg_default get his size calculated
twice (now sure how).

All other databases on that cluster have same wronge results about database
size including template0/template1 databases.

"Maksym Boguk" <maxim.boguk@gmail.com> writes:
> Somehow pg_database_size producing results which are 2х more then reality.

I can't reproduce any such problem here.  I suspect you overlooked some
tablespaces in your manual "du" commands.

            regards, tom lane

> "Maksym Boguk" <maxim.boguk@gmail.com> writes:
>> Somehow pg_database_size producing results which are 2=D1=85 more then r=
eality.
>
> I can't reproduce any such problem here. =C2=A0I suspect you overlooked s=
ome
> tablespaces in your manual "du" commands.
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane
>

No other tablespaces exists as can be seen in output of:
SELECT oid,spcname,spclocation,pg_size_pretty(pg_tablespace_size(spcname)) =
 from
pg_tablespace;
  oid  |  spcname   |  spclocation  | pg_size_pretty
-------+------------+---------------+----------------
  1663 | pg_default |               | 21 MB
  1664 | pg_global  |               | 1092 kB
 16443 | main       | /db/base/main | 124 GB

However I seem to have found reason for such strange behavior.

That is a duplicating link in pg_tblspc:
postgres@db:~$ ls -la /var/lib/postgresql/9.0/main/pg_tblspc/
total 8
drwx------  2 postgres postgres 4096 Jun  3 04:08 .
drwx------ 12 postgres postgres 4096 Sep 28 17:08 ..
lrwxrwxrwx  1 postgres postgres   13 Jun  6 14:39 16384 -> /db/base/main
lrwxrwxrwx  1 postgres postgres   13 Jun  3 04:08 16443 -> /db/base/main

I have no idea how could this happen.
No crashes/power-offs and so on happens on that database for more then year.

Can the second (16384 -> /db/base/main) link be safely deleted?

--=20
Maxim Boguk
Senior Postgresql DBA.

Maxim Boguk <maxim.boguk@gmail.com> writes:
>>> No other tablespaces exists as can be seen in output of:
>>> SELECT oid,spcname,spclocation,pg_size_pretty(pg_tablespace_size(spcname))  from
>>> pg_tablespace;
>>>   oid  |  spcname   |  spclocation  | pg_size_pretty
>>> -------+------------+---------------+----------------
>>>   1663 | pg_default |               | 21 MB
>>>   1664 | pg_global  |               | 1092 kB
>>>  16443 | main       | /db/base/main | 124 GB

> However I seem to have found reason for such strange behavior.

> That is a duplicating link in pg_tblspc:
> postgres@db:~$ ls -la /var/lib/postgresql/9.0/main/pg_tblspc/
> total 8
> drwx------  2 postgres postgres 4096 Jun  3 04:08 .
> drwx------ 12 postgres postgres 4096 Sep 28 17:08 ..
> lrwxrwxrwx  1 postgres postgres   13 Jun  6 14:39 16384 -> /db/base/main
> lrwxrwxrwx  1 postgres postgres   13 Jun  3 04:08 16443 -> /db/base/main

> I have no idea how could this happen.
> No crashes/power-offs and so on happens on that database for more then year.

That is bizarre.  DROP TABLESPACE should certainly have removed the
symlink, so creating and dropping wouldn't explain this.

The other really interesting thing here is that the second symlink,
which must have been created later to judge by its OID, has an earlier
filesystem timestamp.  AFAIK, the timestamps on symlinks never change
after creation.  What I am suspicious of is that the 16384 symlink got
"restored" by some filesystem-level backup/restore action.

FWIW, 16384 is the first possible user-assigned OID, so assuming that
that was real at all, the CREATE TABLESPACE for it would've had to be
the very first user action in this cluster.

What I surmise is that you created a tablespace (with OID 16384), and
some time later you thought better of that and dropped and recreated it
(now with OID 16443, so this wasn't all that long after initdb either),
and then about three days after that, something took it upon itself to
re-create the 16384 symlink.  The only part of Postgres that could
conceivably recreate a previously existing symlink is WAL replay, but
if you've not had any database crashes then a theory involving WAL
replay seems to be foreclosed.  Anyway it's hard to believe that a crash
would replay events as old as three days.

> Can the second (16384 -> /db/base/main) link be safely deleted?

If there's no matching entry in pg_tablespace then it should be junk.
But you might want to check for pg_class entries with reltablespace =
16384 before pulling the trigger.

            regards, tom lane