SEP sesam Extension for PostgreSQL

From SEPsesam

(C)SEP AG

Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.

1 Introduction
2 Prerequisites
3 Installation
- 3.1 up to Sesam version 3.4
- 3.2 Sesam version 3.6 or higher
4 Configuration
- 4.1 Backup
5 Restore
- 5.1 no Recover
- 5.2 with Recover

Introduction

The SEP sesam extension for PostgreSQL allows an uninterruptible backup of PostgreSQL databases.

Features:

Backup of a single database
Restore as Dump File
Restore directly into the database without caching in the filesystem

Prerequisites

PostgreSQL server 8.3.1 on Linux i386 or 7.4.2 on Linux x84_64 (tested)
Sesam client >= 3.4.1.51 on the PostgreSQL database host
Sesam server >= 3.4.1.51 (works also with a older Sesam server version 3.4, but the the GUI doesn't support the complete functionality)
PostgreSQL extension only, if the version of the installed Sesam client <3.6. The Sesam client >= version 3.6 already includes the PostgreSQL extension. In this case the installation steps can be skipped.
Functioning SEP sesam filesystem Backup

Installation

up to Sesam version 3.4

If the Sesam client has been installed by RPM extension should be installed with a RPM package too.
Install or update of the SEP sesam PostgreSQL module by:

RPM

  rpm -Uhv sesam-pgsql-3.4.X-XX.i386.rpm

TGZ

Unpacking of the Tar archive sesam-pgsql_3.4.X.X.tgz
Copy the file sbc_pgsql in the directory <SESAM_ROOT>/bin/sesam

  root@dbsrv:~> cd /tmp                                                       root@dbsrv:/tmp> tar xvzf sesam-pgsql.3.4.1.51.tgz                                  root@dbsrv:/tmp> cp sesam-pgsql_3.4.1.51/sbc_pgsql /opt/sesam/bin/sesam

Sesam version 3.6 or higher

No Special Modules are needed with Sesam 3.6. The PostgreSQL Module isincluded in the regular Client Package.

Configuration

The settings will be made in the SEP sesam GUI.

Backup

Create a new backup task for the backup of the PostgreSQL server with the backup type PostgreSQL
Fill in the backup source with the database name (Watched: the database name is case sensitive)
If you need a username and a password to access the database, set these information in the field Options 1 -> Backup options:

  -a user=<DB User>,password=<Passwort DB Users>

You can specify the same informations in the Restore options. These will be used in the case of a restore operation. If the password has changed you can set it in the restore wizard at the time of the restore request. If the database doesn't need any account information avoid the setting of username and password.

If another operating system user is necessary for the backup (mostly postgres instead of root) you can set it with the option -a osuser=<username>.

  -a osuser=postgres

Restore

A Restore operation offers the possibilies to execute with or without (no) Recover.

no Recover

In the case of No Recover the data will be written as a Dump file onto the filesystem (usually in the directory <SESAM-VAR>/work. You can use this file as inputfile for the PostgreSQL program pg_restore. Select the option No recover after restore in the Restore Wizard to proceed this.

su - postgrespg_restore template1 -C /var/opt/sesam/var/work/pgsql-SF20081128224529.tmp

For additional information about using pg_restore to refer to the man page.

Advantage:

You can do any adaptations on the data before you import them into the database

Drawback:

The restored data will be stored on the filesystem first
The import of data has to be started on the command line interface of the servers operating system

with Recover

The data will be stored directly into the database without caching in the filesystem. Select the option Auto recover after restore in the Restore Wizard to proceed this.

Advantage

The data will be written directly into the database without any stopover
There are no activities on the PostgresSQL server necessary

Drawback

No changes on the data are possible

If you start the restore with the option overwrite existing data the database will be new created. If this set together with the option Restore to the original target path the database will be deleted first and right after that created by using of the information of the backup saveset. If you select the option New restore target and enter a new database name the PostgreSQL command createdb creates a database with this name. The data will be imported into this new database.

Hint

Pay attention on automatically creating a new database and a following import into that database you have to set up necessary database users and roles before the import.

If you select the restore option do not overwrite existing files the data will be restored into a existing database only. If there are any error during the recover process (e.g. Primary Key Error) the restore operation will be displayed as an error in the Sesam GUI. In this case you should check the restore protocol as well as the data in the database.

Retrieved from "http://wiki.sepsoftware.com/wiki/index.php/SEP_sesam_Extension_for_PostgreSQL"