[postgreSQL]too many Client 오류

25.postgreSQL 오류 too many Client

상황 (S) -1

• 갑자기 nestJS 어플리케이션이 먹통이 되었다.
• TypeORM 모듈에서 생성자 함수 실행 후 동기화 SQL코드가 있는 여기서, 무한대기를 하는 현상이 생겼다.
• 더 난감한것은 오류 메시지도 출력하지 않아서 문제점이 숨겨져 있었다.
• 처음에는 NestJS 문제라고 생각되어 모듈을 하나씩 붙여가며 실행해보니,
• TypeORM의 문제였다. DB동기화 옵션을 끄니 우선은 해결이 되었다.

상황 (S) -2

• DB의 동기화 충돌로 생긴 문제로 범위를 좁혀갔지만, 다음의 오류를 보니 예상을 빗나갔다.

[Nest] 11076  - 2021. 10. 28. 오후 5:56:03   ERROR [ErrorHandlerInterceptor] sorry, too many clients already
error: sorry, too many clients already
    at Parser.parseErrorMessage (C:\Users\Dos\Desktop\algoridang\nestjs_server\node_modules\pg-protocol\src\parser.ts:369:69)
    at Parser.handlePacket (C:\Users\Dos\Desktop\algoridang\nestjs_server\node_modules\pg-protocol\src\parser.ts:188:21)
    at Parser.parse (C:\Users\Dos\Desktop\algoridang\nestjs_server\node_modules\pg-protocol\src\parser.ts:103:30)
    at Socket.<anonymous> (C:\Users\Dos\Desktop\algoridang\nestjs_server\node_modules\pg-protocol\src\index.ts:7:48)
    at Socket.emit (events.js:315:20)
    at Socket.EventEmitter.emit (domain.js:467:12)
    at addChunk (internal/streams/readable.js:309:12)
    at readableAddChunk (internal/streams/readable.js:284:9)
    at Socket.Readable.push (internal/streams/readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)

• 위 메시지는 한정된 컨넥션의 갯수 때문에, 모든 클라이언트가 접속을 하지 못하는 상황이다.
• 마침 서비스의 베포를 준비하고 있었고, NodeJS는 클러스터 모드로 16개의 인스턴스를 생성 했다.

과제 (T)

• 그러다보니 컨넥션의 갯수를 늘리는 방법을 찾는 쪽으로 해결의 실마리가 보였다.

액션 (A) & 결과 (R)

• 목적 : 나는 커넥션 수를 조절하고 싶다.

https://hub.docker.com/_/postgres 에서 설정에 관한 자세한 설명이 있었따.

• 1. 아래 명령어로 max_connections 수 조절하기

$ docker run -d --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword postgres -c shared_buffers=256MB -c max_connections=200 

• 2. 직접 conf 파일수정해서 docker 생성시 옵션으로 주기

$ # run postgres with custom config
$ docker run -d --name some-postgres -v "$PWD/my-postgres.conf":/etc/postgresql/postgresql.conf -e POSTGRES_PASSWORD=mysecretpassword postgres -c 'config_file=/etc/postgresql/postgresql.conf'

• 아래 옵션파일을 수정해서, restart를 하도록 해야겠다. ( docker restart algoridang_db )

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()".  Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                TB = terabytes                     h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'           # use data in another directory
                                        # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
                                        # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
                                        # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = ''                 # write an extra PID file
                                        # (change requires restart)


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql'        # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

# - TCP settings -
# see "man tcp" for details

#tcp_keepalives_idle = 0                # TCP_KEEPIDLE, in seconds;
                                        # 0 selects the system default
#tcp_keepalives_interval = 0            # TCP_KEEPINTVL, in seconds;
                                        # 0 selects the system default
#tcp_keepalives_count = 0               # TCP_KEEPCNT;
                                        # 0 selects the system default
#tcp_user_timeout = 0                   # TCP_USER_TIMEOUT, in milliseconds;
                                        # 0 selects the system default

# - Authentication -

#authentication_timeout = 1min          # 1s-600s
#password_encryption = md5              # md5 or scram-sha-256
#db_user_namespace = off

# GSSAPI using Kerberos
#krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
#krb_caseins_users = off

# - SSL -

#ssl = off
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_key_file = 'server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off


#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 128MB                  # min 128kB
                                        # (change requires restart)
#huge_pages = try                       # on, off, or try
                                        # (change requires restart)
#temp_buffers = 8MB                     # min 800kB
#max_prepared_transactions = 0          # zero disables the feature
                                        # (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB                         # min 64kB
#hash_mem_multiplier = 1.0              # 1-1000.0 multiplier on hash table work_mem
#maintenance_work_mem = 64MB            # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#logical_decoding_work_mem = 64MB       # min 64kB
#max_stack_depth = 2MB                  # min 100kB
#shared_memory_type = mmap              # the default is the first option
                                        # supported by the operating system:
                                        #   mmap
                                        #   sysv
                                        #   windows
                                        # (change requires restart)
dynamic_shared_memory_type = posix      # the default is the first option
                                        # supported by the operating system:
                                        #   posix
                                        #   sysv
                                        #   windows
                                        #   mmap
                                        # (change requires restart)

# - Disk -

#temp_file_limit = -1                   # limits per-process temp file space