Re: Using symbolic links with tablespaces

miller_2555 wrote:
>
> Hi -
>     I have a database and used symbolic links in the tablespace
> definitions. I just wanted to validate that I can move the database
> objects to a different physical volume by the following:
>      1) issuing `pg_ctl stop`
>      2) hard copying the tablespace files from one drive to another
>      3) pointing the symbolic links to the new drive
>      4) issuing `pg_ctl start`
>
> A semi-complete example is as follows:
>
> #> mount /dev/snn1 /mnt/myolddrive
> #> mount /dev/sno1 /mnt/mynewdrive
> #> cd /mnt
> #> ln -s /mnt/myolddrive mypsqllink
> #> pg_ctl start
> #> psql mydb -c 'CREATE TABLESPACE "mytablespacename" OWNER wtadmin
> LOCATION ''/mnt/mypsqllink'';'
> #> psql mydb -c 'CREATE TABLE "mytable" ("mycol" int) TABLESPACE
> "mytablespacename";'
> #> pg_ctl stop
> #> cp -r ./myolddrive ./mynewdrive
> #> rm mypsqllink
> #> ln -s /mnt/mynewdrive mypsqllink
> #> pg_ctl start
>
> I would think this is okay, but want to double-check before running...
>
> Thanks!
>
> BTW -
> Linux 2.6.27.29-170.2.78.fc10.x86_64 #1 SMP Fri Jul 31 04:16:20 EDT 2009
> x86_64 GNU/Linux
> psql (PostgreSQL) 8.3.7
>

So this does not work well apparently. At first, I tried simply removing the
existing symbolic link, then recreating it to point to the same location
(i.e. no data was actually moved). When I tried to connect back to the
database, there was an error that stated that the tablespace containing the
database could not be found (I checked and rechecked that the symbolic link
was recreated correctly)! As I had backed-up the database prior to running,
I decided to DROP the database and the associated tablespace. After a slew
of errors, I am left with a role that I cannot DROP because the database's
tablespace depends on the role and I cannot drop the tablespace  because the
database says it doesn't exist (but it is visible in the system catalog).
Here are the relevant details:

postgres=# SELECT * FROM pg_tablespace;
  spcname   | spcowner |              spclocation               | spcacl
------------+----------+----------------------------------------+--------
 pg_default |       10 |                                        |
 pg_global  |       10 |                                        |
 mytablespacename  |   632315 | /mnt/mypsqllink  |
(3 rows)

postgres=# DROP TABLESPACE mytablespacename;
ERROR:  tablespace "mytablespacename" does not exist

postgres=# SELECT * FROM pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | ******** |
|
 wtadmin  |   632315 | f           | f        | f         | ******** |
|
(2 rows)

postgres=# DROP ROLE wtadmin;
ERROR:  role "wtadmin" cannot be dropped because some objects depend on it
DETAIL:  owner of tablespace mytablespacename

How do I drop the tablespace even after recreating the symbolic link? Is
this behaviour expected? I was under the impression that Postgres didn't
really care about the underlying filesystem, so most anything permitted by
the OS would pass muster with Postgres. I'll need to drop these object
before reinitializing the database. Little help?


--
View this message in context: http://www.nabble.com/Using-symbolic-links-with-tablespaces-tp25353894p25356867.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.