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