Commit | Line | Data |
---|---|---|
6f99f3c2 S |
1 | params: |
2 | db_synchronous_commit: "off" | |
3 | db_shared_buffers: "256MB" | |
4 | db_work_mem: "10MB" | |
8019560c | 5 | db_default_text_search_config: "pg_catalog.english" |
cfd0322a GXT |
6 | db_name: discourse |
7 | db_user: discourse | |
83d3ef11 S |
8 | db_wal_level: minimal |
9 | db_max_wal_senders: 0 | |
10 | db_checkpoint_segments: 6 | |
11 | db_logging_collector: off | |
12 | db_log_min_duration_statement: 100 | |
13 | ||
9fb5f2d3 | 14 | hooks: |
9fb5f2d3 | 15 | before_code: |
9fb5f2d3 SS |
16 | - replace: |
17 | filename: /etc/service/unicorn/run | |
18 | from: "# postgres" | |
19 | to: sv start postgres || exit 1 | |
20 | ||
9fb5f2d3 | 21 | run: |
d4eb8c3f | 22 | - exec: locale-gen $LANG && update-locale |
d7ed7814 SS |
23 | - exec: mkdir -p /shared/postgres_run |
24 | - exec: chown postgres:postgres /shared/postgres_run | |
25 | - exec: chmod 775 /shared/postgres_run | |
26 | - exec: rm -fr /var/run/postgresql | |
27 | - exec: ln -s /shared/postgres_run /var/run/postgresql | |
28 | - exec: socat /dev/null UNIX-CONNECT:/shared/postgres_run/.s.PGSQL.5432 || exit 0 && echo postgres already running stop container ; exit 1 | |
61975bbd SS |
29 | - exec: rm -fr /shared/postgres_run/.s* |
30 | - exec: rm -fr /shared/postgres_run/*.pid | |
4cdd4c8a RSS |
31 | - exec: mkdir -p /shared/postgres_run/12-main.pg_stat_tmp |
32 | - exec: chown postgres:postgres /shared/postgres_run/12-main.pg_stat_tmp | |
9fb5f2d3 SS |
33 | - file: |
34 | path: /etc/service/postgres/run | |
35 | chmod: "+x" | |
36 | contents: | | |
37 | #!/bin/sh | |
38 | exec 2>&1 | |
4cdd4c8a | 39 | HOME=/var/lib/postgresql USER=postgres exec thpoff chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/12/bin/postmaster -D /etc/postgresql/12/main |
8194e684 | 40 | |
3f9f3c3b S |
41 | - file: |
42 | path: /etc/service/postgres/log/run | |
43 | chmod: "+x" | |
44 | contents: | | |
45 | #!/bin/sh | |
46 | mkdir -p /var/log/postgres | |
47 | exec svlogd /var/log/postgres | |
074f2b6a S |
48 | |
49 | - file: | |
50 | path: /etc/runit/3.d/99-postgres | |
51 | chmod: "+x" | |
52 | contents: | | |
53 | #!/bin/bash | |
54 | sv stop postgres | |
55 | ||
d69fe0fb S |
56 | - file: |
57 | path: /root/upgrade_postgres | |
58 | chmod: "+x" | |
59 | contents: | | |
60 | #!/bin/bash | |
49b852c5 S |
61 | PG_MAJOR_OLD=`cat /shared/postgres_data/PG_VERSION` |
62 | ||
4cdd4c8a RSS |
63 | if [ ! "12" = "$PG_MAJOR_OLD" ]; then |
64 | echo Upgrading PostgreSQL from version ${PG_MAJOR_OLD} to 12 | |
882358d5 RSS |
65 | free_disk=$(df -P /shared | tail -n 1 | awk '{print $4}') |
66 | required=$(($(du -sb /shared/postgres_data | awk '{print $1}') * 2)) | |
448a70b9 GXT |
67 | |
68 | if [ "$free_disk" -lt "$required" ]; then | |
969cc9b9 | 69 | echo "WARNING: Upgrading PostgresSQL would require an addtional $(numfmt --to=si $(($required - $free_disk))) of disk space" |
448a70b9 | 70 | echo "Please free up some space, or expand your disk, before continuing." |
5e02f7e4 | 71 | echo '' |
4cdd4c8a | 72 | echo 'To avoid upgrading change "templates/postgres.template.yml" TO "templates/postgres.10.template.yml" in containers/app.yml' |
448a70b9 GXT |
73 | exit 1 |
74 | fi | |
75 | ||
d69fe0fb | 76 | rm -fr /shared/postgres_data_new |
4cdd4c8a | 77 | install -d -m 0755 -o postgres -g postgres /shared/postgres_data_new && sudo -u postgres /usr/lib/postgresql/12/bin/initdb -D /shared/postgres_data_new || exit 0 |
a0958e4f | 78 | apt-get update |
49b852c5 | 79 | apt-get install -y postgresql-${PG_MAJOR_OLD} |
4cdd4c8a | 80 | chown -R postgres:postgres /var/lib/postgresql/12 |
61975bbd SS |
81 | /etc/init.d/postgresql stop |
82 | rm -fr /shared/postgres_data/postmaster.pid | |
d69fe0fb | 83 | cd ~postgres |
8194e684 | 84 | cp -pr /etc/postgresql/${PG_MAJOR_OLD}/main/* /shared/postgres_data |
61975bbd SS |
85 | echo >> /shared/postgres_data/postgresql.conf |
86 | echo "data_directory = '/shared/postgres_data'" >> /shared/postgres_data/postgresql.conf | |
9b9cdd3b | 87 | SUCCESS=true |
4cdd4c8a | 88 | sudo -u postgres /usr/lib/postgresql/12/bin/pg_upgrade -d /shared/postgres_data -D /shared/postgres_data_new -b /usr/lib/postgresql/${PG_MAJOR_OLD}/bin -B /usr/lib/postgresql/12/bin || SUCCESS=false |
9b9cdd3b S |
89 | |
90 | if [[ "$SUCCESS" == 'false' ]]; then | |
91 | echo ------------------------------------------------------------------------------------- | |
92 | echo UPGRADE OF POSTGRES FAILED | |
93 | echo | |
94 | echo You are going to need to export your data and import into a clean instance: | |
95 | echo | |
49b852c5 | 96 | |
4cdd4c8a RSS |
97 | if [ "$PG_MAJOR_OLD" = "10" ]; then |
98 | echo 'In containers/app.yml: Change "templates/postgres.template.yml" TO "templates/postgres.10.template.yml"' | |
49b852c5 S |
99 | echo |
100 | fi | |
101 | ||
102 | echo Run "./launcher rebuild app" again | |
9b9cdd3b S |
103 | echo |
104 | echo When your instance is running: | |
49b852c5 | 105 | echo Run "./launcher enter app" |
4cdd4c8a | 106 | echo "Run apt-get remove postgresql-client-10 && apt-get install postgresql-client-12" |
49b852c5 | 107 | echo Run "cd /shared/postgres_backup && sudo -u postgres pg_dump discourse > backup.db" |
9b9cdd3b | 108 | echo |
8194e684 | 109 | |
4cdd4c8a | 110 | if [ "$PG_MAJOR_OLD" = "10" ]; then |
4ea0bfa8 LB |
111 | echo Undo the base_image in your container config |
112 | fi | |
8194e684 | 113 | |
49b852c5 | 114 | echo Run: "./launcher stop app" |
51d2e647 | 115 | echo Run: "sudo mv /var/discourse/shared/standalone/postgres_data /var/discourse/shared/standalone/postgres_data_old" |
49b852c5 | 116 | echo Run: "./launcher rebuild app" |
9b9cdd3b | 117 | echo |
49b852c5 S |
118 | echo Run: "./launcher enter app" |
119 | echo Run: "cd /shared/postgres_backup" | |
120 | echo Run: "sv stop unicorn" | |
121 | echo Run: "sudo -iu postgres dropdb discourse" | |
122 | echo Run: "sudo -iu postgres createdb discourse" | |
123 | echo Run: "sudo -iu postgres psql discourse < backup.db" | |
124 | echo Run: "exit" | |
125 | echo Run: "./launcher rebuild app" | |
9b9cdd3b S |
126 | exit 1 |
127 | fi | |
128 | ||
d69fe0fb S |
129 | mv /shared/postgres_data /shared/postgres_data_old |
130 | mv /shared/postgres_data_new /shared/postgres_data | |
131 | ||
132 | echo ------------------------------------------------------------------------------------- | |
133 | echo UPGRADE OF POSTGRES COMPLETE | |
134 | echo | |
49b852c5 | 135 | echo Old ${PG_MAJOR_OLD} database is stored at /shared/postgres_data_old |
d69fe0fb | 136 | echo |
72b89d4d AH |
137 | echo To complete the upgrade, rebuild again using: |
138 | echo | |
139 | echo ./launcher rebuild app | |
d69fe0fb | 140 | echo ------------------------------------------------------------------------------------- |
b4ab14c2 S |
141 | # Magic exit status to denote no failure |
142 | exit 77 | |
d69fe0fb | 143 | fi |
8194e684 | 144 | |
9fb5f2d3 SS |
145 | - exec: |
146 | cmd: | |
4cdd4c8a RSS |
147 | - chown -R root /var/lib/postgresql/12/main |
148 | - "[ ! -e /shared/postgres_data ] && install -d -m 0755 -o postgres -g postgres /shared/postgres_data && sudo -E -u postgres /usr/lib/postgresql/12/bin/initdb -D /shared/postgres_data || exit 0" | |
9fb5f2d3 | 149 | - chown -R postgres:postgres /shared/postgres_data |
d69fe0fb | 150 | - chown -R postgres:postgres /var/run/postgresql |
9fb5f2d3 | 151 | |
61975bbd | 152 | - exec: /root/upgrade_postgres |
6f99f3c2 S |
153 | - exec: rm /root/upgrade_postgres |
154 | ||
9fb5f2d3 | 155 | - replace: |
4cdd4c8a RSS |
156 | filename: "/etc/postgresql/12/main/postgresql.conf" |
157 | from: "data_directory = '/var/lib/postgresql/12/main'" | |
9fb5f2d3 SS |
158 | to: "data_directory = '/shared/postgres_data'" |
159 | ||
80c11be3 SS |
160 | # listen on all interfaces |
161 | - replace: | |
4cdd4c8a | 162 | filename: "/etc/postgresql/12/main/postgresql.conf" |
80c11be3 SS |
163 | from: /#?listen_addresses *=.*/ |
164 | to: "listen_addresses = '*'" | |
165 | ||
6f99f3c2 S |
166 | # sync commit off is faster and less spiky, also marginally less safe |
167 | - replace: | |
4cdd4c8a | 168 | filename: "/etc/postgresql/12/main/postgresql.conf" |
6f99f3c2 S |
169 | from: /#?synchronous_commit *=.*/ |
170 | to: "synchronous_commit = $db_synchronous_commit" | |
171 | ||
172 | # default is 128MB which is way too small | |
173 | - replace: | |
4cdd4c8a | 174 | filename: "/etc/postgresql/12/main/postgresql.conf" |
6f99f3c2 S |
175 | from: /#?shared_buffers *=.*/ |
176 | to: "shared_buffers = $db_shared_buffers" | |
177 | ||
178 | # default is 1MB which is too small | |
179 | - replace: | |
4cdd4c8a | 180 | filename: "/etc/postgresql/12/main/postgresql.conf" |
6f99f3c2 S |
181 | from: /#?work_mem *=.*/ |
182 | to: "work_mem = $db_work_mem" | |
183 | ||
184 | # allow for other | |
185 | - replace: | |
4cdd4c8a | 186 | filename: "/etc/postgresql/12/main/postgresql.conf" |
6f99f3c2 S |
187 | from: /#?default_text_search_config *=.*/ |
188 | to: "default_text_search_config = '$db_default_text_search_config'" | |
189 | ||
9fb5f2d3 SS |
190 | # Necessary to enable backups |
191 | - exec: | |
192 | cmd: | |
193 | - install -d -m 0755 -o postgres -g postgres /shared/postgres_backup | |
194 | ||
195 | - replace: | |
4cdd4c8a | 196 | filename: "/etc/postgresql/12/main/postgresql.conf" |
9fb5f2d3 | 197 | from: /#?max_wal_senders *=.*/ |
83d3ef11 | 198 | to: "max_wal_senders = $db_max_wal_senders" |
9fb5f2d3 SS |
199 | |
200 | - replace: | |
4cdd4c8a | 201 | filename: "/etc/postgresql/12/main/postgresql.conf" |
9fb5f2d3 | 202 | from: /#?wal_level *=.*/ |
83d3ef11 S |
203 | to: "wal_level = $db_wal_level" |
204 | ||
205 | - replace: | |
4cdd4c8a | 206 | filename: "/etc/postgresql/12/main/postgresql.conf" |
83d3ef11 S |
207 | from: /#?checkpoint_segments *=.*/ |
208 | to: "checkpoint_segments = $db_checkpoint_segments" | |
209 | ||
210 | - replace: | |
4cdd4c8a | 211 | filename: "/etc/postgresql/12/main/postgresql.conf" |
83d3ef11 S |
212 | from: /#?logging_collector *=.*/ |
213 | to: "logging_collector = $db_logging_collector" | |
214 | ||
215 | - replace: | |
4cdd4c8a | 216 | filename: "/etc/postgresql/12/main/postgresql.conf" |
83d3ef11 S |
217 | from: /#?log_min_duration_statement *=.*/ |
218 | to: "log_min_duration_statement = $db_log_min_duration_statement" | |
9fb5f2d3 SS |
219 | |
220 | - replace: | |
4cdd4c8a | 221 | filename: "/etc/postgresql/12/main/pg_hba.conf" |
9fb5f2d3 SS |
222 | from: /^#local +replication +postgres +peer$/ |
223 | to: "local replication postgres peer" | |
80c11be3 SS |
224 | |
225 | # allow all to connect in with md5 auth | |
226 | - replace: | |
4cdd4c8a | 227 | filename: "/etc/postgresql/12/main/pg_hba.conf" |
80c11be3 SS |
228 | from: /^host.*all.*all.*127.*$/ |
229 | to: "host all all 0.0.0.0/0 md5" | |
230 | ||
231 | - exec: | |
232 | background: true | |
c244475c S |
233 | # use fast shutdown for pg |
234 | stop_signal: INT | |
4cdd4c8a | 235 | cmd: HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/12/bin/postmaster -D /etc/postgresql/12/main |
80c11be3 SS |
236 | |
237 | # give db a few secs to start up | |
238 | - exec: "sleep 5" | |
239 | ||
cfd0322a GXT |
240 | - exec: su postgres -c 'createdb $db_name' || true |
241 | - exec: su postgres -c 'psql $db_name -c "create user $db_user;"' || true | |
242 | - exec: su postgres -c 'psql $db_name -c "grant all privileges on database $db_name to $db_user;"' || true | |
243 | - exec: su postgres -c 'psql $db_name -c "alter schema public owner to $db_user;"' | |
7f4eef4c | 244 | - exec: su postgres -c 'psql template1 -c "create extension if not exists hstore;"' |
245 | - exec: su postgres -c 'psql template1 -c "create extension if not exists pg_trgm;"' | |
cfd0322a GXT |
246 | - exec: su postgres -c 'psql $db_name -c "create extension if not exists hstore;"' |
247 | - exec: su postgres -c 'psql $db_name -c "create extension if not exists pg_trgm;"' | |
2b09a847 AT |
248 | - exec: |
249 | stdin: | | |
e4eb2bc0 | 250 | update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = '$db_name' AND encoding = pg_char_to_encoding('SQL_ASCII'); |
cfd0322a | 251 | cmd: sudo -u postgres psql $db_name |
2b09a847 | 252 | raise_on_fail: false |
80c11be3 | 253 | |
be82e068 S |
254 | - file: |
255 | path: /var/lib/postgresql/take-database-backup | |
256 | chown: postgres:postgres | |
257 | chmod: "+x" | |
258 | contents: | | |
259 | #!/bin/bash | |
260 | ID=db-$(date +%F_%T) | |
261 | FILENAME=/shared/postgres_backup/$ID.tar.gz | |
262 | pg_basebackup --format=tar --pgdata=- --xlog --gzip --label=$ID > $FILENAME | |
263 | echo $FILENAME | |
264 | ||
265 | - file: | |
266 | path: /var/spool/cron/crontabs/postgres | |
267 | contents: | | |
268 | # m h dom mon dow command | |
269 | #MAILTO=? | |
e56a65f6 | 270 | #0 */4 * * * /var/lib/postgresql/take-database-backup |
be82e068 | 271 | |
80c11be3 SS |
272 | - exec: |
273 | hook: postgres | |
274 | cmd: "echo postgres installed!" |