add: backup mechanism for postgresql

Change-Id: I65c9cd6c52b7d539fbc7dc59cc7436b64186dd73

diff --git a/backup b/backup
new file mode 100755 (executable)
index 0000000..e1be93e
--- /dev/null
+++ b/backup
@@ -0,0 +1,109 @@
+#!/bin/bash
+printf "Backup service ready\n"
+postgres_cluster=9.6/main
+if ! which chunked >/dev/null; then
+    printf "Installing chunked en-/decoder\n" >&2
+    # Install signing key for debian repo 'deb2.dogcraft.de' where 'chunked' is hosted. Downloadable from "http://deb.dogcraft.de/signer.gpg".
+    apt-key add - >&2 <<EOF
+-----BEGIN PGP PUBLIC KEY BLOCK-----
+Version: GnuPG v1
+
+mQINBFRfgHwBEADKRVJpTEVbB6W37ZnIrh0xDRcsTqNgOIJTi/ZsloxN0c9/G8BU
+knEHMpT++qqG3A6pFfg5rci4mEIirt3CoEJw40asyGVSTCl93PNPyuN4ArYxsAL3
+y9lTBKy2UdSorbmrDNUgc+jkevlQ+xcXBypOOY0zDST5402Wfiyk+VVkBDzlLo8L
+q1VsrVVZGqgIfC5D+p2SeRfVxjzDRuDBDS+ifZaxR57bx2bJrrA92C6r4Qo3i7CQ
+IM955G44BU2k/HSaEbl7woXoxb9DTRzvmJ1/m0HT9cl3ak7Zl/UXhJlQd992/e98
+gkX6S1UtFsIp+fmfWtGyeySRH0av3s2i8gcGwUncyyvQ3XIJJcUisaDxrlK4K6ZH
+3XSpApgRoUWp0yDUxELme+rXxd6S32DHxHGbjgD8Crus99GHa9OaBSjAJ2vDp1+d
+wF6Ol/luaZIhGopI4dOhrscZBl8PE8jDsJbOMMpj+KgWD64nRzmnBGYrvhsBaeUh
+EtvnGCiPiK1ojO49ovxVaxkniIyXyZrej8wAHFBEoC7+KaXR0xml5HPonPOIPYSl
+UHmOPYB+3EehJHDt1p2lAUkjFUNppUyLaArX0PZV3I8mgm5PFwbXgooqWwj1C9kW
+cTU1b81KzEKHxf1CoG/rxRTu6qBzTn5yxs03k8uq/Yn534H5GGKwKm1KswARAQAB
+tChEb2djcmFmdCBEZWIgUHVibGlzaGVyIDxkZWJAZG9nY3JhZnQuZGU+iQI+BBMB
+AgAoBQJUX4B8AhsDBQkHhM4ABgsJCAcDAgYVCAIJCgsEFgIDAQIeAQIXgAAKCRCm
+Mbavn9PflNi6EACw9TlR0LL/3RJ9QM0TUfL0Pn5l+NuANL0HUmMepjbEFSxLnmlA
+ypPP0zhi07Ig6Pxkh0Ni9PYBdxkbtFT5ts0SBBcCYvdE8ZqtU7Yx9YtICrHfcx7Y
+yUsvtvubFfvAT3QfXA1C3PHf4ocyQgW6TdqV5jxmbNi0orhggyBZgmVvvheeCMe2
+4dIHqA2Ny3ODSON6pNF4q8QXL1dLU4AvYcWX+79ROMnyv/woMiNdNYFB+9ylSN9U
+sHLu9deXpaxJR25mpk8NuWgyGQUYwtyqySO2ZAFqlC2pdpZ3hRhxi/biPG0XbtF3
+MJQznfNxmf83FfS3u9fHLrZuCkrwbSBXeniKPmlF1aG7ZpGRIRuDUtNhEkgc2tmi
+4tIG/brg0B9rpm2XEccDPxEO14MminXho6IImwpbhiKqnBfOKFv8OX0zzjQkUaFn
+AFuytwyaNn8dBv5WeNNC+8KwBKoDaPUg4JcgzUyXATz0+SIuMBRqWN1q/rz4GCCz
+Lf/TqEl41h8p1weM33md0p9Fa/xQRe3X9ChVbGyGylaIhniol0IcgROa3t04cO9x
+xdh/wvgmh7wodCE3mK/G3jcTnQcBh6T6qr/z9PqToZJoRFqjPLv78UmnTJ6Za/ZX
+uXeRJJf1C3Nswjfwexo2CeWbbQ3X+PGLcDgUmTG7yxDjKL/8rJ0DWWQ8fg==
+=C8K/
+-----END PGP PUBLIC KEY BLOCK-----
+EOF
+    cp -v modules/lxc/files/dogcraft.list /etc/apt/sources.list.d/ >&2
+    apt-get update >&2
+    apt-get install -y chunked >&2
+fi
+
+while :; do
+    read -r command || break
+    if [[ $command == base ]]; then
+        sudo lxc-attach -n postgres-primary -- su -c 'pg_basebackup -Ft -z -D - -P' postgres | chunked
+        printf "base backup done\n"
+    elif [[ $command == incremental ]]; then
+        read -e req_name
+        read -e req_hash
+        if ! grep -qi -- '^[A-Z0-9]\{24\}$' <<< $req_name; then
+            printf "Error: invalid WAL-name.\n" >&2
+            printf "Error\n"
+        fi
+        real_hash="$(sha256sum "/data/postgres/data/archive/$req_name" | cut -d" " -f1)"
+        if [[ $req_hash != "-" ]] && [[ $real_hash != $req_hash ]]; then
+            printf "Error: hash mismatch on expected %s != provided %s\n" "$real_hash" "$req_hash" >&2
+            printf "Error\n"
+            exit 1
+        fi
+        printf "Ready\n"
+
+        files=( /data/postgres/data/archive/* )
+        for i in "${files[@]}"; do
+            name="$(basename $i)"
+            if ( [[ "$name" != "$req_name".* ]] && [[ $name > $req_name ]] ) || ( ( [[ "$name" == "$req_name".* ]] || [[ $req_name == $name ]] ) && [[ $req_hash == "-" ]] ); then
+                printf "%s\n" "$name"
+            fi
+        done | tar cz -C /data/postgres/data/archive -T - | chunked
+        printf "incremental backup done, confirm cleanup!\n"
+        read -e confirmation
+        if [[ $confirmation == "y" ]]; then
+            printf "Cleaning up archive\n" >&2
+            lxc-attach -n postgres-primary -- pg_archivecleanup /var/lib/postgresql/archive/ "$req_name"
+        else
+            printf "Not doing cleanup\n" >&2
+        fi
+    elif [[ $command == restore ]]; then
+        # for now (and quick development) we override
+        rm -R /data/postgres/data
+
+        if [[ -d /data/postgres/data ]]; then
+            printf "error\n"
+            exit 1
+        fi
+        printf "postgres base\n"
+        mkdir -p "/data/postgres/data/${postgres_cluster}"
+        chunked decode > /data/postgres/data/pg_base.tar.gz
+        mkdir -p /data/postgres/data/restore
+        while :; do
+            printf "incremental?\n"
+            read -e inc
+            if [[ $inc != "y" ]]; then
+                break
+            fi
+            chunked decode | tar xvz -C /data/postgres/data/restore >&2
+        done
+        cat > "/data/postgres/data/${postgres_cluster}/recovery.conf" <<EOF
+restore_command = 'cp /var/lib/postgresql/restore/%f "%p"'
+archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/restore %r'
+recovery_end_command = 'touch /var/lib/postgresql/postgres-ready'
+EOF
+        mkdir -p "/data/postgres/conf/${postgres_cluster}"
+        printf "auto\n" > "/data/postgres/conf/${postgres_cluster}/start.conf"
+        touch "/data/postgres/conf/${postgres_cluster}/postgresql.conf"
+    elif [[ $command == end ]]; then
+        printf "end\n"
+    fi
+done

diff --git a/environments/production/manifests/postgres-primary.pp b/environments/production/manifests/postgres-primary.pp
index c1f015aa7425f3af8522dd693916abf944f88ca1..9d28846af48431ffd362b705c3bcd12c6e605fda 100644 (file)
--- a/environments/production/manifests/postgres-primary.pp
+++ b/environments/production/manifests/postgres-primary.pp
@@ -2,17 +2,31 @@ node postgres-primary {
   include container::contained
   include container::no_ssh
 
   include container::contained
   include container::no_ssh
 

+  exec { 'backup installed':
+    before => Package['postgresql'],
+    notify => Exec['backup permissions corrected'],
+    command => '! [ -f /var/lib/postgresql/9.6/main/PG_VERSION ] && mkdir -p /var/lib/postgresql/9.6/main && tar xzf /var/lib/postgresql/pg_base.tar.gz -C /var/lib/postgresql/9.6/main',
+    onlyif => '[ -f /var/lib/postgresql/pg_base.tar.gz ]',
+    provider => 'shell'
+  }

   package{ 'postgresql':
     ensure => 'installed',
     install_options => ['--no-install-recommends'],
   package{ 'postgresql':
     ensure => 'installed',
     install_options => ['--no-install-recommends'],

-  }
-
+  }->

   class { 'postgresql::globals':
     version => '9.6',
   }->
   class { 'postgresql::server':
       listen_addresses => '*',
   class { 'postgresql::globals':
     version => '9.6',
   }->
   class { 'postgresql::server':
       listen_addresses => '*',

-  } ->
+  }
+  exec { 'backup permissions corrected':
+    require => Class['postgresql::server::install'],
+    before => Class['postgresql::server::initdb'],
+    command => 'chown -R postgres:postgres /var/lib/postgresql && rm /var/lib/postgresql/pg_base.tar.gz',
+    onlyif => '[ -f /var/lib/postgresql/pg_base.tar.gz ]',
+    refreshonly => 'true',
+    provider => 'shell'
+  }

   postgresql::server::db { 'gigi':
     require  => Package['postgresql'],
     user     => 'gigi',
   postgresql::server::db { 'gigi':
     require  => Package['postgresql'],
     user     => 'gigi',


@@ -30,7 +44,7 @@ node postgres-primary {
   }
 
   postgresql::server::db { 'quiz':
   }
 
   postgresql::server::db { 'quiz':

-    require  => Class['postgresql::server'],
+    require  => Exec['backup installed'],

     user     => 'quiz',
     password => postgresql_password('quiz', $passwords[postgres][quiz]),
   }
     user     => 'quiz',
     password => postgresql_password('quiz', $passwords[postgres][quiz]),
   }


@@ -55,6 +69,7 @@ node postgres-primary {
     value => 'on'
   }
   file{'/var/lib/postgresql/archive/':
     value => 'on'
   }
   file{'/var/lib/postgresql/archive/':

+    require  => Exec['backup permissions corrected'],

     ensure => 'directory',
     owner => 'postgres'
   } ->
     ensure => 'directory',
     owner => 'postgres'
   } ->

diff --git a/manager/backup b/manager/backup
new file mode 100755 (executable)
index 0000000..22d536c
--- /dev/null
+++ b/manager/backup
@@ -0,0 +1,138 @@
+#!/bin/bash
+
+targetHost=$1
+targetHost=${targetHost%/}
+source config
+source "$targetHost/config"
+
+if ! which chunked >/dev/null; then
+    printf "Requires 'chunked' package from deb2.dogcraft.de\n" >&2
+    exit 1
+fi
+
+
+
+backup_target="$targetHost/backups"
+coproc {
+    ssh_target "sudo ./backup"
+    read -r end
+}
+read -r line <&${COPROC[0]} || exit 1;
+if [[ $line != "Backup service ready" ]]; then
+    echo "Backup service did not respond"
+    exit 1
+fi
+function start_backup {
+    ident="$(date "+%Y-%m-%d-%H-%M-%S")"
+    self="$backup_target/$ident"
+    mkdir -p "$self"
+    if [[ -h "$backup_target/last" ]]; then
+        last="$(readlink "$backup_target/last")"
+        ln -s "../$last" "$self/prev"
+        rm "$backup_target/last"
+    fi
+    ln -s "$ident" "$backup_target/last"
+}
+function base {
+    printf "base\n" >&${COPROC[1]}
+    chunked decode <&${COPROC[0]} > "$self/pg_base.tar.gz"
+    ls -alsh -- "$self/pg_base.tar.gz"
+    read -r line <&${COPROC[0]} || exit 1;
+    echo "pg_base done: $line"
+    echo "Backup info: "
+    tar xzO backup_label <  "$self/pg_base.tar.gz"
+}
+function incremental {
+    last=
+    hash=
+    if [[ $(find -L "$self" -maxdepth 14 -name "pg_base.tar.gz" | wc -l) -lt 1 ]]; then
+        printf "doing pg_base backup to $self/pg_base.tar.gz:\n"
+        base
+        last="$(tar xzO backup_label <  "$self/pg_base.tar.gz" | grep "^START WAL LOCATION: " | sed "s/.*(file \\(.*\\))/\\1/")"
+        hash="-"
+    else
+        last=""
+        folder="$self"
+        while [[ $last == "" ]]; do
+            folder="$folder/prev"
+            last="$(tar tf "$folder/pg_wal.tar.gz" | grep "^[A-Z0-9]*$" | tail -n 1)"
+        done
+        echo "Found last WAL file in backup: $folder"
+        hash=$(tar xfO "$folder/pg_wal.tar.gz" "$last" | sha256sum | cut -d" " -f1)
+    fi
+    printf "Last WAL-name: %s\n" "$last"
+    printf "incremental\n" >&${COPROC[1]}
+    printf "%s\n" "$last" >&${COPROC[1]}
+    printf "%s\n" "$hash" >&${COPROC[1]}
+    read -r line <&${COPROC[0]} || exit 1;
+    if [[ "$line" != "Ready" ]]; then
+        printf "incremental backup didn't start\n"
+        exit 1
+    fi
+    chunked decode <&${COPROC[0]} > "$self/pg_wal.tar.gz"
+    printf "Tar contents\n"
+    tar tf "$self/pg_wal.tar.gz"
+    ls -alsh -- "$self/pg_wal.tar.gz"
+    read -r line <&${COPROC[0]} || exit 1;
+    if [[ $line == "incremental backup done, confirm cleanup!" ]]; then
+        printf "y\n" >&${COPROC[1]}
+    else
+        printf "Done, but got strange line for cleanup confirmation: %s\n" "$line"
+    fi
+    printf "Done incremental backup\n"
+}
+
+if [[ "$2" == "restore" ]]; then
+    sourceHost=$3
+    sourceHost=${sourceHost%/}
+    backup_target="${sourceHost}/backups"
+    folder="$backup_target/last"
+    printf "Restoring backup %s\n" "$(readlink -e -- "$folder")"
+    tar tf "$folder/pg_wal.tar.gz"
+    while ! [[ -f "$folder/pg_base.tar.gz" ]]; do
+        folder="$folder/prev"
+        printf "Requires backup %s\n" "$(readlink -e -- "$folder")"
+        tar tf "$folder/pg_wal.tar.gz"
+    done
+    #tar tf "$folder/pg_base.tar.gz"
+    printf "restore\n" >&${COPROC[1]}
+    read -r reply <&${COPROC[0]} || exit 1;
+    if [[ $reply != "postgres base" ]]; then
+        printf "Service is not ready to receive backup: %s\n" "$reply"
+        exit 1
+    fi
+    echo "sending base"
+    chunked < "$folder/pg_base.tar.gz" >&${COPROC[1]}
+    echo "done sending base"
+    folder="$backup_target/last"
+    while :; do
+        read -r reply <&${COPROC[0]} || exit 1;
+        if [[ $reply != "incremental?" ]]; then
+            printf "Service is not ready to receive backup: %s\n" "$reply"
+            exit 1
+        fi
+        printf "y\n" >&${COPROC[1]}
+        printf "Sending pg_wal from %s\n" "$(readlink -e -- "$folder")"
+        chunked < "$folder/pg_wal.tar.gz" >&${COPROC[1]}
+        if [[ -f "$folder/pg_base.tar.gz" ]]; then
+            break
+        fi
+        folder="$folder/prev"
+    done
+    read -r reply <&${COPROC[0]} || exit 1;
+    if [[ $reply != "incremental?" ]]; then
+        printf "Service is not ready to receive backup: %s\n" "$reply"
+        exit 1
+    fi
+    printf "n\n" >&${COPROC[1]}
+elif [[ "$2" == "backup" ]]; then
+    start_backup
+    incremental
+else
+    printf "Error, unknown sub command: %s\n" "$2" >&2
+fi
+
+
+printf "end\n" >&${COPROC[1]}
+read -r line <&${COPROC[0]} || exit 1;
+printf "END: %s\n" "$line"

diff --git a/manager/config b/manager/config
index e0e675390226afb857d2f1c3fa91144a29d47d77..3d7b25a323c5be165d15857e4aebaf6051ac2cee 100755 (executable)
--- a/manager/config
+++ b/manager/config
@@ -86,3 +86,13 @@ function mcurl {
 function admin_ssh {
     ssh -i admin-key -p 2222 "admin@$to" "$@"
 }
 function admin_ssh {
     ssh -i admin-key -p 2222 "admin@$to" "$@"
 }

+
+# Install backup is a hook to be triggered before the sytem is set up
+function install_backup {
+    :
+}
+
+function populate_system {
+    # default is create fresh data using bootstrap-user
+    execute-bootstrap-user
+}

diff --git a/manager/setup b/manager/setup
index 0fb4061da7b02515d946f880f020944f39ad8edb..88e451636f6c81dc719677720bec44929ab444da 100755 (executable)
--- a/manager/setup
+++ b/manager/setup
@@ -135,12 +135,14 @@ if [[ "$2" == "update" ]]; then
 fi
 configure
 ensure_nre
 fi
 configure
 ensure_nre

+
+install_backup
+

 title 'S3: puppet-1'
 ssh_target -t 'sudo ./bootstrap'
 title 'S3: puppet-2'
 ssh_target -t 'sudo ./bootstrap'
 
 title 'S3: puppet-1'
 ssh_target -t 'sudo ./bootstrap'
 title 'S3: puppet-2'
 ssh_target -t 'sudo ./bootstrap'
 

-[[ -f ../../migrate ]] && ( cd ../.. && bash migrate "$targetHost")

 if [[ -f tricks ]]; then
     cat tricks | ssh_target 'cat > tricks && chmod +x tricks'
     ssh_target -t 'bash tricks'
 if [[ -f tricks ]]; then
     cat tricks | ssh_target 'cat > tricks && chmod +x tricks'
     ssh_target -t 'bash tricks'


@@ -151,7 +153,7 @@ if [[ $signerLocation == "self" ]] && [[ $(ssh_target 'ps -ef | grep tcpseria[l]
     exit 1
 fi
 
     exit 1
 fi
 

-execute-bootstrap-user
+populate_system

 
 title 'bash'
 eval $(ssh-agent -k)
 
 title 'bash'
 eval $(ssh-agent -k)