Archive:SEP sesam Extension for PostgreSQL

From SEPsesam
Icon archived docs.png THE CONTENT OF THIS PAGE IS OUTDATED
SEP AG has discontinued support for obsolete SEP sesam versions. Instructions are still available for these SEP sesam products, however, SEP AG accepts no responsibility or liability for any errors or inaccuracies in the instructions or for the incorrect operation of obsolete SEP sesam software. It is strongly recommended that you update your SEP sesam software to the latest version. For the latest version of SEP sesam documentation, see documentation home.

Template:Copyright SEP AG en


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

  • Supported Versions
  • 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

Hardware

Hardware requirements/en

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 (Please note: 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
  • A different port can be specified with -a port=<portnumber>

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.

Single DB Restore

To be able to restore single DBs from a Postgres Server you have to consider these preconditions:

  • For each Postgres DB there must be configured a separate backup task (the DB tasks can be started as a group)
  • With the Postgres "all" backup you can currently only restore all DBs.