Postgres + Go + Docker-compose 无法连接数据库：拨号tcp 127.0.0.1:5432: 连接被拒绝

huangapple
go评论108阅读模式
英文:

Postgres + Go + Docker-compose Can't ping database: dial tcp 127.0.0.1:5432: connect: connection refused

问题

这是一个使用Golang编写的脚本，用于创建一个包含三个服务（GO+POSTGRES+NGINX）的Docker Compose环境。主要任务是学习环境配置。数据库需要设置密码，并且gocalc需要连接到该数据库。但是你做错了什么呢？

根据你提供的日志，有几个问题需要解决：

  1. backend_1 无法连接到数据库。错误消息是 Can't ping database: dial tcp 127.0.0.1:5432: connect: connection refused。这可能是因为 backend 服务无法找到 db 服务。请确保在 backend 服务的环境变量中使用正确的数据库主机名和端口。

  2. proxy_1 无法找到 backend。错误消息是 host not found in upstream "backend" in /etc/nginx/conf.d/default.conf:5。这可能是因为 proxy 服务无法找到 backend 服务。请确保在 proxy 服务的配置文件中正确指定了 backend 的主机名。

  3. postgres_1 服务在启动时出现了一些警告和错误，但最终成功启动并准备好接受连接。

你需要检查并修复上述问题，以确保所有服务能够正确连接和通信。

英文:

It is not my golang script, but i should to use them in this task:
package main

  1. import (
  2. 	"database/sql"
  3. 	"fmt"
  4. 	_ "github.com/lib/pq"
  5. 	"log"
  6. 	"net/http"
  8. 	"github.com/caarlos0/env"
  9. 	"github.com/prometheus/client_golang/prometheus"
  10. 	"github.com/prometheus/client_golang/prometheus/promhttp"
  11. )
  13. type config struct {
  14. 	PostgresUri   string `env:"POSTGRES_URI" envDefault:"postgres://root:pass@127.0.0.1/postgres"`
  15. 	ListenAddress string `env:"LISTEN_ADDRESS" envDefault:":5432"`
  16. }
  18. var (
  19. 	db          *sql.DB
  20. 	errorsCount = prometheus.NewCounterVec(
  21. 		prometheus.CounterOpts{
  22. 			Name: "gocalc_errors_count",
  23. 			Help: "Gocalc Errors Count Per Type",
  24. 		},
  25. 		[]string{"type"},
  26. 	)
  28. 	requestsCount = prometheus.NewCounter(
  29. 		prometheus.CounterOpts{
  30. 			Name: "gocalc_requests_count",
  31. 			Help: "Gocalc Requests Count",
  32. 		})
  33. )
  35. func main() {
  36. 	var err error
  38. 	// Initing prometheus
  39. 	prometheus.MustRegister(errorsCount)
  40. 	prometheus.MustRegister(requestsCount)
  42. 	// Getting env
  43. 	cfg := config{}
  44. 	if err = env.Parse(&cfg); err != nil {
  45. 		fmt.Printf("%+v\n", err)
  46. 	}
  48. 	// Connecting to database
  49. 	db, err = sql.Open("postgres", cfg.PostgresUri)
  50. 	if err != nil {
  51. 		log.Fatalf("Can't connect to postgresql: %v", err)
  52. 	}
  53. 	defer db.Close()
  55. 	err = db.Ping()
  56. 	if err != nil {
  57. 		log.Fatalf("Can't ping database: %v", err)
  58. 	}
  60. 	http.HandleFunc("/", handler)
  61. 	http.Handle("/metrics", promhttp.Handler())
  62. 	log.Fatal(http.ListenAndServe(cfg.ListenAddress, nil))
  63. }
  65. func handler(w http.ResponseWriter, r *http.Request) {
  66. 	requestsCount.Inc()
  68. 	keys, ok := r.URL.Query()["q"]
  69. 	if !ok || len(keys[0]) < 1 {
  70. 		errorsCount.WithLabelValues("missing").Inc()
  71. 		log.Println("Url Param 'q' is missing")
  72. 		http.Error(w, "Bad Request", 400)
  73. 		return
  74. 	}
  75. 	q := keys[0]
  76. 	log.Println("Got query: ", q)
  78. 	var result string
  79. 	sqlStatement := fmt.Sprintf("SELECT (%s)::numeric", q)
  80. 	row := db.QueryRow(sqlStatement)
  81. 	err := row.Scan(&result)
  83. 	if err != nil {
  84. 		log.Println("Error from db: %s", err)
  85. 		errorsCount.WithLabelValues("db").Inc()
  86. 		http.Error(w, "Internal Server Error", 500)
  87. 		return
  88. 	}
  90. 	fmt.Fprintf(w, "query %s; result %s", q, result)
  91. }

It is my docker-compose:

  1. version: "3"
  2. services:
  3.   db:
  4.     image: postgres:10
  5.     environment:
  6.       - POSTGRES_PASSWORD=pass
  7.       - POSTGRES_USER=root
  8.     expose:
  9.       - 5432
  10.     
  11.   backend:
  12.     image: dkr-14-gocalc:latest
  13.     environment:
  14.       - POSTGRES_URI=postgres://root:pass@db/postgres
  15.       - LISTEN_ADDRESS=7000
  16.     depends_on:
  17.       - postgres
  18.   proxy:
  19.     image: nginx
  20.     volumes:
  21.       - type: bind
  22.         source: ./nginx.conf
  23.         target: /etc/nginx/conf.d/default.conf
  24.     ports:
  25.       - 8000:80
  26.     depends_on: 
  27.       - backend

And it is dkr14-gocalc image:

  1. FROM golang:1.19.1-alpine AS builder
  2. ENV GO111MODULE=auto
  3. WORKDIR /go/src/
  5. RUN apk add --no-cache git 
  6. COPY main.go ./
  7.  
  8. #init is initializing and writting new go.mod in current dir.
  9. RUN go mod init main.go
  11. RUN go get -d -v github.com/caarlos0/env \
  12.     && go get -d -v github.com/prometheus/client_golang/prometheus \
  13.     && go get -d -v github.com/prometheus/client_golang/prometheus/promhttp \
  14.     && go get -d -v github.com/lib/pq \   
  15.     && go get -d -v database/sql \
  16.     && go get -d -v fmt \
  17.     && go get -d -v log \
  18.     && go get -d -v net/http
  19.  	
  20. RUN go build -o app .
  22. FROM alpine:3.10.3
  23. RUN apk --no-cache add ca-certificates
  24. WORKDIR /root/
  25. COPY --from=builder /go/src/app ./
  26. CMD ["./app"]

I should to make docker compose environment with 3 services GO+POSTGRES+NGINX. The main idea of task is to learn about environment. Database should have password and golalc should to connect to this database. But what have i done incorrect?

It is my log:

  1. backend_1   | 2022/11/07 23:27:33 Can't ping database: dial tcp 127.0.0.1:5432: connect: connection refused
  2. postgres_1  | The files belonging to this database system will be owned by user "postgres".
  3. postgres_1  | This user must also own the server process.
  4. postgres_1  | 
  5. proxy_1     | /docker-entrypoint.sh: /docker-entrypoint.d/ is not empty, will attempt to perform configuration
  6. proxy_1     | /docker-entrypoint.sh: Looking for shell scripts in /docker-entrypoint.d/
  7. postgres_1  | The database cluster will be initialized with locale "en_US.utf8".
  8. postgres_1  | The default database encoding has accordingly been set to "UTF8".
  9. postgres_1  | The default text search configuration will be set to "english".
  10. postgres_1  | 
  11. postgres_1  | Data page checksums are disabled.
  12. proxy_1     | /docker-entrypoint.sh: Launching /docker-entrypoint.d/10-listen-on-ipv6-by-default.sh
  13. proxy_1     | 10-listen-on-ipv6-by-default.sh: info: Getting the checksum of /etc/nginx/conf.d/default.conf
  14. postgres_1  | 
  15. task14_backend_1 exited with code 1
  16. proxy_1     | 10-listen-on-ipv6-by-default.sh: info: /etc/nginx/conf.d/default.conf differs from the packaged version
  17. postgres_1  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
  18. postgres_1  | creating subdirectories ... ok
  19. proxy_1     | /docker-entrypoint.sh: Launching /docker-entrypoint.d/20-envsubst-on-templates.sh
  20. postgres_1  | selecting default max_connections ... 100
  21. proxy_1     | /docker-entrypoint.sh: Launching /docker-entrypoint.d/30-tune-worker-processes.sh
  22. postgres_1  | selecting default shared_buffers ... 128MB
  23. postgres_1  | selecting default timezone ... Etc/UTC
  24. postgres_1  | selecting dynamic shared memory implementation ... posix
  25. proxy_1     | /docker-entrypoint.sh: Configuration complete; ready for start up
  26. postgres_1  | creating configuration files ... ok
  27. postgres_1  | running bootstrap script ... ok
  28. postgres_1  | performing post-bootstrap initialization ... ok
  29. postgres_1  | syncing data to disk ... ok
  30. postgres_1  | 
  31. postgres_1  | Success. You can now start the database server using:
  32. postgres_1  | 
  33. postgres_1  |     pg_ctl -D /var/lib/postgresql/data -l logfile start
  34. postgres_1  | 
  35. postgres_1  | 
  36. postgres_1  | WARNING: enabling "trust" authentication for local connections
  37. postgres_1  | You can change this by editing pg_hba.conf or using the option -A, or
  38. postgres_1  | --auth-local and --auth-host, the next time you run initdb.
  39. proxy_1     | 2022/11/07 23:27:33 [emerg] 1#1: host not found in upstream "backend" in /etc/nginx/conf.d/default.conf:5
  40. proxy_1     | nginx: [emerg] host not found in upstream "backend" in /etc/nginx/conf.d/default.conf:5
  41. postgres_1  | waiting for server to start....2022-11-07 23:27:33.704 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
  42. postgres_1  | 2022-11-07 23:27:33.722 UTC [49] LOG:  database system was shut down at 2022-11-07 23:27:33 UTC
  43. postgres_1  | 2022-11-07 23:27:33.729 UTC [48] LOG:  database system is ready to accept connections
  44. postgres_1  |  done
  45. postgres_1  | server started
  46. task14_proxy_1 exited with code 1
  47. postgres_1  | CREATE DATABASE
  48. postgres_1  | 
  49. postgres_1  | 
  50. postgres_1  | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
  51. postgres_1  | 
  52. postgres_1  | 2022-11-07 23:27:34.130 UTC [48] LOG:  received fast shutdown request
  53. postgres_1  | waiting for server to shut down....2022-11-07 23:27:34.133 UTC [48] LOG:  aborting any active transactions
  54. postgres_1  | 2022-11-07 23:27:34.135 UTC [48] LOG:  worker process: logical replication launcher (PID 55) exited with exit code 1
  55. postgres_1  | 2022-11-07 23:27:34.135 UTC [50] LOG:  shutting down
  56. postgres_1  | 2022-11-07 23:27:34.157 UTC [48] LOG:  database system is shut down
  57. postgres_1  |  done
  58. postgres_1  | server stopped
  59. postgres_1  | 
  60. postgres_1  | PostgreSQL init process complete; ready for start up.
  61. postgres_1  | 
  62. postgres_1  | 2022-11-07 23:27:34.254 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
  63. postgres_1  | 2022-11-07 23:27:34.254 UTC [1] LOG:  listening on IPv6 address "::", port 5432
  64. postgres_1  | 2022-11-07 23:27:34.259 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
  65. postgres_1  | 2022-11-07 23:27:34.283 UTC [76] LOG:  database system was shut down at 2022-11-07 23:27:34 UTC
  66. postgres_1  | 2022-11-07 23:27:34.294 UTC [1] LOG:  database system is ready to accept connections

答案1

得分: 0

问题可能出在你在 docker-compose.yaml 文件中如何暴露数据库容器。在这种情况下，“连接被拒绝”是一个典型的错误。通常，我以不同的方式导出端口（在这段代码中，我只报告了相关部分）：

docker-compose.yaml

  1. version: "3"
  2. services:
  3.   db:
  4.     image: postgres
  5.     environment:
  6.       - POSTGRES_PASSWORD=postgres
  7.       - POSTGRES_USER=postgres
  8.     ports:
  9.       - "5432:5432"

main.go

  1. package main
  3. import (
  4. 	"database/sql"
  5. 	"log"
  7. 	_ "github.com/lib/pq"
  8. )
  10. func main() {
  11. 	// 连接数据库
  12. 	db, err := sql.Open("postgres", "host=127.0.0.1 user=postgres password=postgres dbname=postgres port=5432 sslmode=disable")
  13. 	if err != nil {
  14. 		log.Fatalf("无法连接到postgresql：%v", err)
  15. 	}
  16. 	defer db.Close()
  18. 	err = db.Ping()
  19. 	if err != nil {
  20. 		log.Fatalf("无法ping数据库：%v", err)
  21. 	}
  22. }

通过这个解决方案，我能够成功地从代码中直接ping通Postgres数据库。如果有帮助，请告诉我。

编辑

在与db实例交互的方式上有一点不同：

  1. 如果你试图直接从主机上联系数据库（就像在 main.go 文件中一样），你必须使用你的机器的IP地址（例如 127.0.0.1）来引用它。
  2. 如果你试图从web容器中联系数据库，你必须使用类似 postgres://db:5432 的形式，其中 db 是主机名。

如果这有助于澄清一点，请告诉我。

英文:

The issue could be in how you're exposing your database container in the docker-compose.yaml file. "connection refused" is a typical error in this scenario. Usually, I export the ports in a different way (in this code I reported only the relevant parts):

docker-compose.yaml

  1. version: "3"
  2. services:
  3.   db:
  4.     image: postgres
  5.     environment:
  6.       - POSTGRES_PASSWORD=postgres
  7.       - POSTGRES_USER=postgres
  8.     ports:
  9.       - "5432:5432"

main.go

  1. package main
  3. import (
  4. 	"database/sql"
  5. 	"log"
  7. 	_ "github.com/lib/pq"
  8. )
  10. func main() {
  11. 	// Connecting to database
  12. 	db, err := sql.Open("postgres", "host=127.0.0.1 user=postgres password=postgres dbname=postgres port=5432 sslmode=disable")
  13. 	if err != nil {
  14. 		log.Fatalf("Can't connect to postgresql: %v", err)
  15. 	}
  16. 	defer db.Close()
  18. 	err = db.Ping()
  19. 	if err != nil {
  20. 		log.Fatalf("Can't ping database: %v", err)
  21. 	}
  22. }

With this solution, I was able to successfully ping the Postgres database directly from the code. Let me know if that helps.

Edit

There is a little difference in the way you interact with db instance:

  1. If you're trying to contact the db directly from your host machine (like in the main.go file), you've to refer to it with the IP address of your machine (e.g., 127.0.0.1).
  2. If you're trying to contact the db from your web container, you've to use something like postgres://db:5432 with db as the hostname.

Let me know if this clarifies a little bit.

huangapple
  • 本文由 发表于 2022年11月8日 20:19:13
  • 转载请务必保留本文链接：https://go.coder-hub.com/74360635.html
  • database
  • docker
  • docker-compose
  • go
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定