Обсуждение: Is it possible to control the location of the lock file when starting postgres?

On 07/19/2016 02:49 PM, Steve Langlois wrote:
> Using Postgres 9.2 with CentOS7.
>
>
> I ran
>
>
> + /usr/bin/initdb --pgdata=/usr/xxx/databases/pgsql/data --auth=ident
>
> without issue however when I try to start the database it complains
> about the lockfile.
>
> FATAL:  could not create lock file
> "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied

How are you starting Postgres?

If from system init script then the directory made be set there.
See below also.

>
> I know I can change permissions of /var/run of course but I want to
> change the location of the lock file to a directory I already have
> access to. So I really want to change the default location of the lock
> file to something other than /var/run. I've checked the options to
> initdb as well as looking at the various files in the data directory,
> pg_hba.conf, pg_ident.conf, etc.

https://www.postgresql.org/docs/9.2/static/runtime-config-connection.html

unix_socket_directory (string)

     Specifies the directory of the Unix-domain socket on which the
server is to listen for connections from client applications. The
default is normally /tmp, but can be changed at build time. This
parameter can only be set at server start.

     In addition to the socket file itself, which is named .s.PGSQL.nnnn
where nnnn is the server's port number, an ordinary file named
.s.PGSQL.nnnn.lock will be created in the unix_socket_directory
directory. Neither file should ever be removed manually.

     This parameter is irrelevant on Windows, which does not have
Unix-domain sockets.


>
> Thank you.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Steve Langlois <steve.langlois@tavve.com> writes:
> I ran
> + /usr/bin/initdb --pgdata=/usr/xxx/databases/pgsql/data --auth=ident
> without issue however when I try to start the database it complains about the lockfile.
> FATAL:  could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied

The problem is not the lock file.  The problem is that it wants to create
a socket file there.  You should be able to fix this by changing the value
of unix_socket_directories.

Presumably, you are working with a distro-modified version of Postgres,
because the stock sources don't use /var/run/postgresql as a socket
directory.  You will likely find that your version of libpq.so also
expects /var/run/postgresql as the socket directory, so you won't be
able to make non-TCP connections without explicitly specifying /tmp
(or wherever you choose to put the socket) as the host location.

            regards, tom lane

>Presumably, you are working with a distro-modified version of Postgres,

>because the stock sources don't use /var/run/postgresql as a socket
>directory.  You will likely find that your version of libpq.so also
>expects /var/run/postgresql as the socket directory, so you won't be
>able to make non-TCP connections without explicitly specifying /tmp
>(or wherever you choose to put the socket) as the host location.

Using the following fromsudo yum install postgresql-server postgresql-contrib

postgresql-server-9.2.15-1.el7_2.x86_64

postgresql-contrib-9.2.15-1.el7_2.x86_64

postgresql-9.2.15-1.el7_2.x86_64

postgresql-libs-9.2.15-1.el7_2.x86_64

then why not start it with systemctl  ?  its already setup as a system service.

-- 
john r pierce, recycling bits in santa cruz

Why is the local connection not working without having to add host ::1/128 to the pg_hba.conf file. I thought specifying local all all trust would allow any local connection.

local != localhost...  on your system localhost is resolving to the ipv6 ::1, rather than the ipv4 127.0.0.1  but these are both still tcp/ip 'host' connections.   'local' is used for unix domain socket connections, which are when you don't specify any hostname (or specify a socket path starting with /)

you never did answer my previous question, why are you messing about with manually starting postgres from the wrong user account, when it should be run as a system service with systemctl ?

    $ sudo systemctl start service.postgresql-9.1

and if you want it to auto-start on reboot,

    $ sudo systemctl enable service.postgresql-9.1

-- 
john r pierce, recycling bits in santa cruz

On 07/20/2016 10:44 AM, Steve Langlois wrote:
> Steve Langlois <steve.langlois@tavve.com> writes:
>
>> I ran
>> + /usr/bin/initdb --pgdata=/usr/xxx/databases/pgsql/data --auth=ident
>> without issue however when I try to start the database it complains about the lockfile.
>> FATAL:  could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied
>
> I was able to resolve this issue using -k /tmp with postmaster:
>
> postmaster -k "/tmp" -D "/usr/xxx/databases/pgsql/data"
>
> In postgresql.conf I have
>
> listen_addresses = 'localhost'
> unix_socket_directories = '/tmp'
>
> However when I try to connect with psql
>
> [13:27:08 slanglois@tron data]$ psql postgres
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
>
> The lock file is in /tmp

Tom Lane addressed that in his reply to your post,  here:

https://www.postgresql.org/message-id/16354.1468965669%40sss.pgh.pa.us

>
> [13:28:02 slanglois@tron data]$ ls -al /tmp/.*PGSQL*
> srwxrwxrwx 1 slanglois eng  0 Jul 20 13:19 /tmp/.s.PGSQL.5432
> -rw------- 1 slanglois eng 59 Jul 20 13:19 /tmp/.s.PGSQL.5432.lock
>
> even though I have the following in pg_hba.conf
>
> local all all trust
>
> I tried to specify the host but it still doesn't connect
>
> [13:32:24 slanglois@tron data]$ psql -h localhost postgres
> psql: FATAL:  no pg_hba.conf entry for host "::1", user "slanglois",
> database "postgres", SSL off
>
> To get it to connect I have to add the following to pg_hba.conf
>
> host    all             all             ::1/128                 trust
>
> I was going to put this under a new subject but it seems to me like it
> is still related to the lock file being expected in /var/run/postgres
> when it is trying to connect even though I changed it to /tmp.
>
> Why is the local connection not working without having to add host
> ::1/128 to the pg_hba.conf file. I thought specifying local all all
> trust would allow any local connection.
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/20/2016 4:48 PM, Steve Langlois wrote:
> I am upgrading an existing system running CentOS 5.6 with Postgres
> 8.2.5 to CentOS 7 with 9.2.15. The original system modified the
> postgresql script to manually running postmaster to start the database
> under the current user control. So it is really for compatibility with
> the rest of the code.

'because we did it this way 10 years ago' is a lousy excuse, but whatever.

if you're upgrading the OS and database and everything, why are you
stopping at 9.2?   that version is already 80% through its support life
cycle, I would use 9.4 or 9.5 for maximum support longetivity.    9.1 is
on its final release, 9.2 will likely be desupported in a year or so.


>
> So currently to create the database I run:
>
> /usr/bin/initdb --pgdata=/usr/test/databases/pgsql/data --auth=ident
>
> And to start the database with:
>
> /usr/bin/postmaster -p 5432 -D /usr/test/databases/pgsql/data
>
> If local is used for unix domain socket connections do I change --auth
> to --auth-local=ident for initdb?

to work with the standard centos/rhel builds, you should use su or sudo
to run those commands as the postgres user, rather than whatever this
current user is, otherwise you'll be in a continuous world of hurt.
really, its much easier to just use the systemctl stuff to start/stop.



--
john r pierce, recycling bits in santa cruz

> On Jul 20, 2016, at 8:03 PM, John R Pierce <pierce@hogranch.com> wrote:
> 
> On 7/20/2016 4:48 PM, Steve Langlois wrote:
>> I am upgrading an existing system running CentOS 5.6 with Postgres 8.2.5 to CentOS 7 with 9.2.15. The original
systemmodified the postgresql script to manually running postmaster to start the database under the current user
control.So it is really for compatibility with the rest of the code. 
 
> 
> 'because we did it this way 10 years ago' is a lousy excuse, but whatever.
> 
> if you're upgrading the OS and database and everything, why are you stopping at 9.2?   that version is already 80%
throughits support life cycle, I would use 9.4 or 9.5 for maximum support longetivity.    9.1 is on its final release,
9.2will likely be desupported in a year or so.
 
> 

Oh I agree and I will likely do that as I take over this code but wanted to see if I could get the ‘old’ method to work
soI can unblock others until I can update the code.
 

I was thinking the same about using the 9.5. 9.2 is the current distribution with CentOS 7.2.

Then I try to run psql and 

[20:01:11 slanglois@tron bin]$ psql

psql: could not connect to server: No such file or directory

Is the server running locally and accepting

connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432”?

I would expect local all all trust to allow me to connect.

-- 
john r pierce, recycling bits in santa cruz

On Thu, 2016-07-21 at 00:12 +0000, Steve Langlois wrote:
>
> > On Jul 20, 2016, at 7:48 PM, Steve Langlois <steve.langlois@tavve.c
> > om> wrote:
> >
> > > you never did answer my previous question, why are you messing
> > > about with manually starting postgres from the wrong user
> > > account, when it should be run as a system service with systemctl
> > > ?
> > I am upgrading an existing system running CentOS 5.6 with Postgres
> > 8.2.5 to CentOS 7 with 9.2.15. The original system modified the
> > postgresql script to manually running postmaster to start the
> > database under the current user control. So it is really for
> > compatibility with the rest of the code. 
> >
> > If local is used for unix domain socket connections do I change --
> > auth to --auth-local=ident for initdb?
> >
>
> I tried the following:
>
> /usr/bin/initdb --pgdata=/usr/test/databases/pgsql1/data --auth-
> local=trust
>
> changed unix_socket_directories = '/tmp’ in postgresql.conf.
>
> Then ran:
>
> /usr/bin/postgres -k /tmp -D /usr/tavve/databases/pgsql1/data &
>
> Contents of pg_hba.conf is 
>
> # TYPE  DATABASE        USER            ADDRESS                
> METHOD
>
> # "local" is for Unix domain socket connections only
> local   all             all                                     trust
> # IPv4 local connections:
> host    all             all             127.0.0.1/32            trust
> # IPv6 local connections:
> host    all             all             ::1/128                 trust
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> #local   replication     slanglois                              
>  trust
> #host    replication     slanglois        127.0.0.1/32          
>  trust
> #host    replication     slanglois        ::1/128                
> trust
>
> Then I try to run psql and 
>
> [20:01:11 slanglois@tron bin]$ psql
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432”?
>
> I would expect local all all trust to allow me to connect.



On my testing box this is my pg_hba.conf

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

"local" is for psql, etc.
"host" is for JDBC connections

Connection and authentication settings in postgresql.conf

listen_addresses = 'localhost'
port = 5435
max_connections = 100
unix_socket_directories = '/var/run/postgresql'
#unix_socket_group = ''
unix_socket_permissions = 0770 


(I'm using port 5435 for 9.6beta testing)

psql does not start the database engine.

Whichever method you use to start it, that user (usually postgres) has
to own the /var/run/postgresql path and have write permissions.

E.g.:-

-rw-r--r-- 1 postgres postgres 5 Jul 21 03:57 9.6-main.pid


A similar set-up is used for 9.4 and 9.5. I haven't used 9.2 for some
years now, and as John pointed out, that version is nearing the end of
its maintenance cycle. However, I believe a similar set-up was used.
This is running on Debian.

HTH,

Rob