aboutsummaryrefslogtreecommitdiff
path: root/scripts/guix-data-service-create-small-backup
blob: bc6ed8c91ee48fbd768ff74234f9c385f3df1626 (plain)
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
269
270
271
272
273
274
#!/bin/sh

set -eux

TMP_DATABASE_NAME=guix_data_service_small
DEFAULT_BACKUP_DIRECTORY="/var/lib/guix-data-service/dumps"
BACKUP_DIRECTORY="${GUIX_DATA_SERVICE_BACKUP_DIRECTORY:-$DEFAULT_BACKUP_DIRECTORY}"

if [ -z "${1:-}" ]; then
    DATE="$(date "+%Y-%m-%d")"
else
    DATE="$1"
fi

FULL_BACKUP_NAME="guix_data_service_full.dump"
SMALL_BACKUP_NAME="guix_data_service_small.dump"

renice 19 -p $$
ionice -p $$ -c 3

TMP_DATABASE=$(mktemp -d -t guix-data-service-database-XXXX)
URI=$(pg_tmp -w 0 -d "$TMP_DATABASE")

function cleanup {
    pg_tmp stop -w 1 -d "$TMP_DATABASE"
}
trap cleanup EXIT

psql --no-psqlrc "$URI" --command="CREATE USER guix_data_service"
psql --no-psqlrc "$URI" --command="CREATE DATABASE $TMP_DATABASE_NAME WITH OWNER guix_data_service"

URI_FOR_DATABASE="${URI/test/$TMP_DATABASE_NAME}"

pg_restore --username=guix_data_service --jobs=8 --dbname="$URI_FOR_DATABASE" \
           "$BACKUP_DIRECTORY/$DATE/$FULL_BACKUP_NAME"

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF

DELETE FROM git_branches
USING (
  SELECT MIN(datetime) AS recent_datetime
  FROM (
   SELECT datetime
   FROM git_branches AS recent_branches
   ORDER BY datetime DESC LIMIT 30
  ) AS data
) AS data2
WHERE datetime < recent_datetime;

EOF

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &

CREATE UNLOGGED TABLE tmp_guix_revision_package_derivations AS
SELECT *
FROM guix_revision_package_derivations
WHERE revision_id IN (
  SELECT id
  FROM guix_revisions
  WHERE commit IN (
    SELECT commit FROM git_branches
  )
);

CREATE UNLOGGED TABLE tmp_package_derivations AS
SELECT * FROM package_derivations WHERE id IN (
  SELECT package_derivation_id FROM tmp_guix_revision_package_derivations
);

CREATE UNLOGGED TABLE tmp_derivations AS
WITH RECURSIVE derivation_ids(id) AS (
    SELECT derivation_id FROM tmp_package_derivations
  UNION
    SELECT derivation_outputs.derivation_id
    FROM derivation_ids
    INNER JOIN derivation_inputs
    ON derivation_ids.id = derivation_inputs.derivation_id
    INNER JOIN derivation_outputs
    ON derivation_inputs.derivation_output_id = derivation_outputs.id
)
SELECT * FROM derivations WHERE id IN (SELECT id FROM derivation_ids);

CREATE UNLOGGED TABLE tmp_derivation_inputs AS
SELECT *
FROM derivation_inputs
WHERE derivation_id IN (SELECT id FROM tmp_derivations);

CREATE UNLOGGED TABLE tmp_derivation_outputs AS
SELECT *
FROM derivation_outputs
WHERE derivation_id IN (SELECT id FROM tmp_derivations);

CREATE UNLOGGED TABLE tmp_derivation_output_details AS
SELECT *
FROM derivation_output_details
WHERE id IN (SELECT derivation_output_details_id FROM tmp_derivation_outputs);

CREATE UNLOGGED TABLE tmp_derivation_sources AS
SELECT *
FROM derivation_sources
WHERE derivation_id IN (SELECT id FROM tmp_derivations);

TRUNCATE derivations,
         derivation_inputs,
         derivation_outputs,
         derivation_output_details,
         derivation_sources,
         derivations_by_output_details_set,
         channel_instances,
         guix_revision_system_test_derivations,
         guix_revision_package_derivations,
         package_derivations;

EOF

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &

CREATE UNLOGGED TABLE tmp_builds AS
SELECT * FROM builds WHERE derivation_output_details_set_id IN (
  SELECT id FROM derivation_output_details_sets
);

CREATE UNLOGGED TABLE tmp_build_status AS
SELECT *
FROM build_status
WHERE build_id IN (SELECT id FROM tmp_builds);

TRUNCATE builds, build_status;

EOF

wait

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &

CREATE UNLOGGED TABLE tmp_derivation_source_files AS
SELECT *
FROM derivation_source_files
WHERE id IN (
  SELECT derivation_source_file_id
  FROM tmp_derivation_sources
);

CREATE UNLOGGED TABLE tmp_derivation_source_file_nars AS
SELECT *
FROM derivation_source_file_nars
WHERE derivation_source_file_id IN (
  SELECT id FROM tmp_derivation_source_files
);

TRUNCATE derivation_source_files, derivation_source_file_nars, derivation_sources;

EOF

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &

DELETE FROM guix_revision_lint_warnings WHERE guix_revision_id NOT IN (
  SELECT id FROM guix_revisions WHERE commit IN (SELECT commit FROM git_branches)
);
EOF

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &

DELETE FROM guix_revision_lint_checkers WHERE guix_revision_id NOT IN (
  SELECT id FROM guix_revisions WHERE commit IN (SELECT commit FROM git_branches)
);
EOF

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &

DELETE FROM guix_revision_channel_news_entries WHERE guix_revision_id NOT IN (
  SELECT id FROM guix_revisions WHERE commit IN (SELECT commit FROM git_branches)
);
EOF

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &

WITH guix_revision_ids AS (
  SELECT id FROM guix_revisions WHERE commit IN (
    SELECT commit FROM git_branches
  )
)
DELETE FROM package_versions_by_guix_revision_range
WHERE first_guix_revision_id NOT IN (SELECT id FROM guix_revision_ids) OR
      last_guix_revision_id NOT IN (SELECT id FROM guix_revision_ids);
EOF

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &

WITH guix_revision_ids AS (
  SELECT id FROM guix_revisions WHERE commit IN (
    SELECT commit FROM git_branches
  )
)
DELETE FROM package_derivations_by_guix_revision_range
WHERE first_guix_revision_id NOT IN (SELECT id FROM guix_revision_ids) OR
      last_guix_revision_id NOT IN (SELECT id FROM guix_revision_ids);
EOF

wait

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &

EXPLAIN ANALYZE DELETE FROM guix_revisions WHERE commit NOT IN (SELECT commit FROM git_branches);
EOF

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF &
INSERT INTO derivations
  SELECT * FROM tmp_derivations;

EOF

wait

# Don't do this bit in the background, as it's error prone
psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF

INSERT INTO package_derivations OVERRIDING SYSTEM VALUE
  SELECT * FROM tmp_package_derivations;

INSERT INTO guix_revision_package_derivations
  SELECT * FROM tmp_guix_revision_package_derivations;

INSERT INTO builds OVERRIDING SYSTEM VALUE
  SELECT * FROM tmp_builds;

INSERT INTO build_status OVERRIDING SYSTEM VALUE
  SELECT * FROM tmp_build_status;

INSERT INTO derivation_output_details OVERRIDING SYSTEM VALUE
  SELECT * FROM tmp_derivation_output_details;

INSERT INTO derivation_outputs OVERRIDING SYSTEM VALUE
  SELECT * FROM tmp_derivation_outputs;

INSERT INTO derivation_inputs
  SELECT * FROM tmp_derivation_inputs;

INSERT INTO derivation_source_files OVERRIDING SYSTEM VALUE
  SELECT * FROM tmp_derivation_source_files;

INSERT INTO derivation_sources
  SELECT * FROM tmp_derivation_sources;

INSERT INTO derivation_source_file_nars
  SELECT * FROM tmp_derivation_source_file_nars;
EOF

psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF

DROP TABLE tmp_derivations;
DROP TABLE tmp_package_derivations;
DROP TABLE tmp_guix_revision_package_derivations;
DROP TABLE tmp_builds;
DROP TABLE tmp_build_status;
DROP TABLE tmp_derivation_output_details;
DROP TABLE tmp_derivation_outputs;
DROP TABLE tmp_derivation_inputs;
DROP TABLE tmp_derivation_sources;
DROP TABLE tmp_derivation_source_files;
DROP TABLE tmp_derivation_source_file_nars;

EOF

TEMPORARY_FILE_NAME="${TMPDIR:-/tmp}/guix_data_service_small-$DATE.dump.tmp"

pg_dump --username=guix_data_service \
        --format=custom --compress=9 --serializable-deferrable \
        --username=guix_data_service \
        --file="$TEMPORARY_FILE_NAME" \
        "$URI_FOR_DATABASE"

mv "$TEMPORARY_FILE_NAME" \
   "$BACKUP_DIRECTORY/$DATE/$SMALL_BACKUP_NAME"