Unable to start postgresql

On Tue, Mar 7, 2017 at 11:17 PM, John Iliffe <john.iliffe@iliffe.ca> wrote:

I was unable to get postgres started so I did a very basic compile/install
to test it. The configuration line was:

./configure --prefix=/usr/postgres-9.6.2

--------------------------------
Then I ran make which completed as expected:

make[1]: Leaving directory '/tmp/postgresql-9.6.2/config'
All of PostgreSQL successfully made. Ready to install.

--------------------------------------
Then make check

make check

=======================
All 167 tests passed.
=======================

make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress'
----------------------------------------

Then make install as user root because of directory permissions. I have
the expected files in /usr/postgres_9.6.2

Now, running as user postgres I try and start as stated in the manual

postgres -D /usr/pgsql_tablespaces

initdb has already been run and the directory pgsql_tablespaces has a
number of files as expected. As yet no database has been defined because
psql won't start.

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

Same results if I use pg_ctl to start the process.

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.

I thought it might be a security issue so I put SELinux in permissive mode
but the result is the same. The SELinux journal does not show any warnings
on this process.

[root@prod04 postgresql-9.6.2]# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: permissive <------permissive mode**
Mode from config file: permissive
Policy MLS status: enabled
Policy deny_unknown status: allowed
Max kernel policy version: 30
-----------------------------------------------------------------

One thing that I haven't been able to find any the log files. Where are
they
normally stored?

So, any ideas as to where to go next to debug this would be appreciated!
This is a brand new server that I am trying to configure so I have a fair
amount of security clearance to chase things.

Thanks in advance.

John
=====================================

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This is very suspicicious.

LOG: could not bind IPv4 socket: Cannot assign requested address
HINT: Is another postmaster already running on port 5432? If not, wait a

f>ew seconds and retry.

So check to see if the file "postmaster.pid" exists.
If it does, and postgres is NOT running, just delet or rename it and try to
start.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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 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.

One thing that I haven't been able to find any the log files. Where are they
normally stored?

They seem to be showing up on postmaster's stderr (ie, your terminal)
which again is the vanilla-configuration default if you didn't do anything
to edit the postgresql.conf settings.

I suspect that you're used to the behavior of a vendor-configured postgres
package and have not taken the steps needed to make a build from source
behave the same way. Recommend looking into what patches the vendor
package applies and what configure options are used.

Having said all that, it's very un-obvious why you're failing to bind
to the IPv4 socket. If there's no active postmaster on the machine,
that should be free. I could believe SELinux blocking it, except that
the targeted SELinux policy shouldn't constrain a manually-started
postmaster at all --- not to mention that you're in permissive mode.
Seems like the answer must be elsewhere.

You didn't mention what platform you're on, but the reference to SELinux
makes me think it's probably Red Hat. A bit of digging in RH's support
portal turns up a few mentions of kernel bugs causing unexpected
EADDRNOTAVAIL errors, which matches this symptom ... so how up-to-date
is this server?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/07/2017 08:17 PM, John Iliffe wrote:

I was unable to get postgres started so I did a very basic compile/install
to test it. The configuration line was:

./configure --prefix=/usr/postgres-9.6.2

--------------------------------
Then I ran make which completed as expected:

make[1]: Leaving directory '/tmp/postgresql-9.6.2/config'
All of PostgreSQL successfully made. Ready to install.

--------------------------------------
Then make check

make check

=======================
All 167 tests passed.
=======================

make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress'
----------------------------------------

Then make install as user root because of directory permissions. I have
the expected files in /usr/postgres_9.6.2

Now, running as user postgres I try and start as stated in the manual

postgres -D /usr/pgsql_tablespaces

initdb has already been run and the directory pgsql_tablespaces has a
number of files as expected. As yet no database has been defined because
psql won't start.

Just to be clear you installed in:

/usr/postgres-9.6.2

but created the data directory in:

Also above you say:

"I was unable to get postgres started so I did a very basic
compile/install to test it. "

To me that implies there is another instance of Postgres on the system,
is that the case?

If not could you explain what you meant?

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

Same results if I use pg_ctl to start the process.

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.

I thought it might be a security issue so I put SELinux in permissive mode
but the result is the same. The SELinux journal does not show any warnings
on this process.

[root@prod04 postgresql-9.6.2]# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: permissive <------permissive mode**
Mode from config file: permissive
Policy MLS status: enabled
Policy deny_unknown status: allowed
Max kernel policy version: 30
-----------------------------------------------------------------

One thing that I haven't been able to find any the log files. Where are they
normally stored?

So, any ideas as to where to go next to debug this would be appreciated!
This is a brand new server that I am trying to configure so I have a fair
amount of security clearance to chase things.

Thanks in advance.

John
=====================================

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes, that file was present, so I deleted it. Attempted to restart and got
the same result:

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-07 22:48:24
EST
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: invalid record length at 0/15610C8: 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

When the programme exited it left another postmaster.pid file so I deleted
that one too.

John
===================================
On Tuesday 07 March 2017 23:26:40 Melvin Davidson wrote:

On Tue, Mar 7, 2017 at 11:17 PM, John Iliffe <john.iliffe@iliffe.ca> wrote:

I was unable to get postgres started so I did a very basic
compile/install to test it. The configuration line was:

./configure --prefix=/usr/postgres-9.6.2

--------------------------------
Then I ran make which completed as expected:

make[1]: Leaving directory '/tmp/postgresql-9.6.2/config'
All of PostgreSQL successfully made. Ready to install.

--------------------------------------
Then make check

make check

=======================

All 167 tests passed.

=======================

make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress'
----------------------------------------

Then make install as user root because of directory permissions. I
have the expected files in /usr/postgres_9.6.2

Now, running as user postgres I try and start as stated in the manual

postgres -D /usr/pgsql_tablespaces

initdb has already been run and the directory pgsql_tablespaces has a
number of files as expected. As yet no database has been defined
because psql won't start.

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

Same results if I use pg_ctl to start the process.

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.

I thought it might be a security issue so I put SELinux in permissive
mode but the result is the same. The SELinux journal does not show
any warnings on this process.

[root@prod04 postgresql-9.6.2]# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: permissive <------permissive mode**
Mode from config file: permissive
Policy MLS status: enabled
Policy deny_unknown status: allowed
Max kernel policy version: 30
-----------------------------------------------------------------

One thing that I haven't been able to find any the log files. Where
are they
normally stored?

So, any ideas as to where to go next to debug this would be
appreciated! This is a brand new server that I am trying to configure
so I have a fair amount of security clearance to chase things.

Thanks in advance.

John
=====================================

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This is very suspicicious.

LOG: could not bind IPv4 socket: Cannot assign requested address
HINT: Is another postmaster already running on port 5432? If not, wait
a

f>ew seconds and retry.

So check to see if the file "postmaster.pid" exists.
If it does, and postgres is NOT running, just delet or rename it and try
to start.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

John Iliffe <john.iliffe@iliffe.ca> writes:

When the programme exited it left another postmaster.pid file so I deleted
that one too.

You haven't shown us the program actually exiting, and basically the only
way to get the postmaster to exit without removing its pid file is to
kill -9 it. Now I am suspicious that you in fact haven't killed any
postmasters, but only removed their pidfiles out from under them, which is
an incredibly dangerous thing to do. Check "ps ax" output to see if any
postgres processes are lurking in background.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Mar 8, 2017 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

John Iliffe <john.iliffe@iliffe.ca> writes:

When the programme exited it left another postmaster.pid file so I

deleted

that one too.

You haven't shown us the program actually exiting, and basically the only
way to get the postmaster to exit without removing its pid file is to
kill -9 it. Now I am suspicious that you in fact haven't killed any
postmasters, but only removed their pidfiles out from under them, which is
an incredibly dangerous thing to do. Check "ps ax" output to see if any
postgres processes are lurking in background.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hey, looking closer, I see this in your original log

LOG: database system is ready to accept connections
LOG: autovacuum launcher started

That means Postgres WAS started, just that the postgres port was unable to
be opened.
So if you do a pg_ctl stop, change the port in postgresql.conf to 5433 (or
5434) and then attempt
to restart, is your problem resolved?

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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.

-------------------------------
[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.

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

----------------------------

One thing that I haven't been able to find any the log files. Where
are they normally stored?

They seem to be showing up on postmaster's stderr (ie, your terminal)
which again is the vanilla-configuration default if you didn't do
anything to edit the postgresql.conf settings.

I suspect that you're used to the behavior of a vendor-configured
postgres package and have not taken the steps needed to make a build
from source behave the same way. Recommend looking into what patches
the vendor package applies and what configure options are used.

No, actually I have been using postgresql since about 2007, always from
manual installs. Just never needed to ask a question before! I run a
small publishing business specializing an Amateur Radio training and we
rely on these databases to run just about everything from sales to user
support to accounting. At the moment the old server is on pgsql 9.2.1
which is about 5 years old.

I almost never use the distro supplied application software because I've
had problems with automatic updates making everything fail and no idea what
happened. This way the applications are where I put them and I know when
there is an update.

FYI, my core skills are emphatically NOT as a sysadmin or system
programmer!

Having said all that, it's very un-obvious why you're failing to bind
to the IPv4 socket. If there's no active postmaster on the machine,
that should be free. I could believe SELinux blocking it, except that
the targeted SELinux policy shouldn't constrain a manually-started
postmaster at all --- not to mention that you're in permissive mode.
Seems like the answer must be elsewhere.

You didn't mention what platform you're on, but the reference to SELinux
makes me think it's probably Red Hat. A bit of digging in RH's support
portal turns up a few mentions of kernel bugs causing unexpected
EADDRNOTAVAIL errors, which matches this symptom ... so how up-to-date
is this server?

Good guess but no prize! I am on Fedora 25, downloaded and installed last
Saturday (4 March) and then patched to the current levels with dnf before I
started to do the application software installation.

regards, tom lane

Hi Tom:

First, thanks for the very detailed explanation. Please see answers
embedded above.

Anyhow, given the suggestions that I have received, I decided to start with
a clean system, so I shut down postmaster using pg_ctl stop, checked that
the socket, lock, and pid files were deleted, and rebooted the server.

Same result unfortunately.

John

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

See embedded.

On Wednesday 08 March 2017 00:09:56 Adrian Klaver wrote:

On 03/07/2017 08:17 PM, John Iliffe wrote:

I was unable to get postgres started so I did a very basic
compile/install to test it. The configuration line was:

./configure --prefix=/usr/postgres-9.6.2

--------------------------------
Then I ran make which completed as expected:

make[1]: Leaving directory '/tmp/postgresql-9.6.2/config'
All of PostgreSQL successfully made. Ready to install.

--------------------------------------
Then make check

make check

=======================

All 167 tests passed.

=======================

make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress'
----------------------------------------

Then make install as user root because of directory permissions. I
have the expected files in /usr/postgres_9.6.2

Now, running as user postgres I try and start as stated in the manual

postgres -D /usr/pgsql_tablespaces

initdb has already been run and the directory pgsql_tablespaces has a
number of files as expected. As yet no database has been defined
because psql won't start.

Just to be clear you installed in:

/usr/postgres-9.6.2

yes, and the expected directories /usr/postgres-9.6.2/bin, /include, /lib,
and /share are all there.

but created the data directory in:

/usr/pgsql_tablespaces

Yes, I did that to separate the data from the software in case I decide to
update postgresql in the future. That way I can always revert if
necessary.

Also above you say:

"I was unable to get postgres started so I did a very basic
compile/install to test it. "

Yes, with no changes except the --prefix in the config file I was assured that
I wasn't causing this problem with a badly chosen parameter.

To me that implies there is another instance of Postgres on the system,
is that the case?

No other instances of Postgres on the system. Since it wasn't working I
deleted the original installation by deleting the install directory.

If not could you explain what you meant?

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

Same results if I use pg_ctl to start the process.

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.

I thought it might be a security issue so I put SELinux in permissive
mode but the result is the same. The SELinux journal does not show
any warnings on this process.

[root@prod04 postgresql-9.6.2]# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: permissive <------permissive mode**
Mode from config file: permissive
Policy MLS status: enabled
Policy deny_unknown status: allowed
Max kernel policy version: 30
-----------------------------------------------------------------

One thing that I haven't been able to find any the log files. Where
are they normally stored?

So, any ideas as to where to go next to debug this would be
appreciated! This is a brand new server that I am trying to configure
so I have a fair amount of security clearance to chase things.

Thanks in advance.

John
=====================================

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/08/2017 07:44 AM, John Iliffe wrote:

See embedded.

Just to be clear you installed in:

/usr/postgres-9.6.2

yes, and the expected directories /usr/postgres-9.6.2/bin, /include, /lib,
and /share are all there.

but created the data directory in:

/usr/pgsql_tablespaces

Yes, I did that to separate the data from the software in case I decide to
update postgresql in the future. That way I can always revert if
necessary.

FYI, not that a separate data directory is a problem, but if you are
updating from one major version to another that is not going to work as
the new version will not be able to use the old data directory and if
you do convert it using something like pg_upgrade then the old version
will not be able to use it.

Also above you say:

"I was unable to get postgres started so I did a very basic
compile/install to test it. "

Yes, with no changes except the --prefix in the config file I was assured that
I wasn't causing this problem with a badly chosen parameter.

So did you specify a different --prefix previously or just used the
defaults?

To me that implies there is another instance of Postgres on the system,
is that the case?

No other instances of Postgres on the system. Since it wasn't working I
deleted the original installation by deleting the install directory.

How about the data directory?

If not could you explain what you meant?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

They seem to be showing up on postmaster's stderr (ie, your terminal)
which again is the vanilla-configuration default if you didn't do
anything to edit the postgresql.conf settings.

I suspect that you're used to the behavior of a vendor-configured
postgres package and have not taken the steps needed to make a build
from source behave the same way. Recommend looking into what patches
the vendor package applies and what configure options are used.

No, actually I have been using postgresql since about 2007, always from
manual installs. Just never needed to ask a question before! I run a
small publishing business specializing an Amateur Radio training and we
rely on these databases to run just about everything from sales to user
support to accounting. At the moment the old server is on pgsql 9.2.1
which is about 5 years old.

I almost never use the distro supplied application software because I've
had problems with automatic updates making everything fail and no idea what
happened. This way the applications are where I put them and I know when
there is an update.

FYI, my core skills are emphatically NOT as a sysadmin or system
programmer!

Having said all that, it's very un-obvious why you're failing to bind
to the IPv4 socket. If there's no active postmaster on the machine,
that should be free. I could believe SELinux blocking it, except that
the targeted SELinux policy shouldn't constrain a manually-started
postmaster at all --- not to mention that you're in permissive mode.
Seems like the answer must be elsewhere.

You didn't mention what platform you're on, but the reference to SELinux
makes me think it's probably Red Hat. A bit of digging in RH's support
portal turns up a few mentions of kernel bugs causing unexpected
EADDRNOTAVAIL errors, which matches this symptom ... so how up-to-date
is this server?

Good guess but no prize! I am on Fedora 25, downloaded and installed last
Saturday (4 March) and then patched to the current levels with dnf before I
started to do the application software installation.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

Meant to ask before, can you show the command you are using to connect?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Meant to ask before, can you show the command you are using to connect?

My memory says OP didn't use --host, which often leads to trying the
socket. Do we know that's enabled in pg_hba?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I think you may have hit it but I'm even more confused now.

I looked at the running Postgesql on the current server and there is a 5th
sub-directory called /data. That is on the same level as the /bin, /share,
etc. In this new installation it is not present and neither is the
postgresql.conf file, nor are the hba files that restrict logins.

So, my question: is this a change between version 9.2.1 and 9.6.2 and if
so where is the postgresql.conf file (I can't find it on a scan but it could
be renamed I suppose)?

Or, is there something wrong with the installation? I went by the
successful conclusion message from make install and assumed everything
would be as expected in the directories.

Any ideas as to what may have happened?

John
=========================================
On Wednesday 08 March 2017 10:11:44 Melvin Davidson wrote:

On Wed, Mar 8, 2017 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

John Iliffe <john.iliffe@iliffe.ca> writes:

When the programme exited it left another postmaster.pid file so I

deleted

that one too.

You haven't shown us the program actually exiting, and basically the
only way to get the postmaster to exit without removing its pid file
is to kill -9 it. Now I am suspicious that you in fact haven't
killed any postmasters, but only removed their pidfiles out from
under them, which is an incredibly dangerous thing to do. Check "ps
ax" output to see if any postgres processes are lurking in
background.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hey, looking closer, I see this in your original log

LOG: database system is ready to accept connections
LOG: autovacuum launcher started

That means Postgres WAS started, just that the postgres port was unable
to be opened.
So if you do a pg_ctl stop, change the port in postgresql.conf to 5433
(or 5434) and then attempt
to restart, is your problem resolved?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/08/2017 10:12 AM, John Iliffe wrote:

See embedded.

Ccing list

will not be able to use it.

Yes, but if that happens at least I will be able to back out because I can
go back and start the old postgresql since it is on a different /usr
partition. That's the reason for the install directory being named after
the release version. After I am satisfied that everything is working
properly I usually delete the old install directory. Usually a few months
of operation.

So did you specify a different --prefix previously or just used the
defaults?

No, actually I used the same name for the install directory as this one but
I deleted the old one to completely get rid of the bad code.

No, I was unable to start the original postgresql installation so it never
got used or initialized.

To follow up on what Tom suggested upstream do you have package
installed version of libpq?

It would probably show up in the package manager as something along the
lines of postgresql-client.

Thanks again.

John

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wednesday 08 March 2017 11:37:27 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.

Meant to ask before, can you show the command you are using to connect?

Yes:

[postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces

I tried psql but it won't work, as expected, because socket 5432 is not
available.

John

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/08/2017 10:25 AM, John Iliffe wrote:

On Wednesday 08 March 2017 11:37:27 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.

Meant to ask before, can you show the command you are using to connect?

Yes:

[postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces

This is the command to start Postgres.

I tried psql but it won't work, as expected, because socket 5432 is not
available.

So what is the psql connection command you are using and what is the
error message you are getting?

John

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

John Iliffe <john.iliffe@iliffe.ca> writes:

I tried psql but it won't work, as expected, because socket 5432 is not
available.

Actually, that's not all that expected. psql by default would try to
connect via a Unix socket, so it wouldn't matter whether or not the
postmaster had been able to open an IPv4 port. The most likely reason
for failing to connect via Unix socket is looking in the wrong directory
for the socket, viz "/tmp" vs "/var/run/postgresql", which is why Adrian
is pressing you about other Postgres installations on the machine. If
your psql session is using a Red Hat-supplied libpq.so then it will likely
look in /var/run/postgresql, whereas this stock-sources postmaster is going
to have put it in /tmp by default. (You could adjust the
unix_socket_directories parameter to fix that.) It would also help to
pay close attention to the error message psql gives when it fails to
connect.

Even if you are doing "psql -h localhost", I'm pretty sure "localhost"
will resolve as IPv6 not IPv4 (ie ::1 not 127.0.0.1) on Fedora 25 --- it
does on mine. So if the postmaster successfully opened an IPv6 port,
which I think it would do by default, then it still wouldn't matter that
the IPv4 port wasn't there; the issue should still be masked.

FWIW, this is what I see for network sockets when lsof'ing a stock
postmaster on current Fedora 25:

...
postmaste 20082 tgl 3u IPv6 37256 0t0 TCP localhost:postgres (LISTEN)
postmaste 20082 tgl 4u IPv4 37257 0t0 TCP localhost:postgres (LISTEN)
postmaste 20082 tgl 5u unix 0xffff9eb3435cfc00 0t0 37259 /tmp/.s.PGSQL.5432 type=STREAM
...

or with -n it looks like

postmaste 20082 tgl 3u IPv6 37256 0t0 TCP [::1]:postgres (LISTEN)
postmaste 20082 tgl 4u IPv4 37257 0t0 TCP 127.0.0.1:postgres (LISTEN)
postmaste 20082 tgl 5u unix 0xffff9eb3435cfc00 0t0 37259 /tmp/.s.PGSQL.5432 type=STREAM

What I am suspicious of at this point is that the root of the problem is
networking misconfiguration on your machine, such that IPv4 doesn't work
at all; given the platform's bias towards IPv6 for loopback, you might
not have noticed otherwise. You might check what results you get from
"ping ::1" vs "ping 127.0.0.1" vs "ping localhost".

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general