-
Notifications
You must be signed in to change notification settings - Fork 137
/
Copy pathgcp_startup.sh
233 lines (186 loc) · 8.48 KB
/
gcp_startup.sh
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
#!/usr/bin/env bash
set -euo pipefail
if (( $(id -u) != 0 )); then
echo "***************************************************"
echo "*** FATAL: This script should be ran as ROOT ***"
echo "***************************************************"
exit 1
fi
UTF8PROC_TAG=v2.5.0
MAPNIK_GERMAN_L10N_TAG=v2.5.9.1
PGSQL_GZIP_TAG=v1.0.0
CURL="curl --silent --show-error --location"
# Get configuration metadata (set with --metadata param during VM creation with gcloud)
GET_METADATA="$CURL -H Metadata-Flavor:Google"
PG_VERSION=$($GET_METADATA http://metadata.google.internal/computeMetadata/v1/instance/attributes/pg_version)
OMT_PGDATABASE=$($GET_METADATA http://metadata.google.internal/computeMetadata/v1/instance/attributes/pg_database)
OMT_PGUSER=$($GET_METADATA http://metadata.google.internal/computeMetadata/v1/instance/attributes/pg_user)
OMT_PGPASSWORD=$($GET_METADATA http://metadata.google.internal/computeMetadata/v1/instance/attributes/pg_password)
# PostgreSQL dirs/files updated by this script
# The non-existance of the config file is also used as an indicatior
# that this is the first time this script has ran.
PG_DIR="/etc/postgresql/${PG_VERSION}/main"
PG_CONFIG_FILE="${PG_DIR}/conf.d/99-custom.conf"
PG_HBA_FILE="${PG_DIR}/pg_hba.conf"
if [[ ! -f "${PG_CONFIG_FILE}" ]]; then
echo "************ First time initialization **************"
# Add PostgreSQL packages
$CURL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Install the PostgreSQL server and postgis extension
DEBIAN_FRONTEND=noninteractive apt-get update -qq
DEBIAN_FRONTEND=noninteractive apt-get install -y "postgresql-${PG_VERSION}" postgis
# Install dependencies required to build extensions
DEBIAN_FRONTEND=noninteractive apt-get install -y "postgresql-server-dev-${PG_VERSION}" build-essential git \
xsltproc pandoc libkakasi2-dev libgdal-dev libprotobuf-dev libprotobuf-c-dev protobuf-c-compiler libxml2-dev \
zlib1g-dev bison flex
# Build and install Postgres extentions
cd /opt
echo "Installing utf8proc"
git clone --branch "$UTF8PROC_TAG" --depth 1 https://github.com/JuliaStrings/utf8proc.git
cd utf8proc
make
make install
ldconfig
cd /opt
rm -rf utf8proc
echo "Installing mapnik-german-l10n"
git clone --branch "$MAPNIK_GERMAN_L10N_TAG" --depth 1 https://github.com/openmaptiles/mapnik-german-l10n.git
cd mapnik-german-l10n
git checkout -q
make
make install
cd /opt
rm -rf mapnik-german-l10n
echo "Installing pgsql-gzip"
git clone --branch "$PGSQL_GZIP_TAG" --depth 1 https://github.com/pramsey/pgsql-gzip.git
cd pgsql-gzip
make
make install
cd /opt
rm -rf pgsql-gzip
# remove build deps we no longer need
DEBIAN_FRONTEND=noninteractive apt-get remove --purge -y "postgresql-server-dev-${PG_VERSION}" build-essential git \
xsltproc pandoc libkakasi2-dev libgdal-dev libprotobuf-dev libprotobuf-c-dev protobuf-c-compiler libxml2-dev \
zlib1g-dev bison flex
# Create database
systemctl restart postgresql
sleep 3
sudo -u postgres \
psql -v ON_ERROR_STOP="1" \
-c "create user $OMT_PGUSER with password '$OMT_PGPASSWORD'" \
-c "create database $OMT_PGDATABASE" \
-c "grant all privileges on database $OMT_PGDATABASE to $OMT_PGUSER" \
-c "\c $OMT_PGDATABASE" \
-c "CREATE EXTENSION hstore" \
-c "CREATE EXTENSION postgis" \
-c "CREATE EXTENSION unaccent" \
-c "CREATE EXTENSION fuzzystrmatch" \
-c "CREATE EXTENSION osml10n" \
-c "CREATE EXTENSION gzip" \
-c "CREATE EXTENSION pg_stat_statements"
# set the firwall rules to allow inbound connections from 10.0.0.0/8
cat <<EOF | tee "$PG_HBA_FILE"
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
# Allow external connections.
# Note: add here all the networks you want or need.
# Open for all by default with password
host all all 10.0.0.0/8 md5
EOF
fi # end of the code that only runs on the first startup
#
# This code should execute on every server restart.
# Recompute available memory and CPU count in case the server
# hardware changed, and adjust Postgres configuration.
# The settings assume this machine is dedicated to Postgres.
#
# Get the current number of CPUs and total memory in MB, used in computations below
CPU_COUNT=$(grep -c ^processor /proc/cpuinfo)
MEM_TOTAL_MB="$(awk '/MemTotal/ { printf "%d", $2/1024 }' /proc/meminfo)"
# %% of the RAM - it should be enough for most of the cases
SHARED_BUFFERS="$(( MEM_TOTAL_MB * 30 / 100 ))MB"
# %% of RAM is assumed to be disk cache (probably more too, but better be conservative)
CACHE_SIZE="$(( MEM_TOTAL_MB * 30 / 100 ))MB"
# if you see one of these errors, raise this value
# * too many dynamic shared memory segments
# * remaining connection slots are reserved for non-replication superuser connections
# for low CPU machines (i.e. n1-standard-1), the number should still be sufficiently high.
MAX_CONNECTIONS="$(( 40 + CPU_COUNT * 5 ))"
# this config file will be dynamically generated based on the current machine's resources
cat <<EOF | tee "${PG_CONFIG_FILE}"
#
# THESE VALUES WILL BE REGENERATED ON EVERY MACHINE RESTART
#
#
# Resource Consumption
#
# https://www.postgresql.org/docs/12/runtime-config-resource.html#GUC-SHARED-BUFFERS
shared_buffers = ${SHARED_BUFFERS}
# SSD disk has high concurrency
# https://www.postgresql.org/docs/12/runtime-config-resource.html#GUC-EFFECTIVE-IO-CONCURRENCY
effective_io_concurrency = 300
# https://www.postgresql.org/docs/12/runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER
max_parallel_workers_per_gather = 8
# https://www.postgresql.org/docs/12/runtime-config-resource.html#GUC-WORK-MEM
work_mem = 128MB
# https://www.postgresql.org/docs/12/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM
maintenance_work_mem = 256MB
#
# Query Planning
#
# https://www.postgresql.org/docs/12/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE
effective_cache_size = ${CACHE_SIZE}
# PostgreSQL 11/12 JIT has a bug making large queries execute 100x slower than without JIT
# https://www.postgresql.org/docs/12/runtime-config-query.html#GUC-JIT
jit = off
# https://www.postgresql.org/docs/12/runtime-config-query.html#GUC-RANDOM-PAGE-COST
random_page_cost = 1.0
#
# Connections
#
# https://www.postgresql.org/docs/12/runtime-config-connection.html#GUC-MAX-CONNECTIONS
max_connections = ${MAX_CONNECTIONS}
# listen on all interfaces
# https://www.postgresql.org/docs/12/runtime-config-connection.html#GUC-LISTEN-ADDRESSES
listen_addresses = '*'
#
# Write Ahead Log
#
# https://www.postgresql.org/docs/12/runtime-config-wal.html#GUC-MIN-WAL-SIZE
min_wal_size = 256MB
# https://www.postgresql.org/docs/12/runtime-config-wal.html#GUC-MAX-WAL-SIZE
max_wal_size = 50GB
# https://www.postgresql.org/docs/12/runtime-config-wal.html#GUC-CHECKPOINT-COMPLETION-TARGET
checkpoint_completion_target = 0.8
#
# Replication
#
# https://www.postgresql.org/docs/12/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS
wal_keep_segments = 64
# https://www.postgresql.org/docs/12/runtime-config-replication.html#GUC-WAL-SENDER-TIMEOUT
wal_sender_timeout = 300s
# https://www.postgresql.org/docs/12/runtime-config-replication.html#GUC-MAX-WAL-SENDERS
max_wal_senders = 20
EOF
# Set the owner and restart the postgres to pick up the new configuration
chown -R postgres.postgres "$PG_DIR"
systemctl restart postgresql