Обсуждение: Postgresql 8.0.3, symbolic link to /var/lib/pgsql/data/base
I'm trying to move my databases to another drive. With postmaster off, I create: /home/pgsql mv /var/lib/pgsql/data/base /home/pgsql/ I make sure that /home/pgsql is owned by postgres, and only 'rwx' for the owner. All the permissions look fine. from /var/lib/pgsql/data, I do ln -s /home/pgsql/base Of course the link permissions look like: lrwxrwxrwx 1 postgres postgres 16 Sep 28 14:21 base -> /home/pgsql/base The postmaster starts, but attempts to access the database complain that permission is denied: $ psql -l psql: FATAL: could not access directory "/var/lib/pgsql/data/base/1": Permission denied Any clues about why this won't work? I've searched the archives, but most references were to invalid permissions on the directories and I'm pretty sure I've got those right. All my pgsql dirs look pretty much like: drwx------ 3 postgres postgres 4096 Sep 28 14:21 pgsql Both in the original /var/lib/pgsql area, and the /home/pgsql area.
Hi Jeffrey Jeffrey Tenny wrote: > I'm trying to move my databases to another drive. > > With postmaster off, I create: > > /home/pgsql > > mv /var/lib/pgsql/data/base /home/pgsql/ > > > I make sure that /home/pgsql is owned by postgres, and only 'rwx' for > the owner. All the permissions look fine. > > from /var/lib/pgsql/data, I do > > ln -s /home/pgsql/base > > Of course the link permissions look like: > > lrwxrwxrwx 1 postgres postgres 16 Sep 28 14:21 base -> /home/pgsql/base > > > The postmaster starts, but attempts to access the database complain > that permission is denied: > > $ psql -l > psql: FATAL: could not access directory "/var/lib/pgsql/data/base/1": > Permission denied > > > Any clues about why this won't work? > I've searched the archives, but most references were to invalid > permissions on the directories and I'm pretty sure I've got those right. > > All my pgsql dirs look pretty much like: > drwx------ 3 postgres postgres 4096 Sep 28 14:21 pgsql > > Both in the original /var/lib/pgsql area, and the /home/pgsql area. > Try to issue chown -R postgres.postgres /home/pgsql Sergiusz
Why don't you copy/move the entire data directory (minus pg_xlog if you are separating them)
-----Original Message-----
From: Sergiusz Jarczyk [mailto:sergiusz.jarczyk@qresc.com]
Sent: Wed 9/28/2005 3:34 PM
To: pgsql-admin@postgresql.org
Cc:
Subject: Re: [ADMIN] Postgresql 8.0.3, symbolic link to /var/lib/pgsql/data/base
Hi Jeffrey
Jeffrey Tenny wrote:
> I'm trying to move my databases to another drive.
>
> With postmaster off, I create:
>
> /home/pgsql
>
> mv /var/lib/pgsql/data/base /home/pgsql/
>
>
> I make sure that /home/pgsql is owned by postgres, and only 'rwx' for
> the owner. All the permissions look fine.
>
> from /var/lib/pgsql/data, I do
>
> ln -s /home/pgsql/base
>
> Of course the link permissions look like:
>
> lrwxrwxrwx 1 postgres postgres 16 Sep 28 14:21 base -> /home/pgsql/base
>
>
> The postmaster starts, but attempts to access the database complain
> that permission is denied:
>
> $ psql -l
> psql: FATAL: could not access directory "/var/lib/pgsql/data/base/1":
> Permission denied
>
>
> Any clues about why this won't work?
> I've searched the archives, but most references were to invalid
> permissions on the directories and I'm pretty sure I've got those right.
>
> All my pgsql dirs look pretty much like:
> drwx------ 3 postgres postgres 4096 Sep 28 14:21 pgsql
>
> Both in the original /var/lib/pgsql area, and the /home/pgsql area.
>
Try to issue chown -R postgres.postgres /home/pgsql
Sergiusz
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> I'm trying to move my databases to another drive.
On what platform?
One theory that fits the available facts is that you're running on a
machine with SELinux enabled; the usual selinux policy forbids the
postmaster from accessing files outside /var/lib/pgsql.
regards, tom lane
The platform is Redhat 9, so no SELinux. Tom Lane wrote: > Jeffrey Tenny <jeffrey.tenny@comcast.net> writes: > >>I'm trying to move my databases to another drive. > > > On what platform? > > One theory that fits the available facts is that you're running on a > machine with SELinux enabled; the usual selinux policy forbids the > postmaster from accessing files outside /var/lib/pgsql. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
Sorry, I take that back. The platform is Fedora Core 4, so SELinux may be a concern, though during the installation I selected all the minimal options for SELinux because I wasn't prepared to deal with it. Apologies for the previous incorrect answer, I was typing it on a Redhat 9 machine :-) Jeffrey Tenny wrote: > The platform is Redhat 9, so no SELinux. > > Tom Lane wrote: > >> Jeffrey Tenny <jeffrey.tenny@comcast.net> writes: >> >>> I'm trying to move my databases to another drive. >> >> >> >> On what platform? >> >> One theory that fits the available facts is that you're running on a >> machine with SELinux enabled; the usual selinux policy forbids the >> postmaster from accessing files outside /var/lib/pgsql. >> >> regards, tom lane >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> >
Anjan Dave wrote: > Why don't you copy/move the entire data directory (minus pg_xlog if you are separating them) I tried that too, and then postmaster wouldn't even start. I don't remember if it was a permission error or if it was complaining it couldn't find the configuration files. I suspect whatever symlink problems are manifesting in just moving 'base' also apply to moving 'data'.
Sergiusz Jarczyk wrote: > > Try to issue chown -R postgres.postgres /home/pgsql > I checked the ownership, and it was already ok because aside from the 'root' creation of the alternate directory location and assigning it the correct ownership and permissions, I did the 'mv' of the old directory to the new location logged in as 'postgres'. Ditto the symlink creation (did it as 'postgres').
I've been focusing on the potentially simpler case of just relocating the 'base' directory, so that I don't have to worry about whether postmaster is finding my config files and such. I was hoping the symlink would be a pretty transparent operation, and it doesn't seem that other people have had difficulty doing similar th ings. Sergiusz Jarczyk wrote: > Hi again > Have you tried to run the postmaster with the new location by hand, i.e.: > postmaster -i -D /home/pgsql > > Sergiusz > >
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> Sorry, I take that back. The platform is Fedora Core 4, so SELinux may
> be a concern, though during the installation I selected all the minimal
> options for SELinux because I wasn't prepared to deal with it.
Better check what /usr/sbin/getenforce tells you, then... or look in
/var/log/messages to see if the postmaster's operations are being
refused.
regards, tom lane
Hi again Have you tried to run the postmaster with the new location by hand, i.e.: postmaster -i -D /home/pgsql Sergiusz
Tom Lane wrote: > Jeffrey Tenny <jeffrey.tenny@comcast.net> writes: > >>Sorry, I take that back. The platform is Fedora Core 4, so SELinux may >>be a concern, though during the installation I selected all the minimal >>options for SELinux because I wasn't prepared to deal with it. > > > Better check what /usr/sbin/getenforce tells you, then... or look in > /var/log/messages to see if the postmaster's operations are being > refused. > Interesting. getenforce says "Enforcing". There are no suspicious messages in /var/log/messages or any other log I can find there.
Here's the /var/lib/pgsql/data directory ('.')
-bash-3.00$ ls -ldZ
drwx------ postgres postgres system_u:object_r:postgresql_db_t .
Here are the children:
-bash-3.00$ ls -ldZ *
lrwxrwxrwx postgres postgres user_u:object_r:postgresql_db_t base ->
/home/pgsql/base
drwx------ postgres postgres root:object_r:postgresql_db_t global
drwx------ postgres postgres root:object_r:postgresql_db_t pg_clog
-rw------- postgres postgres user_u:object_r:postgresql_db_t pg_hba.conf
-rw------- postgres postgres user_u:object_r:postgresql_db_t pg_hba.conf~
-rw------- postgres postgres root:object_r:postgresql_db_t pg_ident.conf
drwx------ postgres postgres root:object_r:postgresql_db_t pg_log
drwx------ postgres postgres root:object_r:postgresql_db_t pg_subtrans
drwx------ postgres postgres root:object_r:postgresql_db_t pg_tblspc
-rw------- postgres postgres root:object_r:postgresql_db_t PG_VERSION
drwx------ postgres postgres root:object_r:postgresql_db_t pg_xlog
-rw------- postgres postgres user_u:object_r:postgresql_db_t
postgresql.conf
-rw------- postgres postgres user_u:object_r:postgresql_db_t
postgresql.conf~
-rw------- postgres postgres root:object_r:postgresql_db_t
postmaster.opts
-bash-3.00$ ls -ldZ /home/pgsql
drwx------ postgres postgres root:object_r:user_home_dir_t /home/pgsql
-bash-3.00$ ls -ldZ /home/pgsql/base
drwx------ postgres postgres root:object_r:postgresql_db_t
/home/pgsql/base
-bash-3.00$ ls -ldZ /home/pgsql/base/*
drwx------ postgres postgres root:object_r:postgresql_db_t
/home/pgsql/base/1
drwx------ postgres postgres root:object_r:postgresql_db_t
/home/pgsql/base/17229
drwx------ postgres postgres system_u:object_r:postgresql_db_t
/home/pgsql/base/17230
drwx------ postgres postgres system_u:object_r:postgresql_db_t
/home/pgsql/base/29144
drwx------ postgres postgres system_u:object_r:postgresql_db_t
/home/pgsql/base/29149
Somewhere in here is probably the clue, and a 'chcon' is probably
necessary. I'm clueless about selinux though and have no idea what to do.
Tom Lane wrote:
> Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
>
>>Sorry, I take that back. The platform is Fedora Core 4, so SELinux may
>>be a concern, though during the installation I selected all the minimal
>>options for SELinux because I wasn't prepared to deal with it.
>
>
> Better check what /usr/sbin/getenforce tells you, then... or look in
> /var/log/messages to see if the postmaster's operations are being
> refused.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
I tried a few:
chcon -t postgresql_db_t /home/pgsql
type things. No luck.
It's interesting that there are no permission denials
logged in /var/log/messages, but postgresql
says it can't access /var/lib/pgsql/data/base
because permission is denied (in the postgres log).
I played with the symlink chcon user, and the /home/pgsql user
as well (again, chcon, not chown).
It's clearly selinux though. If I do this:
/usr/sbin/setenforce 0
Then everything works peachy for my testing.
But turning off selinux is probably a one way road,
since all files created in that environment won't have their
context set. So I really need to solve this in the correct fashion.
For now I'll run with the database files under /var/lib/pgsql/data
until I (or someone else) can figure out the selinux/postgresql magic.
Jeffrey Tenny wrote: > Anjan Dave wrote: > >> Why don't you copy/move the entire data directory (minus pg_xlog if >> you are separating them) > > > I tried that too, and then postmaster wouldn't even start. I don't > remember if it was a permission error or if it was complaining it > couldn't find the configuration files. > > I suspect whatever symlink problems are manifesting in just moving > 'base' also apply to moving 'data'. Check the file ownerships. Maybe a chown -R postgres /home/postgres might be in order... Also, if you are using SE-Linux, this can make things interesting... Best Wishes, Chris Travers Metatron Technology Consulting > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > >
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> It's clearly selinux though. If I do this:
> /usr/sbin/setenforce 0
> Then everything works peachy for my testing.
OK, that's pretty conclusive as to the source of the issue.
> It's interesting that there are no permission denials
> logged in /var/log/messages, but postgresql
> says it can't access /var/lib/pgsql/data/base
> because permission is denied (in the postgres log).
I've run into this more than a few times myself. What I've been told
when I complained about it is "that's normal because selinux log
messages are rate-limited to avoid inflating the log files too much".
Which is plausible in itself, but rate-limiting to zero is not helpful.
Feel free to file another gripe in Red Hat's bugzilla.
regards, tom lane