Re: [GENERAL] Unable to start postgresql - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: [GENERAL] Unable to start postgresql |
Date | |
Msg-id | 320ef314-aa43-32e5-09c2-6947f70b6762@aklaver.com Whole thread Raw |
In response to | Re: [GENERAL] Unable to start postgresql (John Iliffe <john.iliffe@iliffe.ca>) |
List | pgsql-general |
On 03/08/2017 01:28 PM, John Iliffe wrote: > On Wednesday 08 March 2017 11:18:59 Adrian Klaver wrote: >> On 03/08/2017 07:37 AM, John Iliffe wrote: >>> On Wednesday 08 March 2017 00:01:32 Tom Lane wrote: >>>> John Iliffe <john.iliffe@iliffe.ca> writes: >>>>> Now, running as user postgres I try and start as stated in the >>>>> manual postgres -D /usr/pgsql_tablespaces >>>>> >>>>> The result is: >>>>> [postgres@prod04 postgresql-9.6.2]$ postgres -D >>>>> /usr/pgsql_tablespaces LOG: could not bind IPv4 socket: Cannot >>>>> assign requested address HINT: Is another postmaster already >>>>> running on port 5432? If not, wait a few seconds and retry. >>>>> LOG: database system was shut down at 2017-03-07 22:22:57 EST >>>>> LOG: MultiXact member wraparound protections are now enabled >>>>> LOG: database system is ready to accept connections >>>>> LOG: autovacuum launcher started >>>> >>>> To clarify: the postmaster *is* starting here. It failed to bind to >>>> the IPv4 port 5432, but it must have succeeded in binding to at >>>> least one other port (IPv6 and/or a Unix socket), else it would have >>>> stopped and you'd have not seen the last four log lines. >>>> >>>> It might be helpful to check with lsof to see what the postmaster >>>> process has open after you do this. >>> >>> I noticed that when I deleted the postmaster.pid file as suggested by >>> another answer and restarted that process issued a lot more messages >>> before crashing :-( Still couldn't connect to port 5432 though. >> >> Have you tried the firewall setup from here: >> >> https://fedoraproject.org/wiki/PostgreSQL >> Firewall >> >> PostgreSQL operates on port 5432 (or whatever else you set in your >> postgresql.conf). In firewalld you can open it like this: >> >> $ # make it last after reboot >> $ firewall-cmd --permanent --add-port=5432/tcp >> $ # change runtime configuration >> $ firewall-cmd --add-port=5432/tcp >> > > OK, I tried this, along with some suggestions from other responses. I also > rebooted to get a completely clean environment again, and have the > following results: > > 1. the firewall now has port 5432 added permanently. This seems to me to > be a security exposure since the socket connection that I need is an > INTERNAL (ie on the same machine) connection, not an incoming connection > from another machine. Does anyone have any comments on that? I don't use Fedora so all I can do is point you at: https://fedoraproject.org/wiki/Firewalld?rd=FirewallD It does have a the concept of an internal zone: https://fedoraproject.org/wiki/Firewalld?rd=FirewallD#Which_zones_are_available.3F Not sure if that applies here though. I am beginning to suspect the firewall is not the issue here though, so once we iron what is you could probably undo the open port. > > 2. The start up messages (still on the screen for convenience) are: > > ------------------------------- > [root@prod04 John]# su postgres > [postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces > could not change directory to "/home/John": Permission denied > server starting This is somewhat suspicious. What if you shutdown the Postgres server and then su - postgres to and run?: pg_ctl start -D /usr/pgsql_tablespaces Along that line what user 'owns' /usr/pgsql_tablespaces? > [postgres@prod04 John]$ LOG: could not bind IPv4 socket: Cannot assign > requested address > HINT: Is another postmaster already running on port 5432? If not, wait a > few seconds and retry. > LOG: database system was shut down at 2017-03-08 10:40:27 EST > LOG: MultiXact member wraparound protections are now enabled > LOG: database system is ready to accept connections > LOG: autovacuum launcher started > ------------------------------------ > > 3. I found the config file (in the tablespace????) and changed socket file > to /var/run/. That caused a failure of the database since user postgres > does not have write authority on /var/run. That isn't the problem at the > moment so I'll file it to think about later! > > There is a socket and a lock file for PGSQL in the /tmp directory. > > srwxrwxrwx. 1 postgres postgres 0 Mar 8 15:32 .s.PGSQL.5432 > -rw-------. 1 postgres postgres 49 Mar 8 15:32 .s.PGSQL.5432.lock > > 4. I did a shut down of postmaster to be sure these weren't abandoned files > and they disappeared. So I conclude that socket #5432 was, in fact, > connected at start up (???) despite what the log says. The pid file also > disappeared as expected. Not so sure that this not actually indicating what Tom suggested that there is an IPv4 config issue. > > 5. Restarted, Same messages as before. The message says fairly > specifically that it can't bind an IPv4 socket. Is there a chance that > there is an IPv6 socket involved here somewhere that I'm not seeing? > > 6 Because: > > ---------------------------------------------------- > psql -U postgres > psql (9.6.2) > Type "help" for help. Well this indicates the socket is working. What if you do?: psql -U postgres -h ::1 > > postgres=# \l > List of databases > Name | Owner | Encoding | Collate | Ctype | Access > privileges > -----------+----------+----------+-------------+-------------+----------------------- > postgres | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | > template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres > + > | | | | | > postgres=CTc/postgres > template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres > + > | | | | | > postgres=CTc/postgres > (3 rows) > > postgres=# > ------------------------------------------------------- > > So, to the extent I can test at the moment, it looks like I have a working > database with a lot of strange messages. Odd that user postgres can log on > without a password but I guess that is an hba file issue to fix. That is set in pg_hba.conf. The default is: # "local" is for Unix domain socket connections only local all all trust where trust is: https://www.postgresql.org/docs/9.6/static/auth-pg-hba-conf.html trust Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication. See Section 20.3.1 for details. > > Whether an external programme, such as one of the web server programmes can > use it is an open question since the web server isn't installed yet. > > More at end. > >> More comments below. >> >>> ------------------------------- >>> [postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces >>> could not change directory to "/home/John": Permission denied >>> server starting >>> [postgres@prod04 John]$ LOG: could not bind IPv4 socket: Cannot >>> assign requested address >>> HINT: Is another postmaster already running on port 5432? If not, >>> wait a few seconds and retry. >>> LOG: database system was interrupted; last known up at 2017-03-08 >>> 09:42:16 EST >>> LOG: database system was not properly shut down; automatic recovery >>> in progress >>> LOG: invalid record length at 0/1561138: wanted 24, got 0 >>> LOG: redo is not required >>> LOG: MultiXact member wraparound protections are now enabled >>> LOG: database system is ready to accept connections >>> LOG: autovacuum launcher started >>> ----------------------------- >>> >>> so I corrected the initial error by changing to the bin directory and >>> starting again, after removing the postmaster.pid file. Same result. >>> >>> lsof says that there is nothing assigned to postmaster at this time. >>> >>> I did manage to get a clean stop this time; no remaining pid file. >>> >>>>> I checked with lsof and there is no process bound to socket 5432. >>>>> There is no entry in /var/run for a socket related to postgresql. >>>> >>>> With the default configure options you used, the postmaster would >>>> have put its Unix socket file into /tmp, not /var/run. I wonder >>>> whether your problem is that you're trying to connect to it with >>>> distro-supplied psql+libpq that expects to find the Unix socket in >>>> /var/run. >>> >>> Yes. socket file and also lock file were there. I'll fix that in >>> config, BUT in the original case they weren't there. >> >> So what is the below? >> >>> srwxrwxrwx. 1 postgres postgres 0 Mar 8 10:10 .s.PGSQL.5432 >>> -rw-------. 1 postgres postgres 49 Mar 8 10:10 >>> .s.PGSQL.5432.lock >>> >>> Still, the first lines of the log are the same; can't connect to >>> socket 5432. >>> >>> The following processes show up in ps >>> >>> root 1149 1136 0 10:18 pts/1 00:00:00 su postgres >>> postgres 1150 1149 0 10:18 pts/1 00:00:00 bash >>> postgres 1230 1 0 10:26 pts/1 00:00:00 >>> >>> /usr/postgres-9.6.2/bin/postgres -D /usr/pgsql_tablespaces >>> >>> postgres 1232 1230 0 10:26 ? 00:00:00 postgres: checkpointer >>> >>> process >>> >>> postgres 1233 1230 0 10:26 ? 00:00:00 postgres: writer >>> process postgres 1234 1230 0 10:26 ? 00:00:00 postgres: wal >>> writer >>> >>> process >>> >>> postgres 1235 1230 0 10:26 ? 00:00:00 postgres: autovacuum >>> >>> launcher process >>> >>> postgres 1236 1230 0 10:26 ? 00:00:00 postgres: stats >>> collector >>> >>> process >>> >>> ---------------------------- >> >> So Postgres is running. >> >>>>> One thing that I haven't been able to find any the log files. Where >>>>> are they normally stored? >> >> Where you configure them: >> >> https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html >> > A few comments: > > 1. I know all you folks are volunteers and I would like to express my > sincere thanks for the rapid and detailed responses. > > 2. I have installed many Linux, and other Unix, instances in the past and > this is the first time I have encountered a "default security on" situation. > Usually I get to configure the server first and then turn on the security! > Also, I didn't specify in the software selection screen that I needed a > firewall so I was caught by surprise on that. Thank you for telling me to > check that. I would have looked for iptables based on experience, not > firewalld. > > 3. I'm not at all sure that this is a viable configuration of PostgreSQL. > If anyone reading this has any comments I humbly await them! > > Regards, > > John > > > > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: