Remote connection postgres container fails

vivazep · November 26, 2024, 3:43pm

Hi, I have installed docker on a OracleLinux 9.4 Virtual machine and I have builded an image FROM oraclelinux:8.10 and installed some stuff manually, including openssh, postgresql17, etc.

In the Dockerfile I have:

USER postgres
works fine: RUN /usr/pgsql-17/bin/initdb -D /var/lib/pgsql/17/data
do nothing: RUN /usr/pgsql-17/bin/postgres -D $PGDATA -k /var/run/postgresql -p 5432 > $PGDATA/logfile 2>&1 &

FInally
USER root
EXPOSE 5432
EXPOSE 22
CMD [“/usr/sbin/sshd”, “-D”]

I run the container with docker run -d --publish-all --name pgw01 pgsqldb

[admin@buda pgsqldb]$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
70f55332a895 pgsqldb “/usr/sbin/sshd -D” 5 seconds ago Up 5 seconds 0.0.0.0:32772->22/tcp, [::]:32772->22/tcp, 0.0.0.0:32773->5432/tcp, [::]:32773->5432/tcp pgw01

From a windows power shell sesion I can connect (ssh) without problem to the container and start the postgress instance

----POWER SHELL SESSION ----
[root@70f55332a895 ~]# su - postgres
[postgres@70f55332a895 ~]$ echo $PGDATA
/var/lib/pgsql/17/data
[postgres@70f55332a895 ~]$ ps -ef
UID PID PPID C STIME TTY TIME CMD
root 1 0 0 15:30 ? 00:00:00 /usr/sbin/sshd -D
root 7 1 0 15:32 ? 00:00:00 sshd: root [priv]
root 9 7 0 15:33 ? 00:00:00 sshd: root@pts/0
root 10 9 0 15:33 pts/0 00:00:00 -bash
root 25 10 0 15:33 pts/0 00:00:00 su - postgres
postgres 26 25 0 15:33 pts/0 00:00:00 -bash
postgres 55 26 0 15:33 pts/0 00:00:00 ps -ef

The I run the command doing nothing from Dockerfile

[postgres@70f55332a895 ~]$ /usr/pgsql-17/bin/postgres -D $PGDATA -k /var/run/postgresql -p 5432 > $PGDATA/logfile 2>&1 &
[1] 56
[postgres@70f55332a895 ~]$
[postgres@70f55332a895 ~]$ ps -ef
UID PID PPID C STIME TTY TIME CMD
root 1 0 0 15:30 ? 00:00:00 /usr/sbin/sshd -D
root 7 1 0 15:32 ? 00:00:00 sshd: root [priv]
root 9 7 0 15:33 ? 00:00:00 sshd: root@pts/0
root 10 9 0 15:33 pts/0 00:00:00 -bash
root 25 10 0 15:33 pts/0 00:00:00 su - postgres
postgres 26 25 0 15:33 pts/0 00:00:00 -bash
postgres 56 26 0 15:35 pts/0 00:00:00 /usr/pgsql-17/bin/postgres -D /var/lib/pgsql/17/data -k /var/run/pos
postgres 57 56 0 15:35 ? 00:00:00 postgres: logger
postgres 58 56 0 15:35 ? 00:00:00 postgres: checkpointer
postgres 59 56 0 15:35 ? 00:00:00 postgres: background writer
postgres 61 56 0 15:35 ? 00:00:00 postgres: walwriter
postgres 62 56 0 15:35 ? 00:00:00 postgres: autovacuum launcher
postgres 63 56 0 15:35 ? 00:00:00 postgres: logical replication launcher
postgres 64 26 0 15:35 pts/0 00:00:00 ps -ef

[postgres@70f55332a895 ~]$ psql
psql (17.2)
Type “help” for help.

postgres=#\q

I have two questions:

1.- Why is not working the postgres start up from Dockerfile?
2.- When I tryed to connect remotly to the postgres intance, the connection is refused

psql -U postgres -h 192.168.0.160 -p 32773
psql: error: connection to server at “192.168.0.160”, port 32773 failed: Connection refused (0x0000274D/10061)
Is the server running on that host and accepting TCP/IP connections?

192.168.0.160: IP running the container
172.17.0.2 : IP container
32773 : Host’s port binding container postgres port 5432

Thank you very much

kind regards

Mauricio Fernández

vivazep · November 27, 2024, 5:00pm

I could start sshd and postgres replacin cmd command

COPY exepg.sh exepg.sh
RUN chmod 755 exepg.sh
RUN mv exepg.sh /usr/bin/exepg.sh
CMD [“/usr/bin/exepg.sh”]

and the content of exepg.sh is

[admin@buda pgsqldb]$ cat exepg.sh
#!/usr/bin/sh
su postgres -c ‘/usr/pgsql-17/bin/pg_ctl start -D /var/lib/pgsql/17/data -l /var/lib/pgsql/17/data/logfile’
su postgres -c ‘/usr/pgsql-17/bin/psql -U postgres -a -f /tmp/crea_moodle.sql > /tmp/crea_moodle.log’
/usr/sbin/sshd -D

I’m still trying to conect remotly to the container

psql -U postgres -h 192.168.0.160 -p 5434
psql: error: connection to server at “192.168.0.160”, port 5434 failed: Connection refused (0x0000274D/10061)
Is the server running on that host and accepting TCP/IP connections?

bluepuma77 · November 27, 2024, 10:58pm

Containers are about isolation, usually you would run one application per container, not everything in a single one, like with a VM.

Share your Dockerfile(s).

vivazep · November 28, 2024, 11:51am

Hi bluepluma77, thanks for answer.

I know container are mono oriented, and they shlould solve one service, but I’m trying to do some experiments.

The Dockerfile is this:

FROM oraclelinux:8.10

USER root

RUN dnf update

RUN dnf install openssh openssh-server
RUN dnf install wget

RUN wget -c https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
RUN rpm -ivh pgdg-redhat-repo-latest.noarch.rpm
RUN dnf -qy module disable postgresql
RUN dnf install postgresql17-server.x86_64 postgresql17-contrib.x86_64

RUN dnf install langpacks-es*

Create SSH directory and set permissions

RUN mkdir -p /root/.ssh
RUN chmod 700 /root/.ssh

Copy the authorized_keys file

COPY authorized_keys /etc/ssh/ssh_host_rsa_key

RUN chmod 600 /etc/ssh/ssh_host_rsa_key

RUN echo ‘root:oracle…,’ | chpasswd

#############

Configure SSH

##############
RUN mkdir /var/run/sshd

Allow root login

RUN sed -i ‘s/#PermitRootLogin prohibit-password/PermitRootLogin yes/’ /etc/ssh/sshd_config

SSH login fix

RUN sed ‘s@session\srequired\spam_loginuid.so@session optional pam_loginuid.so@g’ -i /etc/pam.d/sshd

RUN echo “export LANG=es_CL.utf8” >> /var/lib/pgsql/.bash_profile
RUN echo “export ORACLE_HOME=/usr/lib/oracle/19.25/client64” >> ~/.bash_profile

RUN useradd admin ; echo 12345 | passwd admin --stdin

RUN rm -rf /etc/localtime
RUN ln -s /usr/share/zoneinfo/America/Santiago /etc/localtime

####################

Configure postgres

####################

COPY exepg.sh exepg.sh
RUN chmod 755 exepg.sh
RUN mv exepg.sh /usr/bin/exepg.sh

USER postgres
COPY crea_moodle.sql /tmp/crea_moodle.sql

RUN echo “export PGDATA=/var/lib/pgsql/17/data” >> /var/lib/pgsql/.pgsql_profile
RUN echo “export PGHOME=/usr/pgsql-17”>> /var/lib/pgsql/.pgsql_profile
RUN echo “export PATH=/usr/pgsql-17/bin:$PATH” >> /var/lib/pgsql/.pgsql_profile
RUN echo “export LANG=es_CL.utf8” >> /var/lib/pgsql/.pgsql_profile

RUN chmod 744 /var/lib/pgsql/.pgsql_profile

RUN export PGHOME=/usr/pgsql-17
RUN export PGDATA=/var/lib/pgsql/17/data
RUN export PATH=/usr/pgsql-17/bin:$PATH

RUN /usr/pgsql-17/bin/initdb -D /var/lib/pgsql/17/data

USER root
EXPOSE 5432
EXPOSE 22

CMD [“/usr/bin/exepg.sh”]

The content of the file exepg.sh is:

#!/usr/bin/sh

su postgres -c ‘/usr/pgsql-17/bin/pg_ctl start -D /var/lib/pgsql/17/data -l /var/lib/pgsql/17/data/logfile’

su postgres -c ‘/usr/pgsql-17/bin/psql -U postgres -a -f /tmp/crea_moodle.sql > /tmp/crea_moodle.log’

/usr/sbin/sshd -D

regards

bluepuma77 · November 29, 2024, 9:13am

Are you aware of this?

--publish-all

Publish all exposed ports to random ports

vivazep · November 29, 2024, 10:53am

Yes I was aware of that, and it didn’t worked either. From a cliente I cant connect to the docker through ssh, but not with postgres psql. The database is running…I can connect through psql inside the docker.

[admin@buda ~]$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3733a9dced86 pgsqldb “/usr/bin/exepg.sh” 20 hours ago Up 20 hours 0.0.0.0:2222->22/tcp, [::]:2222->22/tcp, 0.0.0.0:5434->5432/tcp, [::]:5434->5432/tcp pgw01

[admin@buda ~]$ docker network inspect bridge | grep IPv4Address
“IPv4Address”: “172.17.0.2/16”,
Postgres pg_hba.conf

IPv4 local connections:

host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust

I can’t connect through psql from the docker’s host eather. May be there is some issue with docker and exposing mor than port. I need ti reas a lot more

thank you

vivazep · November 29, 2024, 9:50pm

The anser was so simple…

The problem were in postgresql.conf.

I’ve changed listen_addresses = ‘localhost’ for listen_addresses = ‘*’ and could connect remotly

system · December 9, 2024, 9:51pm

This topic was automatically closed 10 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Unable to connect to Postgres inside Docker container General	1	7540	August 3, 2019
Can't connect to Postgres container from Windows 10, but can from WSL Docker Desktop windows	8	18506	October 20, 2024
[POSTGRES - SOLVED] Fail to connect Postgresql container General docker	6	68001	November 24, 2022
Failed login to Docker postgres image via localhost with published port, but accessible by external IP General	1	8072	June 23, 2018
Container command '/bin/sh' not found or does not exist General	9	126524	December 1, 2016