Help with multiple postgre database connections and some problems

mastertenor · July 12, 2024, 1:45pm

Thank you very, very much in advance for your help. The structure I want to set up is to offer postgre database access through different subdns. I've been experimenting for a while and I can't overcome a few problems.

My structure is as follows;

Docker Swarm

10.10.10.101 - Ubuntu - Manager - ubntsrv01
10.10.10.102 - Ubuntu - Manager - ubntsrv02
10.10.10.111 - Ubuntu - Worker 1 - ubntsrv11
10.10.10.112 - Ubuntu - Worker 2 - ubntsrv12
10.10.10.131 - Ubuntu - Nfs Server - ubntsrv31

CONFIG - 1 (docker-compose.yml)

version: '3.8'

services:
traefik:
image: 'traefik:v3.1'
ports:
- "80:80"
- "443:443"
- "5432:5432"
deploy:
mode: global
placement:
constraints:
- node.role==manager
labels:
- "traefik.enable=true"
- "traefik.docker.network=traefik-public"
- "traefik.http.middlewares.https-redirect.redirectscheme.scheme=https"
- "traefik.http.middlewares.https-redirect.redirectscheme.permanent=true"
- "traefik.http.routers.traefik-public-http.rule=Host(traefik.example.com)"
- "traefik.http.routers.traefik-public-http.entrypoints=http"
- "traefik.http.routers.traefik-public-http.middlewares=https-redirect"
- "traefik.http.routers.traefik-public-https.rule=Host(traefik.dynhyp.com)"
- "traefik.http.routers.traefik-public-https.entrypoints=https"
- "traefik.http.routers.traefik-public-https.tls=true"
- "traefik.http.routers.traefik-public-https.service=api@internal"
- "traefik.http.routers.traefik-public-https.tls.certresolver=stagingresolver"
- "traefik.http.services.traefik-public.loadbalancer.server.port=80"
volumes:
- "/var/run/docker.sock:/var/run/docker.sock:ro"
- "traefik-certificates:/certificates"
command:
- "--providers.docker"
- "--providers.docker.exposedbydefault=false"
- "--providers.swarm.endpoint=unix:///var/run/docker.sock"
- "--entrypoints.http.address=:80"
- "--entrypoints.https.address=:443"
- "--entrypoints.postgres.address=:5432"
- "--certificatesresolvers.stagingresolver.acme.email=berk.xxxxx@gmail.com"
- "--certificatesresolvers.stagingresolver.acme.tlschallenge=true"
- "--certificatesresolvers.stagingresolver.acme.storage=/certificates/acme.json"
- "--certificatesresolvers.stagingresolver.acme.caserver=https://acme-staging-v02.api.letsencrypt.org/directory"
- "--accesslog"
- "--log"
- "--api.dashboard=true"
- "--api"
networks:
- traefik-public

customer_000001_postgres:
image: postgres:latest
environment:
POSTGRES_DB: postgres
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres001
volumes:
- customer_000001:/var/lib/postgresql/data
networks:
- traefik-public
deploy:
mode: global
placement:
constraints:
- node.role==manager
labels:
- "traefik.enable=true"
- "traefik.tcp.routers.customer_000001_postgres.entrypoints=postgres"
- "traefik.tcp.routers.customer_000001_postgres.rule=HostSNI(customer1.example.com)"
- "traefik.tcp.routers.customer_000001_postgres.tls=true"
- "traefik.tcp.routers.customer_000001_postgres.tls.certresolver=stagingresolver"
- "traefik.tcp.services.customer_000001_postgres.loadbalancer.server.port=5432"

customer_000002_postgres:
image: postgres:latest
environment:
POSTGRES_DB: postgres
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres002
volumes:
- customer_000002:/var/lib/postgresql/data
networks:
- traefik-public
deploy:
mode: global
placement:
constraints:
- node.role==manager
labels:
- "traefik.enable=true"
- "traefik.tcp.routers.customer_000002_postgres.entrypoints=postgres"
- "traefik.tcp.routers.customer_000002_postgres.rule=HostSNI(customer2.example.com)"
- "traefik.tcp.routers.customer_000002_postgres.tls=true"
- "traefik.tcp.routers.customer_000002_postgres.tls.certresolver=stagingresolver"
- "traefik.tcp.services.customer_000002_postgres.loadbalancer.server.port=5432"

volumes:
traefik-certificates:
driver: local
driver_opts:
type: nfs
o: addr=10.10.10.131,nfsvers=4
device: ":/mnt/nfsdisk/certificates"
customer_000001:
driver: local
driver_opts:
type: nfs
o: addr=10.10.10.131,nfsvers=4
device: ":/mnt/nfsdisk/customer_000001/postgres_data"
customer_000002:
driver: local
driver_opts:
type: nfs
o: addr=10.10.10.131,nfsvers=4
device: ":/mnt/nfsdisk/customer_000002/postgres_data"

networks:
traefik-public:
external: true

When I use config 1;

a-) I can access the traefik dashboard.

b-) I can successfully connect to both servers with pgadmin.

c-) But when I test with nodejs with the connection below, 7 out of 10 tests fail and 3 succeed.

const pool = new Pool({
user: 'postgres',
host: 'customer1.example.com',
database: 'postgres',
password: 'postgres001',
port: 5432,
ssl: {
rejectUnauthorized: false
}
});

PS I:\45-CODE_WORKS\02-PYTHON\test> node testConnection.js
connection successful, time: 2024-07-12T10:13:05.216Z
db list: [ 'postgres', 'db21' ]

PS I:\45-CODE_WORKS\02-PYTHON\test> node testConnection.js
connection successful, time: 2024-07-12T10:19:26.526Z
db list: [ 'postgres', 'db21' ]

PS I:\45-CODE_WORKS\02-PYTHON\test> node testConnection.js
an error occurred: Error: connect ETIMEDOUT 31.210.79.207:5432
at I:\45-CODE_WORKS\02-PYTHON\test\node_modules\pg-pool\index.js:45:11
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async testConnection (I:\45-CODE_WORKS\02-PYTHON\test\testConnection.js:18:25) {
errno: -4039,
code: 'ETIMEDOUT',
syscall: 'connect',
address: '31.xxx.xx.xxx',
port: 5432
}

CONFIG 2 - (Only changes deploy)

deploy:
  mode: replicated
  replicas: 1
  placement:
    constraints:
      - node.role==worker

When I try Config 2, I get ;

a-) I can access the traefik dashboard.
b-) I cannot access my two postgre servers with pgadmin.
c-) when I look at traefik logs I see "ERR Error while dialing backend error="dial tcp... " error.

Notes:

a-) to create traefik-public network;
docker network create --driver=overlay traefik-public

b-) Since I cannot run postgre in worker mode, I naturally see two servers.

c-) When I deploy with Config 1, I share the content of the traefik-public network below.I share below the content of the traefik-public network when I deploy with config 1. I can see my two postgre servers.
[
{
"Name": "traefik-public",
"Id": "9871m36gfb2sit7sbxcofb7jr",
"Created": "2024-07-12T15:24:03.069410331+03:00",
"Scope": "swarm",
"Driver": "overlay",
"EnableIPv6": false,
"IPAM": {
"Driver": "default",
"Options": null,
"Config": [
{
"Subnet": "10.0.1.0/24",
"Gateway": "10.0.1.1"
}
]
},
"Internal": false,
"Attachable": false,
"Ingress": false,
"ConfigFrom": {
"Network": ""
},
"ConfigOnly": false,
"Containers": {
"4cf4edd2ed3e9b68a4f9962e6c205db70ab8153ee62d51417f46e6e33453384c": {
"Name": "traefik_customer_000001_postgres.lcenfyxw90l7ksjjruv5h3juv.2ofxsk272p6cb3vbrkr7hafv9",
"EndpointID": "ae66f422bf6dead62c189569c057aa238c5bd30ccdbe619c6f1282681454eb97",
"MacAddress": "02:42:0a:00:01:24",
"IPv4Address": "10.0.1.36/24",
"IPv6Address": ""
},
"7c9bc3d8ff858acb7066d6af06daad373f565844dc7c56431cab18a2fa2833b9": {
"Name": "traefik_customer_000002_postgres.lcenfyxw90l7ksjjruv5h3juv.rxp1zvjkrji71ueirg4i9xvlj",
"EndpointID": "703bee2afac4bfd75a4a3ade140af88f63e6960a00dbf92acfe90a7c4762db2d",
"MacAddress": "02:42:0a:00:01:27",
"IPv4Address": "10.0.1.39/24",
"IPv6Address": ""
},
"bc9a973af75075cefa7e925a8c511a2e5012d44a158350c2b4adc7fb9fea4506": {
"Name": "traefik_traefik.lcenfyxw90l7ksjjruv5h3juv.ynsj62dioiq10zztn8tfvpbly",
"EndpointID": "ce84033a952440cdb2c664af2caee39a8669dce3b162adfff5283d1a77104a8f",
"MacAddress": "02:42:0a:00:01:1e",
"IPv4Address": "10.0.1.30/24",
"IPv6Address": ""
},
"lb-traefik-public": {
"Name": "traefik-public-endpoint",
"EndpointID": "23fe19f1692a00f72e631011080013a91aec2fc10c5b206b210f81eb9f5f9e8b",
"MacAddress": "02:42:0a:00:01:20",
"IPv4Address": "10.0.1.32/24",
"IPv6Address": ""
}
},
"Options": {
"com.docker.network.driver.overlay.vxlanid_list": "4097"
},
"Labels": {},
"Peers": [
{
"Name": "82dcbcb3327d",
"IP": "10.10.10.101"
},
{
"Name": "0993c2e8be6f",
"IP": "10.10.10.102"
}
]
}
]

d-) When I deploy with Config 2, I share the content of the traefik-public network below.I share below the content of the traefik-public network when I deploy it with config 1. This time I cannot see my two postgre servers.

[
{
"Name": "traefik-public",
"Id": "9871m36gfb2sit7sbxcofb7jr",
"Created": "2024-07-12T16:21:08.919681987+03:00",
"Scope": "swarm",
"Driver": "overlay",
"EnableIPv6": false,
"IPAM": {
"Driver": "default",
"Options": null,
"Config": [
{
"Subnet": "10.0.1.0/24",
"Gateway": "10.0.1.1"
}
]
},
"Internal": false,
"Attachable": false,
"Ingress": false,
"ConfigFrom": {
"Network": ""
},
"ConfigOnly": false,
"Containers": {
"fe7161585965fce6c0dfe701414310db9098be6de8a359f634f602c8e5760071": {
"Name": "traefik_traefik.lcenfyxw90l7ksjjruv5h3juv.yd3iy22ojsa2en0kx0yifhn3w",
"EndpointID": "1a3405163f8bef1a8e4c3ffd1475cdf1b795b07faaaf526bf52eaec129956f36",
"MacAddress": "02:42:0a:00:01:2e",
"IPv4Address": "10.0.1.46/24",
"IPv6Address": ""
},
"lb-traefik-public": {
"Name": "traefik-public-endpoint",
"EndpointID": "c239c221c26086efce91e7ca5f0af2218cc634040e764d66ff73e7e0f944cabe",
"MacAddress": "02:42:0a:00:01:30",
"IPv4Address": "10.0.1.48/24",
"IPv6Address": ""
}
},
"Options": {
"com.docker.network.driver.overlay.vxlanid_list": "4097"
},
"Labels": {},
"Peers": [
{
"Name": "9faa1eda0959",
"IP": "10.10.10.111"
},
{
"Name": "82dcbcb3327d",
"IP": "10.10.10.101"
},
{
"Name": "0993c2e8be6f",
"IP": "10.10.10.102"
}
]
}
]

d-) The errors I get in traefik after Config 2 are as follows;

traefik_traefik.0.yd3iy22ojsa2@ubntsrv01 | 2024-07-12T13:22:40Z ERR Error while dialing backend error="dial tcp 10.0.1.42:5432: i/o timeout"

e-) d-) My traefik dashboard after Config 2.

e-) the HostSNI content that appears in the used by routers and rule section in the screenshots is the original dns and I change it in the docker-compose.yml file before deploying.

I have come this far after much research and effort. I really need your help. Otherwise I will have to give up the project.

Sincerely regards

mastertenor · July 15, 2024, 6:42am

cause and solution of the problem on the docker forum page;

bluepuma77 · July 15, 2024, 7:48am

I think a problem statement before all the code would help to get a clearer picture before diving into the configuration.

Here it's the same as in Docker forum: use 3 backticks before and after code/config for improved readability - im yaml every space matters.

You seem to use Docker Swarm, it needs 3 managers to enable high availability. Every manager is able to act as a worker and run tasks/workloads.

system · July 18, 2024, 7:49am

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

Topic		Replies	Views
Multiple postgres deployments using different subdns Traefik v3 (latest) docker-swarm , tcp	0	34	July 12, 2024
Running multiple databases via Traefik, and accessing the databases from the host machine Traefik v2 docker , tcp	6	7365	December 27, 2023
TCP Routing to two database containers Traefik v2 docker , tcp	2	1362	October 1, 2023
Help in TCP configure of traefik mapped postgresql container - hostSNI Traefik v2 docker	6	6824	February 16, 2022
TCP TLS : connect multi database postgresql? Traefik v2 docker-swarm , tcp	1	459	January 25, 2021

Help with multiple postgre database connections and some problems

Related topics