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