PostgreSQL error on Docker

ibrahimates · February 9, 2019, 6:48pm

Hello All,

I have a problem. When I want to run my project on Docker, I got following error. After error I ve pulled postgresql image from docker hub, but error still continues.

Error;

org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Then after I run this code; docker run -p 5432:5432 postgres , I got following warning;

docker: Error response from daemon: driver failed programming external connectivity on 
endpoint stupefied_dijkstra (3cc2b6e5ae498132ccef04b1b0f5eaa12ed335eb927f84644ac36142683dbecb): Bind for 0.0.0.0:5432 failed: port is already allocated.

I did following way to solve error but still continues.

docker run -p 5433:5432 postgres . Then access postgres from the newly defined port 5433 .

I checked the running containers using docker ps and then I stopped 5432 port.

Help please. Thank you…

gforghetti · February 9, 2019, 7:49pm

Hi.

Not enough details in your post for me to debug your issue.

From your first error message some other another container or another Linux process has port 5432 already open. You can run the docker container ps --filter publish=5432 to see if another container has the port open.
If another container does not have port 5432 open, you can issue this command to see which Linux process has it open: sudo netstat -tlnp | awk '/:5432 */ {print}'

Anyway, I got a postgres docker container to run successfully and I was able to login to the server and run a command. Follow what I did below.

Example:

I ran a postgres container from the Docker Official postgres docker image, detached in the background and specify a password for the postgres user.

🐳 gforghetti:[~] $ docker run --detach --name my-postgres-server --env POSTGRES_PASSWORD=mysecretpassword --publish 5432:5432 postgres:latest
c3a94437c7d72288a9b31cde7b4f306f9692acfadef668fab2a1e5a333deec1f

I display the container(s). The postgrescontainer is up and running.

🐳 gforghetti:[~] $ docker container ls
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
c3a94437c7d7        postgres:latest     "docker-entrypoint.s…"   17 seconds ago      Up 17 seconds       0.0.0.0:5432->5432/tcp   my-postgres-server

I display the processes in the postgres container.

🐳 gforghetti:[~] $ docker container top my-postgres-server
PID                 USER                TIME                COMMAND
13961               999                 0:00                postgres
14054               999                 0:00                postgres: checkpointer
14055               999                 0:00                postgres: background writer
14056               999                 0:00                postgres: walwriter
14057               999                 0:00                postgres: autovacuum launcher
14058               999                 0:00                postgres: stats collector
14059               999                 0:00                postgres: logical replication launcher

I display the postgres container logs

🐳 gforghetti:[~] $ docker container logs my-postgres-server
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 default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

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


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.
waiting for server to start....2019-02-09 19:33:55.751 UTC [43] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-02-09 19:33:55.763 UTC [44] LOG:  database system was shut down at 2019-02-09 19:33:55 UTC
2019-02-09 19:33:55.767 UTC [43] LOG:  database system is ready to accept connections
 done
server started

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

waiting for server to shut down....2019-02-09 19:33:55.843 UTC [43] LOG:  received fast shutdown request
2019-02-09 19:33:55.845 UTC [43] LOG:  aborting any active transactions
2019-02-09 19:33:55.847 UTC [43] LOG:  background worker "logical replication launcher" (PID 50) exited with exit code 1
2019-02-09 19:33:55.848 UTC [45] LOG:  shutting down
2019-02-09 19:33:55.861 UTC [43] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2019-02-09 19:33:55.955 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-02-09 19:33:55.955 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2019-02-09 19:33:55.958 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-02-09 19:33:55.969 UTC [52] LOG:  database system was shut down at 2019-02-09 19:33:55 UTC
2019-02-09 19:33:55.974 UTC [1] LOG:  database system is ready to accept connections

I check to see if the postgress port 5532 is open and not blocked.
I have the nmap command installed on my docker node. It is very useful.

Port 5432 is open.

🐳 gforghetti:[~] $ nmap -p 5432 -Pn 127.0.0.1
Starting Nmap 7.70 ( https://nmap.org ) at 2019-02-09 14:34 EST
Nmap scan report for localhost (127.0.0.1)
Host is up (0.00020s latency).

PORT     STATE SERVICE
5432/tcp open  postgresql

Nmap done: 1 IP address (1 host up) scanned in 0.03 seconds

I can login to the postgres server and display the server version (will be prompted for the password).

🐳 gforghetti:[~] $ psql -h 127.0.0.1 -p 5432 -U postgres -W -c "SELECT version();"
Password:
                                                             version
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 (Debian 11.1-3.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

I can display which container has port 5432 open.

🐳 gforghetti:[~] $ docker container ps --filter publish=5432
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
c3a94437c7d7        postgres:latest     "docker-entrypoint.s…"   4 minutes ago       Up 4 minutes        0.0.0.0:5432->5432/tcp   my-postgres-server

ibrahimates · February 9, 2019, 8:00pm

Hi Forghetti,

I did same thing like following;
1- $ docker run --detach --name postgre --env POSTGRES_PASSWORD=xxxx --publish 5432:5432 postgres:latest
3af43a813f011d792376c2a34c2c1a27763fdb5bb393782d2582afeacc52ae88

2- $ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3af43a813f01 postgres:latest “docker-entrypoint.s…” 10 seconds ago Up 7 seconds 0.0.0.0:5432->5432/tcp postgre
0ff60bb165b1 postgres “docker-entrypoint.s…” About an hour ago Up About an hour 0.0.0.0:5433->5432/tcp loving_lewin

3- $ docker container top postgre
UID PID PPID C STIME TTY TIME CMD
999 5565 5548 0 19:51 ? 00:00:00 postgres
999 5652 5565 0 19:51 ? 00:00:00 postgres: checkpointer
999 5653 5565 0 19:51 ? 00:00:00 postgres: background writer
999 5654 5565 0 19:51 ? 00:00:00 postgres: walwriter
999 5655 5565 0 19:51 ? 00:00:00 postgres: autovacuum launcher
999 5656 5565 0 19:51 ? 00:00:00 postgres: stats collector
999 5657 5565 0 19:51 ? 00:00:00 postgres: logical replication launcher

4- $ docker container logs postgre
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 default max_connections … 100
selecting default shared_buffers … 128MB
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok

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…2019-02-09 19:51:09.244 UTC [40] LOG: listening on Unix socket “/var/run/postgresql/.s.PGSQL.5432”
2019-02-09 19:51:09.273 UTC [41] LOG: database system was shut down at 2019-02-09 19:51:06 UTC
2019-02-09 19:51:09.283 UTC [40] LOG: database system is ready to accept connections
done
server started

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

2019-02-09 19:51:09.315 UTC [40] LOG: received fast shutdown request
waiting for server to shut down…2019-02-09 19:51:09.321 UTC [40] LOG: aborting any active transactions
2019-02-09 19:51:09.324 UTC [40] LOG: background worker “logical replication launcher” (PID 47) exited with exit code 1
2019-02-09 19:51:09.325 UTC [42] LOG: shutting down
2019-02-09 19:51:09.396 UTC [40] LOG: database system is shut down
done
server stopped

PostgreSQL init process complete; ready for start up.

2019-02-09 19:51:09.431 UTC [1] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2019-02-09 19:51:09.432 UTC [1] LOG: listening on IPv6 address “::”, port 5432
2019-02-09 19:51:09.444 UTC [1] LOG: listening on Unix socket “/var/run/postgresql/.s.PGSQL.5432”
2019-02-09 19:51:09.465 UTC [49] LOG: database system was shut down at 2019-02-09 19:51:09 UTC
2019-02-09 19:51:09.473 UTC [1] LOG: database system is ready to accept connections

5- After all I want to run but I got error;

$ docker run -p 8761:8761 product-service
2019-02-09 19:51:25.666 INFO 1 — [ main] trationDelegate$BeanPostProcessorChecker : Bean ‘org.springframework.cloud.autoconfigure.ConfigurationPropertiesRebinderAutoConfiguration’ of type [org.springframework.cloud.autoconfigure.ConfigurationPropertiesRebinderAutoConfiguration$$EnhancerBySpringCGLIB$$a88d0b34] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)

. ____ _ __ _ _
/\ / ’ __ _ () __ __ _ \ \ \
( ( )__ | '_ | '| | ’ / ` | \ \ \
\/ )| |)| | | | | || (| | ) ) ) )
’ || .__|| ||| |_, | / / / /
=========||==============|/=////
:: Spring Boot :: (v2.1.1.RELEASE)

2019-02-09 19:51:26.689 INFO 1 — [ 2019-02-09 19:51:30.390 INFO 1 — [ 2019-02-09 19:51:30.637 INFO 1 — [ 2019-02-09 19:51:31.043 WARN 1 — [ 2019-02-09 19:51:32.351 INFO 1 — [ 2019-02-09 19:51:32.516 INFO 1 — [ 2019-02-09 19:51:32.910 INFO 1 — [ 2019-02-09 19:51:33.016 INFO 1 — [ 2019-02-09 19:51:34.292 INFO 1 — [ 2019-02-09 19:51:34.424 INFO 1 — [ 2019-02-09 19:51:34.425 INFO 1 — [ 2019-02-09 19:51:34.458 INFO 1 — [ 2019-02-09 19:51:34.770 INFO 1 — [ 2019-02-09 19:51:34.774 INFO 1 — [ 2019-02-09 19:51:35.193 WARN 1 — [ 2019-02-09 19:51:35.194 INFO 1 — [ 2019-02-09 19:51:35.272 INFO 1 — [ 2019-02-09 19:51:38.336 INFO 1 — [ 2019-02-09 19:51:38.705 INFO 1 — [ 2019-02-09 19:51:38.710 INFO 1 — [ 2019-02-09 19:51:39.378 INFO 1 — [ 2019-02-09 19:51:39.383 INFO 1 — [ 2019-02-09 19:51:40.628 INFO 1 — [ 2019-02-09 19:51:41.765 ERROR 1 — [ main] com.pcomm.ProductServiceApplication : No active profile set, falling back to default profiles: default
main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data repositories in DEFAULT mode.
main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 229ms. Found 2 repository interfaces.
main] o.s.boot.actuate.endpoint.EndpointId : Endpoint ID ‘service-registry’ contains invalid characters, please migrate to a valid format.
main] o.s.cloud.context.scope.GenericScope : BeanFactory id=a760eb4b-bf65-3569-80fd-e872d54af068
main] trationDelegate$BeanPostProcessorChecker : Bean ‘com.pcomm.product.feignclient.ProductServiceClient’ of type [org.springframework.cloud.openfeign.FeignClientFactoryBean] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
main] trationDelegate$BeanPostProcessorChecker : Bean ‘org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration’ of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$8c730837] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
main] trationDelegate$BeanPostProcessorChecker : Bean ‘org.springframework.cloud.autoconfigure.ConfigurationPropertiesRebinderAutoConfiguration’ of type [org.springframework.cloud.autoconfigure.ConfigurationPropertiesRebinderAutoConfiguration$$EnhancerBySpringCGLIB$$a88d0b34] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8761 (http)
main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
main] org.apache.catalina.core.StandardEngine : Starting Servlet Engine: Apache Tomcat/9.0.13
main] o.a.catalina.core.AprLifecycleListener : The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: [/usr/lib/jvm/java-1.8-openjdk/jre/lib/amd64/server:/usr/lib/jvm/java-1.8-openjdk/jre/lib/amd64:/usr/lib/jvm/java-1.8-openjdk/jre/…/lib/amd64:/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib]
main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
main] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 8005 ms
main] c.n.c.sources.URLConfigurationSource : No URLs will be polled as dynamic configuration sources.
main] c.n.c.sources.URLConfigurationSource : To enable URLs as dynamic configuration sources, define System property archaius.configurationSource.additionalUrls or make config.properties available on classpath.
main] c.netflix.config.DynamicPropertyFactory : DynamicPropertyFactory is initialized with configuration sources: com.netflix.config.ConcurrentCompositeConfiguration@5c18016b
main] c.s.j.s.i.a.WebApplicationImpl : Initiating Jersey application, version ‘Jersey: 1.19.1 03/11/2016 02:08 PM’
main] c.n.d.provider.DiscoveryJerseyProvider : Using JSON encoding codec LegacyJacksonJson
main] c.n.d.provider.DiscoveryJerseyProvider : Using JSON decoding codec LegacyJacksonJson
main] c.n.d.provider.DiscoveryJerseyProvider : Using XML encoding codec XStreamXml
main] c.n.d.provider.DiscoveryJerseyProvider : Using XML decoding codec XStreamXml
main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting…
main] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Exception during pool initialization.

org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:280) ~[postgresql-42.2.5.jar!/:42.2.5]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.2.5.jar!/:42.2.5]
at org.postgresql.jdbc.PgConnection.(PgConnection.java:195) ~[postgresql-42.2.5.jar!/:42.2.5]
at org.postgresql.Driver.makeConnection(Driver.java:454) ~[postgresql-42.2.5.jar!/:42.2.5]
at org.postgresql.Driver.connect(Driver.java:256) ~[postgresql-42.2.5.jar!/:42.2.5]
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136) ~[HikariCP-3.2.0.jar!/:na]
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369) ~[HikariCP-3.2.0.jar!/:na]
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198) ~[HikariCP-3.2.0.jar!/:na]
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467) [HikariCP-3.2.0.jar!/:na]
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541) [HikariCP-3.2.0.jar!/:na]
at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:115) [HikariCP-3.2.0.jar!/:na]
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) [HikariCP-3.2.0.jar!/:na]
at com.zaxxer.hikari.HikariDataSource$$FastClassBySpringCGLIB$$eeb1ae86.invoke() [HikariCP-3.2.0.jar!/:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) [spring-core-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) [spring-aop-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) [spring-aop-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136) [spring-aop-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124) [spring-aop-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:18…

gforghetti · February 9, 2019, 8:21pm

You are still running 2 postgres database servers.

$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3af43a813f01 postgres:latest “docker-entrypoint.s…” 10 seconds ago Up 7 seconds 0.0.0.0:5432->5432/tcp postgre
0ff60bb165b1 postgres “docker-entrypoint.s…” About an hour ago Up About an hour 0.0.0.0:5433->5432/tcp loving_lewin

You should delete the server that has published port 5433:5432 which has the container id of 0ff60bb165b1 and container name of loving_lewin.

docker container rm 0ff60bb165b1 -f

Now I see this error which to me means your application cannot connect to the database server.

org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

localhost is the issue. Your application is running in a container, the container cannot resolve localhost.

In this scenario you need to link your application container to your database container with the --link option.

See below.

I display the database container. It has a name of my-postgres-server which is the name I gave it when I started the container.

🐳  gforghetti:[~] $ docker container ls
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
c3a94437c7d7        postgres:latest     "docker-entrypoint.s…"   About an hour ago   Up About an hour    0.0.0.0:5432->5432/tcp   my-postgres-server

I add the --link argument to my application container and link it to the database container. In this example I’m running a cat /etc/hosts command to display the entries in the /etc/host file to show what happens when you use the --link argument.

🐳  gforghetti:[~] $ docker container run -it --name my-app --link my-postgres-server:my-db-host httpd:latest cat /etc/hosts
127.0.0.1	localhost
::1	localhost ip6-localhost ip6-loopback
fe00::0	ip6-localnet
ff00::0	ip6-mcastprefix
ff02::1	ip6-allnodes
ff02::2	ip6-allrouters
172.17.0.2	my-db-host c3a94437c7d7 my-postgres-server
172.17.0.3	f6ceb71dc555
🐳  gforghetti:[~] $

Notice that docker added an entry to the /etc/hosts file of the application container:
172.17.0.2 my-db-host c3a94437c7d7 my-postgres-server

Syntax of the --link

--linkcontainer-name:alias

Your application can access the database server by specifying either the alias name: my-db-host (useful), the container id of the database container: c3a94437c7d7 (not very useful), or the container name my-postgres-server (also useful),

gforghetti · February 9, 2019, 8:59pm

Now running containers this way and linking them is okay for development.

But Docker has better deploying of applications stacks using docker-compose (single node only) and docker stack deploy (swarm wide), which have better ways to network and connect containers without having to link them.

You need to spend some time and read up on Docker Swarm and networking.

I recommend this page -> https://docs.docker.com/samples/

And this is a good example of docker-compose -> https://docs.docker.com/compose/rails/

ibrahimates · February 9, 2019, 9:13pm

Thank you Gary Forghetti. I am appreciate for your time, your recommendation.