Thread: ERROR: tablespace "archive2" is not empty
Hi,
I've run into an odd problem - I have what seems to be a "zombie" tablespace
that PostgreSQL won't let me drop, but nothing inside it is active.
% sudo -H -u postgres psql template1 -c "drop tablespace archive2;"
ERROR: tablespace "archive2" is not empty
% sudo find /media/ssd/archive2/ -type f -ls
36962439 393940 -rw------- 1 postgres postgres 403390464 Jun 27 2014
/media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877054
36962444 1048580 -rw------- 1 postgres postgres 1073741824 Jun 27 2014
/media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056.2
36962442 1048580 -rw------- 1 postgres postgres 1073741824 Jun 27 2014
/media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056
36962446 808 -rw------- 1 postgres postgres 827392 Jun 27 2014
/media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056_fsm
36962441 8 -rw------- 1 postgres postgres 8192 Jun 27 2014
/media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877054_vm
36962445 282384 -rw------- 1 postgres postgres 289161216 Jun 27 2014
/media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056.3
36962440 120 -rw------- 1 postgres postgres 122880 Jun 27 2014
/media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877054_fsm
36962443 1048580 -rw------- 1 postgres postgres 1073741824 Jun 27 2014
/media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056.1
% sudo -H -u postgres psql template1 -c "SELECT datname FROM pg_database WHERE oid = 117264;"
datname
----------
mydb
(1 row)
% sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054,
7877056);"
oid | relname | relkind
-----+---------+---------
(0 rows)
% sudo -H -u postgres psql mydb -c "SELECT t.relname AS catalog, d.objid AS oid_dependent FROM pg_catalog.pg_class t
JOINpg_catalog.pg_depend d ON (t.oid = d.classid) WHERE refobjid in (7877054, 7877056);"
catalog | oid_dependent
---------+---------------
(0 rows)
% sudo -H -u postgres psql mydb -c "SELECT oid, datname FROM pg_database WHERE dattablespace = (SELECT oid FROM
pg_tablespaceWHERE spcname = 'archive2');"
oid | datname
-----+---------
(0 rows)
% sudo -H -u postgres psql giscloud -c "SELECT pg_tablespace_databases((SELECT oid FROM pg_tablespace WHERE spcname =
'archive2'));"
pg_tablespace_databases
-------------------------
117264
(1 row)
So there are no actual references to these files, yet the relationship
persists sufficiently for the DROP TABLESPACE to error out.
I've reviewed what seems to be a similar example from 2008 on this list:
http://grokbase.com/t/postgresql/pgsql-general/086g1yrpbq/error-when-trying-to-drop-a-tablespace
but there was no ultimate solution then. I'm hoping there would be now :)
Is it safe if I get rid of it by setting up a slave server to this machine,
but then shut that postmaster down and manually remove the contents of this
directory, bring it back up, let it get back in sync again, and then promote
the slave to become the master, with all the accompanying arrangements on
the side?
TIA.
--
2. That which causes joy or happiness.
Hi, On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054, 7877056);" > oid | relname | relkind > -----+---------+--------- > (0 rows) That's the wrong query. The files on disk are relefilenodes not oids. Try WHERE pg_relation_filenode(oid) IN ... Greetings, Andres Freund
On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote:
> Hi,
>
> On 2015-10-19 10:49:11 +0200, Josip Rodin wrote:
> > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054,
7877056);"
> > oid | relname | relkind
> > -----+---------+---------
> > (0 rows)
>
> That's the wrong query. The files on disk are relefilenodes not
> oids. Try WHERE pg_relation_filenode(oid) IN ...
Oh, okay, but still:
% sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);"
pg_relation_filenode
----------------------
(1 row)
% sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877056);"
pg_relation_filenode
----------------------
(1 row)
--
2. That which causes joy or happiness.
On 10/19/15 4:14 AM, Josip Rodin wrote: > On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: >> Hi, >> >> On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: >>> % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054, 7877056);" >>> oid | relname | relkind >>> -----+---------+--------- >>> (0 rows) >> >> That's the wrong query. The files on disk are relefilenodes not >> oids. Try WHERE pg_relation_filenode(oid) IN ... > > Oh, okay, but still: > > % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);" > pg_relation_filenode > ---------------------- > > (1 row) > > % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877056);" > pg_relation_filenode > ---------------------- pg_relation_filenode accepts the OID of a table. For what you're trying to do you'd need pg_relation_filenode(tablespace oid, relfilenode). -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
On 2015-10-19 11:14:33 +0200, Josip Rodin wrote: > On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: > > Hi, > > > > On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > > > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054, 7877056);" > > > oid | relname | relkind > > > -----+---------+--------- > > > (0 rows) > > > > That's the wrong query. The files on disk are relefilenodes not > > oids. Try WHERE pg_relation_filenode(oid) IN ... > > Oh, okay, but still: > > % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);" > pg_relation_filenode > ---------------------- > > (1 row) Please actually run the query I suggested above. pg_relation_filenode(oid) returns the relfilenode of the table with the passed in oid - which you then compare with the relfilenode you saw on disk. Greetings, Andres Freund
On Mon, Oct 19, 2015 at 04:39:55AM -0500, Jim Nasby wrote:
> On 10/19/15 4:14 AM, Josip Rodin wrote:
> >On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote:
> >>Hi,
> >>
> >>On 2015-10-19 10:49:11 +0200, Josip Rodin wrote:
> >>>% sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054,
7877056);"
> >>> oid | relname | relkind
> >>>-----+---------+---------
> >>>(0 rows)
> >>
> >>That's the wrong query. The files on disk are relefilenodes not
> >>oids. Try WHERE pg_relation_filenode(oid) IN ...
> >
> >Oh, okay, but still:
> >
> >% sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);"
> > pg_relation_filenode
> >----------------------
> >
> >(1 row)
> >
> >% sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877056);"
> > pg_relation_filenode
> >----------------------
>
> pg_relation_filenode accepts the OID of a table. For what you're
> trying to do you'd need pg_relation_filenode(tablespace oid,
> relfilenode).
That function, with two integer parameters, does not exist on this
PostgreSQL (9.1).
--
2. That which causes joy or happiness.
On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote:
> On 2015-10-19 11:14:33 +0200, Josip Rodin wrote:
> > On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote:
> > > Hi,
> > >
> > > On 2015-10-19 10:49:11 +0200, Josip Rodin wrote:
> > > > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN
(7877054,7877056);"
> > > > oid | relname | relkind
> > > > -----+---------+---------
> > > > (0 rows)
> > >
> > > That's the wrong query. The files on disk are relefilenodes not
> > > oids. Try WHERE pg_relation_filenode(oid) IN ...
> >
> > Oh, okay, but still:
> >
> > % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);"
> > pg_relation_filenode
> > ----------------------
> >
> > (1 row)
>
> Please actually run the query I suggested
> above. pg_relation_filenode(oid) returns the relfilenode of the table
> with the passed in oid - which you then compare with the relfilenode you
> saw on disk.
Oh, sorry, but yet again, there's just nothing there:
% sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class WHERE
pg_relation_filenode(oid)IN (7877054, 7877056);"
oid | relname | relkind
-----+---------+---------
(0 rows)
--
2. That which causes joy or happiness.
On 10/19/2015 03:18 AM, Josip Rodin wrote: > On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote: >> On 2015-10-19 11:14:33 +0200, Josip Rodin wrote: >>> On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: >>>> Hi, >>>> >>>> On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: >>>>> % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054, 7877056);" >>>>> oid | relname | relkind >>>>> -----+---------+--------- >>>>> (0 rows) >>>> >>>> That's the wrong query. The files on disk are relefilenodes not >>>> oids. Try WHERE pg_relation_filenode(oid) IN ... >>> >>> Oh, okay, but still: >>> >>> % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);" >>> pg_relation_filenode >>> ---------------------- >>> >>> (1 row) >> >> Please actually run the query I suggested >> above. pg_relation_filenode(oid) returns the relfilenode of the table >> with the passed in oid - which you then compare with the relfilenode you >> saw on disk. > > Oh, sorry, but yet again, there's just nothing there: > > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class WHERE pg_relation_filenode(oid)IN (7877054, 7877056);" > oid | relname | relkind > -----+---------+--------- > (0 rows) > What happens if you do?: select oid, * from pg_tablespace ; select * from pg_class where reltablespace = <archive2 oid>; -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote:
> What happens if you do?:
>
> select oid, * from pg_tablespace ;
mydb=> select oid, * from pg_tablespace where spcname = 'archive2';
oid | spcname | spcowner | spclocation | spcacl | spcoptions
---------+----------+----------+----------------------+--------+------------
7849107 | archive2 | 10 | /media/archive2/psql | |
(1 row)
> select * from pg_class where reltablespace = <archive2 oid>;
mydb=> select * from pg_class where reltablespace = 7849107;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples |
reltoastrelid| reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids
|relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------
(0 rows)
It's there, but it's not there-there :)
--
2. That which causes joy or happiness.
On 10/19/2015 07:56 AM, Josip Rodin wrote: > On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: >> What happens if you do?: >> >> select oid, * from pg_tablespace ; > > mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; > oid | spcname | spcowner | spclocation | spcacl | spcoptions > ---------+----------+----------+----------------------+--------+------------ > 7849107 | archive2 | 10 | /media/archive2/psql | | > (1 row) > >> select * from pg_class where reltablespace = <archive2 oid>; > > mydb=> select * from pg_class where reltablespace = 7849107; > relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples| reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks |relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions > ---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------ > (0 rows) > > It's there, but it's not there-there :) > I think the important part is that: /media/archive2/psql is not the same as: /media/ssd/archive2/postgresql from your first post. -- Adrian Klaver adrian.klaver@aklaver.com
On 10/19/2015 07:56 AM, Josip Rodin wrote: > On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: >> What happens if you do?: >> >> select oid, * from pg_tablespace ; > > mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; > oid | spcname | spcowner | spclocation | spcacl | spcoptions > ---------+----------+----------+----------------------+--------+------------ > 7849107 | archive2 | 10 | /media/archive2/psql | | > (1 row) > >> select * from pg_class where reltablespace = <archive2 oid>; > > mydb=> select * from pg_class where reltablespace = 7849107; > relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples| reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks |relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions > ---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------ > (0 rows) > > It's there, but it's not there-there :) > Should have added. What does a listing of pg_tblspc/ in your $PGDATA directory show? -- Adrian Klaver adrian.klaver@aklaver.com
Josip Rodin <joy+pgsql@entuzijast.net> writes:
> On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote:
>>>> That's the wrong query. The files on disk are relefilenodes not
>>>> oids. Try WHERE pg_relation_filenode(oid) IN ...
> Oh, sorry, but yet again, there's just nothing there:
> % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class WHERE
pg_relation_filenode(oid)IN (7877054, 7877056);"
> oid | relname | relkind
> -----+---------+---------
> (0 rows)
Seeing that those files are all of similar date, I wonder if they are
tables that got orphaned in a crash, ie, the pg_class rows were removed
but the backend crashed before physically unlinking the files.
Anyway, if you've satisfied yourself that there are no pg_class entries
for these files, you could just manually remove the files.
I concur with Adrian's nearby suggestion of checking for rows with
reltablespace matching the tablespace's OID before you do anything
drastic, though.
regards, tom lane
On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote:
> On 10/19/2015 07:56 AM, Josip Rodin wrote:
> >On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote:
> >>What happens if you do?:
> >>
> >>select oid, * from pg_tablespace ;
> >
> >mydb=> select oid, * from pg_tablespace where spcname = 'archive2';
> > oid | spcname | spcowner | spclocation | spcacl | spcoptions
> >---------+----------+----------+----------------------+--------+------------
> > 7849107 | archive2 | 10 | /media/archive2/psql | |
> >(1 row)
> >
> >>select * from pg_class where reltablespace = <archive2 oid>;
> >
> >mydb=> select * from pg_class where reltablespace = 7849107;
> > relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages |
reltuples| reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks
|relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions
>
>---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------
> >(0 rows)
> >
> >It's there, but it's not there-there :)
> >
>
> Should have added.
>
> What does a listing of pg_tblspc/ in your $PGDATA directory show?
Ah, I forgot to mention. 7849107 is symlinked to /media/archive2/postgresql,
so the symlink is referencing a real directory, and spclocation is broken
because that doesn't exist. But that sounds like an -ENOENT and not
-ENOPERM, no?
--
2. That which causes joy or happiness.
Josip Rodin <joy+pgsql@entuzijast.net> writes:
> Ah, I forgot to mention. 7849107 is symlinked to /media/archive2/postgresql,
> so the symlink is referencing a real directory, and spclocation is broken
> because that doesn't exist. But that sounds like an -ENOENT and not
> -ENOPERM, no?
spclocation doesn't get updated if you change the symlink. That's
okay, more or less, because it's never actually used for anything.
We got rid of that column in later versions.
regards, tom lane
On Mon, Oct 19, 2015 at 08:24:10AM -0700, Tom Lane wrote:
> Josip Rodin <joy+pgsql@entuzijast.net> writes:
> > On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote:
> >>>> That's the wrong query. The files on disk are relefilenodes not
> >>>> oids. Try WHERE pg_relation_filenode(oid) IN ...
>
> > Oh, sorry, but yet again, there's just nothing there:
>
> > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class WHERE
pg_relation_filenode(oid)IN (7877054, 7877056);"
> > oid | relname | relkind
> > -----+---------+---------
> > (0 rows)
>
> Seeing that those files are all of similar date, I wonder if they are
> tables that got orphaned in a crash, ie, the pg_class rows were removed
> but the backend crashed before physically unlinking the files.
>
> Anyway, if you've satisfied yourself that there are no pg_class entries
> for these files, you could just manually remove the files.
>
> I concur with Adrian's nearby suggestion of checking for rows with
> reltablespace matching the tablespace's OID before you do anything
> drastic, though.
OK, so given that that's all missing, too, I'm in the clear, then?
How likely would it be for the server to, at some point in the future, to
somehow deduce a new reference to that tablespace and do a FATAL when it
finds nothing there?
BTW how safe is the standby/failover idea in general? Changing pg_tblspc
across promotions, so to speak.
--
2. That which causes joy or happiness.
On 10/19/2015 08:28 AM, Josip Rodin wrote: > On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote: >> On 10/19/2015 07:56 AM, Josip Rodin wrote: >>> On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: >>>> What happens if you do?: >>>> >>>> select oid, * from pg_tablespace ; >>> >>> mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; >>> oid | spcname | spcowner | spclocation | spcacl | spcoptions >>> ---------+----------+----------+----------------------+--------+------------ >>> 7849107 | archive2 | 10 | /media/archive2/psql | | >>> (1 row) >>> >>>> select * from pg_class where reltablespace = <archive2 oid>; >>> >>> mydb=> select * from pg_class where reltablespace = 7849107; >>> relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples| reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks |relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions >>> ---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------ >>> (0 rows) >>> >>> It's there, but it's not there-there :) >>> >> >> Should have added. >> >> What does a listing of pg_tblspc/ in your $PGDATA directory show? > > Ah, I forgot to mention. 7849107 is symlinked to /media/archive2/postgresql, Except what you showed in you first post was: /media/ssd/archive2/postgresql Note the /media/ssd/* > so the symlink is referencing a real directory, and spclocation is broken > because that doesn't exist. But that sounds like an -ENOENT and not > -ENOPERM, no? > -- Adrian Klaver adrian.klaver@aklaver.com
On 10/19/2015 10:26 AM, Josip Rodin wrote: > > > On 19. listopada 2015. 17:46:19 CEST, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> On 10/19/2015 08:28 AM, Josip Rodin wrote: >>> On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote: >>>> On 10/19/2015 07:56 AM, Josip Rodin wrote: >>>>> On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: >>>>>> What happens if you do?: >>>>>> >>>>>> select oid, * from pg_tablespace ; >>>>> >>>>> mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; >>>>> oid | spcname | spcowner | spclocation | spcacl | >> spcoptions >>>>> >> ---------+----------+----------+----------------------+--------+------------ >>>>> 7849107 | archive2 | 10 | /media/archive2/psql | | >>>>> (1 row) >>>>> >>>>>> select * from pg_class where reltablespace = <archive2 oid>; >>>>> >>>>> mydb=> select * from pg_class where reltablespace = 7849107; >>>>> relname | relnamespace | reltype | reloftype | relowner | relam | >> relfilenode | reltablespace | relpages | reltuples | reltoastrelid | >> reltoastidxid | relhasindex | relisshared | relpersistence | relkind | >> relnatts | relchecks | relhasoids | relhaspkey | relhasrules | >> relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions >>>>> >> ---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------ >>>>> (0 rows) >>>>> >>>>> It's there, but it's not there-there :) >>>>> >>>> >>>> Should have added. >>>> >>>> What does a listing of pg_tblspc/ in your $PGDATA directory show? >>> >>> Ah, I forgot to mention. 7849107 is symlinked to >> /media/archive2/postgresql, >> >> Except what you showed in you first post was: >> >> /media/ssd/archive2/postgresql >> >> Note the /media/ssd/* > > Sorry, that's another typo. It's the latter. The symlink is indeed pointing to the 5 GB of leftovers. This: % sudo find /media/ssd/archive2/ -type f -ls 36962439 393940 -rw------- 1 postgres postgres 403390464 Jun 27 2014 /media/ssd/archive2 is a typo or is there another symlink involved? So, is there anything at /media/archive2/psql? Or more to the point how did spclocation and the symlink get to be different? > >>> so the symlink is referencing a real directory, and spclocation is >> broken >>> because that doesn't exist. But that sounds like an -ENOENT and not >>> -ENOPERM, no? >>> > -- Adrian Klaver adrian.klaver@aklaver.com
On 10/19/2015 11:25 AM, Josip Rodin wrote: > On Mon, Oct 19, 2015 at 11:15:08AM -0700, Adrian Klaver wrote: >>> Sorry, that's another typo. It's the latter. The symlink is indeed pointing to the 5 GB of leftovers. >> >> This: >> >> % sudo find /media/ssd/archive2/ -type f -ls >> 36962439 393940 -rw------- 1 postgres postgres 403390464 Jun 27 >> 2014 /media/ssd/archive2 >> >> is a typo or is there another symlink involved? >> >> So, is there anything at /media/archive2/psql? > > This time I'll just paste command output to make sure it's verbatim :) > > % sudo ls -la /media/ssd/postgresql/9.1/main/pg_tblspc/7849107 > lrwxrwxrwx 1 postgres postgres 30 Sep 30 2014 /media/ssd/postgresql/9.1/main/pg_tblspc/7849107 -> /media/ssd/archive2/postgresql > > % sudo ls -la $(sudo readlink -f /media/ssd/postgresql/9.1/main/pg_tblspc/7849107) > total 12 > drwx------ 3 postgres root 4096 Jun 25 2014 . > drwxr-xr-x 3 root root 4096 Sep 30 2014 .. > drwx------ 3 postgres postgres 4096 Jun 25 2014 PG_9.1_201105231 > > % sudo ls -la /media/archive2/psql > ls: cannot access /media/archive2/psql: No such file or directory > >> Or more to the point how did spclocation and the symlink get to be >> different? > > No idea, I inherited this machine. But like Tom said, spclocation being > wrong is apparently harmless. Other then you cannot DROP the tablespace:) This will probably needed to be resolved for the reasons that came up in this thread: http://www.postgresql.org/message-id/CAAW2xfeA1mPw4RW3-4LUTW+y4ruQFPT5Bx4c3DKUb01f4ejabw@mail.gmail.com it is a very long thread so you might to take a look at this: http://www.postgresql.org/message-id/7681.1389548920@sss.pgh.pa.us What happens if you change spclocation to the correct location? > -- Adrian Klaver adrian.klaver@aklaver.com
On 19. listopada 2015. 17:46:19 CEST, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >On 10/19/2015 08:28 AM, Josip Rodin wrote: >> On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote: >>> On 10/19/2015 07:56 AM, Josip Rodin wrote: >>>> On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: >>>>> What happens if you do?: >>>>> >>>>> select oid, * from pg_tablespace ; >>>> >>>> mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; >>>> oid | spcname | spcowner | spclocation | spcacl | >spcoptions >>>> >---------+----------+----------+----------------------+--------+------------ >>>> 7849107 | archive2 | 10 | /media/archive2/psql | | >>>> (1 row) >>>> >>>>> select * from pg_class where reltablespace = <archive2 oid>; >>>> >>>> mydb=> select * from pg_class where reltablespace = 7849107; >>>> relname | relnamespace | reltype | reloftype | relowner | relam | >relfilenode | reltablespace | relpages | reltuples | reltoastrelid | >reltoastidxid | relhasindex | relisshared | relpersistence | relkind | >relnatts | relchecks | relhasoids | relhaspkey | relhasrules | >relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions >>>> >---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------ >>>> (0 rows) >>>> >>>> It's there, but it's not there-there :) >>>> >>> >>> Should have added. >>> >>> What does a listing of pg_tblspc/ in your $PGDATA directory show? >> >> Ah, I forgot to mention. 7849107 is symlinked to >/media/archive2/postgresql, > >Except what you showed in you first post was: > >/media/ssd/archive2/postgresql > >Note the /media/ssd/* Sorry, that's another typo. It's the latter. The symlink is indeed pointing to the 5 GB of leftovers. >> so the symlink is referencing a real directory, and spclocation is >broken >> because that doesn't exist. But that sounds like an -ENOENT and not >> -ENOPERM, no? >>
On Mon, Oct 19, 2015 at 11:15:08AM -0700, Adrian Klaver wrote:
> >Sorry, that's another typo. It's the latter. The symlink is indeed pointing to the 5 GB of leftovers.
>
> This:
>
> % sudo find /media/ssd/archive2/ -type f -ls
> 36962439 393940 -rw------- 1 postgres postgres 403390464 Jun 27
> 2014 /media/ssd/archive2
>
> is a typo or is there another symlink involved?
>
> So, is there anything at /media/archive2/psql?
This time I'll just paste command output to make sure it's verbatim :)
% sudo ls -la /media/ssd/postgresql/9.1/main/pg_tblspc/7849107
lrwxrwxrwx 1 postgres postgres 30 Sep 30 2014 /media/ssd/postgresql/9.1/main/pg_tblspc/7849107 ->
/media/ssd/archive2/postgresql
% sudo ls -la $(sudo readlink -f /media/ssd/postgresql/9.1/main/pg_tblspc/7849107)
total 12
drwx------ 3 postgres root 4096 Jun 25 2014 .
drwxr-xr-x 3 root root 4096 Sep 30 2014 ..
drwx------ 3 postgres postgres 4096 Jun 25 2014 PG_9.1_201105231
% sudo ls -la /media/archive2/psql
ls: cannot access /media/archive2/psql: No such file or directory
> Or more to the point how did spclocation and the symlink get to be
> different?
No idea, I inherited this machine. But like Tom said, spclocation being
wrong is apparently harmless.
--
2. That which causes joy or happiness.
On 10/20/2015 12:22 AM, Josip Rodin wrote: > On Mon, Oct 19, 2015 at 12:02:17PM -0700, Adrian Klaver wrote: >>> No idea, I inherited this machine. But like Tom said, spclocation being >>> wrong is apparently harmless. >> >> Other then you cannot DROP the tablespace:) This will probably >> needed to be resolved for the reasons that came up in this thread: >> >> http://www.postgresql.org/message-id/CAAW2xfeA1mPw4RW3-4LUTW+y4ruQFPT5Bx4c3DKUb01f4ejabw@mail.gmail.com >> >> it is a very long thread so you might to take a look at this: >> >> http://www.postgresql.org/message-id/7681.1389548920@sss.pgh.pa.us >> >> >> What happens if you change spclocation to the correct location? > > The suggestion makes perfect sense, but sadly there's no difference: > > % sudo -H -u postgres psql template1 -c "update pg_tablespace set spclocation = '/media/ssd/archive2/postgresql' wherespcname = 'archive2';" > UPDATE 1 > % sudo -H -u postgres psql template1 -c "drop tablespace archive2;" > ERROR: tablespace "archive2" is not empty > > I guess UTSL would be my remaining recourse, but I was hoping someone > who already knows that code path offhand could put me at ease :) This database(cluster) has been running without any issues related to not finding archive2 tablespace objects, correct? The following found nothing in pg_class that is using the tablespace: mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; oid | spcname | spcowner | spclocation | spcacl | spcoptions ---------+----------+----------+----------------------+--------+------------ 7849107 | archive2 | 10 | /media/archive2/psql | | mydb=> select * from pg_class where reltablespace = 7849107; (0 rows) It seems at a practical level the tablespace does not exist from the view point of the database. Assuming you have had no tablespace issues to date, I would be inclined to go the manual route. As always backup before proceeding. By manual, I mean mv the files in: /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/ to somewhere else, then try to DROP the tablespace. > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Oct 19, 2015 at 12:02:17PM -0700, Adrian Klaver wrote: > >No idea, I inherited this machine. But like Tom said, spclocation being > >wrong is apparently harmless. > > Other then you cannot DROP the tablespace:) This will probably > needed to be resolved for the reasons that came up in this thread: > > http://www.postgresql.org/message-id/CAAW2xfeA1mPw4RW3-4LUTW+y4ruQFPT5Bx4c3DKUb01f4ejabw@mail.gmail.com > > it is a very long thread so you might to take a look at this: > > http://www.postgresql.org/message-id/7681.1389548920@sss.pgh.pa.us > > > What happens if you change spclocation to the correct location? The suggestion makes perfect sense, but sadly there's no difference: % sudo -H -u postgres psql template1 -c "update pg_tablespace set spclocation = '/media/ssd/archive2/postgresql' where spcname= 'archive2';" UPDATE 1 % sudo -H -u postgres psql template1 -c "drop tablespace archive2;" ERROR: tablespace "archive2" is not empty I guess UTSL would be my remaining recourse, but I was hoping someone who already knows that code path offhand could put me at ease :) -- 2. That which causes joy or happiness.