leafo / pgmoon

A pure Lua Postgres driver for use in OpenResty & more
MIT License
395 stars 93 forks source link

postgresql say too many clients already. #47

Closed yjdwbj closed 3 years ago

yjdwbj commented 7 years ago

Hello,here my postgresql conf.

grep -v '^#' /etc/postgresql/9.5/main/postgresql.conf  | grep -v '^$' | grep -v '#'
archive_mode = on
effective_cache_size = 20GB
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_checkpoints = on
log_disconnections = on
log_timezone = 'PRC'
stats_temp_directory = '/var/run/postgresql/9.5-main.pg_stat_tmp'
datestyle = 'iso, ymd'
timezone = 'PRC'
default_text_search_config = 'pg_catalog.english'
root@25-108:/home/www/test_openresty# grep -v '^#' /etc/postgresql/9.5/main/postgresql.conf  | grep -v '^$' 
data_directory = '/data/pgdata'     # use data in another directory
                    # (change requires restart)
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'   # host-based authentication file
                    # (change requires restart)
ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'   # ident configuration file
                    # (change requires restart)
external_pid_file = '/var/run/postgresql/9.5-main.pid'          # write an extra PID file
                    # (change requires restart)
listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
port = 5432             # (change requires restart)
max_connections = 4000                  # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                    # (change requires restart)
                    # (change requires restart)
                    # (change requires restart)
                    # (change requires restart)
ssl = true              # (change requires restart)
                    # (change requires restart)
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'      # (change requires restart)
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'     # (change requires restart)
                    # 0 selects the system default
                    # 0 selects the system default
                    # 0 selects the system default
shared_buffers = 16GB # min 128kB
                    # (change requires restart)
                    # (change requires restart)
temp_buffers = 32MB         # min 800kB
                    # (change requires restart)
work_mem = 8192MB               # min 64kB
maintenance_work_mem = 8192MB       # min 1MB
max_stack_depth = 4096          # min 100kB
dynamic_shared_memory_type = posix  # the default is the first option
                    # supported by the operating system:
                    #   posix
                    #   sysv
                    #   windows
                    #   mmap
                    # use none to disable dynamic shared memory
                    # in kB, or -1 for no limit
                    # (change requires restart)
vacuum_cost_delay = 10ms            # 0-100 milliseconds
vacuum_cost_limit = 10000       # 1-10000 credits
bgwriter_delay = 10ms           # 10-10000ms between rounds
wal_level =  hot_standby  # minimal, archive, hot_standby, or logical
                    # (change requires restart)
synchronous_commit = on     # synchronization level;
                    # off, local, remote_write, or on
                    # supported by the operating system:
                    #   open_datasync
                    #   fdatasync (default on Linux)
                    #   fsync
                    #   fsync_writethrough
                    #   open_sync
                    # (change requires restart)
wal_buffers = 16MB          # min 32kB, -1 sets based on shared_buffers
                    # (change requires restart)
wal_writer_delay = 20ms     # 1-10000 milliseconds
checkpoint_timeout = 5min       # range 30s-1h
archive_mode = on
                # (change requires restart)
                # placeholders: %p = path of file to archive
                #               %f = file name only
                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
                # number of seconds; 0 disables
max_wal_senders = 32        # max number of walsender processes
                # (change requires restart)
                # (change requires restart)
                # (change requires restart)
                # comma-separated list of application_name
                # from standby(s); '*' = all
hot_standby = on # "on" allows queries during recovery
                    # (change requires restart)
                    # when reading WAL from archive;
                    # -1 allows indefinite delay
                    # when reading streaming WAL;
                    # -1 allows indefinite delay
                    # 0 disables
                    # query conflicts
                    # communication from master
                    # in milliseconds; 0 disables
                    # retrieve WAL after a failed attempt
effective_cache_size = 20GB
                    # JOIN clauses
log_destination = 'csvlog'      # Valid values are combinations of
                    # stderr, csvlog, syslog, and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.
logging_collector = on      # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)
                    # can be absolute or relative to PGDATA
                    # can include strftime() escapes
                    # begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
                    # same name as the new log file will be
                    # truncated rather than appended to.
                    # But such truncation only occurs on
                    # time-driven rotation, not on restarts
                    # or size-driven rotation.  Default is
                    # off, meaning append to existing files
                    # in all cases.
log_rotation_age = 1d           # Automatic rotation of logfiles will
                    # happen after that time.  0 disables.
log_rotation_size = 10MB        # Automatic rotation of logfiles will
                    # happen after that much log output.
                    # 0 disables.
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
client_min_messages = notice # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   log
                    #   notice
                    #   warning
                    #   error
log_min_messages = notice # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic (effectively off)
log_min_duration_statement = 1000ms # -1 is disabled, 0 logs all statements
                    # and their durations, > 0 logs only
                    # statements running at least this number
                    # of milliseconds
log_checkpoints = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_line_prefix = '%t [%p-%l] %q%u@%d '         # special values:
                    #   %a = application name
                    #   %u = user name
                    #   %d = database name
                    #   %r = remote host and port
                    #   %h = remote host
                    #   %p = process ID
                    #   %t = timestamp without milliseconds
                    #   %m = timestamp with milliseconds
                    #   %i = command tag
                    #   %e = SQL state
                    #   %c = session ID
                    #   %l = session line number
                    #   %s = session start timestamp
                    #   %v = virtual transaction ID
                    #   %x = transaction ID (0 if none)
                    #   %q = stop here in non-session
                    #        processes
                    #   %% = '%'
                    # e.g. '<%u%%%d> '
log_lock_waits = on # log lock waits >= deadlock_timeout
                    # than the specified size in kilobytes;
                    # -1 disables, 0 logs all temp files
log_timezone = 'PRC'
                    # (change requires restart)
stats_temp_directory = '/var/run/postgresql/9.5-main.pg_stat_tmp'
autovacuum = on         # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
log_autovacuum_min_duration = 0     # -1 disables, 0 logs all actions and
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
                    # (change requires restart)
                    # vacuum
                    # analyze
                    # (change requires restart)
                    # before forced vacuum
                    # (change requires restart)
                    # autovacuum, in milliseconds;
                    # -1 means use vacuum_cost_delay
                    # autovacuum, -1 means use
                    # vacuum_cost_limit
                    # only default tablespace
datestyle = 'iso, ymd'
timezone = 'PRC'
                    # abbreviations.  Currently, there are
                    #   Default
                    #   Australia (historical usage)
                    #   India
                    # You can create your own file in
                    # share/timezonesets/.
                    # encoding
lc_messages = 'en_US.UTF-8'         # locale for system error message
                    # strings
lc_monetary = 'zh_CN.UTF-8'         # locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8'          # locale for number formatting
lc_time = 'zh_CN.UTF-8'             # locale for time formatting
default_text_search_config = 'pg_catalog.english'
                    # (change requires restart)
                    # (change requires restart)
                    # directory 'conf.d'
root@25-108:/home/www/test_openresty# ^C
root@25-108:/home/www/test_openresty# grep -v '^#' /etc/postgresql/9.5/main/postgresql.conf  | grep -v '^$'  
data_directory = '/data/pgdata'     # use data in another directory
                    # (change requires restart)
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'   # host-based authentication file
                    # (change requires restart)
ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'   # ident configuration file
                    # (change requires restart)
external_pid_file = '/var/run/postgresql/9.5-main.pid'          # write an extra PID file
                    # (change requires restart)
listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
port = 5432             # (change requires restart)
max_connections = 4000                  # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                    # (change requires restart)
                    # (change requires restart)
                    # (change requires restart)
                    # (change requires restart)
ssl = true              # (change requires restart)
                    # (change requires restart)
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'      # (change requires restart)
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'     # (change requires restart)
                    # 0 selects the system default
                    # 0 selects the system default
                    # 0 selects the system default
shared_buffers = 16GB # min 128kB
                    # (change requires restart)
                    # (change requires restart)
temp_buffers = 32MB         # min 800kB
                    # (change requires restart)
work_mem = 8192MB               # min 64kB
maintenance_work_mem = 8192MB       # min 1MB
max_stack_depth = 4096          # min 100kB
dynamic_shared_memory_type = posix  # the default is the first option
                    # supported by the operating system:
                    #   posix
                    #   sysv
                    #   windows
                    #   mmap
                    # use none to disable dynamic shared memory
                    # in kB, or -1 for no limit
                    # (change requires restart)
vacuum_cost_delay = 10ms            # 0-100 milliseconds
vacuum_cost_limit = 10000       # 1-10000 credits
bgwriter_delay = 10ms           # 10-10000ms between rounds
wal_level =  hot_standby  # minimal, archive, hot_standby, or logical
                    # (change requires restart)
synchronous_commit = on     # synchronization level;
                    # off, local, remote_write, or on
                    # supported by the operating system:
                    #   open_datasync
                    #   fdatasync (default on Linux)
                    #   fsync
                    #   fsync_writethrough
                    #   open_sync
                    # (change requires restart)
wal_buffers = 16MB          # min 32kB, -1 sets based on shared_buffers
                    # (change requires restart)
wal_writer_delay = 20ms     # 1-10000 milliseconds
checkpoint_timeout = 5min       # range 30s-1h
archive_mode = on
                # (change requires restart)
                # placeholders: %p = path of file to archive
                #               %f = file name only
                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
                # number of seconds; 0 disables
max_wal_senders = 32        # max number of walsender processes
                # (change requires restart)
                # (change requires restart)
                # (change requires restart)
                # comma-separated list of application_name
                # from standby(s); '*' = all
hot_standby = on # "on" allows queries during recovery
                    # (change requires restart)
                    # when reading WAL from archive;
                    # -1 allows indefinite delay
                    # when reading streaming WAL;
                    # -1 allows indefinite delay
                    # 0 disables
                    # query conflicts
                    # communication from master
                    # in milliseconds; 0 disables
                    # retrieve WAL after a failed attempt
effective_cache_size = 20GB
                    # JOIN clauses
log_destination = 'csvlog'      # Valid values are combinations of
                    # stderr, csvlog, syslog, and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.
logging_collector = on      # Enable capturing of stderr and csvlog

016-12-20 11:14:09 CST [24633-1] [unknown]@[unknown] LOG: incomplete startup packet 2016-12-20 11:14:09 CST [25669-1] mqtt@test_mqtt FATAL: sorry, too many clients already 2016-12-20 11:14:09 CST [25667-1] mqtt@test_mqtt FATAL: remaining connection slots are reserved for non-replication superuser connections 2016-12-20 11:14:09 CST [25668-1] mqtt@test_mqtt FATAL: remaining connection slots are reserved for non-replication superuser connections 2016-12-20 11:14:09 CST [25670-1] mqtt@test_mqtt FATAL: remaining connection slots are reserved for non-replication superuser connections

leafo commented 7 years ago

are you closing your connections or re-using them after you're done with them?