Can't connect to database by pgcli

Docker Hub
1 
docker run -it \ -e POSTGRES_USER="root" \ -e POSTGRES_PASSWORD="root" \ -e POSTGRES_DB="ny_taxi" \ -v $(pwd)/ny_taxi_postgres_data:/var/lib/postgresql/data \ -p 5432:5432 \ postgres:13

Here is the command that I use to create database.
When container has been created, another folder with similar name appears on my directory. Also, there is no file in my “ny_taxi_postgres_data” directory.

image
“ny_taxi_postgres_data;C” is the duplicate directory.

Then I try to access to the database by using pgcli with this command, it gives me this result.

$ pgcli -h localhost -p 5432 -u root -d ny_taxi
Password for root:
connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "root"

My current container and network

$ docker ps
CONTAINER ID   IMAGE         COMMAND                  CREATED              STATUS              PORTS                    NAMES
a510958c25ba   postgres:13   "docker-entrypoint.s…"   About a minute ago   Up About a minute   0.0.0.0:5432->5432/tcp   condescending_feistel

$ docker network ls
NETWORK ID     NAME      DRIVER    SCOPE
066e01d5e304   bridge    bridge    local
f896e9e41670   host      host      local
3fd1189d34cc   none      null      local

Also, when there are no error on my logs when I try to access to database via pgcli.
As if my command can’t reach to database.

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2022-08-13 09:33:05.185 UTC [48] LOG:  starting PostgreSQL 13.8 (Debian 13.8-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-08-13 09:33:05.193 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-08-13 09:33:05.224 UTC [49] LOG:  database system was shut down at 2022-08-13 09:33:02 UTC
2022-08-13 09:33:05.236 UTC [48] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down...2022-08-13 09:33:06.147 UTC [48] LOG:  received fast shutdown request
.2022-08-13 09:33:06.156 UTC [48] LOG:  aborting any active transactions
2022-08-13 09:33:06.157 UTC [48] LOG:  background worker "logical replication launcher" (PID 55) exited with exit code 1
2022-08-13 09:33:06.157 UTC [50] LOG:  shutting down
2022-08-13 09:33:06.215 UTC [48] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2022-08-13 09:33:06.281 UTC [1] LOG:  starting PostgreSQL 13.8 (Debian 13.8-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-08-13 09:33:06.281 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-08-13 09:33:06.281 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2022-08-13 09:33:06.300 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-08-13 09:33:06.320 UTC [62] LOG:  database system was shut down at 2022-08-13 09:33:06 UTC
2022-08-13 09:33:06.332 UTC [1] LOG:  database system is ready to accept connections
1 Like
2

It works for me. Below, I am using host.docker.internal to connect to the host’s localhost from within the container in which I install pgcli, to allow it to connect to the database container like if I were connecting from the host directly. (I hope Docker is not being smart about that.)

But otherwise it should work the same:

docker run -it --rm python /bin/bash

root@fea386fdb294:/# pip install pgcli
<log removed>

root@fea386fdb294:/# pgcli -h host.docker.internal -p 5432 -u root -d ny_taxi
Password for root:
Server: PostgreSQL 13.8 (Debian 13.8-1.pgdg110+1)
Version: 3.4.1
Home: http://pgcli.com

root@host:ny_taxi> \q
Goodbye!

root@fea386fdb294:/# exit

Rather than using host.docker.internal, I could also have used --network="host" along with locahost, like:

docker run -it --rm --network="host" python /bin/bash
root@docker-desktop:/# pip install pgcli
<log removed>

root@docker-desktop:/# pgcli -h localhost -p 5432 -u root -d ny_taxi
Password for root: 
Server: PostgreSQL 13.8 (Debian 13.8-1.pgdg110+1)
Version: 3.4.1
Home: http://pgcli.com
root@localhost:ny_taxi> \q
Goodbye!

root@docker-desktop:/# exit

For the latter, using the wrong password gives me the same error like you saw when using pgcli as installed on the host:

root@docker-desktop:/# pgcli -h localhost -p 5432 -u root -d ny_taxi
Password for root: 
could not connect to server: Connection refused
	Is the server running on host "localhost" (::1) and accepting
	TCP/IP connections on port 5432?
FATAL:  password authentication failed for user "root"

For the first option, on macOS with the latest Docker Desktop, the error is a bit different:

root@fea386fdb294:/# pgcli -h host.docker.internal -p 5432 -u root -d ny_taxi
Password for root: 
FATAL:  password authentication failed for user "root"

Regardless, both work fine when using the proper credentials. So, I’d say that using pgcli installed on the host (rather than a temporary container just for my testing), should work when providing the correct password.

Aside, it seems you somehow copied a multi-line command into a single-line command, but still have the \ line continuation characters in there, which are now escaping the spaces that follow those. Should not cause your problem though.

1 Like
3

I missed that edit. Indeed, I do get errors when providing the wrong password, for both ways I showed with a temporary container for pgcli above:

2022-08-13 10:16:42.365 UTC [172] FATAL:  password authentication failed for user "root"
2022-08-13 10:16:42.365 UTC [172] DETAIL:  Password does not match for user "root".
	Connection matched pg_hba.conf line 99: "host all all all md5"

Shutting down the database container, gets me no prompt for the password at all. Like when using host.docker.internal (without --network="host" in the docker run command):

root@80ac6bee9da5:/# pgcli -h host.docker.internal -p 5432 -u root -d ny_taxi
could not connect to server: Connection refused
	Is the server running on host "host.docker.internal" (192.168.65.2) and accepting
	TCP/IP connections on port 5432?

And for localhost along with --network="host":

root@docker-desktop:/# pgcli -h localhost -p 5432 -u root -d ny_taxi
could not connect to server: Connection refused
	Is the server running on host "localhost" (::1) and accepting
	TCP/IP connections on port 5432?
could not connect to server: Connection refused
	Is the server running on host "localhost" (127.0.0.1) and accepting
	TCP/IP connections on port 5432?

So, I’d say that seeing the password prompt implies that pgcli can connect to the server.

Aside, often servers (databases, web servers) by default only allow connections from localhost, and without --network="host" the host’s localhost is simply not the same as the container’s localhost. However, using the docker run command you showed in your first post seems to indicate that such is not the case for PostgreSQL, unless Docker is being smart about that when I use pgcli from another container.

Any chance you have another PostgreSQL server running on your local machine? (And have missed errors about publishing port 5432 failing as it was already in use?)

Also, you may want to repeat my tests, to use the latest pgcli:

root@docker-desktop:/# pgcli --version
Version: 3.4.1
1 Like
4

As you can see from my docker desktop, There are no PostgreSQL server running on my local machine.

image
image1258×677 37 KB

My pgcli is the same version as you.

What about my local directory which I try to mount with directory inside the container.
I think it’s supposed to have some file in the directory isn’t it?

Or should I try to connect to database via pgAdmin4 instead.

5

Docker is not the only way a development machine could be running PostgreSQL, but I’m sure you would have known. :slight_smile:

Yes, sorry I missed those details from your first post. So, weird things in your setup:

  • On your local machine the ny_taxi_postgres_data folder is empty
  • There is an additional ny_taxi_postgres_data;C folder
  • No errors in the logging

Any data in the second folder? Any error in the logging when explicitly using a bad password?

Are you on Windows? Any chance the local path has spaces in it? Then quote the local path of the bind mount. That is, something like: -v "$(pwd)/ny_taxi_postgres_data":/var/lib/postgresql/data (untested).

If that does not help: any virus scanner you can disable when trying again? Can you use docker exec -it <container name> /bin/sh to go into the container and see if ls -l /var/lib/postgresql/data shows any data? (There is also a clickable button in Docker Desktop next to a running container, to achieve that.)

I don’t think that changes anything. Did you try to repeat my test commands?

In case it helps, my full logs below.

Log on macOS 
% docker run -it -e POSTGRES_USER="root" -e POSTGRES_PASSWORD="root" -e POSTGRES_DB="ny_taxi" -v $(pwd)/ny_taxi_postgres_data:/var/lib/postgresql/data -p 5432:5432 postgres:13

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2022-08-14 08:00:50.146 UTC [49] LOG:  starting PostgreSQL 13.8 (Debian 13.8-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-08-14 08:00:50.161 UTC [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-08-14 08:00:50.310 UTC [50] LOG:  database system was shut down at 2022-08-14 08:00:36 UTC
2022-08-14 08:00:50.402 UTC [49] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

2022-08-14 08:01:01.849 UTC [49] LOG:  received fast shutdown request
waiting for server to shut down...2022-08-14 08:01:01.874 UTC [49] LOG:  aborting any active transactions
2022-08-14 08:01:01.886 UTC [49] LOG:  background worker "logical replication launcher" (PID 56) exited with exit code 1
2022-08-14 08:01:01.891 UTC [51] LOG:  shutting down
.2022-08-14 08:01:02.143 UTC [49] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2022-08-14 08:01:02.507 UTC [1] LOG:  starting PostgreSQL 13.8 (Debian 13.8-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-08-14 08:01:02.508 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-08-14 08:01:02.508 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2022-08-14 08:01:02.539 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-08-14 08:01:02.761 UTC [63] LOG:  database system was shut down at 2022-08-14 08:01:02 UTC
2022-08-14 08:01:02.846 UTC [1] LOG:  database system is ready to accept connections
Files 
# Same on local machine:
ls -l ny_taxi_postgres_data 

# ...and in container:
ls -l /var/lib/postgresql/data

total 112
-rw-------   1 arjan  staff      3 Aug 14 09:59 PG_VERSION
drwx------   6 arjan  staff    192 Aug 14 10:00 base
drwx------  60 arjan  staff   1920 Aug 14 10:02 global
drwx------   2 arjan  staff     64 Aug 14 09:59 pg_commit_ts
drwx------   2 arjan  staff     64 Aug 14 09:59 pg_dynshmem
-rw-------   1 arjan  staff   4782 Aug 14 10:00 pg_hba.conf
-rw-------   1 arjan  staff   1636 Aug 14 09:59 pg_ident.conf
drwx------   5 arjan  staff    160 Aug 14 10:01 pg_logical
drwx------   4 arjan  staff    128 Aug 14 09:59 pg_multixact
drwx------   2 arjan  staff     64 Aug 14 09:59 pg_notify
drwx------   2 arjan  staff     64 Aug 14 09:59 pg_replslot
drwx------   2 arjan  staff     64 Aug 14 09:59 pg_serial
drwx------   2 arjan  staff     64 Aug 14 09:59 pg_snapshots
drwx------   2 arjan  staff     64 Aug 14 10:01 pg_stat
drwx------   5 arjan  staff    160 Aug 14 10:04 pg_stat_tmp
drwx------   3 arjan  staff     96 Aug 14 09:59 pg_subtrans
drwx------   2 arjan  staff     64 Aug 14 09:59 pg_tblspc
drwx------   2 arjan  staff     64 Aug 14 09:59 pg_twophase
drwx------   4 arjan  staff    128 Aug 14 09:59 pg_wal
drwx------   3 arjan  staff     96 Aug 14 09:59 pg_xact
-rw-------   1 arjan  staff     88 Aug 14 09:59 postgresql.auto.conf
-rw-------   1 arjan  staff  28156 Aug 14 09:59 postgresql.conf
-rw-------   1 arjan  staff     36 Aug 14 10:01 postmaster.opts
-rw-------   1 arjan  staff     94 Aug 14 10:01 postmaster.pid
6

Maybe an easier first test:

What do you get when not using any bind mount for the data at all? Not recommended, as the data will be stored in the ephemeral container, hence will be gone when the container is deleted. Below, the container is even deleted right after stopping it, due to the --rm option. Regardless, it will allow you to test the connection:

docker run --rm --name "pg13-test" -it -e POSTGRES_USER="root" -e POSTGRES_PASSWORD="root" -e POSTGRES_DB="dummy" -p 5432:5432 postgres:13

Above, I also added a name, --name "pg13-test" So, in another terminal window, running the following will show you the data is still in the container (but that container’s /var/lib/postgresql/data is not bound to some local folder):

docker exec pg13-test ls -l /var/lib/postgresql/data

If your original problem is caused by the -v bind mount, then the above should allow connections just fine, and should also log errors when providing the wrong password:

pgcli -h localhost -p 5432 -u root -d dummy

The setup does not get much simpler than this. So, if that does not work then I’d guess some virus scanner is to blame.

7

The problem is the port.

Because I have pgAdmin4 using my 5432 port.

So when I try to create PostgreSQL again, it can’t connect
because 5432 port has been using for the entire time.

It took me 2 days to realize the root cause.
I’m so dumb HAHAHAHA

Anyway, thank you so much for your help.
I truly appreciate it.

8

Hmmm, glad things are working now. However, as far as I know pgAdmin is a client application that connects to a server on (default) server port 5432, but itself does not use that port. Instead, on the client side it will use a random port (often a very high number). Also, multiple clients should be able to connect to the same server port 5432.

Also, blaming pgAdmin for keeping the port occupied does not explain the two folders you were seeing, one being empty, the other having a weird name.

So, sorry to disappoint you, but I’m afraid you may not have solved all problems yet. Above all: make sure that data is stored on your local machine. Not too important for some Getting Started, but in a real setup, failing to map the container’s /var/lib/postgresql/data to a persistent location will make you lose your data.

9

I had the same issue. I first stopped PGAdmin which was running on my laptop. That didn’t solve my issue. I had to stop the postres service under windows services. Using windows power shell also helps

10

Just restart the container or your PC. You might as well need stop postgresql running on you local machine. This worked for me

11

I restarted but still getting same error connection failed: password authentication failed for user "root"