生物网站模板黑龙江省建筑工程网

生物网站模板,黑龙江省建筑工程网,综合型网站建设,环保网站建设1.关闭归档模式#xff1a;需重启pg 2.非活跃的复制槽会阻止WAL日志清理。检查复制槽状态#xff1a; 如果发现activefalse的复制槽且delay_size很大#xff0c;说明该复制槽阻塞了WAL清理。根据业务需求决定是否删除#xff1a; 处理过程如下#xff1a; [rootpg pg_w…1.关闭归档模式需重启pg2.非活跃的复制槽会阻止WAL日志清理。检查复制槽状态如果发现activefalse的复制槽且delay_size很大说明该复制槽阻塞了WAL清理。根据业务需求决定是否删除处理过程如下[rootpg pg_wal]# du -sh *|wc -l1569[rootpg pg_wal]# pwd/opt/pgsql/data/pg_wal[rootpg pg_wal]# ll |headtotal 25776640-rw------- 1 postgres postgres 16777216 Dec 16 08:48 0000000100000A6E00000003-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000004-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000005-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000006-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000007-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000008-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000009-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E0000000A-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E0000000B[rootpg pg_wal]# ls -lt|headtotal 25891328-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002Edrwx------ 2 postgres postgres 3764224 Dec 16 09:33 archive_status-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002D-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002C-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002B-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002A-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A7400000029-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A7400000028-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A7400000027[rootpg pg_wal]# ls -l|headtotal 25989632-rw------- 1 postgres postgres 16777216 Dec 16 08:48 0000000100000A6E00000003-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000004-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000005-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000006-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000007-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000008-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000009-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E0000000A-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E0000000B[rootpg pg_wal]# ls|wc -l1593[rootpg pg_wal]# ls|wc -l1594[rootpg pg_wal]# ls|wc -l1595[rootpg pg_wal]# ls|wc -l1595[rootpg pg_wal]# ls|wc -l1595[rootpg pg_wal]# ls|wc -l1595[rootpg pg_wal]# pwd/opt/pgsql/data/pg_wal[rootpg pg_wal]# ls|wc -l1596[rootpg pg_wal]# su - postgresLast login: Tue Dec 16 09:31:10 CST 2025 on pts/1-bash-4.2$ psqlPassword for user postgres:psql (12.2)Type help for help.postgres# SELECT * FROM pg_stat_archiver;archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time | stats_reset-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1599 | 0000000100000A7400000040 | 2025-12-16 09:35:34.80874108 | 173 | 0000000100000A6E00000003 | 2025-12-16 08:50:01.13960608 | 2025-12-16 08:48:25.82804108(1 row)postgres# SELECT * FROM pg_stat_activity WHERE state active AND now() - xact_start interval 1 hour;datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change| wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------(0 rows)postgres#SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn)) AS delay_sizepostgres-# FROM pg_replication_slots;slot_name | active | delay_size-----------------------------------recovery_slot|f|1206GB(1 row)postgres#SELECT pg_drop_replication_slot(recovery_slot);pg_drop_replication_slot--------------------------(1 row)postgres# SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn)) AS delay_sizeFROM pg_replication_slots;slot_name | active | delay_size-------------------------------(0 rows)postgres# SELECT * FROM pg_stat_archiver;archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time | stats_reset-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1634 | 0000000100000A7400000063 | 2025-12-16 09:38:31.70895608 | 173 | 0000000100000A6E00000003 | 2025-12-16 08:50:01.13960608 | 2025-12-16 08:48:25.82804108(1 row)postgres# SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn)) AS delay_sizeFROM pg_replication_slots;slot_name | active | delay_size-------------------------------(0 rows)postgres# \q-bash-4.2$ df -hTFilesystem Type Size Used Avail Use% Mounted ondevtmpfs devtmpfs 16G 0 16G 0% /devtmpfs tmpfs 16G 5.5M 16G 1% /dev/shmtmpfs tmpfs 16G 1.6G 15G 11% /runtmpfs tmpfs 16G 0 16G 0% /sys/fs/cgroup/dev/mapper/centos-root xfs 1.2T 984G 231G 82% //dev/sda1 xfs 1014M 150M 865M 15% /boottmpfs tmpfs 3.2G 0 3.2G 0% /run/user/1000-bash-4.2$ psqlPassword for user postgres:psql (12.2)Type help for help.postgres# SELECT pg_size_pretty(SUM(size)) AS total_wal_size FROM pg_ls_waldir();total_wal_size----------------26 GB(1 row)postgres# SELECT pg_size_pretty(SUM(size)) AS total_wal_size FROM pg_ls_waldir();total_wal_size----------------26 GB(1 row)postgres# SELECT name, setting FROM pg_settings WHERE name archive_mode;name | setting-----------------------archive_mode | on(1 row)postgres# SELECT name, setting FROM pg_settings WHERE name like archive%;name | setting-----------------------------------------------------------------------------------------archive_cleanup_command |archive_command | test ! -f /opt/pgsql/archive/%f cp %p /opt/pgsql/archive/%farchive_mode | onarchive_timeout | 0(4 rows)postgres# alter system set archive_command;ALTER SYSTEMpostgres# alter system set archive_modeOFF;ALTER SYSTEMpostgres# SELECT name, setting FROM pg_settings WHERE name like archive%;name | setting-----------------------------------------------------------------------------------------archive_cleanup_command |archive_command | test ! -f /opt/pgsql/archive/%f cp %p /opt/pgsql/archive/%farchive_mode | onarchive_timeout | 0(4 rows)postgres# \q-bash-4.2$ cd /opt/pgsql/data/-bash-4.2$ cp postgresql.conf postgresql.conf.20251216-bash-4.2$ grep archive postgresql.confarchive_mode on # enables archiving; off, on, or alwaysarchive_command test ! -f /opt/pgsql/archive/%f cp %p /opt/pgsql/archive/%f # command to use to archive a logfile segment# placeholders: %p path of file to archive# e.g. test ! -f /mnt/server/archivedir/%f cp %p /mnt/server/archivedir/%f#archive_timeout 0 # force a logfile segment switch after this#restore_command # command to use to restore an archived logfile segment# e.g. cp /mnt/server/archivedir/%f %p#archive_cleanup_command # command to execute at every restartpoint#max_standby_archive_delay 30s # max delay before canceling queries# when reading WAL from archive;-bash-4.2$ ll /opt/pgsql/archivetotal 32768-rw------- 1 postgres postgres 16777216 Dec 16 09:40 0000000100000A7400000070-rw------- 1 postgres postgres 16777216 Dec 16 09:43 0000000100000A7400000071-bash-4.2$ vi postgresql.confwal_level logical # 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_init_zero on # zero-fill new WAL files#wal_recycle on # recycle WAL files#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-1dmax_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 alwaysarchive_command # (change requires restart)#archive_command test ! -f /opt/pgsql/archive/%f cp %p /opt/pgsql/archive/%f # 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# - Archive Recovery -# These are only used in recovery mode.#restore_command # command to use to restore an archived logfile segment# placeholders: %p path of file to restore# %f file name only# e.g. cp /mnt/server/archivedir/%f %p# (change requires restart)#archive_cleanup_command # command to execute at every restartpoint#recovery_end_command # command to execute at completion of recovery# - Recovery Target -# Set these only when performing a targeted recovery.postgresql.conf 754L, 26874C written-bash-4.2$ grep archive postgresql.confarchive_mode off # enables archiving; off, on, or alwaysarchive_command # (change requires restart)#archive_command test ! -f /opt/pgsql/archive/%f cp %p /opt/pgsql/archive/%f # command to use to archive a logfile segment# placeholders: %p path of file to archive# e.g. test ! -f /mnt/server/archivedir/%f cp %p /mnt/server/archivedir/%f#archive_timeout 0 # force a logfile segment switch after this#restore_command # command to use to restore an archived logfile segment# e.g. cp /mnt/server/archivedir/%f %p#archive_cleanup_command # command to execute at every restartpoint#max_standby_archive_delay 30s # max delay before canceling queries# when reading WAL from archive;-bash-4.2$ ll postgresql*-rw------- 1 postgres postgres 221 Dec 16 09:44 postgresql.auto.conf-rw------- 1 postgres postgres 26874 Dec 16 09:46 postgresql.conf-rw------- 1 postgres postgres 26852 Dec 16 09:45 postgresql.conf.20251216-bash-4.2$ more postgresql.auto.conf# Do not edit this file manually!# It will be overwritten by the ALTER SYSTEM command.max_connections 1000search_path $user, public, mt, zdmh, yjya, ghkt, tjgx, zxktarchive_command archive_mode off-bash-4.2$ psqlPassword for user postgres:psql (12.2)Type help for help.postgres# SHOW archive_command;archive_command----------------------------------------------------------------test ! -f /opt/pgsql/archive/%f cp %p /opt/pgsql/archive/%f(1 row)postgres# SHOW archive_mode;archive_mode--------------on(1 row)postgres#-bash-4.2$ pwd/opt/pgsql/data-bash-4.2$ du -sh pg_wal1013M pg_wal-bash-4.2$ psqlPassword for user postgres:psql (12.2)Type help for help.postgres# SELECT pg_size_pretty(SUM(size)) AS total_wal_size FROM pg_ls_waldir();total_wal_size----------------1008 MB(1 row)postgres#-bash-4.2$ ll pg_wal|wc -l65相关参考/opt/pgsql/data/pg_w...点击查看元宝的回答https://yb.tencent.com/s/xl7VZQUqwXRd