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-custom/wal-g.conf
+sed -i "s/.*recovery_target_time/#recovery_target_time/" /etc/postgresql-custom/wal-g.conf
+sed -i "s/.*recovery_target_action/#recovery_target_action/" /etc/postgresql-custom/wal-g.conf
+
+# enable archive_command
+sed -i "s/.*archive_mode/archive_mode/" /etc/postgresql-custom/wal-g.conf
+sed -i "s/.*archive_command/archive_command/" /etc/postgresql-custom/wal-g.conf
+sed -i "s/.*archive_timeout/archive_timeout/" /etc/postgresql-custom/wal-g.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-custom/wal-g.conf
+sed -i "s/.*archive_command/#archive_command/" /etc/postgresql-custom/wal-g.conf
+sed -i "s/.*archive_timeout/#archive_timeout/" /etc/postgresql-custom/wal-g.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-custom/wal-g.conf
+sed -i "s/.*archive_command/archive_command/" /etc/postgresql-custom/wal-g.conf
+sed -i "s/.*archive_timeout/archive_timeout/" /etc/postgresql-custom/wal-g.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
+}
@@ -0,0 +1,16 @@
+# - Archiving -
+
+#archive_mode = on
+#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'
+#archive_timeout = 120
+
+
+# - Archive Recovery -
+
+#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'
+
+
+# - Recovery Target -
+
+#recovery_target_time = ''
+#recovery_target_action = 'promote'
@@ -791,6 +791,9 @@ jit_provider = 'llvmjit'		# JIT library to use
 # User-supplied custom parameters, override any automatically generated ones
 #include = '/etc/postgresql-custom/custom-overrides.conf'
 
+# WAL-G specific configurations
+#include = '/etc/postgresql-custom/wal-g.conf'
+
 #------------------------------------------------------------------------------
 # CUSTOMIZED OPTIONS
 #------------------------------------------------------------------------------

@@ -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,56 @@
       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'
+
+- name: Move custom wal-g.conf file to /etc/postgresql-custom/wal-g.conf
+  template:
+    src: "files/postgresql_config/custom_walg.conf.j2"
+    dest: /etc/postgresql-custom/wal-g.conf
+    mode: 0664
+    owner: postgres
+    group: postgres
+
+- name: Include /etc/postgresql-custom/wal-g.conf in postgresql.conf
+  become: yes
+  replace:
+    path: /etc/postgresql/postgresql.conf
+    regexp: "#include = '/etc/postgresql-custom/wal-g.conf'"
+    replace: "include = '/etc/postgresql-custom/wal-g.conf'"
+
 # 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

@@ -1,3 +1,3 @@
 {
-  "postgres-version": "14.1.0.20"
+  "postgres-version": "14.1.0.21"
 }
@@ -1 +1 @@
-sudo rm /home/ubuntu/.ssh/authorized_keys
+sudo rm /home/ubuntu/.ssh/authorized_keys
@@ -53,4 +53,4 @@ dd if=/dev/zero of=/zerofile &
       sleep 5
     done
 sync; rm /zerofile; sync
-cat /dev/null > /var/log/lastlog; cat /dev/null > /var/log/wtmp
+cat /dev/null > /var/log/lastlog; cat /dev/null > /var/log/wtmp
@@ -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.log 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
Origina E8DF l file line number	Diff line number	Diff line change
		@@ -1 +1 @@
		sudo rm /home/ubuntu/.ssh/authorized_keys
		sudo rm /home/ubuntu/.ssh/authorized_keys