質問編集履歴
3
発生している問題を修正
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -12,8 +12,14 @@
|
|
|
12
12
|
いざ、pgpoolのインストールサーバのpsqlコマンドでのinsert を実行すると、だんまりになります。
|
|
13
13
|
アプリケーションからpgpoolを介したinsertも同様です。
|
|
14
14
|
|
|
15
|
+
CTRL+Cで強制終了すると、
|
|
16
|
+
```psqlでキャンセル
|
|
17
|
+
^C
|
|
18
|
+
キャンセル要求を送信しました
|
|
15
|
-
|
|
19
|
+
WARNING: ユーザからの要求により同期レプリケーションの待ち状態をキャンセルしています
|
|
16
|
-
|
|
20
|
+
DETAIL: トランザクションはローカルではすでにコミット済みですが、スタンバイ側にはレプリケーションされていない可能性があります。
|
|
21
|
+
```
|
|
22
|
+
と出ますが、その後selectするとinsertしたものが出てきます。
|
|
17
23
|
|
|
18
24
|
### 実現したいこと
|
|
19
25
|
|
2
postgresql.confの設定値
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -191,3 +191,38 @@
|
|
|
191
191
|
cache_unsafe_memqcache_table_list = ''
|
|
192
192
|
|
|
193
193
|
```
|
|
194
|
+
|
|
195
|
+
```postgresql.conf
|
|
196
|
+
listen_addresses = '*' # what IP address(es) to listen on;
|
|
197
|
+
max_connections = 100 # (change requires restart)
|
|
198
|
+
password_encryption = scram-sha-256 # md5 or scram-sha-256
|
|
199
|
+
shared_buffers = 128MB # min 128kB
|
|
200
|
+
dynamic_shared_memory_type = posix # the default is the first option
|
|
201
|
+
wal_level = replica # minimal, replica, or logical
|
|
202
|
+
synchronous_commit = remote_apply # synchronization level;
|
|
203
|
+
wal_log_hints = on # also do full page writes of non-critical updates
|
|
204
|
+
max_wal_size = 1GB
|
|
205
|
+
min_wal_size = 80MB
|
|
206
|
+
archive_mode = on # enables archiving; off, on, or always
|
|
207
|
+
archive_command = 'cp "%p" "/usr/pgsql-13/archivedir/%f"' # command to use to archive a logfile segment
|
|
208
|
+
max_wal_senders = 10 # max number of walsender processes
|
|
209
|
+
max_replication_slots = 10 # max number of replication slots
|
|
210
|
+
synchronous_standby_names = 's1' # standby servers that provide sync rep
|
|
211
|
+
hot_standby = on # "off" disallows queries during recovery
|
|
212
|
+
log_destination = 'stderr' # Valid values are combinations of
|
|
213
|
+
logging_collector = on # Enable capturing of stderr and csvlog
|
|
214
|
+
log_directory = 'log' # directory where log files are written,
|
|
215
|
+
log_filename = 'postgresql-%a.log' # log file name pattern,
|
|
216
|
+
log_truncate_on_rotation = on # If on, an existing log file with the
|
|
217
|
+
log_rotation_age = 1d # Automatic rotation of logfiles will
|
|
218
|
+
log_rotation_size = 0 # Automatic rotation of logfiles will
|
|
219
|
+
log_line_prefix = '%m [%p] ' # special values:
|
|
220
|
+
log_timezone = 'Asia/Tokyo'
|
|
221
|
+
datestyle = 'iso, ymd'
|
|
222
|
+
timezone = 'Asia/Tokyo'
|
|
223
|
+
lc_messages = 'ja_JP.UTF-8' # locale for system error message
|
|
224
|
+
lc_monetary = 'ja_JP.UTF-8' # locale for monetary formatting
|
|
225
|
+
lc_numeric = 'ja_JP.UTF-8' # locale for number formatting
|
|
226
|
+
lc_time = 'ja_JP.UTF-8' # locale for time formatting
|
|
227
|
+
default_text_search_config = 'pg_catalog.simple'
|
|
228
|
+
```
|
1
pgpoolの設定値追加
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -25,4 +25,169 @@
|
|
|
25
25
|
Postgres 13.3
|
|
26
26
|
Pgpool 4.2.4
|
|
27
27
|
|
|
28
|
+
```pgpool.conf
|
|
29
|
+
backend_clustering_mode = 'streaming_replication'
|
|
30
|
+
listen_addresses = '*'
|
|
31
|
+
port = 9999
|
|
32
|
+
socket_dir = '/var/run/postgresql'
|
|
33
|
+
reserved_connections = 0
|
|
34
|
+
pcp_listen_addresses = '*'
|
|
35
|
+
pcp_port = 9898
|
|
36
|
+
pcp_socket_dir = '/var/run/postgresql'
|
|
37
|
+
listen_backlog_multiplier = 2
|
|
38
|
+
serialize_accept = off
|
|
39
|
+
backend_hostname0 = '172.30.2.101'
|
|
40
|
+
backend_port0 = 5432
|
|
41
|
+
backend_weight0 = 1
|
|
42
|
+
backend_data_directory0 = '/usr/pgsql-13/data'
|
|
43
|
+
backend_flag0 = 'ALLOW_TO_FAILOVER'
|
|
44
|
+
backend_application_name0 = 'server0'
|
|
45
|
+
backend_hostname1 = '172.30.2.102'
|
|
46
|
+
backend_port1 = 5432
|
|
47
|
+
backend_weight1 = 1
|
|
48
|
+
backend_data_directory1 = '/usr/pgsql-13/data'
|
|
49
|
+
backend_flag1 = 'ALLOW_TO_FAILOVER'
|
|
50
|
+
backend_application_name1 = 'server1'
|
|
51
|
+
enable_pool_hba = off
|
|
52
|
+
pool_passwd = 'pool_passwd'
|
|
53
|
+
authentication_timeout = 1min
|
|
54
|
+
allow_clear_text_frontend_auth = off
|
|
55
|
+
ssl = off
|
|
56
|
+
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
|
|
57
|
+
ssl_prefer_server_ciphers = off
|
|
58
|
+
ssl_ecdh_curve = 'prime256v1'
|
|
59
|
+
ssl_dh_params_file = ''
|
|
60
|
+
num_init_children = 32
|
|
61
|
+
max_pool = 4
|
|
62
|
+
child_life_time = 5min
|
|
63
|
+
child_max_connections = 0
|
|
64
|
+
connection_life_time = 0
|
|
65
|
+
client_idle_limit = 0
|
|
66
|
+
log_destination = 'syslog'
|
|
67
|
+
log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.
|
|
68
|
+
log_connections = off
|
|
69
|
+
log_disconnections = off
|
|
70
|
+
log_hostname = off
|
|
71
|
+
log_statement = off
|
|
72
|
+
log_per_node_statement = off
|
|
73
|
+
log_client_messages = off
|
|
74
|
+
log_standby_delay = 'if_over_threshold'
|
|
75
|
+
syslog_facility = 'LOCAL0'
|
|
76
|
+
syslog_ident = 'pgpool'
|
|
77
|
+
client_min_messages = notice # values in order of decreasing detail:
|
|
78
|
+
log_min_messages = warning # values in order of decreasing detail:
|
|
79
|
+
pid_file_name = '/var/run/pgpool/pgpool.pid'
|
|
80
|
+
logdir = '/tmp'
|
|
81
|
+
connection_cache = on
|
|
82
|
+
reset_query_list = 'ABORT; DISCARD ALL'
|
|
83
|
+
replicate_select = off
|
|
84
|
+
insert_lock = off
|
|
85
|
+
lobj_lock_table = ''
|
|
86
|
+
replication_stop_on_mismatch = off
|
|
87
|
+
failover_if_affected_tuples_mismatch = off
|
|
88
|
+
load_balance_mode = on
|
|
89
|
+
ignore_leading_white_space = on
|
|
90
|
+
read_only_function_list = ''
|
|
91
|
+
write_function_list = ''
|
|
92
|
+
primary_routing_query_pattern_list = ''
|
|
93
|
+
database_redirect_preference_list = ''
|
|
94
|
+
app_name_redirect_preference_list = ''
|
|
95
|
+
allow_sql_comments = off
|
|
96
|
+
disable_load_balance_on_write = 'transaction'
|
|
97
|
+
dml_adaptive_object_relationship_list= ''
|
|
98
|
+
statement_level_load_balance = off
|
|
99
|
+
sr_check_period = 10
|
|
100
|
+
sr_check_user = 'postgres'
|
|
101
|
+
sr_check_password = 'testpass'
|
|
102
|
+
sr_check_database = 'postgres'
|
|
103
|
+
delay_threshold = 10000000
|
|
104
|
+
follow_primary_command = ''
|
|
105
|
+
health_check_period = 60
|
|
106
|
+
health_check_timeout = 20
|
|
107
|
+
health_check_user = 'postgres'
|
|
108
|
+
health_check_password = 'testpass'
|
|
109
|
+
health_check_database = ''
|
|
110
|
+
health_check_max_retries = 0
|
|
111
|
+
health_check_retry_delay = 1
|
|
112
|
+
connect_timeout = 10000
|
|
113
|
+
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
|
|
114
|
+
failback_command = ''
|
|
115
|
+
failover_on_backend_error = on
|
|
116
|
+
detach_false_primary = off
|
|
117
|
+
search_primary_node_timeout = 5min
|
|
118
|
+
recovery_user = 'postgres'
|
|
119
|
+
recovery_password = 'testpass'
|
|
120
|
+
recovery_1st_stage_command = 'recovery_1st_stage.sh'
|
|
121
|
+
recovery_2nd_stage_command = ''
|
|
122
|
+
recovery_timeout = 90
|
|
123
|
+
client_idle_limit_in_recovery = 0
|
|
124
|
+
auto_failback = off
|
|
125
|
+
auto_failback_interval = 1min
|
|
126
|
+
use_watchdog = on
|
|
127
|
+
trusted_servers = ''
|
|
128
|
+
ping_path = '/bin'
|
|
129
|
+
hostname0 = '172.30.2.99'
|
|
130
|
+
wd_port0 = 9000
|
|
131
|
+
pgpool_port0 = 9999
|
|
132
|
+
hostname1 = '172.30.2.100'
|
|
133
|
+
wd_port1 = 9000
|
|
134
|
+
pgpool_port1 = 9999
|
|
135
|
+
hostname2 = '172.30.2.103'
|
|
136
|
+
wd_port2 = 9000
|
|
137
|
+
pgpool_port2 = 9999
|
|
138
|
+
wd_priority = 1
|
|
139
|
+
wd_authkey = ''
|
|
140
|
+
wd_ipc_socket_dir = '/var/run/postgresql'
|
|
141
|
+
delegate_IP = ''
|
|
142
|
+
if_cmd_path = '/sbin'
|
|
143
|
+
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
|
|
144
|
+
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
|
|
145
|
+
arping_path = '/usr/sbin'
|
|
146
|
+
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'
|
|
147
|
+
clear_memqcache_on_escalation = on
|
|
148
|
+
wd_escalation_command = ''
|
|
149
|
+
wd_de_escalation_command = ''
|
|
150
|
+
failover_when_quorum_exists = on
|
|
151
|
+
failover_require_consensus = on
|
|
152
|
+
allow_multiple_failover_requests_from_node = off
|
|
153
|
+
enable_consensus_with_half_votes = off
|
|
154
|
+
wd_monitoring_interfaces_list = ''
|
|
155
|
+
wd_lifecheck_method = 'heartbeat'
|
|
156
|
+
wd_interval = 10
|
|
157
|
+
heartbeat_hostname0 = '172.30.2.99'
|
|
158
|
+
heartbeat_port0 = 9694
|
|
159
|
+
heartbeat_device0 = ''
|
|
160
|
+
heartbeat_hostname1 = '172.30.2.100'
|
|
161
|
+
heartbeat_port1 = 9694
|
|
162
|
+
heartbeat_device1 = ''
|
|
163
|
+
heartbeat_hostname2 = '172.30.2.103'
|
|
164
|
+
heartbeat_port2 = 9694
|
|
165
|
+
heartbeat_device2 = ''
|
|
166
|
+
wd_heartbeat_keepalive = 2
|
|
167
|
+
wd_heartbeat_deadtime = 30
|
|
168
|
+
wd_life_point = 3
|
|
169
|
+
wd_lifecheck_query = 'SELECT 1'
|
|
170
|
+
wd_lifecheck_dbname = 'template1'
|
|
171
|
+
wd_lifecheck_user = 'nobody'
|
|
172
|
+
wd_lifecheck_password = ''
|
|
173
|
+
relcache_expire = 0
|
|
174
|
+
relcache_size = 256
|
|
175
|
+
check_temp_table = catalog
|
|
176
|
+
check_unlogged_table = on
|
|
177
|
+
enable_shared_relcache = on
|
|
178
|
+
relcache_query_target = primary
|
|
179
|
+
memory_cache_enabled = off
|
|
180
|
+
memqcache_method = 'shmem'
|
|
181
|
+
memqcache_memcached_host = 'localhost'
|
|
182
|
+
memqcache_memcached_port = 11211
|
|
183
|
+
memqcache_total_size = 64MB
|
|
184
|
+
memqcache_max_num_cache = 1000000
|
|
185
|
+
memqcache_expire = 0
|
|
186
|
+
memqcache_auto_cache_invalidation = on
|
|
187
|
+
memqcache_maxcache = 400kB
|
|
188
|
+
memqcache_cache_block_size = 1MB
|
|
189
|
+
memqcache_oiddir = '/var/log/pgpool/oiddir'
|
|
190
|
+
cache_safe_memqcache_table_list = ''
|
|
191
|
+
cache_unsafe_memqcache_table_list = ''
|
|
28
192
|
|
|
193
|
+
```
|