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