Einleitung

Beim Einsatz von PostgreSQL als Datenbankserver lassen sich verschiedene Aufgaben mit Shellscripts bzw. Windows-Batchdateien automatisieren. Nachfolgend sind verschiedene Scripts zum sichern, löschen, initialisieren und wiederherstellen von Datenbanken auf PostgreSQL aufgeführt.

Scripts für Linux

Die folgenden Scripts wurden unter openSuSE 13.1 mit PostreSQL 9.3 geschrieben.

Datenbank sichern (Postgres 9.3)

Die Postgres-Datenbank kann mit nachfolgendem Script gesichert werden. Dabei wird die in der Tabelle t_ad_relase abgelegte Nuclosversion als Dateiname für das Backup übernommen.

#!/bin/bash
# ----------------------------------------------------------------------
# /usr/local/bin/pgdump - backup nuclos pgsql database to file
# max@kalhammer.com 2014-02-14
# ----------------------------------------------------------------------

pghost=localhost
pgname=$(hostname)
pgport=5432
pgbase=nuclosdb
pguser=postgres
pgpass=ds011$SK
pgpath=/data/backup
pgpsql=/opt/PostgreSQL/9.3/bin/psql
nuclos=`sudo -u postgres $pgpsql -d $pgbase -c "select strrelease from
       nuclos.t_ad_release order by datinstalled desc limit 1;
       " | tail -n 3 | head -n 1`
pgfile=$pgpath/$pgname\_nuclos\_$nuclos\_$(date +%Y-%m-%d)_$(date +%H-%M).pgdump
pgfile=`echo $pgfile | tr -d " "`
pgdump=/opt/PostgreSQL/9.3/bin/pg_dump
pgopts='--no-password --format tar --blobs --create'
echo $pghost:$pgport:$pgbase:$pguser:$pgpass > ~/.pgpass
chmod 600 ~/.pgpass
$pgdump --host $pghost --port $pgport --username $pguser $pgopts --file $pgfile $pgbase
echo "PostgreSQL Backup to file="$pgfile

# ----------------------------------------------------------------------

Datenbank sichern (Postgres 10)

Das folgende Script wurden unter Ubuntu 18.04.2 LTS mit PostreSQL 10 geschrieben.

#!/bin/bash
# ----------------------------------------------------------------------
# /usr/local/bin/pgdump - backup nuclos pgsql 10 database to file
# based on version from max@kalhammer.com
# 2019-08-18
# ----------------------------------------------------------------------
 
pghost=localhost
pgname=$(hostname)
pgport=5432
pgbase=nuclosdb
pguser=postgres
pgpass=YourPwd
pgpath=/data/backup
pgpsql=/usr/lib/postgresql/10/bin/psql
nuclos=`sudo -u postgres $pgpsql -d $pgbase -c "select strrelease from
       nuclos.t_ad_release order by datinstalled desc limit 1;
       " | tail -n 3 | head -n 1`
pgfile=$pgpath/$pgname\_nuclos\_$nuclos\_$(date +%Y-%m-%d)_$(date +%H-%M).pgdump
pgfile=`echo $pgfile | tr -d " "`
pgdump=/usr/lib/postgresql/10/bin/pg_dump
pgopts='--no-password --format tar --blobs --create'
echo $pghost:$pgport:$pgbase:$pguser:$pgpass > ~/.pgpass
chmod 600 ~/.pgpass
$pgdump --host $pghost --port $pgport --username $pguser $pgopts --file $pgfile $pgbase
echo "PostgreSQL Backup to file="$pgfile
 
# ----------------------------------------------------------------------

Datenbank löschen

Mit folgendem kleinen Script kann die Datenbank gelöscht werden.

#!/bin/bash
# ----------------------------------------------------------------------
# /usr/local/bin/pgdrop - drop nuclos pgsql database
# max@kalhammer.com 2014-02-09
# ----------------------------------------------------------------------

pghost=localhost
pgport=5432
pgbase=nuclosdb
pguser=postgres
pgpass=nuclos
pgopts=
pgdrop=/opt/PostgreSQL/9.3/bin/dropdb

echo $pghost:$pgport:$pgbase:$pguser:$pgpass > ~/.pgpass
chmod 600 ~/.pgpass
echo "Dropping Database $pgbase on $pghost"
$pgdrop --host=$pghost --port=$pgport --username=$pguser $pgopts $pgbase

# ----------------------------------------------------------------------

Datenbank initialisieren

Nach dem Löschen einer Datenbank muß vor dem Restore die Datenbank (Standardname nuclosdb) und das Datenbankschema (nuclos) angelegt werden. Dies erledigt folgendes Script.
Wird erstmalig eine Datenbank angelegt, so kann mit dem Kommandozeilenparameter "init" zusätzlich der Datenbankuser (Standardname nuclos) angelegt werden. Normalerweise erledigt
dies der Nuclos-Installer. Es ist dann sinnvoll, wenn z. B. Nuclos auf einem vom Datenbankserver getrennten Applicationserver installiert ist.

#!/bin/bash
# ----------------------------------------------------------------------
# /usr/local/bin/pgprep - prepare nuclos pgsql database
# max@kalhammer.com 2014-02-09
# ----------------------------------------------------------------------

pghost=localhost
pgport=5432
pguser=postgres
pgpass=nuclos
pgnusr=nuclos
pgnpwd=nuclos
pgbase=nuclosdb
pgscma=nuclos
pgopts=--no-password
pgpath=/data/pgsql
pgtemp=/var/tmp/pgprep.sql
pgpsql=/opt/PostgreSQL/9.3/bin/psql

echo $pghost:$pgport:$pgbase:$pguser:$pgpass > ~/.pgpass
chmod 600 ~/.pgpass
echo "-- Postgres Database Preparation for Nuclos"                                                             > $pgtemp
echo "-- ========================================"                                                            >> $pgtemp
if [ "$1" = "init" ]; then
 echo "-- Create Role $pgnusr"                                                                                >> $pgtemp
 echo "CREATE ROLE $pgnusr LOGIN UNENCRYPTED PASSWORD '$pgnpwd' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;" >> $pgtemp
fi
#cho "CREATE TABLESPACE $pgnusr WITH OWNER = $pgnusr LOCATION = '$pgpath';"                                   >> $pgtemp
echo "-- Create Database $pgbase with owner $pguser"                                                          >> $pgtemp
echo "CREATE DATABASE $pgbase WITH OWNER = $pgnusr TEMPLATE = template1 ENCODING = 'UTF8';"                   >> $pgtemp
echo "\c $pgbase"                                                                                             >> $pgtemp
echo "-- Create Schema $pgscma for $pgnusr"                                                                   >> $pgtemp
echo "CREATE SCHEMA $pgscma AUTHORIZATION $pgnusr;"                                                           >> $pgtemp
if [ "$1" = "init" ]; then
 echo "-- Create Language plpgsgl"                                                                            >> $pgtemp
 echo "CREATE LANGUAGE plpgsql;"                                                                              >> $pgtemp
fi
chmod 666 $pgtemp
su - $pguser -c "$pgpsql -a --file=$pgtemp"
rm $pgtemp

# ----------------------------------------------------------------------

Datenbank wiederherstellen

Für das Wiederherstellen der Datenbank oder Umzug auf einen anderen Server kann nach Initalisierung mit pgprep nachfolgende Script verwendet werden.

#!/bin/bash
# ----------------------------------------------------------------------
# /usr/local/bin/pgrest - restore nuclos pgsql database
# max@kalhammer.com 2014-02-09
# ----------------------------------------------------------------------

pghost=localhost
pgport=5432
pgbase=nuclosdb
pguser=postgres
pgpass=nuclos
pgfile=$1
pgopts='--no-password --verbose'
pgrest=/opt/PostgreSQL/9.3/bin/pg_restore

echo $pghost:$pgport:$pgbase:$pguser:$pgpass > ~/.pgpass
chmod 600 ~/.pgpass
$pgrest --host $pghost --port $pgport --username $pguser $pgopts --dbname $pgbase $pgfile

# ----------------------------------------------------------------------

Batchdateien für Windows

Die folgenden Batchdateien wurden auf Windows XP mit PostreSQL 9.2 geschrieben.

Datenbank sichern

Die Postgres-Datenbank kann mit nachfolgender Batchdatei gesichert werden. Die in der Tabelle t_ad_relase abgelegte Nuclosversion wird nicht ausgelesen.

@echo off
rem  ----------------------------------------------------------------------
rem  backup nuclos pgsql database
rem  max@kalhammer.com 2013-04-09
rem  ----------------------------------------------------------------------

set nuclos=3.12.b
set pghost=%computername%
set pgname=de1vc000
set pgport=5432
set pgbase=nuclosdb
set pguser=postgres
set pgpass=nuclos
set pgfile=d:\dump\%pgname%_nuclos_%nuclos%_%date:~6,4%-%date:~3,2%-%date:~0,2%_%time:~0,2%-%time:~3,2%.backup
set pgopts=--no-password --format tar --blobs --create --verbose
set pgdump=c:\programme\postgresql\9.2\bin\pg_dump.exe
set pppfil=%appdata%\postgresql\pgpass.conf

echo %pghost%:%pgport%:%pgbase%:%pguser%:%pgpass% > "%pppfil%"
%pgdump% --host=%pghost% --port=%pgport% --username=%pguser% %pgopts% --file "%pgfile%" %pgbase% 

rem  ----------------------------------------------------------------------

Datenbank löschen

Mit folgender Batchdatei kann die Datenbank gelöscht werden.

@echo off
rem  ----------------------------------------------------------------------
rem  drop nuclos pgsql database
rem  max@kalhammer.com 2013-04-09
rem  ----------------------------------------------------------------------

set pghost=%computername%
set pgport=5432
set pgbase=nuclosdb
set pguser=postgres
set pgpass=nuclos
set pgopts=
set pgdrop=c:\programme\postgresql\9.2\bin\dropdb.exe
set pppfil=%appdata%\postgresql\pgpass.conf

echo %pghost%:%pgport%:%pgbase%:%pguser%:%pgpass% > "%pppfil%"
%pgdrop% --host=%pghost% --port=%pgport% --username=%pguser% %pgopts% %pgbase%

rem  ----------------------------------------------------------------------

Datenbank initialisieren

Nach dem Löschen einer Datenbank muß vor dem Restore die Datenbank (Standardname nuclosdb) und das Datenbankschema (nuclos) angelegt werden. Dies erledigt folgende Batchdatei.
Wird erstmalig eine Datenbank angelegt, so kann mit dem Kommandozeilenparameter "init" zusätzlich der Datenbankuser (Standardname nuclos) angelegt werden. Normalerweise erledigt
dies der Nuclos-Installer. Es ist dann sinnvoll, wenn z. B. Nuclos auf einem vom Datenbankserver getrennten Applicationserver installiert ist.

@echo off
rem  ----------------------------------------------------------------------
rem  prepare nuclos pgsql database
rem  max@kalhammer.com 2013-04-09
rem  ----------------------------------------------------------------------

set pghost=%computername%
set pgport=5432
set pguser=postgres
set pgpass=nuclos
set pgnusr=nuclos
set pgnpwd=nuclos
set pgbase=nuclosdb
set pgopts=--no-password
set pgpath=c:\pgsql
set pgtemp=%TEMP%\pgprep.sql
set pgpsql=c:\programme\postgresql\9.2\bin\psql.exe
set pppfil=%appdata%\postgresql\pgpass.conf

echo %pghost%:%pgport%:%pgbase%:%pguser%:%pgpass% > "%pppfil%"
echo "-- Prepare Database %pgbase%"                                                                           >  "%pgtemp%"
if /i "%1" == "init" (
 echo CREATE ROLE %pgnusr% LOGIN UNENCRYPTED PASSWORD '%pgnpwd%' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; >> "%pgtemp%"
)
rem echo CREATE TABLESPACE %pgnusr% WITH OWNER = %pgnusr% LOCATION = '%pgpath%';                              >> "%pgtemp%"
echo CREATE DATABASE %pgbase% WITH OWNER = %pgnusr% TEMPLATE = template1 ENCODING = 'UTF8';                   >> "%pgtemp%"
echo \c %pgbase%                                                                                              >> "%pgtemp%"
if /i "%1" == "init" (
 echo CREATE SCHEMA %pgnusr% AUTHORIZATION %pgnusr%;                                                          >> "%pgtemp%"
 echo CREATE LANGUAGE plpgsql;                                                                                >> "%pgtemp%"
)
%pgpsql% --file="%pgtemp%"

rem  ----------------------------------------------------------------------

Datenbank wiederherstellen

Für das Wiederherstellen der Datenbank oder Umzug auf einen anderen Server kann nach Initalisierung mit pgprep nachfolgende Script verwendet werden.

@echo off
rem  ----------------------------------------------------------------------
rem  restore nuclos pgsql database
rem  max@kalhammer.com 2013-04-09
rem  ----------------------------------------------------------------------

set pghost=%computername%
set pgport=5432
set pgbase=nuclosdb
set pguser=postgres
set pgpass=nuclos
set pgfile=%1
set pgopts=--no-password --verbose
set pgrest=c:\programme\postgresql\9.2\bin\pg_restore.exe
set pppfil=%appdata%\postgresql\pgpass.conf

echo %pghost%:%pgport%:%pgbase%:%pguser%:%pgpass% > "%pppfil%"
%pgrest% --host=%pghost% --port=%pgport% --username=%pguser% %pgopts% --dbname %pgbase% %pgfile%

rem  ----------------------------------------------------------------------