Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-20 Thread Josip Rodin
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

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-20 Thread Adrian Klaver
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

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Andres Freund
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

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Josip Rodin
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 > >

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Josip Rodin
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

[GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Josip Rodin
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/

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Josip Rodin
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,

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Jim Nasby
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 |

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Andres Freund
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

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Adrian Klaver
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

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Josip Rodin
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

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Adrian Klaver
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 |

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Josip Rodin
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

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Adrian Klaver
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 |

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Tom Lane
Josip Rodin 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: > %

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Adrian Klaver
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,

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Tom Lane
Josip Rodin 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?

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Josip Rodin
On Mon, Oct 19, 2015 at 08:24:10AM -0700, Tom Lane wrote: > Josip Rodin 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

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Josip Rodin
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

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Josip Rodin
On 19. listopada 2015. 17:46:19 CEST, Adrian Klaver 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,

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Adrian Klaver
On 10/19/2015 10:26 AM, Josip Rodin wrote: On 19. listopada 2015. 17:46:19 CEST, Adrian Klaver 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,

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

2015-10-19 Thread Adrian Klaver
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