Re: Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running - Mailing list pgsql-hackers

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Uh, I still cannot reproduce the failure:
>
> I would imagine you need -w option on the start.  The whole issue
> here is whether start's wait-for-server-start code works.

Thanks, I am now able to reproduce this.  I was able to get this to
report the .pgpass problem:

    $ psql postgres
    psql: FATAL:  password authentication failed for user "postgres"
    password retrieved from file "/u/postgres/.pgpass"

    $ pg_ctl stop
    waiting for server to shut down.... done
    server stopped

    $ pg_ctl -w -l /dev/null start
    waiting for server to start....FATAL:  password authentication failed
    for user "postgres"
    password retrieved from file "/u/postgres/.pgpass"
    .FATAL:  password authentication failed for user "postgres"
    password retrieved from file "/u/postgres/.pgpass"
    .FATAL:  password authentication failed for user "postgres"
    password retrieved from file "/u/postgres/.pgpass"
    .^C

I basically report the connection error string if it starts with "FATAL:".

I originally tried to check for an ERRCODE_INVALID_PASSWORD error field
(see // comments), but it seems there is no way to access this, i.e.
PQgetResult(conn) on a connection failure is always NULL.

Anyway, perhaps FATAL is a better test because it will report any major
failure, not just a .pgpass one.

Patch attached.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/src/bin/pg_ctl/pg_ctl.c b/src/bin/pg_ctl/pg_ctl.c
index 14d36b5..df71c16 100644
*** a/src/bin/pg_ctl/pg_ctl.c
--- b/src/bin/pg_ctl/pg_ctl.c
*************** typedef enum
*** 70,75 ****
--- 70,78 ----
  } CtlCommand;

  #define DEFAULT_WAIT    60
+ //
+ ///* This is part of the protocol so just define it */
+ //#define ERRCODE_INVALID_PASSWORD "28P01"

  static bool do_wait = false;
  static bool wait_set = false;
*************** test_postmaster_connection(bool do_check
*** 511,516 ****
--- 514,523 ----
          if ((conn = PQconnectdb(connstr)) != NULL &&
              (PQstatus(conn) == CONNECTION_OK ||
               PQconnectionNeedsPassword(conn)))
+ //            /* only works with >= 9.0 servers */
+ //            (PQgetResult(conn) &&
+ //            strcmp(PQresultErrorField(PQgetResult(conn), PG_DIAG_SQLSTATE),
+ //               ERRCODE_INVALID_PASSWORD) == 0)))
          {
              PQfinish(conn);
              success = true;
*************** test_postmaster_connection(bool do_check
*** 518,523 ****
--- 525,533 ----
          }
          else
          {
+             /* report fatal errors like invalid .pgpass passwords */
+             if (strncmp(PQerrorMessage(conn), "FATAL:", strlen("FATAL:")) == 0)
+                 fputs(PQerrorMessage(conn), stderr);
              PQfinish(conn);

  #if defined(WIN32)