CODEWITHSUNDEEP
postgresqlbackuprestorewal
rene marxis
rene marxis

Reputation: 415

postgres backup with WAL

i am in no way a db admin, so please don't shoot me if i'm doing it completly wrong ...

I have to add some archiving to a productive postgres database (newest version in docker container) and trying to build some scripts to use with WAL.

The idea is to have a weekly script, that does a full backup to a new directory and then creates a symlink to this new directory that is used by the WAL script to write it's logs. Also the weekly script will delete old backups older than 30 days.

I would be very happy for any comments on this...

db settings

wal_level = replica
archive_mode = on
archive_command = '/archive/archive_wal.sh "%p" "%f"'
archive_timeout = 300

weekly script:

#!/bin/bash

#create base archive dir
#base_arch_dir=/tmp/archive/
base_arch_dir=/archive/
if [ ! -d "$base_arch_dir" ]; then
  mkdir "$base_arch_dir"
  chown -R postgres:postgres "$base_arch_dir"
fi

#create dir for week
dir="$base_arch_dir"$(date '+%Y_%m_%d__%H_%M_%S')
if [ ! -d "$dir" ]; then
  mkdir "$dir"
  chown -R postgres:postgres "$dir"
fi

#change/create the symlink
newdir="$base_arch_dir"wals
ln -fsn "$dir" "$newdir"
chown -R postgres:postgres "$newdir"

#do the base backup to the wals dir
if pg_basebackup -D "$newdir" -F tar -R -X fetch -z -Z 9 -U postgres; then
  find "$base_arch_dir"* -type d -mtime +31|xargs rm -rf
fi

crchive script:

#!/bin/bash

set -e

arch_dir=/archive/wals
arch_log="$arch_dir/arch.log"

if [ ! -d "$arch_dir" ]; then
  echo arch_dir '"$arch_dir"' does not exist >> "$arch_log"
  exit -1
fi

#get the variables from postgres
p=$1
f=$2

if [ -f "$arch_dir"/$f.xz ]; then 
  echo wal file '"$arch_dir"/$f.xz' already exists
  exit -1
fi

pxz -2 -z --keep -c $p > "$arch_dir"/$f.xz

Thank you in advance

Upvotes: 1

Views: 1334

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22893

It's not terribly difficult to put together your own archiving scripts, but there are a few things you need to keep track of, because when you need your backups you really need them. There are some packaged backup systems for PostgreSQL. You may find these two a good place to start, but others are available.

Upvotes: 2

Related Questions