Postgres Pro
Downloads
Home   >   mailing lists

Re: "Failed to connect to Postgres database" - Mailing list pgsql-general

From Marco Ippolito
Subject Re: "Failed to connect to Postgres database"
Date
Msg-id CAFegzBRWs4TdvCz12Cw6ftEUbForA7mkAmZgw9s6-F6VJfieNw@mail.gmail.com
Whole thread Raw
In response to Re: "Failed to connect to Postgres database"  (rob stone <floriparob@gmail.com>)
Responses Re: "Failed to connect to Postgres database"
List pgsql-general
Tree view
I need to understand this:

I dropped the previous fabmnet cluster and re-created a new one:

(base) postgres@pc:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory                 Log file
11  fabmnet 5433 online postgres /var/lib/postgresql/11/fabmnet /var/log/postgresql/postgresql-11-fabmnet.log
11  main    5432 online postgres /var/lib/postgresql/11/main    /var/log/postgresql/postgresql-11-main.log

I can connect with SSL to cluster 11/main:

    (base) postgres@pc:~$ psql -h localhost
    Password for user postgres:
    psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
    SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
    Type "help" for help.

    postgres=# \l
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  
    -    ----------+----------+----------+-------------+-------------+-----------------------
     marco     | marco    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
     postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
     template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
    (4 rows)

    postgres=# \conninfo
    You are connected to database "postgres" as user "postgres" on host "localhost" at port "5432".
    SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
    postgres=#


(base) postgres@pc:/var/lib/postgresql/11/main$ ls -lah
total 92K
drwx------ 19 postgres postgres 4.0K Sep 27 09:38 .
drwxr-xr-x  4 postgres postgres 4.0K Sep 27 10:58 ..
-rw-------  1 postgres postgres    3 Sep 18 16:22 PG_VERSION
drwx------  6 postgres postgres 4.0K Sep 27 11:57 base
drwx------  2 postgres postgres 4.0K Sep 27 09:39 global
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_commit_ts
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_dynshmem
drwx------  4 postgres postgres 4.0K Sep 27 11:57 pg_logical
drwx------  4 postgres postgres 4.0K Sep 18 16:22 pg_multixact
drwx------  2 postgres postgres 4.0K Sep 27 09:38 pg_notify
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_replslot
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_serial
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_snapshots
drwx------  2 postgres postgres 4.0K Sep 27 09:38 pg_stat
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_stat_tmp
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_subtrans
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_tblspc
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_twophase
drwx------  3 postgres postgres 4.0K Sep 18 16:22 pg_wal
drwx------  2 postgres postgres 4.0K Sep 18 16:22 pg_xact
-rw-------  1 postgres postgres   88 Sep 18 16:22 postgresql.auto.conf
-rw-------  1 postgres postgres  130 Sep 27 09:38 postmaster.opts
-rw-------  1 postgres postgres  108 Sep 27 09:38 postmaster.pid


(base) marco@pc:~$ ps ax | grep post
 1030 ?        S      0:00 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
 1107 ?        Ss     0:00 postgres: 11/main: checkpointer  
 1108 ?        Ss     0:00 postgres: 11/main: background writer  
 1109 ?        Ss     0:00 postgres: 11/main: walwriter  
 1110 ?        Ss     0:00 postgres: 11/main: autovacuum launcher  
 1111 ?        Ss     0:00 postgres: 11/main: stats collector  
 1112 ?        Ss     0:00 postgres: 11/main: logical replication launcher  
 5003 ?        S      0:00 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/fabmnet -c config_file=/etc/postgresql/11/fabmnet/postgresql.conf
 5005 ?        Ss     0:00 postgres: 11/fabmnet: checkpointer  
 5006 ?        Ss     0:00 postgres: 11/fabmnet: background writer  
 5007 ?        Ss     0:00 postgres: 11/fabmnet: walwriter  
 5008 ?        Ss     0:00 postgres: 11/fabmnet: autovacuum launcher  
 5009 ?        Ss     0:00 postgres: 11/fabmnet: stats collector  
 5010 ?        Ss     0:00 postgres: 11/fabmnet: logical replication launcher  
 6543 pts/1    S      0:00 sudo -su postgres
 6867 pts/1    S+     0:00 /usr/lib/postgresql/11/bin/psql -h localhost
 6878 ?        Ss     0:00 postgres: 11/main: postgres postgres ::1(49156) idle
 6883 pts/0    S      0:00 sudo -su postgres
 8167 pts/2    S+     0:00 grep --color=auto post


Bu I cannot do the same with cluster 11/fabmnet :

(base) postgres@pc:/var/lib/postgresql/11/fabmnet$ ls -lah
total 104K
drwx------ 19 postgres postgres 4.0K Sep 27 11:28 .
drwxr-xr-x  4 postgres postgres 4.0K Sep 27 10:58 ..
-rw-------  1 postgres postgres    3 Sep 27 10:58 PG_VERSION
drwx------  6 postgres postgres 4.0K Sep 27 11:40 base
drwx------  2 postgres postgres 4.0K Sep 27 11:13 global
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_commit_ts
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_dynshmem
drwx------  4 postgres postgres 4.0K Sep 27 11:45 pg_logical
drwx------  4 postgres postgres 4.0K Sep 27 10:58 pg_multixact
drwx------  2 postgres postgres 4.0K Sep 27 10:59 pg_notify
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_replslot
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_serial
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_snapshots
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_stat
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_stat_tmp
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_subtrans
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_tblspc
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_twophase
drwx------  3 postgres postgres 4.0K Sep 27 10:58 pg_wal
drwx------  2 postgres postgres 4.0K Sep 27 10:58 pg_xact
-rw-------  1 postgres postgres   88 Sep 27 10:58 postgresql.auto.conf
-rw-------  1 postgres postgres  136 Sep 27 10:59 postmaster.opts
-rw-------  1 postgres postgres  111 Sep 27 10:59 postmaster.pid
-rw-------  1 root     root     4.4K Sep 27 11:26 server.crt
-rw-------  1 root     root     1.7K Sep 27 11:26 server.key

I created the server.crt and server.key files following the indications found here: https://vibhork.blogspot.com/2011/07/how-to-enable-ssl-in-postgresqlppas.html

(base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

So, before passing to the fabric-ca side , I need to solve this aspect.

Marco


Il giorno ven 27 set 2019 alle ore 04:28 rob stone <floriparob@gmail.com> ha scritto:
Hi,

On Thu, 2019-09-26 at 16:21 +0200, Marco Ippolito wrote:
>
> db:
>   type: postgres
>   datasource: host=localhost port=5433 user=fabmnet_admin   
>   password=password dbname=fabmnetdb sslmode=verify-full
>

>
> (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
> admin:adminpw
> 2019/09/26 15:56:50 [INFO] Configuration file location: /home/marco
> /fabric/fabric-ca/fabric-ca-server-config.yaml
> 2019/09/26 15:56:50 [INFO] Starting server in home directory:
> /home/marco/fabric/fabric-ca
> 2019/09/26 15:56:50 [INFO] Server Version: 1.4.4
> 2019/09/26 15:56:50 [INFO] Server Levels: &{Identity:2 Affiliation:1
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/26 15:56:50 [INFO] The CA key and certificate already exist
> 2019/09/26 15:56:50 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/26 15:56:50 [INFO] The certificate is at: /home/marco/fabric
> /fabric-ca/ca-cert.pem
> 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> 'fabmnetdb'
> 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> 'postgres'
> 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> 'template1'
> 2019/09/26 15:56:50 [ERROR] Error occurred initializing database:
> Failed
> to connect to Postgres database. Postgres requires connecting to a
> specific database, the following databases were tried: [fabmnetdb
> postgres template1]. Please create one of these database before
> continuing


Why is it trying to connect to *any* database?

In the fabric-ca docs it shows the connection string as a single line
but your configuration file has it split over two lines.
My uneducated guess is that it is ignoring the 'password=password
dbname=fabmnetdb sslmode=verify-full'
line and thus unable to connect to fabmnetdb.

Cheers,
Robert


pgsql-general by date:

Previous
From: Luca Ferrari
Date:
Subject: incoherent dead tuples between pg_stat_user_tables and pgstattuple?
Next
From: Luca Ferrari
Date:
Subject: Re: pgq is one of the most underrated pg related stuff