1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
|
# Copyright (c) 2021, PostgreSQL Global Development Group
# Minimal test testing synchronous replication sync_state transition
use strict;
use warnings;
use PostgresNode;
use TestLib;
use Test::More tests => 11;
# Query checking sync_priority and sync_state of each standby
my $check_sql =
"SELECT application_name, sync_priority, sync_state FROM pg_stat_replication ORDER BY application_name;";
# Check that sync_state of each standby is expected (waiting till it is).
# If $setting is given, synchronous_standby_names is set to it and
# the configuration file is reloaded before the test.
sub test_sync_state
{
my ($self, $expected, $msg, $setting) = @_;
if (defined($setting))
{
$self->safe_psql('postgres',
"ALTER SYSTEM SET synchronous_standby_names = '$setting';");
$self->reload;
}
ok($self->poll_query_until('postgres', $check_sql, $expected), $msg);
return;
}
# Start a standby and check that it is registered within the WAL sender
# array of the given primary. This polls the primary's pg_stat_replication
# until the standby is confirmed as registered.
sub start_standby_and_wait
{
my ($primary, $standby) = @_;
my $primary_name = $primary->name;
my $standby_name = $standby->name;
my $query =
"SELECT count(1) = 1 FROM pg_stat_replication WHERE application_name = '$standby_name'";
$standby->start;
print("### Waiting for standby \"$standby_name\" on \"$primary_name\"\n");
$primary->poll_query_until('postgres', $query);
return;
}
# Initialize primary node
my $node_primary = get_new_node('primary');
$node_primary->init(allows_streaming => 1);
$node_primary->start;
my $backup_name = 'primary_backup';
# Take backup
$node_primary->backup($backup_name);
# Create all the standbys. Their status on the primary is checked to ensure
# the ordering of each one of them in the WAL sender array of the primary.
# Create standby1 linking to primary
my $node_standby_1 = get_new_node('standby1');
$node_standby_1->init_from_backup($node_primary, $backup_name,
has_streaming => 1);
start_standby_and_wait($node_primary, $node_standby_1);
# Create standby2 linking to primary
my $node_standby_2 = get_new_node('standby2');
$node_standby_2->init_from_backup($node_primary, $backup_name,
has_streaming => 1);
start_standby_and_wait($node_primary, $node_standby_2);
# Create standby3 linking to primary
my $node_standby_3 = get_new_node('standby3');
$node_standby_3->init_from_backup($node_primary, $backup_name,
has_streaming => 1);
start_standby_and_wait($node_primary, $node_standby_3);
# Check that sync_state is determined correctly when
# synchronous_standby_names is specified in old syntax.
test_sync_state(
$node_primary, qq(standby1|1|sync
standby2|2|potential
standby3|0|async),
'old syntax of synchronous_standby_names',
'standby1,standby2');
# Check that all the standbys are considered as either sync or
# potential when * is specified in synchronous_standby_names.
# Note that standby1 is chosen as sync standby because
# it's stored in the head of WalSnd array which manages
# all the standbys though they have the same priority.
test_sync_state(
$node_primary, qq(standby1|1|sync
standby2|1|potential
standby3|1|potential),
'asterisk in synchronous_standby_names',
'*');
# Stop and start standbys to rearrange the order of standbys
# in WalSnd array. Now, if standbys have the same priority,
# standby2 is selected preferentially and standby3 is next.
$node_standby_1->stop;
$node_standby_2->stop;
$node_standby_3->stop;
# Make sure that each standby reports back to the primary in the wanted
# order.
start_standby_and_wait($node_primary, $node_standby_2);
start_standby_and_wait($node_primary, $node_standby_3);
# Specify 2 as the number of sync standbys.
# Check that two standbys are in 'sync' state.
test_sync_state(
$node_primary, qq(standby2|2|sync
standby3|3|sync),
'2 synchronous standbys',
'2(standby1,standby2,standby3)');
# Start standby1
start_standby_and_wait($node_primary, $node_standby_1);
# Create standby4 linking to primary
my $node_standby_4 = get_new_node('standby4');
$node_standby_4->init_from_backup($node_primary, $backup_name,
has_streaming => 1);
$node_standby_4->start;
# Check that standby1 and standby2 whose names appear earlier in
# synchronous_standby_names are considered as sync. Also check that
# standby3 appearing later represents potential, and standby4 is
# in 'async' state because it's not in the list.
test_sync_state(
$node_primary, qq(standby1|1|sync
standby2|2|sync
standby3|3|potential
standby4|0|async),
'2 sync, 1 potential, and 1 async');
# Check that sync_state of each standby is determined correctly
# when num_sync exceeds the number of names of potential sync standbys
# specified in synchronous_standby_names.
test_sync_state(
$node_primary, qq(standby1|0|async
standby2|4|sync
standby3|3|sync
standby4|1|sync),
'num_sync exceeds the num of potential sync standbys',
'6(standby4,standby0,standby3,standby2)');
# The setting that * comes before another standby name is acceptable
# but does not make sense in most cases. Check that sync_state is
# chosen properly even in case of that setting. standby1 is selected
# as synchronous as it has the highest priority, and is followed by a
# second standby listed first in the WAL sender array, which is
# standby2 in this case.
test_sync_state(
$node_primary, qq(standby1|1|sync
standby2|2|sync
standby3|2|potential
standby4|2|potential),
'asterisk before another standby name',
'2(standby1,*,standby2)');
# Check that the setting of '2(*)' chooses standby2 and standby3 that are stored
# earlier in WalSnd array as sync standbys.
test_sync_state(
$node_primary, qq(standby1|1|potential
standby2|1|sync
standby3|1|sync
standby4|1|potential),
'multiple standbys having the same priority are chosen as sync',
'2(*)');
# Stop Standby3 which is considered in 'sync' state.
$node_standby_3->stop;
# Check that the state of standby1 stored earlier in WalSnd array than
# standby4 is transited from potential to sync.
test_sync_state(
$node_primary, qq(standby1|1|sync
standby2|1|sync
standby4|1|potential),
'potential standby found earlier in array is promoted to sync');
# Check that standby1 and standby2 are chosen as sync standbys
# based on their priorities.
test_sync_state(
$node_primary, qq(standby1|1|sync
standby2|2|sync
standby4|0|async),
'priority-based sync replication specified by FIRST keyword',
'FIRST 2(standby1, standby2)');
# Check that all the listed standbys are considered as candidates
# for sync standbys in a quorum-based sync replication.
test_sync_state(
$node_primary, qq(standby1|1|quorum
standby2|1|quorum
standby4|0|async),
'2 quorum and 1 async',
'ANY 2(standby1, standby2)');
# Start Standby3 which will be considered in 'quorum' state.
$node_standby_3->start;
# Check that the setting of 'ANY 2(*)' chooses all standbys as
# candidates for quorum sync standbys.
test_sync_state(
$node_primary, qq(standby1|1|quorum
standby2|1|quorum
standby3|1|quorum
standby4|1|quorum),
'all standbys are considered as candidates for quorum sync standbys',
'ANY 2(*)');
|
