SEP sesam Extension for PostgreSQL

From SEPsesam

Jump to: navigation, search

SEP sesam Extension for PostgreSQL

(C)SEP AG

Copyright 1999-2011 by SEP AG. All Rights reserved.

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.

Contents

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 file system

Prerequisites

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

Installation

up to SEP sesam version 3.4

If the SEP sesam Client has been installed by RPM, the 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

SEP sesam version 3.6 or higher

No Special Modules are needed with SEP sesam 3.6. The PostgreSQL Module is included 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=<Password DB Users>

You can specify the same information 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 possibilities 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 file system (usually in the directory <SESAM-VAR>/work. You can use this file as input file for the PostgreSQL program pg_restore. Select the option No recover after restore in the Restore Wizard to proceed this. 

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

For additional information about using pg_restore 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 file system first
  • The import of data has to be started on the command line interface of the server's operating system

with Recover

The data will be stored directly into the database without caching in the file system. 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 an existing database only. If there are any errors during the recover process (e.g. Primary Key Error) the restore operation will be displayed as an error in the SEP 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"
Personal tools