supabase · dragarcia · Apr 4, 2022 · Feb 17, 2022 · Feb 17, 2022 · Feb 17, 2022
@@ -0,0 +1,7 @@
+#! /usr/bin/env bash
+
+set -euo pipefail
+
+WALG_SENTINEL_USER_DATA="{ \"backup_id\": $1, \"project_id\": $2 }" nohup wal-g backup-push /var/lib/postgresql/data --config /etc/wal-g/config.json --verify >> /var/log/wal-g/backup-push.log 2>&1 &
+
+echo "WAL-G backup job commenced"
@@ -0,0 +1,26 @@
+#! /usr/bin/env bash
+
+set -euo pipefail
+
+# disable 169.254.169.254 for postgres
+sed -i "/#\\sdon't\\sdelete\\sthe\\s'COMMIT'/ i -I OUTPUT 1 --proto tcp --destination 169.254.169.254 --match owner --uid-owner postgres --jump REJECT\\n" /etc/ufw/before.rules
+ufw reload
+
+# move config file to its final location and change its ownership
+mv /etc/postgresql/wal-g-config.json /etc/wal-g/config.json
+chown wal-g:wal-g /etc/wal-g/config.json
+
+
+# disable recovery commands in the event of a restart
+sed -i "s/.*restore_command/#restore_command/" /etc/postgresql/postgresql.conf
+sed -i "s/.*recovery_target_time/#recovery_target_time/" /etc/postgresql/postgresql.conf
+sed -i "s/.*recovery_target_action/#recovery_target_action/" /etc/postgresql/postgresql.conf
+
+# enable archive_command
+sed -i "s/.*archive_mode/archive_mode/" /etc/postgresql/postgresql.conf
+sed -i "s/.*archive_command/archive_command/" /etc/postgresql/postgresql.conf
+sed -i "s/.*archive_timeout/archive_timeout/" /etc/postgresql/postgresql.conf
+
+systemctl restart postgresql
+
+echo "Cleanup post WAL-G restoration complete"
@@ -0,0 +1,11 @@
+#! /usr/bin/env bash
+
+set -euo pipefail
+
+sed -i "s/.*archive_mode/#archive_mode/" /etc/postgresql/postgresql.conf
+sed -i "s/.*archive_command/#archive_command/" /etc/postgresql/postgresql.conf
+sed -i "s/.*archive_timeout/#archive_timeout/" /etc/postgresql/postgresql.conf
+
+systemctl restart postgresql
+
+echo "WAL-G successfully disabled"
@@ -0,0 +1,11 @@
+#! /usr/bin/env bash
+
+set -euo pipefail
+
+sed -i "s/.*archive_mode/archive_mode/" /etc/postgresql/postgresql.conf
+sed -i "s/.*archive_command/archive_command/" /etc/postgresql/postgresql.conf
+sed -i "s/.*archive_timeout/archive_timeout/" /etc/postgresql/postgresql.conf
+
+systemctl restart postgresql
+
+echo "WAL-G successfully enabled"
@@ -0,0 +1,9 @@
+/var/log/wal-g/*.log {
+    size 50M
+    rotate 3  
+    copytruncate
+    delaycompress
+    compress
+    notifempty
+    missingok
+}
@@ -242,20 +242,20 @@ checkpoint_flush_after = 256kB		# measured in pages, 0 disables
 
 # - Archiving -
 
-#archive_mode = off		# enables archiving; off, on, or always
+#archive_mode = on		# enables archiving; off, on, or always
 				# (change requires restart)
-#archive_command = ''		# command to use to archive a logfile segment
+#archive_command = 'sudo -u wal-g wal-g wal-push %p --config /etc/wal-g/config.json >> /var/log/wal-g/wal-push.log 2>&1'		# command to use to archive a logfile segment
 				# placeholders: %p = path of file to archive
 				#               %f = file name only
 				# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
-#archive_timeout = 0		# force a logfile segment switch after this
+#archive_timeout = 120		# force a logfile segment switch after this
 				# number of seconds; 0 disables
 
 # - Archive Recovery -
 
 # These are only used in recovery mode.
 
-#restore_command = ''		# command to use to restore an archived logfile segment
+#restore_command = 'sudo -u wal-g wal-g wal-fetch "%f" "%p" --config /etc/wal-g/config.json >> /var/log/wal-g/wal-fetch.log 2>&1'		# command to use to restore an archived logfile segment
 				# placeholders: %p = path of file to restore
 				#               %f = file name only
 				# e.g. 'cp /mnt/server/archivedir/%f %p'
@@ -283,7 +283,7 @@ checkpoint_flush_after = 256kB		# measured in pages, 0 disables
 				# (change requires restart)
 #recovery_target_timeline = 'latest'	# 'current', 'latest', or timeline ID
 				# (change requires restart)
-#recovery_target_action = 'pause'	# 'pause', 'promote', 'shutdown'
+#recovery_target_action = 'promote'	# 'pause', 'promote', 'shutdown'
 				# (change requires restart)
 
 

@@ -35,6 +35,9 @@
       tags:
         - install-pgbouncer
 
+    - name: Install WAL-G
+      import_tasks: tasks/setup-wal-g.yml
+
     - name: Install Supabase specific content
       import_tasks: tasks/setup-supabase-internal.yml
       tags:
@@ -64,9 +67,6 @@
         state: absent
       loop: "{{ sql_files }}"
 
-    - name: Install WAL-G
-      import_tasks: tasks/setup-wal-g.yml
-
     - name: Install PostgREST
       import_tasks: tasks/setup-postgrest.yml
       tags:
@@ -103,15 +103,16 @@
         policy: deny
         direction: incoming
 
-    - name: Setup logrotate for postgres logs
-      copy:
-        src: files/logrotate-postgres
-        dest: /etc/logrotate.d/postgres
-
-    - name: Setup logrotate for postgres CSV logs
+    - name: Move logrotate files to /etc/logrotate.d/ 
       copy:
-        src: files/logrotate-postgres-csv
-        dest: /etc/logrotate.d/postgres-csv
+        src: "files/logrotate_config/{{ item.file }}"
+        dest: "/etc/logrotate.d/{{ item.file }}"
+        mode: "0700"
+        owner: root
+      loop:
+        - { file: "logrotate-postgres-csv.conf" }
+        - { file: "logrotate-postgres.conf" }
+        - { file: "logrotate-walg.conf" }
 
     - name: Disable cron access
       copy:

@@ -1,20 +1,30 @@
 - name: adminapi - system user
   user:
     name: adminapi
-    groups: root,admin,postgres,pgbouncer
+    groups: root,admin,postgres,pgbouncer,wal-g
     append: yes
 
-- name: disk script
+- name: Move shell scripts to /root dir
   copy:
-    src: files/grow_fs.sh
-    dest: /root/grow_fs.sh
-    mode: '0700'
+    src: "files/admin_api_scripts/{{ item.file }}"
+    dest: "/root/{{ item.file }}"
+    mode: "0700"
     owner: root
+  loop:
+    - { file: "commence_walg_backup.sh" }
+    - { file: "complete_walg_restore.sh" }
+    - { file: "disable_walg.sh" }
+    - { file: "enable_walg.sh" }
+    - { file: "grow_fs.sh" }
 
 - name: give adminapi user permissions
   copy:
     content: |
       %adminapi ALL= NOPASSWD: /root/grow_fs.sh
+      %adminapi ALL= NOPASSWD: /root/commence_walg_backup.sh
+      %adminapi ALL= NOPASSWD: /root/complete_walg_restore.sh
+      %adminapi ALL= NOPASSWD: /root/disable_walg.sh
+      %adminapi ALL= NOPASSWD: /root/enable_walg.sh
       %adminapi ALL= NOPASSWD: /usr/bin/systemctl daemon-reload
       %adminapi ALL= NOPASSWD: /usr/bin/systemctl restart postgresql.service
       %adminapi ALL= NOPASSWD: /usr/bin/systemctl restart adminapi.service

@@ -71,13 +71,19 @@
     target: install-world-bin
     chdir: /tmp/postgresql-{{ postgresql_release }}
 
+- name: Create postgres group
+  group:
+    name: postgres
+    state: present
+
 # Create postgres user
 - name: Create postgres user
   user:
     name: postgres
     shell: /bin/false
     comment: Postgres user
-    groups: ssl-cert
+    group: postgres
+    groups: postgres,ssl-cert
 
 - name: Create relevant directories
   file:
@@ -141,7 +147,7 @@
   become: yes
   become_user: postgres
   shell:
-    cmd: /usr/lib/postgresql/bin/pg_ctl -D /var/lib/postgresql/data initdb
+    cmd: /usr/lib/postgresql/bin/pg_ctl -D /var/lib/postgresql/data initdb -o "--allow-group-access"
   vars:
     ansible_command_timeout: 60
     # Circumvents the following error:

@@ -24,7 +24,7 @@
 # Download WAL-G
 - name: wal-g - download latest version
   git:
-    repo: https://github.com/darora/wal-g.git
+    repo: https://github.com/wal-g/wal-g.git
     dest: /tmp/wal-g
     version: "{{ wal_g_release }}"
   become: yes
@@ -61,6 +61,41 @@
       USE_LIBSODIUM: true
   become: yes
 
+- name: Create wal-g group
+  group:
+    name: wal-g
+    state: present

+- name: Create wal-g user
+  user:
+    name: wal-g
+    shell: /bin/false
+    comment: WAL-G user
+    group: wal-g
+    groups: wal-g, postgres
+
+- name: Give postgres access to execute wal-g binary as wal-g user
+  copy:
+    content: |
+      postgres   ALL=(wal-g) NOPASSWD: /usr/local/bin/wal-g
+    dest: /etc/sudoers.d/postgres
+
+- name: Create a config directory owned by wal-g
+  file:
+    path: /etc/wal-g
+    state: directory
+    owner: wal-g
+    group: wal-g
+    mode: '0760'
+
+- name: Create /etc/wal-g/config.json
+  file:
+    path: /etc/wal-g/config.json
+    state: touch
+    owner: wal-g
+    group: wal-g
+    mode: '0760'
+
 # Clean up Go
 - name: Uninstall Go
   become: yes

@@ -19,7 +19,7 @@ golang_version_checksum:
   arm64: sha256:06f505c8d27203f78706ad04e47050b49092f1b06dc9ac4fbee4f0e4d015c8d4
   amd64: sha256:550f9845451c0c94be679faf116291e7807a8d78b43149f9506c1b15eb89008c
 
-wal_g_release: "v1.1-backports"
+wal_g_release: "v1.1"
 
 sfcgal_release: "1.3.10"
 sfcgal_release_checksum: sha1:f4add34a00afb0b5f594685fc646565a2bda259b

@@ -11,4 +11,10 @@ touch /var/log/pgbouncer.log
 chown pgbouncer:postgres /var/log/pgbouncer.log
 
 mkdir /var/log/postgresql
-chown postgres:postgres /var/log/postgresql
+chown postgres:postgres /var/log/postgresql
+
+mkdir /var/log/wal-g
+cd /var/log/wal-g
+touch backup-push.l
5134
og backup-fetch.log wal-push.log wal-fetch.log
+chown -R wal-g:wal-g /var/log/wal-g
+chmod -R 0700 /var/log/wal-g