[bacula/docs] / docs / manuals / de / catalog / postgresql.tex

%%
%%

\chapter{PostgreSQL Installation und Konfiguration}
\label{PostgreSqlChapter}
\index[general]{PostgreSQL!Installation und Konfiguration }
\index[general]{PostgreSQL Installation und Konfiguration }
\index[general]{Update}

Wenn Sie sich dazu entschlie{\ss}en PostgreSQL zu verwenden,
sollten Sie sich \"{u}ber den Aufwand, den ein Datenbank-Update
mit sich bringt, im Klaren sein. Grunds\"{a}tzlich werden Sie bei
jeder neuen Hauptversion von PostgreSQL Ihre alte Datenbank
exportieren m\"{u}ssen, um sie dann in die neue Version einzupflegen.
Das wird dadurch erforderlich, dass sich regelm\"{a}{\ss}ig 
einige internen "`Datenformate"' \"{a}ndern und von den
PostgreSQL-Entwicklern keine Tools zu Verf\"{u}gung gestellt
werden, um den Update-Vorgang zu automatisieren. Falls Sie den
Daten-Ex- und -Import vergessen sollten, kann es sein das Sie
auf die Datenbank nicht mehr zugreifen k\"{o}nnen, da die neue
PostgreSQL-Version nicht mit den alten Datenbank-Dateien
zusammenarbeitet.

Sollten Sie PostgreSQL aus dem Quelltext selbst
kompilieren, m\"{u}ssen Sie dem configure-Kommando die
Option {\bf \verb:--:enable-thread-safety} \"{u}bergeben.

\section{PostgreSQL Installation}
\index[general]{PostgreSQL!Installation }
Wenn Sie bei der Konfiguration des Bacula-Quelltextes{\bf ./configure
\verb:--:with-postgresql=PostgreSQL-Verzeichnis} angeben, m\"{u}ssen Sie
PostgreSQL mindestens in Version 7.4 installiert haben. \"{A}ltere
PostgreSQL-Versionen finktionieren mit bacula nicht. Falls PostgreSQL
in einem Standard-Verzeichnis installiert ist, brauchen Sie das
"`PostgreSQL-Verzeichnis"' nicht angeben. Wenn es aber zum Beispiel in
Ihrem Home-Verzeichnis installiert ist, m\"{u}ssen Sie den kompletten
Pfad angeben.

Die Konfiguration und Installation von PostgreSQL ist zwar nicht sehr
schwer, kann aber beim ersten Mal etwas verwirrend sein. Wenn Sie es vorziehen,
k\"{o}nnen Sie PostgreSQL auch \"{u}ber die Paket-Verwaltung Ihres Betriebssystems
installieren. Vorkompilierte Pakete finden Sie auf www.postgresql.org
unter \elink{Downloads}{http://www.postgresql.org/download/}

Wenn Sie PostgreSQL lieber aus dem Quelltext selbst kompilieren m\"{o}chten,
empfehlen wir Ihnen den Anweisungen in der \elink{PostgreSQL
Dokumentation}{http://www.postgresql.org/docs/} zu folgen.

Benutzer von FreeBSD finden in diesem \elink{FreeBSD Diary
Artikel}{http://www.freebsddiary.org/postgresql.php} weitere n\"{u}tzliche
Informationen. Selbstverst\"{a}ndlich enth\"{a}lt der Artikel auch f\"{u}r
Nicht-FreeBSD-Benutzer wissenswertes bez\"{u}glich der Installation und
Konfiguration von PostgreSQL

Falls Sie die Bacula "`Batch-Insert"'-Funktion benutzen wollen, die
standardm\"{a}{\ss}ig aktiviert ist und f\"{u}r eine schnelle Verarbeitung
der Attribute der gesicherten Datein sorgt, m\"{u}ssen Sie unbedingt darauf
achten, dass PostgreSQL mit der Option {\bf \verb:--:enable-thread-safety}
kompiliert wurde. Bei den meisten gro{\ss}en Linux-Distributionen ist das
der Fall. Aber falls Sie nicht sichert sind k\"{o}nnen Sie mit folgendem
Kommando feststellen, ob Ihr PostgreSQL gegen die pthreads-Bibliothek
gelinked ist:

\footnotesize
\begin{verbatim}
  nm /usr/lib/libpq.a | grep pthread_mutex_lock
\end{verbatim}
\normalsize

Die Kommandos m\"{u}ssen in etwa eine Zeile wie diese zur\"{u}ckgeben:

\footnotesize
\begin{verbatim}
         U pthread_mutex_lock
\end{verbatim}
\normalsize

wenn das der Fall ist, ist alles in Ordnung. Wenn keine Zeilen zur\"{u}ckgegeben
werden, wird Bacula beim kompilieren die "`Batch-Insert"`-Funktion deaktivieren.
Wenn Sie sie trotzdem benutzen wollen, m\"{u}ssen Sie PostgreSQL mit der Option
\verb:--:enable-thread-safety neu kompilieren.

Nach der PostgreSQL-Installation fahren Sie bitte mit der Installation von Bacula
fort. Sp\"{a}ter wenn Sie diese abgeschlossen haben, lesen Sie hier weiter, um
die Konfiguration von PostgreSQL zu beenden. Bitte beachten Sie, dass einige
Schritte im weiteren Verlauf der PostgreSQL-Konfiguration Scripte ben\"{o}tigen,
die erst bei der Installation von Bacula erstellt werden. Auch wenn Sie f\"{u}r
die Installation von PostgreSQL vorkompilierte Pakete verwendet haben (zum
Beispiel rpm oder deb) m\"{u}ssen Sie sp\"{a}ter hier weitermachen, um die
Konfiguration von PostgreSQL zu vervollst\"{a}ndigen.

\label{PostgreSQL_configure}
\section{Configuring PostgreSQL}
\index[general]{PostgreSQL!Configuring PostgreSQL -- }

At this point, you should have built and installed PostgreSQL, or already have
a running PostgreSQL, and you should have configured, built and installed {\bf
Bacula}. If not, please complete these items before proceeding. 

Please note that the {\bf ./configure} used to build {\bf Bacula} will need to
include {\bf \verb:--:with-postgresql=PostgreSQL-directory}, where {\bf
PostgreSQL-directory} is the directory name that you specified on the
./configure command for configuring PostgreSQL (if you didn't specify a
directory or PostgreSQL is installed in a default location, you do not need to
specify the directory). This is needed so that Bacula can find the necessary
include headers and library files for interfacing to PostgreSQL. 

{\bf Bacula} will install scripts for manipulating the database (create,
delete, make tables etc) into the main installation directory. These files
will be of the form *\_bacula\_* (e.g. create\_bacula\_database). These files
are also available in the \lt{}bacula-src\gt{}/src/cats directory after
running ./configure. If you inspect create\_bacula\_database, you will see
that it calls create\_postgresql\_database. The *\_bacula\_* files are
provided for convenience. It doesn't matter what database you have chosen;
create\_bacula\_database will always create your database. 

Now you will create the Bacula PostgreSQL database and the tables that Bacula
uses. These instructions assume that you already have PostgreSQL running. You
will need to perform these steps as a user that is able to create new
databases. This can be the PostgreSQL user (on most systems, this is the pgsql
user). 

\begin{enumerate}
\item cd \lt{}install-directory\gt{}

   This directory contains the Bacula catalog  interface routines.  

\item ./create\_bacula\_database

   This script creates the PostgreSQL {\bf bacula} database.  
   Before running this command, you should carefully think about
   what encoding sequence you want for the text fields (paths, files, ...).
   Ideally, the encoding should be set to UTF8. However, many Unix systems
   have filenames that are not encoded in UTF8, either because you have
   not set UTF8 as your default character set or because you have imported
   files from elsewhere (e.g. MacOS X).  For this reason, Bacula uses
   SQL\_ASCII as the default encoding.  If you want to change this,
   please modify the script before running it, but be forewarned that
   Bacula backups will fail if PostgreSQL finds any non-UTF8 sequences.

   If running the script fails, it is probably because the database is
   owned by a user other than yourself.  On many systems, the database
   owner is {\bf pgsql} and on others such as Red Hat and Fedora it is {\bf
   postgres}.  You can find out which it is by examining your /etc/passwd
   file.  To create a new user under either your name or with say the name
   {\bf bacula}, you can do the following:

\begin{verbatim}
   su
   (enter root password)
   su pgsql (or postgres)
   createuser kern (or perhaps bacula)
   Shall the new user be allowed to create databases? (y/n) y
   Shall the new user be allowed to create more new users? (y/n) (choose
         what you want)
   exit
\end{verbatim}
    
    At this point, you should be able to execute the
    ./create\_bacula\_database command.

\item ./make\_bacula\_tables

   This script creates the PostgreSQL tables used by {\bf Bacula}.  
\item ./grant\_bacula\_privileges

   This script creates the database user {\bf bacula}  with restricted access
rights. You may  want to modify it to suit your situation. Please note that 
this database is not password protected.  

\end{enumerate}

Each of the three scripts (create\_bacula\_database, make\_bacula\_tables, and
grant\_bacula\_privileges) allows the addition of a command line argument.
This can be useful for specifying the user name. For example, you might need
to add {\bf -h hostname} to the command line to specify a remote database
server. 

To take a closer look at the access privileges that you have setup with the
above, you can do: 

\footnotesize
\begin{verbatim}
PostgreSQL-directory/bin/psql --command \\dp bacula
\end{verbatim}
\normalsize

Also, I had an authorization problem with the password. In the end,
I had to modify my {\bf pg\_hba.conf} file (in /var/lib/pgsql/data on my machine)
from:

\footnotesize
\begin{verbatim}
  local   all    all        ident  sameuser
to
  local   all    all        trust  sameuser
\end{verbatim}
\normalsize

This solved the problem for me, but it is not always a good thing
to do from a security standpoint.  However, it allowed me to run
my regression scripts without having a password.

A more secure way to perform database authentication is with md5
password hashes.  Begin by editing the {\bf pg\_hba.conf} file, and
just prior the the existing ``local'' and ``host'' lines, add the line:

\footnotesize
\begin{verbatim}
  local bacula bacula md5
\end{verbatim}
\normalsize

and restart the Postgres database server (frequently, this can be done
using "`/etc/init.d/postgresql restart"' or "`service postgresql restart"') to
put this new authentication rule into effect.

Next, become the Postgres administrator, postgres, either by logging
on as the postgres user, or by using su to become root and then using
su - postgres to become postgres.  Add a password to the bacula
database for the bacula user using:

\footnotesize
\begin{verbatim}
  \$ psql bacula
  bacula=# alter user bacula with password 'secret';
  ALTER USER
  bacula=# \\q
\end{verbatim}
\normalsize

You'll have to add this password to two locations in the
bacula-dir.conf file: once to the Catalog resource and once to the
RunBeforeJob entry in the BackupCatalog Job resource.  With the
password in place, these two lines should look something like:

\footnotesize
\begin{verbatim}
  dbname = bacula; user = bacula; password = "secret"
    ... and ...
  # WARNING!!! Passing the password via the command line is insecure.
  # see comments in make_catalog_backup for details.
  RunBeforeJob = "/etc/make_catalog_backup bacula bacula secret"
\end{verbatim}
\normalsize

Naturally, you should choose your own significantly more random
password, and ensure that the bacula-dir.conf file containing this
password is readable only by the root.

Even with the files containing the database password properly
restricted, there is still a security problem with this approach: on
some platforms, the environment variable that is used to supply the
password to Postgres is available to all users of the
local system.  To eliminate this problem, the Postgres team have
deprecated the use of the environment variable password-passing
mechanism and recommend the use of a .pgpass file instead.  To use
this mechanism, create a file named .pgpass containing the single
line:

\footnotesize
\begin{verbatim}
  localhost:5432:bacula:bacula:secret
\end{verbatim}
\normalsize

This file should be copied into the home directory of all accounts
that will need to gain access to the database: typically, root,
bacula, and any users who will make use of any of the console
programs.  The files must then have the owner and group set to match
the user (so root:root for the copy in ~root, and so on), and the mode
set to 600, limiting access to the owner of the file.

\section{Re-initializing the Catalog Database}
\index[general]{Database!Re-initializing the Catalog }
\index[general]{Re-initializing the Catalog Database }

After you have done some initial testing with {\bf Bacula}, you will probably
want to re-initialize the catalog database and throw away all the test Jobs
that you ran. To do so, you can do the following: 

\footnotesize
\begin{verbatim}
  cd <install-directory>
  ./drop_bacula_tables
  ./make_bacula_tables
  ./grant_bacula_privileges
\end{verbatim}
\normalsize

Please note that all information in the database will be lost and you will be
starting from scratch. If you have written on any Volumes, you must write an
end of file mark on the volume so that Bacula can reuse it. Do so with: 

\footnotesize
\begin{verbatim}
   (stop Bacula or unmount the drive)
   mt -f /dev/nst0 rewind
   mt -f /dev/nst0 weof
\end{verbatim}
\normalsize

Where you should replace {\bf /dev/nst0} with the appropriate tape drive
device name for your machine. 

\section{Installing PostgreSQL from RPMs}
\index[general]{PostgreSQL!Installing from RPMs}
\index[general]{Installing PostgreSQL from RPMs}
If you are installing PostgreSQL from RPMs, you will need to install
both the PostgreSQL binaries and the client libraries.  The client
libraries are usually found in a devel package, so you must
install:

\footnotesize
\begin{verbatim}
  postgresql
  postgresql-devel
  postgresql-server
  postgresql-libs
\end{verbatim}
\normalsize

These will be similar with most other package managers too.  After
installing from rpms, you will still need to run the scripts that set up
the database and create the tables as described above.


\section{Converting from MySQL to PostgreSQL}
\index[general]{PostgreSQL!Converting from MySQL to }
\index[general]{Converting from MySQL to PostgreSQL }

The conversion procedure presented here was worked out by Norm Dressler
\lt{}ndressler at dinmar dot com\gt{} 

This process was tested using the following software versions: 

\begin{itemize}
\item Linux Mandrake 10/Kernel 2.4.22-10 SMP 
\item Mysql Ver 12.21 Distrib 4.0.15, for mandrake-linux-gnu (i586) 
\item PostgreSQL 7.3.4 
\item Bacula 1.34.5 
   \end{itemize}

WARNING: Always as a precaution, take a complete backup of your databases
before proceeding with this process! 

\begin{enumerate}
\item Shutdown bacula (cd /etc/bacula;./bacula stop)  
\item Run the following command to dump your Mysql database:  

   \footnotesize
\begin{verbatim}
       mysqldump -f -t -n >bacula-backup.dmp
    
\end{verbatim}
\normalsize

\item Make a backup of your /etc/bacula directory (but leave the  original in
   place).  
\item Go to your Bacula source directory and rebuild it to include  PostgreSQL
   support rather then Mysql support. Check the  config.log file for your
   original configure command and replace  enable-mysql with enable-postgresql.  
\item Recompile Bacula with a make and if everything compiles  completely,
   perform a make install.  
\item Shutdown Mysql. 
\item Start PostgreSQL on your system.  
\item Create a bacula user in Postgres with the createuser command.  Depending on
   your Postgres install, you may have to SU to the  user who has privileges to
   create a user.  
\item Verify your pg\_hba.conf file contains sufficient permissions to  allow
   bacula to access the server. Mine has the following since  it's on a secure
   network:  

\footnotesize
\begin{verbatim}
local all all trust
                
host all all 127.0.0.1 255.255.255.255 trust
                
NOTE: you should restart your postgres server if you
      made changes
      
\end{verbatim}
\normalsize

\item Change into the /etc/bacula directory and prepare the database  and
   tables with the following commands:  

\footnotesize
\begin{verbatim}
./create_postgresql_database
                                
./make_postgresql_tables
                                
./grant_postgresql_privileges
       
\end{verbatim}
\normalsize

\item Verify you have access to the database:  

   \footnotesize
\begin{verbatim}
  
psql -Ubacula bacula
      
\end{verbatim}
\normalsize

You should not get any errors.  
\item Load your database from the Mysql database dump with:  

   \footnotesize
\begin{verbatim}
psql -Ubacula bacula <bacula-backup.dmp>
      
\end{verbatim}
\normalsize

\item Resequence your tables with the following commands:  

   \footnotesize
\begin{verbatim}
psql -Ubacula bacula
                
SELECT SETVAL('basefiles_baseid_seq', (SELECT
MAX(baseid) FROM basefiles));
SELECT SETVAL('client_clientid_seq', (SELECT
MAX(clientid) FROM client));
SELECT SETVAL('file_fileid_seq', (SELECT MAX(fileid)
FROM file));
SELECT SETVAL('filename_filenameid_seq', (SELECT
MAX(filenameid) FROM filename));
                
SELECT SETVAL('fileset_filesetid_seq', (SELECT
MAX(filesetid) FROM fileset));
                
SELECT SETVAL('job_jobid_seq', (SELECT MAX(jobid) FROM job));
SELECT SETVAL('jobmedia_jobmediaid_seq', (SELECT
MAX(jobmediaid) FROM jobmedia));
SELECT SETVAL('media_mediaid_seq', (SELECT MAX(mediaid) FROM media));
SELECT SETVAL('path_pathid_seq', (SELECT MAX(pathid) FROM path));
                
SELECT SETVAL('pool_poolid_seq', (SELECT MAX(poolid) FROM pool));
       
\end{verbatim}
\normalsize

\item At this point, start up Bacula, verify your volume library and  perform
   a test backup to make sure everything is working  properly. 
\end{enumerate}

\section{Upgrading PostgreSQL}
\index[general]{Upgrading PostgreSQL }
\index[general]{Upgrading!PostgreSQL }
\index[general]{Upgrading}
If you upgrade PostgreSQL, you must reconfigure, rebuild, and re-install 
Bacula otherwise you are likely to get bizarre failures.  If you
to modify the bacula.spec file to account for the new PostgreSQL version.
You can do so by rebuilding from the source rpm. To do so, you may need
install from rpms and you upgrade PostgreSQL, you must also rebuild Bacula.


\section{Credits}
\index[general]{Credits }
Many thanks to Dan Langille for writing the PostgreSQL driver. This will
surely become the most popular database that Bacula supports.