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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general