This repository has been archived by the owner on Jul 6, 2021. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 14
/
.gitlab-ci.yml
268 lines (245 loc) · 15.5 KB
/
.gitlab-ci.yml
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
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
image: ubuntu:16.04
stages:
- test
- build-image
workflow:
rules:
- if: $CI_MERGE_REQUEST_ID
when: never
- when: always
variables:
POSTGRES_DB: dbname # Set database.
POSTGRES_USER: test_user # Set username.
POSTGRES_PASSWORD: ""
POSTGRES_HOST_AUTH_METHOD: "trust"
test-general:
stage: test
before_script:
- apt-get update
- apt-get install -y jq curl wget git s3cmd sudo golang-1.9-go git
- echo "export PATH=\$PATH:/usr/lib/go-1.9/bin" >> ~/.profile
- source ~/.profile
- cd ./pghrep && make install main && cd ..
- sudo .ci/prepare_cluster.sh "11"
- ps ax | grep postgres
- psql -U postgres -p 5432 -c 'show data_directory;'
- psql -U postgres -p 5433 -c 'show data_directory;'
- psql -U postgres -p 5434 -c 'show data_directory;'
# Configure ssh keys
- mkdir -p ~/.ssh && echo "${PRIVATE_KEY}" > ~/.ssh/id_rsa && echo "${PUBLIC_KEY}" > ~/.ssh/authorized_keys && echo "${PUBLIC_KEY}" > ~/.ssh/id_rsa.pub && chmod 644 ~/.ssh/authorized_keys && chmod 644 ~/.ssh/id_rsa.pub && chmod 600 ~/.ssh/id_rsa
- echo "${PRIVATE_KEY}" > ~/.ssh/gitlab_key && chmod 600 ~/.ssh/gitlab_key
- echo "Host gitlab.com" > ~/.ssh/config && echo " Preferredauthentications publickey" >> ~/.ssh/config && echo " IdentityFile ~/.ssh/gitlab_key" >> ~/.ssh/config
- apt-get -y install openssh-server
- service ssh restart
script:
- bash -version
- psql -d dbname -U test_user -c "SELECT version();"
- psql -p 5433 -d dbname -U test_user -c "SELECT version();"
- psql -p 5434 -d dbname -U test_user -c "SELECT version();"
- psql -d dbname -U test_user -f .ci/test_db_dump.sql
- .ci/prepare_test_db.sh
- vacuumdb -U test_user dbname --analyze
- export ARTIFACTS_PATH=$(pwd)/artifacts && echo $ARTIFACTS_PATH
# First run only for K00X reports
- ./checkup collect -h postgres.test1.node --pg-port 5433 --username test_user --project test --dbname dbname -e 1 --file resources/checks/K000_query_analysis.sh > >(tee -a std.log) 2> >(tee -a err.log >&2)
- ./checkup collect -h postgres.test2.node --pg-port 5434 --username test_user --project test --dbname dbname -e 1 --file resources/checks/K000_query_analysis.sh > >(tee -a std.log) 2> >(tee -a err.log >&2)
- ./checkup collect -h postgres.test3.node --username test_user --project test --dbname dbname -e 1 --file resources/checks/K000_query_analysis.sh > >(tee -a std.log) 2> >(tee -a err.log >&2)
# Last run of checkup
- ./checkup collect -h postgres.test1.node --pg-port 5433 --username test_user --project test --dbname dbname -e 1 > >(tee -a std.log) 2> >(tee -a err.log >&2)
- ./checkup collect -h postgres.test2.node --pg-port 5434 --username test_user --project test --dbname dbname -e 1 > >(tee -a std.log) 2> >(tee -a err.log >&2)
- ./checkup collect -h postgres.test3.node --username test_user --project test --dbname dbname -e 1 > >(tee -a std.log) 2> >(tee -a err.log >&2)
- ./checkup process --project test
# Check results
- cat err.log
- grep -v "To compare results, 2 runs are needed. Please run './checkup collect' once again for this epoch." err.log > err1.log || true
- grep -v "this is not a real error. Just run check again." err1.log > err.log || true
- grep -v "Failed to create bus connection" err.log > err1.log || true
- cat err1.log
- export TEST_RESULT=$(stat --format="%s" err1.log) && echo $TEST_RESULT
- ([[ $TEST_RESULT -ne 0 ]]) && exit 1
- (git config --global user.name "postgres-ai" && git config --global user.email "nik@postgres.ai" && git config --global push.default simple) || true
- eval $(ssh-agent -s) || true
- ssh-add ~/.ssh/gitlab_key || true
- ssh-keyscan -t rsa gitlab.com >> ~/.ssh/known_hosts || true
- ssh -T git@gitlab.com || true
- (cd ~/ && git clone git@gitlab.com:postgres-ai-team/postgres-checkup-tests.git) || true
- (mkdir -p ~/postgres-checkup-tests/$CI_COMMIT_REF_NAME && [[ "$CI_COMMIT_REF_NAME" != "master" ]] && rm -Rf ~/postgres-checkup-tests/$CI_COMMIT_REF_NAME/*) || true
- (cp -Rf $ARTIFACTS_PATH/test/* ~/postgres-checkup-tests/$CI_COMMIT_REF_NAME/) || true
- (cd ~/postgres-checkup-tests && git add ./$CI_COMMIT_REF_NAME/* && git commit -m "$CI_COMMIT_REF_NAME artifacts" && git push) || true
- exit $TEST_RESULT
artifacts:
paths:
- ./artifacts/test
- ./std.log
- ./err.log
expire_in: 1 week
.prepare:
stage: test
before_script:
- apt-get update
- apt-get install -y jq curl wget git s3cmd sudo golang-1.9-go git
- wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
- echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main $PG_SERVER_VERSION" > /etc/apt/sources.list.d/pgdg.list
- apt-get update
- apt-get -y upgrade
- apt-get -y install postgresql-client-11
- psql --version
- echo "export PATH=\$PATH:/usr/lib/go-1.9/bin" >> ~/.profile
- source ~/.profile
- cd ./pghrep && make install main && cd ..
.test-check:
stage: test
extends: ".prepare"
script:
- psql -h postgres -d dbname -U test_user -c "SELECT version();"
- echo "Test H003 Non indexed FKs"
- psql -h postgres -d dbname -U test_user -f .ci/h003_step_1.sql
- ./checkup collect -c .ci/test.yml --file ./resources/checks/H003_non_indexed_fks.sh
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H003_non_indexed_fks.json | jq '.results .postgres .data .indexes') && ([[ "$result" == "[]" ]] || [[ "$result" == "null" ]]) && exit 301
- psql -h postgres -d dbname -U test_user -f .ci/h003_step_2.sql
- rm -Rf ./artifacts/
- ./checkup collect -c .ci/test.yml --file ./resources/checks/H003_non_indexed_fks.sh
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H003_non_indexed_fks.json | jq '.results .postgres .data .indexes') && echo "$result" && cat ./artifacts/test/json_reports/$data_dir/H003_non_indexed_fks.json && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 302
- echo "H003 passed"
- echo "Test H004 redundant indexes"
- psql -h postgres -d dbname -U test_user -f .ci/test_db_dump.sql
- ./checkup collect -h postgres --username test_user --project test --dbname dbname -e 1 --file ./resources/checks/H004_redundant_indexes.sh
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_idx_id"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 201
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_idx_f1"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 202
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_idx_f1_uniq"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 203
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_idx_pkey"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 204
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_ref_idx_1"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 205
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_ref_idx_2"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 206
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."exp_redundant.t_with_redundant_ref_idx_1"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 207
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."exp_redundant.t_with_redundant_ref_idx_2"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 208
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.ctnr_idx01"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 209
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json" | jq '.results .postgres .data .redundant_indexes ."public.ctnr_idx04"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 210
- echo "H002 passed"
- ./checkup -c .ci/test.yml --file ./resources/checks/F005_index_bloat.sh
- ./checkup -c .ci/test.yml --file ./resources/checks/L001_table_sizes.sh
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && f005_indexes_size=$(cat ./artifacts/test/json_reports/$data_dir/F005_index_bloat.json | jq '.results .postgres .data .index_bloat_total .real_size_bytes_sum') && echo $f005_indexes_size
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && l001_indexes_size=$(cat ./artifacts/test/json_reports/$data_dir/L001_table_sizes.json | jq '.results .postgres .data .tables_data_total .indexes_size_bytes_sum') && echo $l001_indexes_size
- diff=$((f005_indexes_size - l001_indexes_size)) && diff=${diff#-} && echo $diff && ratio=$((diff * 100 / l001_indexes_size)) && echo $ratio && ([[ $ratio -gt "5" ]]) && exit 303
- echo "F005/L001 Total indexes size passed"
- ./checkup -c .ci/test.yml --file ./resources/checks/F004_heap_bloat.sh
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && ([[ ! -f "./artifacts/test/json_reports/$data_dir/F004_heap_bloat.json" ]] || [[ ! -f "./artifacts/test/md_reports/$data_dir/F004.md" ]]) && exit 304
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && msg=$(grep "can be reduced 0.00 times" ./artifacts/test/md_reports/$data_dir/F004.md) && [[ ! -z "$msg" ]] && exit 304
- ([[ "$CI_COMMIT_REF_NAME" != "master" ]]) && exit 0
# Check small indexes
- .ci/prepare_test_db.sh postgres
- ./checkup -c .ci/test.yml --file ./resources/checks/H002_unused_indexes.sh
- ./checkup -c .ci/test.yml --file ./resources/checks/H004_redundant_indexes.sh
## unused
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H002_unused_indexes.json" | jq '.results .postgres .data .never_used_indexes ."public.i_u_12"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 210
## redundant
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json" | jq '.results .postgres .data .redundant_indexes ."public.i_r_12"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 211
- echo "Check small indexes in small db mode passed"
- .ci/prepare_large_db.sh postgres
- ./checkup -c .ci/test.yml --file ./resources/checks/H002_unused_indexes.sh
- ./checkup -c .ci/test.yml --file ./resources/checks/H004_redundant_indexes.sh
## unused
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H002_unused_indexes.json" | jq '.results .postgres .data .never_used_indexes ."public.i_u_12"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 212
## redundant
- data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json" | jq '.results .postgres .data .redundant_indexes ."public.i_r_12"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 213
- echo "Check small indexes in large db mode passed"
test-pghrep:
stage: test
extends: ".prepare"
script:
- cd pghrep && make install && make test
test-check-9.6:
extends: ".test-check"
stage: "test"
services:
- postgres:9.6
test-check-10:
extends: ".test-check"
stage: "test"
services:
- postgres:10
test-check-11:
extends: ".test-check"
stage: "test"
services:
- postgres:11
test-check-12:
extends: ".test-check"
stage: "test"
services:
- postgres:12
test-check-cli:
services:
- postgres:11
stage: "test"
extends: ".prepare"
script: |
errcount=0
printTail=" "
for f in tests/cli_*.sh; do
printf "$f${printTail:0:-${#f}}"
bash "$f" -H
status=$?
if [ "$status" -ne 0 ]; then
errcount="$(($errcount+1))"
fi
done
if [ "$errcount" -ne 0 ]; then
>&2 echo "Oh no! $errcount tests failed"
exit 1
fi
.job_template: &build_image_definition
image: docker:19
stage: build-image
artifacts:
paths:
- bin
services:
- docker:dind
script:
- apk update && apk upgrade && apk add --no-cache bash # TODO(anatoly): Remove dependency.
- bash ./.ci/docker_build_push.sh
.only_var_template: &only_tag_release
rules:
- if: $CI_COMMIT_TAG =~ /^[0-9.]+$/
.only_var_template: &only_tag_rc
rules:
- if: $CI_COMMIT_TAG =~ /^[0-9.]+[\-_]*[a-zA-Z]+[a-zA-Z0-9.\-_]*[a-zA-Z0-9]+$/
.only_var_template: &only_master
rules:
- if: $CI_COMMIT_BRANCH == "master"
.only_var_template: &only_feature
rules:
- if: $CI_COMMIT_REF_SLUG && $CI_COMMIT_REF_SLUG != "master"
build-image-feature:
<<: *build_image_definition
<<: *only_feature
variables:
REGISTRY_USER: "${CI_REGISTRY_USER}"
REGISTRY_PASSWORD: "${CI_REGISTRY_PASSWORD}"
REGISTRY: "${CI_REGISTRY}"
DOCKER_NAME: "registry.gitlab.com/postgres-ai/postgres-checkup"
TAGS: "${DOCKER_NAME}:${CI_COMMIT_REF_SLUG}"
build-image-master:
<<: *build_image_definition
<<: *only_master
variables:
DOCKER_NAME: "registry.gitlab.com/postgres-ai/postgres-checkup"
TAGS: "${DOCKER_NAME}:master,${DOCKER_NAME}:master-${CI_COMMIT_SHORT_SHA}"
build-image-latest:
<<: *build_image_definition
<<: *only_tag_release
variables:
REGISTRY_USER: "${DH_CI_REGISTRY_USER}"
REGISTRY_PASSWORD: "${DH_CI_REGISTRY_PASSWORD}"
REGISTRY: "${DH_CI_REGISTRY}"
DOCKER_NAME: "postgresai/postgres-checkup"
TAGS: "${DOCKER_NAME}:latest,${DOCKER_NAME}:${CI_COMMIT_TAG}"
build-image-rc:
<<: *build_image_definition
<<: *only_tag_rc
variables:
REGISTRY_USER: "${DH_CI_REGISTRY_USER}"
REGISTRY_PASSWORD: "${DH_CI_REGISTRY_PASSWORD}"
REGISTRY: "${DH_CI_REGISTRY}"
DOCKER_NAME: "postgresai/postgres-checkup"
TAGS: "${DOCKER_NAME}:${CI_COMMIT_TAG}"