Thread: [GENERAL] Unable to start postgresql

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

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

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

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.

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

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

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

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

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

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

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

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?

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

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

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

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

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

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

On Wednesday 08 March 2017 15:13:29 Tom Lane wrote:
> 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

Hi Tom:

By now you have probably seen that I did get PSQL to run.  I had assumed
that meant that everything was at least running if not as expected, at
least running.  But I did an lsof and I don't get anything at all for
postmaster.  postgres has a lot of open files but they are libraries and so
forth so not applicable to this situation.

I did the pings you suggest with the following results:

-------------------------------------------
[root@prod04 John]# ping localhost
PING localhost(localhost (::1)) 56 data bytes
64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.078 ms
64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.060 ms
64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.059 ms
64 bytes from localhost (::1): icmp_seq=4 ttl=64 time=0.064 ms
64 bytes from localhost (::1): icmp_seq=5 ttl=64 time=0.059 ms
^C
--- localhost ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4084ms
rtt min/avg/max/mdev = 0.059/0.064/0.078/0.007 ms
[root@prod04 John]#
[root@prod04 John]# ping ::1
PING ::1(::1) 56 data bytes
64 bytes from ::1: icmp_seq=1 ttl=64 time=0.074 ms
64 bytes from ::1: icmp_seq=2 ttl=64 time=0.059 ms
64 bytes from ::1: icmp_seq=3 ttl=64 time=0.059 ms
64 bytes from ::1: icmp_seq=4 ttl=64 time=0.060 ms
64 bytes from ::1: icmp_seq=5 ttl=64 time=0.059 ms
^C
--- ::1 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4108ms
rtt min/avg/max/mdev = 0.059/0.062/0.074/0.007 ms
[root@prod04 John]#
[root@prod04 John]#
[root@prod04 John]# ping 127.0.0.1
PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.057 ms
64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.045 ms
64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.046 ms
64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.049 ms
^C
--- 127.0.0.1 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3066ms
rtt min/avg/max/mdev = 0.045/0.049/0.057/0.006 ms
[root@prod04 John]# ping 192.168.1.8   <--- current server as a network
test

PING 192.168.1.8 (192.168.1.8) 56(84) bytes of data.
64 bytes from 192.168.1.8: icmp_seq=1 ttl=64 time=1.77 ms
64 bytes from 192.168.1.8: icmp_seq=2 ttl=64 time=0.627 ms
From 192.168.1.1: icmp_seq=2 Redirect Host(New nexthop: 192.168.1.8)
64 bytes from 192.168.1.8: icmp_seq=3 ttl=64 time=0.274 ms
^C
--- 192.168.1.8 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2003ms
rtt min/avg/max/mdev = 0.274/0.890/1.771/0.639 ms

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

Also, I am connected to the server by ssh so at least that IPv4 function is
working.  (the router here only handles IPv4).

I did add 5432 to the firewalld configuration and rebooted to pick it up.

So, I think the network configuration is OK.  I set that up as the first task
after installing the OS.

John

On 03/08/2017 02:01 PM, John Iliffe wrote:

Please use Reply All so others get your posts.
Ccing list

> On Wednesday 08 March 2017 16:40:03 you wrote:
>> On 03/08/2017 01:28 PM, John Iliffe wrote:
>>>
>>> -------------------------------
>>> [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.
>>

For the list the below is from a previous message which I forgot to Cc.

>> What if you shutdown the Postgres server and then su - postgres to and
>> run?:
> I was starting postgres as user postgres; but it was still in my home
> directory. Everything did start but I changed to /usr/pgsql_dataspaces and
> the message went away.  Since there is no requirement to actually cd to
> anything the message is a bit of a red herring.
>
> That said, I'm not sure how it will be handled by the system start up task.
> I'll have to check when I add it.  These are unusual manual (re)starts.
>>
>> pg_ctl start -D /usr/pgsql_tablespaces
>>
>> Along that line what user 'owns' /usr/pgsql_tablespaces?
>>
> /usr/pgsql_tablespaces is owned by postgres, group postgres, with
> permissions drwe...... on the directory and rw....... on all the files.
> This would seem correct to me.
>

>> Well this indicates the socket is working.
>>
>> What if you do?:
>>
>> psql -U postgres -h ::1
>>
> [root@prod04 John]# psql -U postgres -h ::1
> psql (9.6.2)
> Type "help" for help.

So Postgres is binding to IPv6.


>> That is set in pg_hba.conf. The default is:
>>
>> # "local" is for Unix domain socket connections only
>> local   all             all                                     trust
>>

> Yes, I recall major changes to that file that I did on the current server.
> But that was at least 5 years ago :-(
>
> I have that one fairly tightly locked down since this is a web server.
>


>>>>> lsof says that there is nothing assigned to postmaster at this time.


Given that you can connect I have to believe lsof would show something,
so what options are you using with lsof and what user are you running it as?

--
Adrian Klaver
adrian.klaver@aklaver.com

On 03/08/2017 01:48 PM, John Iliffe wrote:
> On Wednesday 08 March 2017 15:13:29 Tom Lane wrote:
>> 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
>
> Hi Tom:
>
> By now you have probably seen that I did get PSQL to run.  I had assumed
> that meant that everything was at least running if not as expected, at
> least running.  But I did an lsof and I don't get anything at all for
> postmaster.  postgres has a lot of open files but they are libraries and so
> forth so not applicable to this situation.
>
> I did the pings you suggest with the following results:
>
> -------------------------------------------
> [root@prod04 John]# ping localhost
> PING localhost(localhost (::1)) 56 data bytes
> 64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.078 ms
> 64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.060 ms
> 64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.059 ms
> 64 bytes from localhost (::1): icmp_seq=4 ttl=64 time=0.064 ms
> 64 bytes from localhost (::1): icmp_seq=5 ttl=64 time=0.059 ms
> ^C
> --- localhost ping statistics ---
> 5 packets transmitted, 5 received, 0% packet loss, time 4084ms
> rtt min/avg/max/mdev = 0.059/0.064/0.078/0.007 ms
> [root@prod04 John]#
> [root@prod04 John]# ping ::1
> PING ::1(::1) 56 data bytes
> 64 bytes from ::1: icmp_seq=1 ttl=64 time=0.074 ms
> 64 bytes from ::1: icmp_seq=2 ttl=64 time=0.059 ms
> 64 bytes from ::1: icmp_seq=3 ttl=64 time=0.059 ms
> 64 bytes from ::1: icmp_seq=4 ttl=64 time=0.060 ms
> 64 bytes from ::1: icmp_seq=5 ttl=64 time=0.059 ms
> ^C
> --- ::1 ping statistics ---
> 5 packets transmitted, 5 received, 0% packet loss, time 4108ms
> rtt min/avg/max/mdev = 0.059/0.062/0.074/0.007 ms
> [root@prod04 John]#
> [root@prod04 John]#
> [root@prod04 John]# ping 127.0.0.1
> PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
> 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.057 ms
> 64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.045 ms
> 64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.046 ms
> 64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.049 ms
> ^C
> --- 127.0.0.1 ping statistics ---
> 4 packets transmitted, 4 received, 0% packet loss, time 3066ms
> rtt min/avg/max/mdev = 0.045/0.049/0.057/0.006 ms
> [root@prod04 John]# ping 192.168.1.8   <--- current server as a network
> test
>
> PING 192.168.1.8 (192.168.1.8) 56(84) bytes of data.
> 64 bytes from 192.168.1.8: icmp_seq=1 ttl=64 time=1.77 ms
> 64 bytes from 192.168.1.8: icmp_seq=2 ttl=64 time=0.627 ms
> From 192.168.1.1: icmp_seq=2 Redirect Host(New nexthop: 192.168.1.8)
> 64 bytes from 192.168.1.8: icmp_seq=3 ttl=64 time=0.274 ms
> ^C
> --- 192.168.1.8 ping statistics ---
> 3 packets transmitted, 3 received, 0% packet loss, time 2003ms
> rtt min/avg/max/mdev = 0.274/0.890/1.771/0.639 ms
>
> --------------------------------------------

So what does /etc/hosts contain?

>
> Also, I am connected to the server by ssh so at least that IPv4 function is
> working.  (the router here only handles IPv4).
>
> I did add 5432 to the firewalld configuration and rebooted to pick it up.
>
> So, I think the network configuration is OK.  I set that up as the first task
> after installing the OS.
>
> John
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Wednesday 08 March 2017 17:11:16 Adrian Klaver wrote:
> On 03/08/2017 02:01 PM, John Iliffe wrote:
>
> Please use Reply All so others get your posts.
> Ccing list
>
> > On Wednesday 08 March 2017 16:40:03 you wrote:
> >> On 03/08/2017 01:28 PM, John Iliffe wrote:
> >>> -------------------------------
> >>> [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.
>
> For the list the below is from a previous message which I forgot to Cc.
>
> >> What if you shutdown the Postgres server and then su - postgres to
> >> and
> >
> >> run?:
> > I was starting postgres as user postgres; but it was still in my home
> > directory. Everything did start but I changed to /usr/pgsql_dataspaces
> > and the message went away.  Since there is no requirement to actually
> > cd to anything the message is a bit of a red herring.
> >
> > That said, I'm not sure how it will be handled by the system start up
> > task. I'll have to check when I add it.  These are unusual manual
> > (re)starts.
> >
> >> pg_ctl start -D /usr/pgsql_tablespaces
> >>
> >> Along that line what user 'owns' /usr/pgsql_tablespaces?
> >
> > /usr/pgsql_tablespaces is owned by postgres, group postgres, with
> > permissions drwe...... on the directory and rw....... on all the
> > files. This would seem correct to me.
> >
> >> Well this indicates the socket is working.
> >>
> >> What if you do?:
> >>
> >> psql -U postgres -h ::1
> >
> > [root@prod04 John]# psql -U postgres -h ::1
> > psql (9.6.2)
> > Type "help" for help.
>
> So Postgres is binding to IPv6.
>
> >> That is set in pg_hba.conf. The default is:
> >>
> >> # "local" is for Unix domain socket connections only
> >> local   all             all                                     trust
> >
> > Yes, I recall major changes to that file that I did on the current
> > server. But that was at least 5 years ago :-(
> >
> > I have that one fairly tightly locked down since this is a web server.
> >
> >>>>> lsof says that there is nothing assigned to postmaster at this
> >>>>> time.
>
> Given that you can connect I have to believe lsof would show something,
> so what options are you using with lsof and what user are you running it
> as?

[root@prod04 John]# lsof | grep postmaster
[root@prod04 John]#

No parameters so I get the whole list.  As noted postmaster doesn't find
anything whereas I think that I said that the hits on postgres are for the
table space, the libraries and such.  I did find the following just now for
postgres:

postgres  1991      postgres    5u     unix 0xffff9e53a16edc00       0t0
       63085 /tmp/.s.PGSQL.5432 type=STREAM

That said, the start up error still shows up, including on the run that I
extracted that from.

------------------------------
[postgres@prod04 John]$ cd /usr/pgsql_tablespaces
[postgres@prod04 pgsql_tablespaces]$ pg_ctl start -D /usr/pgsql_tablespaces
server starting
[postgres@prod04 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-08 23:05:14 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
--------------------------------
[postgres@prod04 pgsql_tablespaces]$ exit
exit
[root@prod04 John]# lsof -n | grep postmaster
[root@prod04 John]# lsof -n | grep postgres | grep 5432
postgres  2760      postgres    5u     unix 0xffff9e5390b5b800       0t0
         69422 /tmp/.s.PGSQL.5432 type=STREAM
[root@prod04 John]#
----------------------------------

This is a domain socket I think, in which case it still isn't getting the
IP socket for some reason.

Regards,

John

On Wednesday 08 March 2017 17:22:21 Adrian Klaver wrote:
> On 03/08/2017 01:48 PM, John Iliffe wrote:
> > On Wednesday 08 March 2017 15:13:29 Tom Lane wrote:
> >> 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
> >
> > Hi Tom:
> >
> > By now you have probably seen that I did get PSQL to run.  I had
> > assumed that meant that everything was at least running if not as
> > expected, at least running.  But I did an lsof and I don't get
> > anything at all for postmaster.  postgres has a lot of open files but
> > they are libraries and so forth so not applicable to this situation.
> >
> > I did the pings you suggest with the following results:
> >
> > -------------------------------------------
> > [root@prod04 John]# ping localhost
> > PING localhost(localhost (::1)) 56 data bytes
> > 64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.078 ms
> > 64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.060 ms
> > 64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.059 ms
> > 64 bytes from localhost (::1): icmp_seq=4 ttl=64 time=0.064 ms
> > 64 bytes from localhost (::1): icmp_seq=5 ttl=64 time=0.059 ms
> > ^C
> > --- localhost ping statistics ---
> > 5 packets transmitted, 5 received, 0% packet loss, time 4084ms
> > rtt min/avg/max/mdev = 0.059/0.064/0.078/0.007 ms
> > [root@prod04 John]#
> > [root@prod04 John]# ping ::1
> > PING ::1(::1) 56 data bytes
> > 64 bytes from ::1: icmp_seq=1 ttl=64 time=0.074 ms
> > 64 bytes from ::1: icmp_seq=2 ttl=64 time=0.059 ms
> > 64 bytes from ::1: icmp_seq=3 ttl=64 time=0.059 ms
> > 64 bytes from ::1: icmp_seq=4 ttl=64 time=0.060 ms
> > 64 bytes from ::1: icmp_seq=5 ttl=64 time=0.059 ms
> > ^C
> > --- ::1 ping statistics ---
> > 5 packets transmitted, 5 received, 0% packet loss, time 4108ms
> > rtt min/avg/max/mdev = 0.059/0.062/0.074/0.007 ms
> > [root@prod04 John]#
> > [root@prod04 John]#
> > [root@prod04 John]# ping 127.0.0.1
> > PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
> > 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.057 ms
> > 64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.045 ms
> > 64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.046 ms
> > 64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.049 ms
> > ^C
> > --- 127.0.0.1 ping statistics ---
> > 4 packets transmitted, 4 received, 0% packet loss, time 3066ms
> > rtt min/avg/max/mdev = 0.045/0.049/0.057/0.006 ms
> > [root@prod04 John]# ping 192.168.1.8   <--- current server as a
> > network test
> >
> > PING 192.168.1.8 (192.168.1.8) 56(84) bytes of data.
> > 64 bytes from 192.168.1.8: icmp_seq=1 ttl=64 time=1.77 ms
> > 64 bytes from 192.168.1.8: icmp_seq=2 ttl=64 time=0.627 ms
> > From 192.168.1.1: icmp_seq=2 Redirect Host(New nexthop: 192.168.1.8)
> > 64 bytes from 192.168.1.8: icmp_seq=3 ttl=64 time=0.274 ms
> > ^C
> > --- 192.168.1.8 ping statistics ---
> > 3 packets transmitted, 3 received, 0% packet loss, time 2003ms
> > rtt min/avg/max/mdev = 0.274/0.890/1.771/0.639 ms
> >
> > --------------------------------------------
>
> So what does /etc/hosts contain?
----------------------------
[root@prod04 John]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4
        localhost4.localdomain4
::1         localhost localhost.localdomain localhost6
        localhost6.localdomain6
192.168.1.8 prod03.coaxpublications.ca localhost
192.168.1.1 default
----------------------------

Fairly standard I should think.

There is no internal DNS server, I rely on the Internet DNS to resolve the
internal connections to the various named web servers but they all run on
the same instance of Apache anyway.

>
> > Also, I am connected to the server by ssh so at least that IPv4
> > function is working.  (the router here only handles IPv4).
> >
> > I did add 5432 to the firewalld configuration and rebooted to pick it
> > up.
> >
> > So, I think the network configuration is OK.  I set that up as the
> > first task after installing the OS.
> >
> > John

In case it is relevant, the postgresql.conf file is the default that is
generated by install.  I was unable to move to /var/run due to permission
problems.  I know how to resolve this but I don't want too many balls in
the air at once.

John

John Iliffe <john.iliffe@iliffe.ca> writes:
> [root@prod04 John]# lsof -n | grep postmaster
> [root@prod04 John]# lsof -n | grep postgres | grep 5432
> postgres  2760      postgres    5u     unix 0xffff9e5390b5b800       0t0
>          69422 /tmp/.s.PGSQL.5432 type=STREAM

That isn't proving a lot: as I showed in my example lsof output, Fedora's
lsof will map "5432" to "postgres" in the context of an IP port number.
(I'm sure there's a way to turn that off, but -n ain't it.)

            regards, tom lane

On Wednesday 08 March 2017 23:35:10 Tom Lane wrote:
> John Iliffe <john.iliffe@iliffe.ca> writes:
> > [root@prod04 John]# lsof -n | grep postmaster
> > [root@prod04 John]# lsof -n | grep postgres | grep 5432
> > postgres  2760      postgres    5u     unix 0xffff9e5390b5b800
> > 0t0
> >
> >          69422 /tmp/.s.PGSQL.5432 type=STREAM
>
> That isn't proving a lot: as I showed in my example lsof output,
> Fedora's lsof will map "5432" to "postgres" in the context of an IP
> port number. (I'm sure there's a way to turn that off, but -n ain't
> it.)
>
>             regards, tom lane

Yes, but your lsof output also showed a line for postmaster and mine
doesn't.  In your case postmaster has an IPv6 TCP socket (but no IPv4 I
notice) whereas mine has neither.

The postgres output seems to be equivalent.

As for lsof, I have searched the man page to try and find appropriate
parameters for when all I want to know about is IP sockets but to date no
joy.

The following is from ss, the new version of netstat:

------------------------------------
tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
tcp    LISTEN     0      128       ::1:postgres             :::*
------------------------------------

Unfortunately a request to resolve the socket numbers (ss -rl) fails.

What I was able to get is the SELinux context of these sockets but frankly,
it means nothing to me at his point.  With SELinux in permissive mode it
shouldn't be relevant, but just in case it helps you, here it is:

---------------------------------------
[root@prod04 John]# ss -aZ | grep post
u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 69422                 *
0                     users:
(("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=5))
udp    ESTAB      0      0         ::1:49481                   ::1:49481
users:
(("postgres",pid=2766,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2765,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2764,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2763,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2762,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8))
tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
users:
(("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=4))
tcp    LISTEN     0      128       ::1:postgres             :::*
users:
(("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=3))
-------------------------------------------

Regards,

John

John Iliffe <john.iliffe@iliffe.ca> writes:
> On Wednesday 08 March 2017 23:35:10 Tom Lane wrote:
>> That isn't proving a lot: as I showed in my example lsof output,
>> Fedora's lsof will map "5432" to "postgres" in the context of an IP
>> port number. (I'm sure there's a way to turn that off, but -n ain't
>> it.)

> Yes, but your lsof output also showed a line for postmaster and mine
> doesn't.

That's because I started mine by saying "postmaster" not "postgres".
It's not real relevant, just ancient habit of mine.

> In your case postmaster has an IPv6 TCP socket (but no IPv4 I
> notice)

Uh, what?  I showed an IPv6, an IPv4, and a Unix socket.

> The following is from ss, the new version of netstat:
> ------------------------------------
> tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
> tcp    LISTEN     0      128       ::1:postgres             :::*
> ------------------------------------

Well, that's pretty interesting, because it proves that *something* has
got IPv4 port 5432 open.  If not your manually-started postmaster, then
what?  You need to inquire into that a bit harder.  Running lsof as root
and examining all processes might help.

            regards, tom lane

On 03/08/2017 09:28 PM, Tom Lane wrote:
> John Iliffe <john.iliffe@iliffe.ca> writes:
>> On Wednesday 08 March 2017 23:35:10 Tom Lane wrote:
>>> That isn't proving a lot: as I showed in my example lsof output,
>>> Fedora's lsof will map "5432" to "postgres" in the context of an IP
>>> port number. (I'm sure there's a way to turn that off, but -n ain't
>>> it.)
>
>> Yes, but your lsof output also showed a line for postmaster and mine
>> doesn't.
>
> That's because I started mine by saying "postmaster" not "postgres".
> It's not real relevant, just ancient habit of mine.
>
>> In your case postmaster has an IPv6 TCP socket (but no IPv4 I
>> notice)
>
> Uh, what?  I showed an IPv6, an IPv4, and a Unix socket.
>
>> The following is from ss, the new version of netstat:
>> ------------------------------------
>> tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
>> tcp    LISTEN     0      128       ::1:postgres             :::*
>> ------------------------------------
>
> Well, that's pretty interesting, because it proves that *something* has
> got IPv4 port 5432 open.  If not your manually-started postmaster, then
> what?  You need to inquire into that a bit harder.  Running lsof as root
> and examining all processes might help.

Or using ss, something like:

sudo ss -l -p| grep post
u_str  LISTEN     0      128    /tmp/.s.PGSQL.5442 15355                 * 0
users:(("postmaster",pid=848,fd=5))
u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 15913                 * 0
users:(("postmaster",pid=849,fd=5))
tcp    LISTEN     0      128     *:postgresql            *:*                     users:(("postmaster",pid=849,fd=3))
tcp    LISTEN     0      128    127.0.0.1:5442                  *:*
users:(("postmaster",pid=848,fd=4))
tcp    LISTEN     0      128    :::postgresql           :::*                     users:(("postmaster",pid=849,fd=4))
tcp    LISTEN     0      128       ::1:5442                 :::*
users:(("postmaster",pid=848,fd=3))

>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Thursday 09 March 2017 09:17:51 Adrian Klaver wrote:
> On 03/08/2017 09:28 PM, Tom Lane wrote:
> > John Iliffe <john.iliffe@iliffe.ca> writes:
> >> On Wednesday 08 March 2017 23:35:10 Tom Lane wrote:
> >>> That isn't proving a lot: as I showed in my example lsof output,
> >>> Fedora's lsof will map "5432" to "postgres" in the context of an IP
> >>> port number. (I'm sure there's a way to turn that off, but -n ain't
> >>> it.)
> >>
> >> Yes, but your lsof output also showed a line for postmaster and mine
> >> doesn't.
> >
> > That's because I started mine by saying "postmaster" not "postgres".
> > It's not real relevant, just ancient habit of mine.
> >
> >> In your case postmaster has an IPv6 TCP socket (but no IPv4 I
> >> notice)
> >
> > Uh, what?  I showed an IPv6, an IPv4, and a Unix socket.
> >
> >> The following is from ss, the new version of netstat:
> >> ------------------------------------
> >> tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
> >> tcp    LISTEN     0      128       ::1:postgres             :::*
> >> ------------------------------------
> >
> > Well, that's pretty interesting, because it proves that *something*
> > has got IPv4 port 5432 open.  If not your manually-started
> > postmaster, then what?  You need to inquire into that a bit harder.
> > Running lsof as root and examining all processes might help.
>
> Or using ss, something like:
>
> sudo ss -l -p| grep post
----------------------------------
> (1) u_str  LISTEN     0      128    /tmp/.s.PGSQL.5442 15355
> * 0                     users:(("postmaster",pid=848,fd=5))
-----------------------------------
> (2)  u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 15913
* 0       users:(("postmaster",pid=849,fd=5))
----------------------------------
> (3)  tcp    LISTEN    > 0      128     *:postgresql      *:*
> users:(("postmaster",pid=849,fd=3))
-----------------------------------
> (4)  tcp    LISTEN     0      128    127.0.0.1:5442                  *:*
> users:(("postmaster",pid=848,fd=4))
-----------------------------------
> (5)  tcp    LISTEN     0      128   :::postgresql           :::*
> users:(("postmaster",pid=849,fd=4))
-----------------------------------
> (6)  tcp    LISTEN     0      128      ::1:5442                 :::*
> users:(("postmaster",pid=848,fd=3))
>
> >             regards, tom lane

Thanks folks.  I'm getting way out of my depth here.  My ss output is
below.

***I have reformatted Adrian's output above because word wrap made it
unreadable in this reply.  I also numbered the separate lines so I could
reference what I am saying**

Comparing my results with Adrian's example, I notice that we both have the
Unix domain socket 5432 running (1) and we both have an IPv4 and an IPv6
socket open for postgres (3) and (5) BUT he has a couple of TCP sockets
that I don't have.(4) and (6) that are assigned specifically to 5442 in his
case.  Shouldn't I have these same lines but assigned to 5432?

I'm assuming that there are two instances of postgresql running on his
machine (?) on sockets 5432 and 5442 (?).

Here is my matching ss output

------------------------------
[root@prod04 John]# ss -l -p | grep post
u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 69422                 *
0                     users:(("postgres",pid=2760,fd=5))
tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
users:(("postgres",pid=2760,fd=4))
tcp    LISTEN     0      128       ::1:postgres             :::*
users:(("postgres",pid=2760,fd=3))
[root@prod04 John]#
---------------------------------

I also stopped and restarted postgresql to see if SELinux picked up
anything on any tcp activity and it did not, so it doesn't seem to be the
culprit.

I ran netstat and grepped 5432 and no hits so nothing else has this socket.
Just as a test I changed the socket in postgresql.conf to 9876 and tried
again.  Same results as above.

[postgres@prod04 pgsql_tablespaces]$ LOG:  could not bind IPv4 socket:
Cannot assign requested address
HINT:  Is another postmaster already running on port 9876? If not, wait a
few seconds and retry.

Referring back to an earlier post, I changed the location for the domain
socket and lock file from /tmp to /var/run/postgres in the postgresql.conf
file and set the permissions so postgres could run.  Now psql complains that
the domain socket is not present.  There doesn't seem to be a config entry
or file for psql.  What did I miss?

John

John Iliffe <john.iliffe@iliffe.ca> writes:
> Here is my matching ss output

> ------------------------------
> [root@prod04 John]# ss -l -p | grep post
> u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 69422                 *
> 0                     users:(("postgres",pid=2760,fd=5))
> tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
> users:(("postgres",pid=2760,fd=4))
> tcp    LISTEN     0      128       ::1:postgres             :::*
> users:(("postgres",pid=2760,fd=3))
> [root@prod04 John]#
> ---------------------------------

Well, that's absolutely fascinating, because it proves that your
postmaster (PID 2760) *did* successfully bind to the IPv4 port,
along with IPv6 and Unix socket too.

So now we're left with the question of why you got this bleat
along the way:

> [postgres@prod04 pgsql_tablespaces]$ LOG:  could not bind IPv4 socket:
> Cannot assign requested address

AFAICS, the explanation must be that getaddrinfo() returned two IPv4
addresses, one of which got bound successfully and the other not.
The bleat is about the non-working address, but you still ended up
with a working IPv4 socket.

This recalls my earlier theory that there's something weird about
your networking configuration, but now we have a bit more information
about what the weirdness must look like.  It might be useful to have
a look at "ifconfig" output on your machine.

Also, are you setting "listen_addresses" to something non-default?

> Referring back to an earlier post, I changed the location for the domain
> socket and lock file from /tmp to /var/run/postgres in the postgresql.conf
> file and set the permissions so postgres could run.  Now psql complains that
> the domain socket is not present.  There doesn't seem to be a config entry
> or file for psql.  What did I miss?

Um ... that we were guessing that you were using a Red-Hat-supplied libpq.so
that expected the socket to be in /var/run/postgres.  Evidently that's
not the case.

(I will complain that you're still being absolutely terrible about
reporting error messages precisely.  I do not want to see you say again
that "program X complains something".  Please *copy and paste the exact
error message* from now on.  Even if the details don't seem significant
to you, they may be to us.)

            regards, tom lane

On Thursday 09 March 2017 12:08:01 Tom Lane wrote:
> John Iliffe <john.iliffe@iliffe.ca> writes:
> > Here is my matching ss output
> >
> > ------------------------------
> > [root@prod04 John]# ss -l -p | grep post
> > u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 69422
> >   * 0                     users:(("postgres",pid=2760,fd=5))
> > tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
> > users:(("postgres",pid=2760,fd=4))
> > tcp    LISTEN     0      128       ::1:postgres             :::*
> > users:(("postgres",pid=2760,fd=3))
> > [root@prod04 John]#
> > ---------------------------------
>
> Well, that's absolutely fascinating, because it proves that your
> postmaster (PID 2760) *did* successfully bind to the IPv4 port,
> along with IPv6 and Unix socket too.
>
> So now we're left with the question of why you got this bleat
>
> along the way:
> > [postgres@prod04 pgsql_tablespaces]$ LOG:  could not bind IPv4 socket:
> > Cannot assign requested address
>
> AFAICS, the explanation must be that getaddrinfo() returned two IPv4
> addresses, one of which got bound successfully and the other not.
> The bleat is about the non-working address, but you still ended up
> with a working IPv4 socket.
>
> This recalls my earlier theory that there's something weird about
> your networking configuration, but now we have a bit more information
> about what the weirdness must look like.  It might be useful to have
> a look at "ifconfig" output on your machine.
>
---------------------------------------------
[root@prod04 John]# ifconfig -a
enp0s31f6: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.6  netmask 255.255.255.255  broadcast 192.168.1.6
        inet6 fe80::62d4:f478:8bbb:34a1  prefixlen 64  scopeid 0x20<link>
        ether 38:d5:47:19:0d:cb  txqueuelen 1000  (Ethernet)
        RX packets 105284  bytes 128902991 (122.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 54500  bytes 5397679 (5.1 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        device interrupt 16  memory 0xf7000000-f7020000

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1  (Local Loopback)
        RX packets 7678  bytes 2703160 (2.5 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 7678  bytes 2703160 (2.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

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

> Also, are you setting "listen_addresses" to something non-default?
>
No, I made no change to the default listen address in postgresql.conf.  In
fact it is commented out.

[root@prod04 John]# cat /usr/pgsql_tablespaces/postgresql.conf | grep
listen
#listen_addresses = 'localhost'         # what IP address(es) to listen on;


> > Referring back to an earlier post, I changed the location for the
> > domain socket and lock file from /tmp to /var/run/postgres in the
> > postgresql.conf file and set the permissions so postgres could run.
> > Now psql complains that the domain socket is not present.  There
> > doesn't seem to be a config entry or file for psql.  What did I miss?
>
> Um ... that we were guessing that you were using a Red-Hat-supplied
> libpq.so that expected the socket to be in /var/run/postgres.
> Evidently that's not the case.
>
No, everything for Postgresql was installed as a separate downloaded
package from the PGSQL web site.

> (I will complain that you're still being absolutely terrible about
> reporting error messages precisely.  I do not want to see you say again
> that "program X complains something".  Please *copy and paste the exact
> error message* from now on.  Even if the details don't seem significant
> to you, they may be to us.)
>
My sincere apologies for this.  I appreciate the assistance and the time
that you and Adrian have spent so far.

>             regards, tom lane

John Iliffe <john.iliffe@iliffe.ca> writes:
> On Thursday 09 March 2017 12:08:01 Tom Lane wrote:
>> AFAICS, the explanation must be that getaddrinfo() returned two IPv4
>> addresses, one of which got bound successfully and the other not.
>> The bleat is about the non-working address, but you still ended up
>> with a working IPv4 socket.
>>
>> This recalls my earlier theory that there's something weird about
>> your networking configuration, but now we have a bit more information
>> about what the weirdness must look like.  It might be useful to have
>> a look at "ifconfig" output on your machine.

> [root@prod04 John]# ifconfig -a
> enp0s31f6: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
>         inet 192.168.1.6  netmask 255.255.255.255  broadcast 192.168.1.6
>         inet6 fe80::62d4:f478:8bbb:34a1  prefixlen 64  scopeid 0x20<link>
> ...
> lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
>         inet 127.0.0.1  netmask 255.0.0.0
>         inet6 ::1  prefixlen 128  scopeid 0x10<host>

Ah-hah.  You earlier showed your /etc/hosts file:

----------------------------
[root(at)prod04 John]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4
        localhost4.localdomain4
::1         localhost localhost.localdomain localhost6
        localhost6.localdomain6
192.168.1.8 prod03.coaxpublications.ca localhost
192.168.1.1 default
----------------------------

So given that, getaddrinfo is going to resolve "localhost" into the
set of addresses "127.0.0.1", "::1", and "192.168.1.8".  PG successfully
binds to the first two, which match your "lo" interface, while it fails
to bind to the last one because *that ain't the address on your ethernet
card*.  The postmaster will start anyway, as long as it was able to bind
to at least one of the addresses reported for "localhost", so the only
real damage is the confusing log message.

We could probably have saved a whole lot of time here if the "could not
bind" bleat would print out the specific address that it's failing to
bind to.  I have a really vague recollection of having wanted to do that
and not having found any portable library function for converting a
struct addrinfo to a string, but that was years if not decades ago.
We oughta try harder.

(BTW, it strikes me as pretty insecure to list a non-loopback address
as part of "localhost", but that's a different topic.)

            regards, tom lane

On Thursday 09 March 2017 12:49:49 Tom Lane wrote:
> John Iliffe <john.iliffe@iliffe.ca> writes:
> > On Thursday 09 March 2017 12:08:01 Tom Lane wrote:
> >> AFAICS, the explanation must be that getaddrinfo() returned two IPv4
> >> addresses, one of which got bound successfully and the other not.
> >> The bleat is about the non-working address, but you still ended up
> >> with a working IPv4 socket.
> >>
> >> This recalls my earlier theory that there's something weird about
> >> your networking configuration, but now we have a bit more information
> >> about what the weirdness must look like.  It might be useful to have
> >> a look at "ifconfig" output on your machine.
> >
> > [root@prod04 John]# ifconfig -a
> > enp0s31f6: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
> >
> >         inet 192.168.1.6  netmask 255.255.255.255  broadcast
> >         192.168.1.6 inet6 fe80::62d4:f478:8bbb:34a1  prefixlen 64
> >         scopeid 0x20<link>
> >
> > ...
> > lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
> >
> >         inet 127.0.0.1  netmask 255.0.0.0
> >         inet6 ::1  prefixlen 128  scopeid 0x10<host>
>
> Ah-hah.  You earlier showed your /etc/hosts file:
>
> ----------------------------
> [root(at)prod04 John]# cat /etc/hosts
> 127.0.0.1   localhost localhost.localdomain localhost4
>         localhost4.localdomain4
>
> ::1         localhost localhost.localdomain localhost6
>
>         localhost6.localdomain6
> 192.168.1.8 prod03.coaxpublications.ca localhost
> 192.168.1.1 default
> ----------------------------
>
> So given that, getaddrinfo is going to resolve "localhost" into the
> set of addresses "127.0.0.1", "::1", and "192.168.1.8".  PG successfully
> binds to the first two, which match your "lo" interface, while it fails
> to bind to the last one because *that ain't the address on your ethernet
> card*.  The postmaster will start anyway, as long as it was able to bind
> to at least one of the addresses reported for "localhost", so the only
> real damage is the confusing log message.
>
> We could probably have saved a whole lot of time here if the "could not
> bind" bleat would print out the specific address that it's failing to
> bind to.  I have a really vague recollection of having wanted to do that
> and not having found any portable library function for converting a
> struct addrinfo to a string, but that was years if not decades ago.
> We oughta try harder.
>
> (BTW, it strikes me as pretty insecure to list a non-loopback address
> as part of "localhost", but that's a different topic.)
>
>             regards, tom lane

Ohhhhh... me bad!

That was an inexcusable error on my part.  I put the prod03 route in the
wrong file and I'll have to look up where it should be  Not your problem of
course.   And, of course, it isn't localhost at all.  (this machine is
prod04)  Not sure when I did that.

Anyhow, deleted that entry and PostgreSQL started properly with no error
messages.

Here is the status:

start up messages:

server stopped
[postgres@prod04 pgsql_tablespaces]$ pg_ctl start -D /usr/pgsql_tablespaces
server starting
[postgres@prod04 pgsql_tablespaces]$ LOG:  database system was shut down at
2017-03-09 14:22:17 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

Thank you and Adrian for all the trouble and especially for the speed at
which you gave me answers.

Regards,

John

On 03/09/2017 08:14 AM, John Iliffe wrote:
> On Thursday 09 March 2017 09:17:51 Adrian Klaver wrote:
>> On 03/08/2017 09:28 PM, Tom Lane wrote:
>>> John Iliffe <john.iliffe@iliffe.ca> writes:

>
> Comparing my results with Adrian's example, I notice that we both have the
> Unix domain socket 5432 running (1) and we both have an IPv4 and an IPv6
> socket open for postgres (3) and (5) BUT he has a couple of TCP sockets
> that I don't have.(4) and (6) that are assigned specifically to 5442 in his
> case.  Shouldn't I have these same lines but assigned to 5432?
>
> I'm assuming that there are two instances of postgresql running on his
> machine (?) on sockets 5432 and 5442 (?).

My mistake, I should have mentioned there are indeed two instances of
Postgres running.

>
> Here is my matching ss output
>
> ------------------------------
> [root@prod04 John]# ss -l -p | grep post
> u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 69422                 *
> 0                     users:(("postgres",pid=2760,fd=5))
> tcp    LISTEN     0      128    127.0.0.1:postgres              *:*
> users:(("postgres",pid=2760,fd=4))
> tcp    LISTEN     0      128       ::1:postgres             :::*
> users:(("postgres",pid=2760,fd=3))
> [root@prod04 John]#
> ---------------------------------
>



--
Adrian Klaver
adrian.klaver@aklaver.com

On 03/08/2017 10:12 AM, John Iliffe wrote:
> 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.

/data would be your Postgres data directory.

In your new install you put that here:

/usr/pgsql_tablespaces

When you did the initdb it created the files there. So look there for
postgresql.conf and pg_hba.conf

>
> 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)?

No change those files follow the data directory.

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


--
Adrian Klaver
adrian.klaver@aklaver.com

On 03/08/2017 10:12 AM, John Iliffe wrote:
> 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?

Just realized this was an old post that just came through. I also
realized that my explanation of where the *.conf files are was specific
to a source default install. Package installs may locate them elsewhere.
In that case as a Postgres superuser(postgres for example) do:

test=# select * from pg_settings where sourcefile is not null;
  .....

name            | DateStyle
setting         | ISO, MDY
unit            | NULL
category        | Client Connection Defaults / Locale and Formatting
short_desc      | Sets the display format for date and time values.
extra_desc      | Also controls interpretation of ambiguous date inputs.
context         | user
vartype         | string
source          | configuration file
min_val         | NULL
max_val         | NULL
enumvals        | NULL
boot_val        | ISO, MDY
reset_val       | ISO, MDY
sourcefile      | /usr/local/pgsql/data/postgresql.conf
sourceline      | 538
pending_restart | f
  .....

And look for the sourcefile location.

>
> John



--
Adrian Klaver
adrian.klaver@aklaver.com

On Thursday 09 March 2017 16:37:28 you wrote:
> On 03/08/2017 10:12 AM, John Iliffe wrote:
> > 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?
>
> Just realized this was an old post that just came through. I also
> realized that my explanation of where the *.conf files are was specific
> to a source default install. Package installs may locate them elsewhere.
> In that case as a Postgres superuser(postgres for example) do:
>
> test=# select * from pg_settings where sourcefile is not null;
>   .....
>
> name            | DateStyle
> setting         | ISO, MDY
> unit            | NULL
> category        | Client Connection Defaults / Locale and Formatting
> short_desc      | Sets the display format for date and time values.
> extra_desc      | Also controls interpretation of ambiguous date inputs.
> context         | user
> vartype         | string
> source          | configuration file
> min_val         | NULL
> max_val         | NULL
> enumvals        | NULL
> boot_val        | ISO, MDY
> reset_val       | ISO, MDY
> sourcefile      | /usr/local/pgsql/data/postgresql.conf
> sourceline      | 538
> pending_restart | f
>   .....
>
> And look for the sourcefile location.
>
> > John


Thanks Adrian.

I had figured that out eventually, and found the missing files where you said
they were.

Appreciate the info on how to get the configuration values.  A lot seems to
have changed since I started using Postgresql and I guess I should really
read up on how things work now and not in 2008!

Regards,

John

On Friday 10 March 2017 09:25:25 Melvin Davidson wrote:
> On Thu, Mar 9, 2017 at 10:01 PM, John Iliffe <john.iliffe@iliffe.ca> wrote:
> > On Thursday 09 March 2017 16:37:28 you wrote:
> > > On 03/08/2017 10:12 AM, John Iliffe wrote:
> > > > 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?
> > >
> > > Just realized this was an old post that just came through. I also
> > > realized that my explanation of where the *.conf files are was
> > > specific to a source default install. Package installs may locate
> > > them elsewhere. In that case as a Postgres superuser(postgres for
> > > example) do:
> > >
> > > test=# select * from pg_settings where sourcefile is not null;
> > >
> > >   .....
> > >
> > > name            | DateStyle
> > > setting         | ISO, MDY
> > > unit            | NULL
> > > category        | Client Connection Defaults / Locale and Formatting
> > > short_desc      | Sets the display format for date and time values.
> > > extra_desc      | Also controls interpretation of ambiguous date
> > > inputs. context         | user
> > > vartype         | string
> > > source          | configuration file
> > > min_val         | NULL
> > > max_val         | NULL
> > > enumvals        | NULL
> > > boot_val        | ISO, MDY
> > > reset_val       | ISO, MDY
> > > sourcefile      | /usr/local/pgsql/data/postgresql.conf
> > > sourceline      | 538
> > > pending_restart | f
> > >
> > >   .....
> > >
> > > And look for the sourcefile location.
> > >
> > > > John
> >
> > Thanks Adrian.
> >
> > I had figured that out eventually, and found the missing files where
> > you said
> > they were.
> >
> > Appreciate the info on how to get the configuration values.  A lot
> > seems to have changed since I started using Postgresql and I guess I
> > should really read up on how things work now and not in 2008!
> >
> > 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
>
> *John,*
>
>
>
>
> *If you are really serious about reading up, I suggest you pick from the
> books on this
> url:https://www.packtpub.com/all-books?search=&offset=84&rows=&sort=
> <https://www.packtpub.com/all-books?search=&offset=84&rows=&sort=>*
>
> *Probably "PostgreSQL Administration Essentials" would be good to start
> with.*
Thank you.  I was unaware of this source.

I have a similar book but it is about 8 years old so probably out of date.

John

On 03/10/2017 07:08 AM, John Iliffe wrote:
> On Friday 10 March 2017 09:25:25 Melvin Davidson wrote:

>> *Probably "PostgreSQL Administration Essentials" would be good to start
>> with.*
> Thank you.  I was unaware of this source.
>
> I have a similar book but it is about 8 years old so probably out of date.

Two resources that help me keep within shouting distance of the latest
and greatest:

Hubert 'depesz' Lubaczewski
Waiting for PostgreSQL * series:

https://www.depesz.com/
In the search box type 'Waiting for'

The pgsql-announce list:

http://www.postgresql.org/mailpref/pgsql-announce

In particular the PostgreSQL Weekly News that comes out Sunday or Monday
from David Fetter.

>
> John
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Sat, Mar 11, 2017 at 12:15 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> Two resources that help me keep within shouting distance of the latest and
> greatest:
>
> Hubert 'depesz' Lubaczewski
> Waiting for PostgreSQL * series:
>
> https://www.depesz.com/
> In the search box type 'Waiting for'
>
> The pgsql-announce list:
>
> http://www.postgresql.org/mailpref/pgsql-announce
>
> In particular the PostgreSQL Weekly News that comes out Sunday or Monday
> from David Fetter.

Ans also Planet Postgres is a good resource on the matter, it gathers
posts of many blogs from hackers and users:
https://planet.postgresql.org/
--
Michael