| 1 | params: |
| 2 | db_synchronous_commit: "off" |
| 3 | db_shared_buffers: "256MB" |
| 4 | db_work_mem: "10MB" |
| 5 | db_default_text_search_config: "pg_catalog.english" |
| 6 | db_name: discourse |
| 7 | db_user: discourse |
| 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 | |
| 14 | |
| 15 | hooks: |
| 16 | before_code: |
| 17 | - replace: |
| 18 | filename: /etc/service/unicorn/run |
| 19 | from: "# postgres" |
| 20 | to: sv start postgres || exit 1 |
| 21 | |
| 22 | run: |
| 23 | - exec: apt-get remove -y postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5 |
| 24 | - exec: apt-get update && apt-get install -y postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 |
| 25 | - exec: /etc/init.d/postgresql stop |
| 26 | - exec: mkdir -p /shared/postgres_run |
| 27 | - exec: chown postgres:postgres /shared/postgres_run |
| 28 | - exec: chmod 775 /shared/postgres_run |
| 29 | - exec: rm -fr /var/run/postgresql |
| 30 | - exec: ln -s /shared/postgres_run /var/run/postgresql |
| 31 | - exec: socat /dev/null UNIX-CONNECT:/shared/postgres_run/.s.PGSQL.5432 || exit 0 && echo postgres already running stop container ; exit 1 |
| 32 | - exec: rm -fr /shared/postgres_run/.s* |
| 33 | - exec: rm -fr /shared/postgres_run/*.pid |
| 34 | - exec: mkdir -p /shared/postgres_run/9.3-main.pg_stat_tmp |
| 35 | - exec: chown postgres:postgres /shared/postgres_run/9.3-main.pg_stat_tmp |
| 36 | - replace: |
| 37 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 38 | from: /port =.*/ |
| 39 | to: "port = 5432" |
| 40 | - file: |
| 41 | path: /etc/service/postgres/run |
| 42 | chmod: "+x" |
| 43 | contents: | |
| 44 | #!/bin/sh |
| 45 | exec 2>&1 |
| 46 | echo -1000 >/proc/self/oom_score_adj |
| 47 | HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/9.3/bin/postmaster -D /etc/postgresql/9.3/main |
| 48 | |
| 49 | - file: |
| 50 | path: /etc/runit/3.d/99-postgres |
| 51 | chmod: "+x" |
| 52 | contents: | |
| 53 | #!/bin/bash |
| 54 | sv stop postgres |
| 55 | |
| 56 | - exec: |
| 57 | cmd: |
| 58 | - chown -R root /var/lib/postgresql/9.3/main |
| 59 | - "[ ! -e /shared/postgres_data ] && install -d -m 0755 -o postgres -g postgres /shared/postgres_data && sudo -E -u postgres /usr/lib/postgresql/9.3/bin/initdb -D /shared/postgres_data || exit 0" |
| 60 | - chown -R postgres:postgres /shared/postgres_data |
| 61 | - chown -R postgres:postgres /var/run/postgresql |
| 62 | |
| 63 | - replace: |
| 64 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 65 | from: "data_directory = '/var/lib/postgresql/9.3/main'" |
| 66 | to: "data_directory = '/shared/postgres_data'" |
| 67 | |
| 68 | # listen on all interfaces |
| 69 | - replace: |
| 70 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 71 | from: /#?listen_addresses *=.*/ |
| 72 | to: "listen_addresses = '*'" |
| 73 | |
| 74 | # sync commit off is faster and less spiky, also marginally less safe |
| 75 | - replace: |
| 76 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 77 | from: /#?synchronous_commit *=.*/ |
| 78 | to: "synchronous_commit = $db_synchronous_commit" |
| 79 | |
| 80 | # default is 128MB which is way too small |
| 81 | - replace: |
| 82 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 83 | from: /#?shared_buffers *=.*/ |
| 84 | to: "shared_buffers = $db_shared_buffers" |
| 85 | |
| 86 | # default is 1MB which is too small |
| 87 | - replace: |
| 88 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 89 | from: /#?work_mem *=.*/ |
| 90 | to: "work_mem = $db_work_mem" |
| 91 | |
| 92 | # allow for other |
| 93 | - replace: |
| 94 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 95 | from: /#?default_text_search_config *=.*/ |
| 96 | to: "default_text_search_config = '$db_default_text_search_config'" |
| 97 | |
| 98 | # Necessary to enable backups |
| 99 | - exec: |
| 100 | cmd: |
| 101 | - install -d -m 0755 -o postgres -g postgres /shared/postgres_backup |
| 102 | |
| 103 | - replace: |
| 104 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 105 | from: /#?max_wal_senders *=.*/ |
| 106 | to: "max_wal_senders = $db_max_wal_senders" |
| 107 | |
| 108 | - replace: |
| 109 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 110 | from: /#?wal_level *=.*/ |
| 111 | to: "wal_level = $db_wal_level" |
| 112 | |
| 113 | - replace: |
| 114 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 115 | from: /#?checkpoint_segments *=.*/ |
| 116 | to: "checkpoint_segments = $db_checkpoint_segments" |
| 117 | |
| 118 | - replace: |
| 119 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 120 | from: /#?logging_collector *=.*/ |
| 121 | to: "logging_collector = $db_logging_collector" |
| 122 | |
| 123 | - replace: |
| 124 | filename: "/etc/postgresql/9.3/main/postgresql.conf" |
| 125 | from: /#?log_min_duration_statement *=.*/ |
| 126 | to: "log_min_duration_statement = $db_log_min_duration_statement" |
| 127 | |
| 128 | - replace: |
| 129 | filename: "/etc/postgresql/9.3/main/pg_hba.conf" |
| 130 | from: /^#local +replication +postgres +peer$/ |
| 131 | to: "local replication postgres peer" |
| 132 | |
| 133 | # allow all to connect in with md5 auth |
| 134 | - replace: |
| 135 | filename: "/etc/postgresql/9.3/main/pg_hba.conf" |
| 136 | from: /^host.*all.*all.*127.*$/ |
| 137 | to: "host all all 0.0.0.0/0 md5" |
| 138 | |
| 139 | - exec: |
| 140 | background: true |
| 141 | # use fast shutdown for pg |
| 142 | stop_signal: INT |
| 143 | cmd: HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/9.3/bin/postmaster -D /etc/postgresql/9.3/main |
| 144 | |
| 145 | # give db a few secs to start up |
| 146 | - exec: "sleep 5" |
| 147 | |
| 148 | - exec: su postgres -c 'createdb $db_name' || true |
| 149 | - exec: su postgres -c 'psql $db_name -c "create user $db_user;"' || true |
| 150 | - exec: su postgres -c 'psql $db_name -c "grant all privileges on database $db_name to $db_user;"' || true |
| 151 | - exec: su postgres -c 'psql $db_name -c "alter schema public owner to $db_user;"' |
| 152 | - exec: su postgres -c 'psql template1 -c "create extension if not exists hstore;"' |
| 153 | - exec: su postgres -c 'psql template1 -c "create extension if not exists pg_trgm;"' |
| 154 | - exec: su postgres -c 'psql $db_name -c "create extension if not exists hstore;"' |
| 155 | - exec: su postgres -c 'psql $db_name -c "create extension if not exists pg_trgm;"' |
| 156 | - exec: |
| 157 | stdin: | |
| 158 | update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = '$db_name' AND encoding = pg_char_to_encoding('SQL_ASCII'); |
| 159 | cmd: sudo -u postgres psql $db_name |
| 160 | raise_on_fail: false |
| 161 | |
| 162 | - file: |
| 163 | path: /var/lib/postgresql/take-database-backup |
| 164 | chown: postgres:postgres |
| 165 | chmod: "+x" |
| 166 | contents: | |
| 167 | #!/bin/bash |
| 168 | ID=db-$(date +%F_%T) |
| 169 | FILENAME=/shared/postgres_backup/$ID.tar.gz |
| 170 | pg_basebackup --format=tar --pgdata=- --xlog --gzip --label=$ID > $FILENAME |
| 171 | echo $FILENAME |
| 172 | |
| 173 | - file: |
| 174 | path: /var/spool/cron/crontabs/postgres |
| 175 | contents: | |
| 176 | # m h dom mon dow command |
| 177 | #MAILTO=? |
| 178 | #0 */4 * * * /var/lib/postgresql/take-database-backup |
| 179 | |
| 180 | - exec: |
| 181 | hook: postgres |
| 182 | cmd: "echo postgres installed!" |