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 JOIN pg_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_tablespace WHERE 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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/archive2is 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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/archive2is 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 directoryOr 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:
/messages/by-id/CAAW2xfeA1mPw4RW3-4LUTW+y4ruQFPT5Bx4c3DKUb01f4ejabw@mail.gmail.com
it is a very long thread so you might to take a look at this:
/messages/by-id/7681.1389548920@sss.pgh.pa.us
What happens if you change spclocation to the correct location?
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general