RE: why wal_max_size does not work? - Mailing list pgsql-bugs
| From | Fan Liu |
|---|---|
| Subject | RE: why wal_max_size does not work? |
| Date | |
| Msg-id | HE1PR0701MB256955E71E222942F36D48F99EC10@HE1PR0701MB2569.eurprd07.prod.outlook.com Whole thread Raw |
| In response to | RE: why wal_max_size does not work? (Fan Liu <fan.liu@ericsson.com>) |
| List | pgsql-bugs |
Update:
This issue happens again. We really hope someone from PostgreSQL development could provide some suggestion. At least
someworkaround could avid issue like this.
---------- LOG on replica NODE/POD--------------
postgres=# select pg_walfile_name( pg_current_wal_lsn());
ERROR: recovery is in progress
HINT: WAL control functions cannot be executed during recovery.
postgres=# SHOW max_wal_size;
max_wal_size
--------------
1GB
(1 row)
postgres=# show archive_mode;
archive_mode
--------------
off
(1 row)
postgres=# show archive_command;
archive_command
-----------------
(disabled)
(1 row)
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start |
backend_xmin| state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_pr
iority | sync_state
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+-----------+------------+-----------+-----------+------------+--------
-------+------------
(0 rows)
postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin |
restart_lsn| confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)
---------------- LOG on primary NODE/POD ------------
postgres=# select pg_walfile_name( pg_current_wal_lsn());
pg_walfile_name
--------------------------
000001310000000000000009
(1 row)
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |
backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | f
lush_lag | replay_lag | sync_priority | sync_state
-------+----------+---------+----------------------+-----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+--
---------+------------+---------------+------------
85707 | 16398 | replica | eric-adp-ss7-db-pg-1 | 192.168.127.167 | | 37592 | 2020-04-09
01:19:30.895663+00| | streaming | 0/9030318 | 0/9030318 | 0/9030318 | 0/9030318 | |
| | 1 | sync
85708 | 16398 | replica | eric-adp-ss7-db-pg-0 | 192.168.186.103 | | 40294 | 2020-04-09
01:19:30.895917+00| | streaming | 0/9030318 | 0/9030318 | 0/9030318 | 0/9030318 | |
| | 0 | async
(2 rows)
postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin
|restart_lsn | confirmed_flush_lsn
----------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
eric_adp_ss7_db_pg_1 | | physical | | | f | t | 85707 | |
|0/9030318 |
eric_adp_ss7_db_pg_0 | | physical | | | f | t | 85708 | |
|0/9030318 |
(2 rows)
postgres=#
postgres=# SHOW max_wal_size;
max_wal_size
--------------
1GB
(1 row)
eric-adp-ss7-db-pg-2:/var/lib/postgresql/data/pgdata/pg_wal # du -h .
16K ./archive_status
3.3G .
BRs,
Fan Liu
ADP Document Database PG
>>-----Original Message-----
>>From: Fan Liu
>>Sent: 2020年4月1日 14:51
>>To: 'Jaime Casanova' <jaime.casanova@2ndquadrant.com>
>>Cc: 'Sergei Kornilov' <sk@zsrv.org>; 'PostgreSQL mailing lists'
>><pgsql-bugs@lists.postgresql.org>
>>Subject: RE: why wal_max_size does not work?
>>
>>Hi Jaime,
>>
>>Our wal_keep_segments = '8'
>>
>>And I need update some finding from my side.
>>We found that our program has a BUG, which some log files may not cleaned as
>>expected.
>>Those log files may not much, but day by day, it could possible to have a lot
>>of logs. Then it's possible cause "no space left"
>>
>>Base on the previous info
>>>>also, show us the output of: select * from pg_replication_slots if
>>>>there's any replication slot not connected to the server is
>>>>protecting, primary will keep the wals until the server comes back
>>
>>I have an assumption
>>1) log out of control, keep increasing
>>2) one of the NODE met "no space left", become cycling restart.
>>3) the Master NODE keep WAL files for that replica, waiting for replica up and
>>sync data.
>>4) rest of two NODE are full filled with WAL files.
>>
>>Could you help me with below question?
>>1) When replica NODE recovered and able to connect to primary, "max_wal_size"
>>should start to be effective, right?
>>2) Is there more scenario could cause WAL file not recycling? (except consider
>>high traffic load in a short time)
>>
>>BRs,
>>Fan Liu
>>ADP Document Database PG
>>
>>>>-----Original Message-----
>>>>From: Fan Liu
>>>>Sent: 2020年3月31日 9:24
>>>>To: 'Jaime Casanova' <jaime.casanova@2ndquadrant.com>
>>>>Cc: 'Sergei Kornilov' <sk@zsrv.org>; 'PostgreSQL mailing lists'
>>>><pgsql-bugs@lists.postgresql.org>
>>>>Subject: RE: why wal_max_size does not work?
>>>>
>>>>Hello，
>>>>
>>>>Any update?
>>>>
>>>>I have another question.
>>>>Now I have recovered the NODE by deleted some old WAL files. But I
>>>>still keep the pg_wal folder around 2.4G.
>>>>Currently, all replicas are up and working. On the replica NODE, the
>>>>WAL files still keep and looks like will not be recycling.
>>>>So, is this an expected behavior? Should I clean up those old WAL files? (e.g.
>>>>use pg_archivecleanup?)
>>>>
>>>>
>>>>BRs,
>>>>Fan Liu
>>>>ADP Document Database PG
>>>>
>>>>>>-----Original Message-----
>>>>>>From: Fan Liu
>>>>>>Sent: 2020年3月27日 15:12
>>>>>>To: Jaime Casanova <jaime.casanova@2ndquadrant.com>
>>>>>>Cc: Sergei Kornilov <sk@zsrv.org>; PostgreSQL mailing lists
>>>>>><pgsql-bugs@lists.postgresql.org>
>>>>>>Subject: RE: why wal_max_size does not work?
>>>>>>
>>>>>>Hello Jaime,
>>>>>>
>>>>>>Thanks for the supporting.
>>>>>>
>>>>>>>>also, show us the output of: select * from pg_replication_slots if
>>>>>>>>there's any replication slot not connected to the server is
>>>>>>>>protecting, primary will keep the wals until the server comes back
>>>>>>
>>>>>>One more question related with above scenario. If the replica NODE
>>>>>>fixed and reconnect to primary success after some time.
>>>>>>Then will the WAL be recycling?
>>>>>>
>>>>>>If I config archive, will this kind of issue be solved?
>>>>>>
>>>>>>
>>>>>>Just in case I will also provide the log you requested.
>>>>>>
>>>>>>postgres=# select * from pg_replication_slots;
>>>>>> slot_name | plugin | slot_type | datoid | database | temporary
>>|
>>>>>>active | active_pid | xmin | catalog_xmin | restart_lsn |
>>>>>>confirmed_flush_lsn
>>>>>>----------------------+--------+-----------+--------+----------+----
>>>>>>----------------------+--------+-----------+--------+----------+--
>>>>>>----------------------+--------+-----------+--------+----------+----
>>>>>>-+--------+------------+------+--------------+-------------+--------
>>>>>>-+--------+------------+------+--------------+-------------+--
>>>>>>-+--------+------------+------+--------------+-------------+----
>>>>>>-------
>>>>>> eric_adp_ss7_db_pg_1 | | physical | | | f |
>>t
>>>>>>| 98153 | | | 0/9003A78 |
>>>>>> eric_adp_ss7_db_pg_2 | | physical | | | f |
>>t
>>>>>>| 98154 | | | 0/9003A78 |
>>>>>>(2 rows)
>>>>>>
>>>>>>postgres=#
>>>>>>
>>>>>>--------------- postgresql.base.conf --------------------------- cat
>>>>>>/var/lib/postgresql/data/pgdata/postgresql.base.conf
>>>>>># -----------------------------
>>>>>># 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 = 'localhost' # 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 = 100 # (change requires restart)
>>>>>>#superuser_reserved_connections = 3 # (change requires restart)
>>>>>>#unix_socket_directories = '/var/run/postgresql, /tmp' #
>>>>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)
>>>>>>
>>>>>># - Security and Authentication -
>>>>>>
>>>>>>#authentication_timeout = 1min # 1s-600s
>>>>>>#ssl = off
>>>>>>#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
>>>>>>#ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1'
>>>>>>#ssl_dh_params_file = ''
>>>>>>#ssl_cert_file = 'server.crt'
>>>>>>#ssl_key_file = 'server.key'
>>>>>>#ssl_ca_file = ''
>>>>>>#ssl_crl_file = ''
>>>>>>#password_encryption = md5 # md5 or scram-sha-256
>>>>>>#db_user_namespace = off
>>>>>>#row_security = on
>>>>>>
>>>>>># GSSAPI using Kerberos
>>>>>>#krb_server_keyfile = ''
>>>>>>#krb_caseins_users = off
>>>>>>
>>>>>># - TCP Keepalives -
>>>>>># see "man 7 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
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># 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
>>>>>>#maintenance_work_mem = 64MB # min 1MB
>>>>>>#replacement_sort_tuples = 150000 # limits use of replacement selection
>>>>sort
>>>>>>#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
>>>>>>#max_stack_depth = 2MB # 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
>>>>>> # (change requires restart)
>>>>>>
>>>>>># - Disk -
>>>>>>
>>>>>>#temp_file_limit = -1 # limits per-process temp file space
>>>>>> # in kB, or -1 for no limit
>>>>>>
>>>>>># - Kernel Resource Usage -
>>>>>>
>>>>>>#max_files_per_process = 1000 # min 25
>>>>>> # (change requires restart)
>>>>>>#shared_preload_libraries = '' # (change requires restart)
>>>>>>
>>>>>># - Cost-Based Vacuum Delay -
>>>>>>
>>>>>>#vacuum_cost_delay = 0 # 0-100 milliseconds
>>>>>>#vacuum_cost_page_hit = 1 # 0-10000 credits
>>>>>>#vacuum_cost_page_miss = 10 # 0-10000 credits
>>>>>>#vacuum_cost_page_dirty = 20 # 0-10000 credits
>>>>>>#vacuum_cost_limit = 200 # 1-10000 credits
>>>>>>
>>>>>># - Background Writer -
>>>>>>
>>>>>>#bgwriter_delay = 200ms # 10-10000ms between rounds
>>>>>>#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
>>>>>>#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers
>>>>>>scanned/round
>>>>>>#bgwriter_flush_after = 512kB # measured in pages, 0 disables
>>>>>>
>>>>>># - Asynchronous Behavior -
>>>>>>
>>>>>>#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
>>>>>>#max_worker_processes = 8 # (change requires restart)
>>>>>>#max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
>>>>>>#max_parallel_workers = 8 # maximum number of max_worker_processes
>>>>that
>>>>>> # can be used in parallel queries
>>>>>>#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate
>>>>>> # (change requires restart)
>>>>>>#backend_flush_after = 0 # measured in pages, 0 disables
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># WRITE AHEAD LOG
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>># - Settings -
>>>>>>
>>>>>>#wal_level = replica # minimal, replica, or logical
>>>>>> # (change requires restart)
>>>>>>#fsync = on # flush data to disk for crash safety
>>>>>> # (turning this off can cause
>>>>>> # unrecoverable data corruption)
>>>>>>#synchronous_commit = on # synchronization level;
>>>>>> # off, local, remote_write, remote_apply, or on
>>>>>>#wal_sync_method = fsync # the default is the first option
>>>>>> # supported by the operating system:
>>>>>> # open_datasync
>>>>>> # fdatasync (default on Linux)
>>>>>> # fsync
>>>>>> # fsync_writethrough
>>>>>> # open_sync
>>>>>>#full_page_writes = on # recover from partial page writes
>>>>>>#wal_compression = off # enable compression of full-page writes
>>>>>>#wal_log_hints = off # also do full page writes of non-critical
>>>>>>updates
>>>>>> # (change requires restart)
>>>>>>#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
>>>>>> # (change requires restart)
>>>>>>#wal_writer_delay = 200ms # 1-10000 milliseconds
>>>>>>#wal_writer_flush_after = 1MB # measured in pages, 0 disables
>>>>>>
>>>>>>#commit_delay = 0 # range 0-100000, in microseconds
>>>>>>#commit_siblings = 5 # range 1-1000
>>>>>>
>>>>>># - Checkpoints -
>>>>>>
>>>>>>#checkpoint_timeout = 5min # range 30s-1d
>>>>>>#max_wal_size = 1GB
>>>>>>#min_wal_size = 80MB
>>>>>>#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 -
>>>>1.0
>>>>>>#checkpoint_flush_after = 256kB # measured in pages, 0 disables
>>>>>>#checkpoint_warning = 30s # 0 disables
>>>>>>
>>>>>># - Archiving -
>>>>>>
>>>>>>#archive_mode = off # enables archiving; off, on, or always
>>>>>> # (change requires restart)
>>>>>>#archive_command = '' # command to use to archive a logfile segment
>>>>>> # 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'
>>>>>>#archive_timeout = 0 # force a logfile segment switch after this
>>>>>> # number of seconds; 0 disables
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># REPLICATION
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>># - Sending Server(s) -
>>>>>>
>>>>>># Set these on the master and on any standby that will send replication
>>data.
>>>>>>
>>>>>>#max_wal_senders = 10 # max number of walsender processes
>>>>>> # (change requires restart)
>>>>>>#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
>>>>>>#wal_sender_timeout = 60s # in milliseconds; 0 disables
>>>>>>
>>>>>>#max_replication_slots = 10 # max number of replication slots
>>>>>> # (change requires restart)
>>>>>>#track_commit_timestamp = off # collect timestamp of transaction commit
>>>>>> # (change requires restart)
>>>>>>
>>>>>># - Master Server -
>>>>>>
>>>>>># These settings are ignored on a standby server.
>>>>>>
>>>>>>#synchronous_standby_names = '' # standby servers that provide sync rep
>>>>>> # method to choose sync standbys, number of sync standbys,
>>>>>> # and comma-separated list of application_name
>>>>>> # from standby(s); '*' = all
>>>>>>#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
>>>>>>
>>>>>># - Standby Servers -
>>>>>>
>>>>>># These settings are ignored on a master server.
>>>>>>
>>>>>>#hot_standby = on # "off" disallows queries during recovery
>>>>>> # (change requires restart)
>>>>>>#max_standby_archive_delay = 30s # max delay before canceling queries
>>>>>> # when reading WAL from archive;
>>>>>> # -1 allows indefinite delay
>>>>>>#max_standby_streaming_delay = 30s # max delay before canceling queries
>>>>>> # when reading streaming WAL;
>>>>>> # -1 allows indefinite delay
>>>>>>#wal_receiver_status_interval = 10s # send replies at least this often
>>>>>> # 0 disables
>>>>>>#hot_standby_feedback = off # send info from standby to prevent
>>>>>> # query conflicts
>>>>>>#wal_receiver_timeout = 60s # time that receiver waits for
>>>>>> # communication from master
>>>>>> # in milliseconds; 0 disables
>>>>>>#wal_retrieve_retry_interval = 5s # time to wait before retrying to
>>>>>> # retrieve WAL after a failed attempt
>>>>>>
>>>>>># - Subscribers -
>>>>>>
>>>>>># These settings are ignored on a publisher.
>>>>>>
>>>>>>#max_logical_replication_workers = 4 # taken from max_worker_processes
>>>>>> # (change requires restart)
>>>>>>#max_sync_workers_per_subscription = 2 # taken from
>>>>>>max_logical_replication_workers
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># QUERY TUNING
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>># - Planner Method Configuration -
>>>>>>
>>>>>>#enable_bitmapscan = on
>>>>>>#enable_hashagg = on
>>>>>>#enable_hashjoin = on
>>>>>>#enable_indexscan = on
>>>>>>#enable_indexonlyscan = on
>>>>>>#enable_material = on
>>>>>>#enable_mergejoin = on
>>>>>>#enable_nestloop = on
>>>>>>#enable_seqscan = on
>>>>>>#enable_sort = on
>>>>>>#enable_tidscan = on
>>>>>>
>>>>>># - Planner Cost Constants -
>>>>>>
>>>>>>#seq_page_cost = 1.0 # measured on an arbitrary scale
>>>>>>#random_page_cost = 4.0 # same scale as above
>>>>>>#cpu_tuple_cost = 0.01 # same scale as above
>>>>>>#cpu_index_tuple_cost = 0.005 # same scale as above
>>>>>>#cpu_operator_cost = 0.0025 # same scale as above
>>>>>>#parallel_tuple_cost = 0.1 # same scale as above
>>>>>>#parallel_setup_cost = 1000.0 # same scale as above
>>>>>>#min_parallel_table_scan_size = 8MB
>>>>>>#min_parallel_index_scan_size = 512kB #effective_cache_size = 4GB
>>>>>>
>>>>>># - Genetic Query Optimizer -
>>>>>>
>>>>>>#geqo = on
>>>>>>#geqo_threshold = 12
>>>>>>#geqo_effort = 5 # range 1-10
>>>>>>#geqo_pool_size = 0 # selects default based on effort
>>>>>>#geqo_generations = 0 # selects default based on effort
>>>>>>#geqo_selection_bias = 2.0 # range 1.5-2.0
>>>>>>#geqo_seed = 0.0 # range 0.0-1.0
>>>>>>
>>>>>># - Other Planner Options -
>>>>>>
>>>>>>#default_statistics_target = 100 # range 1-10000
>>>>>>#constraint_exclusion = partition # on, off, or partition
>>>>>>#cursor_tuple_fraction = 0.1 # range 0.0-1.0
>>>>>>#from_collapse_limit = 8
>>>>>>#join_collapse_limit = 8 # 1 disables collapsing of explicit
>>>>>> # JOIN clauses
>>>>>>#force_parallel_mode = off
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># ERROR REPORTING AND LOGGING
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>># - Where to Log -
>>>>>>
>>>>>>log_destination = 'stderr' # Valid values are combinations of
>>>>>> # stderr, csvlog, syslog, and eventlog,
>>>>>> # depending on platform. csvlog
>>>>>> # requires logging_collector to be on.
>>>>>>
>>>>>># This is used when logging to stderr:
>>>>>>logging_collector = on # Enable capturing of stderr and csvlog
>>>>>> # into log files. Required to be on for
>>>>>> # csvlogs.
>>>>>> # (change requires restart)
>>>>>>
>>>>>># These are only used if logging_collector is on:
>>>>>>#log_directory = 'log' # directory where log files are written,
>>>>>> # can be absolute or relative to PGDATA
>>>>>>#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name
>>pattern,
>>>>>> # can include strftime() escapes
>>>>>>#log_file_mode = 0600 # creation mode for log files,
>>>>>> # begin with 0 to use octal notation
>>>>>>#log_truncate_on_rotation = off # 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.
>>>>>>
>>>>>># These are relevant when logging to syslog:
>>>>>>#syslog_facility = 'LOCAL0'
>>>>>>#syslog_ident = 'postgres'
>>>>>>#syslog_sequence_numbers = on
>>>>>>#syslog_split_messages = on
>>>>>>
>>>>>># This is only relevant when logging to eventlog (win32):
>>>>>># (change requires restart)
>>>>>>#event_source = 'PostgreSQL'
>>>>>>
>>>>>># - When to Log -
>>>>>>
>>>>>>#log_min_messages = warning # values in order of decreasing detail:
>>>>>> # debug5
>>>>>> # debug4
>>>>>> # debug3
>>>>>> # debug2
>>>>>> # debug1
>>>>>> # info
>>>>>> # notice
>>>>>> # warning
>>>>>> # error
>>>>>> # log
>>>>>> # fatal
>>>>>> # panic
>>>>>>
>>>>>>#log_min_error_statement = error # values in order of decreasing detail:
>>>>>> # debug5
>>>>>> # debug4
>>>>>> # debug3
>>>>>> # debug2
>>>>>> # debug1
>>>>>> # info
>>>>>> # notice
>>>>>> # warning
>>>>>> # error
>>>>>> # log
>>>>>> # fatal
>>>>>> # panic (effectively off)
>>>>>>
>>>>>>#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
>>>>>> # and their durations, > 0 logs only
>>>>>> # statements running at least this number
>>>>>> # of milliseconds
>>>>>>
>>>>>>
>>>>>># - What to Log -
>>>>>>
>>>>>>#debug_print_parse = off
>>>>>>#debug_print_rewritten = off
>>>>>>#debug_print_plan = off
>>>>>>#debug_pretty_print = on
>>>>>>#log_checkpoints = off
>>>>>>#log_connections = off
>>>>>>#log_disconnections = off
>>>>>>#log_duration = off
>>>>>>#log_error_verbosity = default # terse, default, or verbose messages
>>>>>>#log_hostname = off
>>>>>>#log_line_prefix = '%m [%p] ' # special values:
>>>>>>log_line_prefix = '%m %d %u [%p]'
>>>>>> # %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
>>>>>> # %n = timestamp with milliseconds (as a Unix epoch)
>>>>>> # %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 = off # log lock waits >= deadlock_timeout
>>>>>>#log_statement = 'none' # none, ddl, mod, all
>>>>>>#log_replication_commands = off
>>>>>>#log_temp_files = -1 # log temporary files equal or larger
>>>>>> # than the specified size in kilobytes;
>>>>>> # -1 disables, 0 logs all temp files log_timezone = 'UTC'
>>>>>>
>>>>>>
>>>>>># - Process Title -
>>>>>>
>>>>>>#cluster_name = '' # added to process titles if nonempty
>>>>>> # (change requires restart)
>>>>>>#update_process_title = on
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># RUNTIME STATISTICS
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>># - Query/Index Statistics Collector -
>>>>>>
>>>>>>#track_activities = on
>>>>>>#track_counts = on
>>>>>>#track_io_timing = off
>>>>>>#track_functions = none # none, pl, all
>>>>>>#track_activity_query_size = 1024 # (change requires restart)
>>>>>>#stats_temp_directory = 'pg_stat_tmp'
>>>>>>
>>>>>>
>>>>>># - Statistics Monitoring -
>>>>>>
>>>>>>#log_parser_stats = off
>>>>>>#log_planner_stats = off
>>>>>>#log_executor_stats = off
>>>>>>#log_statement_stats = off
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># AUTOVACUUM PARAMETERS
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>>#autovacuum = on # Enable autovacuum subprocess? 'on'
>>>>>> # requires track_counts to also be on.
>>>>>>#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
>>>>>> # their durations, > 0 logs only
>>>>>> # actions running at least this number
>>>>>> # of milliseconds.
>>>>>>#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
>>>>>> # (change requires restart)
>>>>>>#autovacuum_naptime = 1min # time between autovacuum runs
>>>>>>#autovacuum_vacuum_threshold = 50 # min number of row updates before
>>>>>> # vacuum
>>>>>>#autovacuum_analyze_threshold = 50 # min number of row updates before
>>>>>> # analyze
>>>>>>#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
>>>>>>vacuum
>>>>>>#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
>>>>>>analyze
>>>>>>#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
>>>>>>vacuum
>>>>>> # (change requires restart)
>>>>>>#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact
>>age
>>>>>> # before forced vacuum
>>>>>> # (change requires restart)
>>>>>>#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
>>>>>> # autovacuum, in milliseconds;
>>>>>> # -1 means use vacuum_cost_delay
>>>>>>#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
>>>>>> # autovacuum, -1 means use
>>>>>> # vacuum_cost_limit
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># CLIENT CONNECTION DEFAULTS
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>># - Statement Behavior -
>>>>>>
>>>>>>#client_min_messages = notice # values in order of decreasing detail:
>>>>>> # debug5
>>>>>> # debug4
>>>>>> # debug3
>>>>>> # debug2
>>>>>> # debug1
>>>>>> # log
>>>>>> # notice
>>>>>> # warning
>>>>>> # error
>>>>>>#search_path = '"$user", public' # schema names
>>>>>>#default_tablespace = '' # a tablespace name, '' uses the default
>>>>>>#temp_tablespaces = '' # a list of tablespace names, '' uses
>>>>>> # only default tablespace
>>>>>>#check_function_bodies = on
>>>>>>#default_transaction_isolation = 'read committed'
>>>>>>#default_transaction_read_only = off #default_transaction_deferrable
>>>>>>= off #session_replication_role = 'origin'
>>>>>>#statement_timeout = 0 # in milliseconds, 0 is disabled
>>>>>>#lock_timeout = 0 # in milliseconds, 0 is disabled
>>>>>>#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
>>>>>>#vacuum_freeze_min_age = 50000000
>>>>>>#vacuum_freeze_table_age = 150000000
>>>>>>#vacuum_multixact_freeze_min_age = 5000000
>>>>>>#vacuum_multixact_freeze_table_age = 150000000
>>>>>>#bytea_output = 'hex' # hex, escape
>>>>>>#xmlbinary = 'base64'
>>>>>>#xmloption = 'content'
>>>>>>#gin_fuzzy_search_limit = 0
>>>>>>#gin_pending_list_limit = 4MB
>>>>>>
>>>>>># - Locale and Formatting -
>>>>>>
>>>>>>datestyle = 'iso, mdy'
>>>>>>#intervalstyle = 'postgres'
>>>>>>timezone = 'UTC'
>>>>>>#timezone_abbreviations = 'Default' # Select the set of available time
>>>>zone
>>>>>> # abbreviations. Currently, there are
>>>>>> # Default
>>>>>> # Australia (historical usage)
>>>>>> # India
>>>>>> # You can create your own file in
>>>>>> # share/timezonesets/.
>>>>>>#extra_float_digits = 0 # min -15, max 3
>>>>>>#client_encoding = sql_ascii # actually, defaults to database
>>>>>> # encoding
>>>>>>
>>>>>># These settings are initialized by initdb, but they can be changed.
>>>>>>lc_messages = 'en_US.UTF-8' # locale for system error message
>>>>>> # strings
>>>>>>lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
>>>>>>lc_numeric = 'en_US.UTF-8' # locale for number formatting
>>>>>>lc_time = 'en_US.UTF-8' # locale for time formatting
>>>>>>
>>>>>># default configuration for text search default_text_search_config =
>>>>>>'pg_catalog.english'
>>>>>>
>>>>>># - Other Defaults -
>>>>>>
>>>>>>#dynamic_library_path = '$libdir'
>>>>>>#local_preload_libraries = ''
>>>>>>#session_preload_libraries = ''
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># LOCK MANAGEMENT
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>>#deadlock_timeout = 1s
>>>>>>#max_locks_per_transaction = 64 # min 10
>>>>>> # (change requires restart)
>>>>>>#max_pred_locks_per_transaction = 64 # min 10
>>>>>> # (change requires restart)
>>>>>>#max_pred_locks_per_relation = -2 # negative values mean
>>>>>> # (max_pred_locks_per_transaction
>>>>>> # / -max_pred_locks_per_relation) - 1
>>>>>>#max_pred_locks_per_page = 2 # min 0
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># VERSION/PLATFORM COMPATIBILITY
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>># - Previous PostgreSQL Versions -
>>>>>>
>>>>>>#array_nulls = on
>>>>>>#backslash_quote = safe_encoding # on, off, or safe_encoding
>>>>>>#default_with_oids = off
>>>>>>#escape_string_warning = on
>>>>>>#lo_compat_privileges = off
>>>>>>#operator_precedence_warning = off
>>>>>>#quote_all_identifiers = off
>>>>>>#standard_conforming_strings = on
>>>>>>#synchronize_seqscans = on
>>>>>>
>>>>>># - Other Platforms and Clients -
>>>>>>
>>>>>>#transform_null_equals = off
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># ERROR HANDLING
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>>#exit_on_error = off # terminate session on any error?
>>>>>>#restart_after_crash = on # reinitialize after backend crash?
>>>>>>#data_sync_retry = off # retry or panic on failure to fsync
>>>>>> # data?
>>>>>> # (change requires restart)
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># CONFIG FILE INCLUDES
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>># These options allow settings to be loaded from files other than
>>>>>>the # default postgresql.conf.
>>>>>>
>>>>>>#include_dir = '' # include files ending in '.conf' from
>>>>>> # a directory, e.g., 'conf.d'
>>>>>>#include_if_exists = '' # include file only if it exists
>>>>>>#include = '' # include file
>>>>>>
>>>>>>
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>># CUSTOMIZED OPTIONS
>>>>>>#-------------------------------------------------------------------
>>>>>>--
>>>>>>----
>>>>>>-----
>>>>>>
>>>>>># Add settings for extensions here
>>>>>>
>>>>>>
>>>>>>BRs,
>>>>>>Fan Liu
>>>>>>ADP Document Database PG
>>>>>>
>>>>>>>>-----Original Message-----
>>>>>>>>From: Jaime Casanova <jaime.casanova@2ndquadrant.com>
>>>>>>>>Sent: 2020年3月27日 14:36
>>>>>>>>To: Fan Liu <fan.liu@ericsson.com>
>>>>>>>>Cc: Sergei Kornilov <sk@zsrv.org>; PostgreSQL mailing lists
>>>>>>>><pgsql-bugs@lists.postgresql.org>
>>>>>>>>Subject: Re: why wal_max_size does not work?
>>>>>>>>
>>>>>>>>On Fri, 27 Mar 2020 at 01:10, Fan Liu <fan.liu@ericsson.com> wrote:
>>>>>>>>>
>>>>>>>>> So, I wan to know under what kind of scenario that there will
>>>>>>>>> not be recycling
>>>>>>>>old WAL files.
>>>>>>>>> Is there any way I can check the recycling? For example, logs?
>>>>>>>>>
>>>>>>>>
>>>>>>>>please, i would like to see the contents of postgresql.base.conf',
>>>>>>>>maybe is changing a parameter that is not touched in
>>>>>>>>postgresql.conf, and postgresql.auto.conf for the same reason
>>>>>>>>
>>>>>>>>also, show us the output of: select * from pg_replication_slots if
>>>>>>>>there's any replication slot not connected to the server is
>>>>>>>>protecting, primary will keep the wals until the server comes back
>>>>>>>>
>>>>>>>>finally, while this seems not to be your case, a high
>>>>>>>>wal_keep_segments can cause this problem too
>>>>>>>>
>>>>>>>>--
>>>>>>>>Jaime Casanova www.2ndQuadrant.com
>>>>>>>>PostgreSQL Development, 24x7 Support, Remote DBA, Training &
>>>>>>>>Services
pgsql-bugs by date: