Обсуждение: Using symbolic links with tablespaces
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
--
View this message in context: http://www.nabble.com/Using-symbolic-links-with-tablespaces-tp25353894p25353894.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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.
miller_2555 <nabble.30.miller_2555@spamgourmet.com> writes:
> So this does not work well apparently.
The only obvious thing wrong with your example is failing to preserve
ownership/permissions of the moved files. I think the upper directories
are probably not readable by Postgres so it's choking.
BTW, Postgres *already* refers to tablespaces via symlinks, so you could
do this with a tad less runtime overhead by re-pointing the appropriate
symlink inside $PGDATA/pg_tblspc/.
regards, tom lane
Tom Lane-2 wrote: > > miller_2555 <nabble.30.miller_2555@spamgourmet.com> writes: >> So this does not work well apparently. > > The only obvious thing wrong with your example is failing to preserve > ownership/permissions of the moved files. I think the upper directories > are probably not readable by Postgres so it's choking. > > BTW, Postgres *already* refers to tablespaces via symlinks, so you could > do this with a tad less runtime overhead by re-pointing the appropriate > symlink inside $PGDATA/pg_tblspc/. > > 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 > > Thanks for the tip on the overhead, I'll have to look into it. However, I hadn't yet copied any files when running into issues with Postgres. I merely removed the old symlink and created another (though it appears that I would also have had to re-link the $PGDATA/pg_tblspc/ files as well since that symlink would have probably broken upon deletion of the target symlink) . I am still uncertain as to how the system tables are inconsistent, though (I did not directly modify any of the system tables). Thanks. -- View this message in context: http://www.nabble.com/Using-symbolic-links-with-tablespaces-tp25353894p25358474.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.