| 1 |
\chapter{Installing and Configuring \postgresql{}} |
| 2 |
\label{blb:PostgreSqlChapter} |
| 3 |
\index[general]{\postgresql{}!Installing and Configuring } |
| 4 |
\index[general]{Installing and Configuring \postgresql{} } |
| 5 |
\index[general]{Upgrading} |
| 6 |
|
| 7 |
If you are considering using \postgresql{}, you should be aware |
| 8 |
of their philosophy of upgrades, which could be |
| 9 |
destabilizing for a production shop. Basically at every major version |
| 10 |
upgrade, you are required to dump your database in an ASCII format, |
| 11 |
do the upgrade, and then reload your database (or databases). This is |
| 12 |
because they frequently update the ``data format'' from version to |
| 13 |
version, and they supply no tools to automatically do the conversion. |
| 14 |
If you forget to do the ASCII dump, your database may become totally |
| 15 |
useless because none of the new tools can access it due to the format |
| 16 |
change, and the \postgresql{} server will not be able to start. |
| 17 |
|
| 18 |
If you are building \postgresql{} from source, please be sure to add |
| 19 |
the \textbf{{-}{-}enable-thread-safety} option when doing the \btool{./configure} |
| 20 |
for \postgresql{}. |
| 21 |
|
| 22 |
\section{Installing \postgresql{}} |
| 23 |
\index[general]{\postgresql{}!Installing } |
| 24 |
|
| 25 |
If you use the \btool{./configure {-}{-}with-postgresql=PostgreSQL-Directory} |
| 26 |
statement for configuring \textbf{\bacula{}}, you will need \postgresql{} version 7.4 |
| 27 |
or later installed. NOTE! \postgresql{} versions earlier than 7.4 do not work |
| 28 |
with \bacula{}. If \postgresql{} is installed in the standard system location, you |
| 29 |
need only enter \textbf{{-}{-}with-postgresql} since the configure program will |
| 30 |
search all the standard locations. If you install \postgresql{} in your home |
| 31 |
directory or some other non-standard directory, you will need to provide the |
| 32 |
full path with the \textbf{{-}{-}with-postgresql} option. |
| 33 |
|
| 34 |
Installing and configuring \postgresql{} is not difficult but can be confusing |
| 35 |
the first time. If you prefer, you may want to use a package provided by your |
| 36 |
chosen operating system. Binary packages are available on most \postgresql{} |
| 37 |
mirrors. |
| 38 |
|
| 39 |
If you prefer to install from source, we recommend following the instructions |
| 40 |
found in the \bfootref{http://www.postgresql.org/docs/}{\postgresql{} documentation}. |
| 41 |
|
| 42 |
If you are using FreeBSD, |
| 43 |
\bfootref{http://www.freebsddiary.org/postgresql.php}{this FreeBSD Diary article} |
| 44 |
will be useful. Even if you are not using FreeBSD, the article will contain |
| 45 |
useful configuration and setup information. |
| 46 |
|
| 47 |
If you configure the Batch Insert code in \bacula{} (attribute inserts are |
| 48 |
10 times faster), you \textbf{must} be using a \postgresql{} that was built with |
| 49 |
the \textbf{{-}{-}enable-thread-safety} option, otherwise you will get |
| 50 |
data corruption. Most major Linux distros have thread safety turned on, but |
| 51 |
it is better to check. One way is to see if the \postgresql{} library that |
| 52 |
\bacula{} will be linked against references pthreads. This can be done |
| 53 |
with a command such as: |
| 54 |
|
| 55 |
\begin{bVerbatim} |
| 56 |
nm /usr/lib/libpq.a | grep pthread_mutex_lock |
| 57 |
\end{bVerbatim} |
| 58 |
|
| 59 |
The above command should print a line that looks like: |
| 60 |
|
| 61 |
\begin{bVerbatim} |
| 62 |
U pthread_mutex_lock |
| 63 |
\end{bVerbatim} |
| 64 |
|
| 65 |
if does, then everything is OK. If it prints nothing, do not enable batch |
| 66 |
inserts when building \bacula{}. |
| 67 |
|
| 68 |
After installing \postgresql{}, you should return to completing the installation |
| 69 |
of \textbf{\bacula{}}. Later, after \bacula{} is installed, come back to this chapter |
| 70 |
to complete the installation. Please note, the installation files used in the |
| 71 |
second phase of the \postgresql{} installation are created during the \bacula{} |
| 72 |
Installation. You must still come back to complete the second phase of the |
| 73 |
\postgresql{} installation even if you installed binaries (e.g. rpm, deb, |
| 74 |
\ldots{}). |
| 75 |
|
| 76 |
|
| 77 |
\label{blb:PostgreSQL_configure} |
| 78 |
\section{Configuring \postgresql{}} |
| 79 |
\index[general]{\postgresql{}!Configuring \postgresql{} -- } |
| 80 |
|
| 81 |
At this point, you should have built and installed \postgresql{}, or already have |
| 82 |
a running \postgresql{}, and you should have configured, built and installed \textbf{\bacula{}}. |
| 83 |
If not, please complete these items before proceeding. |
| 84 |
|
| 85 |
Please note that the \btool{./configure} used to build \textbf{\bacula{}} will need to |
| 86 |
include \textbf{{-}{-}with-postgresql=PostgreSQL-directory}, where |
| 87 |
\textbf{PostgreSQL-directory} is the directory name that you specified on the |
| 88 |
\btool{./configure} command for configuring \postgresql{} (if you didn't specify a |
| 89 |
directory or \postgresql{} is installed in a default location, you do not need to |
| 90 |
specify the directory). This is needed so that \bacula{} can find the necessary |
| 91 |
include headers and library files for interfacing to \postgresql{}. |
| 92 |
|
| 93 |
An important thing to note here is that \textbf{\bacula{}} makes two connections |
| 94 |
to the \postgresql{} server for each backup job that is currently running. If |
| 95 |
you are intending to run a large number of concurrent jobs, check the value |
| 96 |
of \textbf{max\_connections} in your \postgresql{} configuration file to ensure |
| 97 |
that it is larger than the setting \textbf{Maximum Concurrent Jobs} |
| 98 |
in your director configuration. |
| 99 |
\textbf{Setting this too low will result in some backup jobs failing to run correctly!} |
| 100 |
|
| 101 |
\textbf{\bacula{}} will install scripts for manipulating the database (create, |
| 102 |
delete, make tables etc) into the main installation directory. These files |
| 103 |
will be of the form \bfilename{*\_bacula\_*} (e.g. \bfilename{create\_bacula\_database}). These files |
| 104 |
are also available in the \bbracket{bacula-src}/src/cats directory after |
| 105 |
running \btool{./configure}. If you inspect \bfilename{create\_bacula\_database}, you will see |
| 106 |
that it calls \bfilename{create\_postgresql\_database}. The \bfilename{*\_bacula\_*} files are |
| 107 |
provided for convenience. It doesn't matter what database you have chosen; |
| 108 |
\bfilename{create\_bacula\_database} will always create your database. |
| 109 |
|
| 110 |
Now you will create the \bacula{} \postgresql{} database and the tables that \bacula{} |
| 111 |
uses. These instructions assume that you already have \postgresql{} running. You |
| 112 |
will need to perform these steps as a user that is able to create new |
| 113 |
databases. This can be the \postgresql{} user (on most systems, this is the \texttt{pgsql} |
| 114 |
user). |
| 115 |
|
| 116 |
\begin{benumerate} |
| 117 |
\item cd \bbracket{install-directory} |
| 118 |
|
| 119 |
This directory contains the \bacula{} catalog interface routines. |
| 120 |
|
| 121 |
\item Create the database owner (\textbf{bacula}) |
| 122 |
On many systems, the \postgresql{} master |
| 123 |
owner is \texttt{pgsql} and on others such as Red Hat and Fedora it |
| 124 |
is \texttt{postgres}. You can find out which it is by examining your \bfilename{/etc/passwd} |
| 125 |
file. To create a new user under either your name or with say the name |
| 126 |
\texttt{bacula}, you can do the following: |
| 127 |
|
| 128 |
\begin{bVerbatim} |
| 129 |
su |
| 130 |
(enter root password) |
| 131 |
su pgsql (for rpm systems or postgres for Debian systems) |
| 132 |
createuser bacula |
| 133 |
Shall the new user be allowed to create databases? (y/n) y |
| 134 |
Shall the new user be allowed to create more new users? (y/n) (choose |
| 135 |
what you want) |
| 136 |
exit |
| 137 |
\end{bVerbatim} |
| 138 |
|
| 139 |
If you have a newer \postgresql{}, you may need to use the \textbf{-s} option on the \btool{createuser} command. Example: |
| 140 |
|
| 141 |
\begin{bVerbatim} |
| 142 |
createuser -s bacula |
| 143 |
\end{bVerbatim} |
| 144 |
|
| 145 |
Normally the \texttt{bacula} user must be able to create new databases, |
| 146 |
if you use the script in the next item, |
| 147 |
or you will have to create one for it, but it does not need to |
| 148 |
create new users. |
| 149 |
|
| 150 |
\item \btool{./create\_bacula\_database} |
| 151 |
|
| 152 |
This script creates the \postgresql{} \textbf{bacula} database. |
| 153 |
Before running this command, you should carefully think about |
| 154 |
what encoding sequence you want for the text fields (paths, files, \ldots{}). |
| 155 |
We strongly recommend that you use the default value of SQL\_ASCII |
| 156 |
that is in the \btool{create\_bacula\_database} script. Please be warned |
| 157 |
that if you change this value, your backups may fail. After running |
| 158 |
the script, you can check with the command: |
| 159 |
|
| 160 |
\begin{bVerbatim} |
| 161 |
psql -l |
| 162 |
\end{bVerbatim} |
| 163 |
|
| 164 |
and the column marked \textbf{Encoding} should be \textbf{SQL\_ASCII} for |
| 165 |
all your \bacula{} databases (normally \textbf{bacula}). |
| 166 |
|
| 167 |
\item \btool{./make\_bacula\_tables} |
| 168 |
|
| 169 |
This script creates the \postgresql{} tables used by \textbf{\bacula{}}. |
| 170 |
\item \btool{./grant\_bacula\_privileges} |
| 171 |
|
| 172 |
This script creates the database user \textbf{bacula} with restricted access |
| 173 |
rights. You may want to modify it to suit your situation. Please note that |
| 174 |
this database is not password protected. |
| 175 |
\end{benumerate} |
| 176 |
|
| 177 |
Each of the three scripts (\btool{create\_bacula\_database}, \btool{make\_bacula\_tables}, and |
| 178 |
\btool{grant\_bacula\_privileges}) allows the addition of a command line argument. |
| 179 |
This can be useful for specifying the user name. For example, you might need |
| 180 |
to add \textbf{-h hostname} to the command line to specify a remote database |
| 181 |
server. |
| 182 |
|
| 183 |
To take a closer look at the access privileges that you have setup with the |
| 184 |
above, you can do: |
| 185 |
|
| 186 |
\begin{bVerbatim} |
| 187 |
PostgreSQL-directory/bin/psql --command \dp bacula |
| 188 |
\end{bVerbatim} |
| 189 |
|
| 190 |
For people who are not Postgresql experts, it is sometimes difficult |
| 191 |
to get the authorization working correctly with Bacula. |
| 192 |
One simple, but not recommended way, to authorize Bacula is |
| 193 |
to modify your {\bf pg\_hba.conf} file (in /var/lib/pgsql/data |
| 194 |
or in /var/lib/postgresql/8.x or in /etc/postgres/8.x/main on |
| 195 |
other distributions) from: |
| 196 |
|
| 197 |
\begin{bVerbatim} |
| 198 |
local all all ident sameuser |
| 199 |
\end{bVerbatim} |
| 200 |
to |
| 201 |
\begin{bVerbatim} |
| 202 |
local all all trust |
| 203 |
\end{bVerbatim} |
| 204 |
|
| 205 |
This is a quick way to solve the problem, but it is not always a good thing |
| 206 |
to do from a security standpoint. However, it allows one to run |
| 207 |
my regression scripts without having a password. |
| 208 |
|
| 209 |
A more secure way to perform database authentication is with md5 |
| 210 |
password hashes. Begin by editing the {\bf pg\_hba.conf} file, and |
| 211 |
above the existing {\bf local} and {\bf host} lines, add the line: |
| 212 |
|
| 213 |
\begin{bVerbatim} |
| 214 |
local bacula bacula md5 |
| 215 |
\end{bVerbatim} |
| 216 |
|
| 217 |
then restart the \postgresql{} database server (frequently, this can be done |
| 218 |
using \btool{/etc/init.d/postgresql restart} or \btool{service postgresql restart}) to |
| 219 |
put this new authentication rule into effect. |
| 220 |
|
| 221 |
\smallskip |
| 222 |
More detailed information on the {\bf pg\_hba.conf} file can be found at |
| 223 |
\bulink{PostgreSQL pg\_hba.conf Documentation} |
| 224 |
{https://www.postgresql.org/docs/10/auth-pg-hba-conf.html} |
| 225 |
|
| 226 |
\smallskip |
| 227 |
|
| 228 |
Next, become the Postgres administrator, postgres, either by logging |
| 229 |
on as the postgres user, or by using su to become root and then using |
| 230 |
{\bf su - postgres} or {\bf su - pgsql} to become postgres. |
| 231 |
Add a password to the {\bf bacula} database for the {\bf bacula} user using: |
| 232 |
|
| 233 |
\begin{bVerbatim} |
| 234 |
$ psql bacula |
| 235 |
bacula=# alter user bacula with password 'secret'; |
| 236 |
ALTER USER |
| 237 |
bacula=# \q |
| 238 |
\end{bVerbatim} |
| 239 |
|
| 240 |
You'll have to add this password to two locations in the |
| 241 |
\bfilename{bacula-dir.conf} file: once to the Catalog resource and once to the |
| 242 |
RunBeforeJob entry in the BackupCatalog Job resource. With the |
| 243 |
password in place, these two lines should look something like: |
| 244 |
|
| 245 |
\begin{bVerbatim} |
| 246 |
dbname = bacula; user = bacula; password = "secret" |
| 247 |
\end{bVerbatim} |
| 248 |
\ldots{} and \ldots{} |
| 249 |
\begin{bVerbatim} |
| 250 |
# WARNING!!! Passing the password via the command line is insecure. |
| 251 |
# see comments in make_catalog_backup.pl for details. |
| 252 |
RunBeforeJob = "/etc/make_catalog_backup.pl MyCatalog" |
| 253 |
\end{bVerbatim} |
| 254 |
|
| 255 |
Naturally, you should choose your own significantly more random |
| 256 |
password, and ensure that the \bfilename{bacula-dir.conf} file containing this |
| 257 |
password is readable only by the \texttt{root} user. |
| 258 |
|
| 259 |
Even with the files containing the database password properly |
| 260 |
restricted, there is still a security problem with this approach: on |
| 261 |
some platforms, the environment variable that is used to supply the |
| 262 |
password to \postgresql{} is available to all users of the |
| 263 |
local system. To eliminate this problem, the \postgresql{} team have |
| 264 |
deprecated the use of the environment variable password-passing |
| 265 |
mechanism and recommend the use of a \bfilename{.pgpass} file instead. To use |
| 266 |
this mechanism, create a file named \bfilename{.pgpass} containing the single |
| 267 |
line: |
| 268 |
|
| 269 |
\begin{bVerbatim} |
| 270 |
localhost:5432:bacula:bacula:secret |
| 271 |
\end{bVerbatim} |
| 272 |
|
| 273 |
This file should be copied into the home directory of all accounts |
| 274 |
that will need to gain access to the database: typically, \texttt{root}, |
| 275 |
\texttt{bacula}, and any users who will make use of any of the console |
| 276 |
programs. The files must then have the owner and group set to match |
| 277 |
the user (so \texttt{root}\string:\texttt{root} for the copy in \bdirectoryname{~root}, and so on), and the mode |
| 278 |
set to 600, limiting access to the owner of the file. |
| 279 |
|
| 280 |
\section{Re-initializing the Catalog Database} |
| 281 |
\index[general]{Database!Re-initializing the Catalog } |
| 282 |
\index[general]{Re-initializing the Catalog Database } |
| 283 |
|
| 284 |
After you have done some initial testing with \textbf{\bacula{}}, you will probably |
| 285 |
want to re-initialize the catalog database and throw away all the test Jobs |
| 286 |
that you ran. To do so, you can do the following: |
| 287 |
|
| 288 |
\begin{bVerbatim} |
| 289 |
cd <install-directory> |
| 290 |
./drop_bacula_tables |
| 291 |
./make_bacula_tables |
| 292 |
./grant_bacula_privileges |
| 293 |
\end{bVerbatim} |
| 294 |
|
| 295 |
Please note that all information in the database will be lost and you will be |
| 296 |
starting from scratch. If you have written on any Volumes, you must write an |
| 297 |
end of file mark on the volume so that \bacula{} can reuse it. Do so with: |
| 298 |
|
| 299 |
\begin{bVerbatim} |
| 300 |
(stop Bacula or unmount the drive) |
| 301 |
mt -f /dev/nst0 rewind |
| 302 |
mt -f /dev/nst0 weof |
| 303 |
\end{bVerbatim} |
| 304 |
|
| 305 |
Where you should replace \bfilename{/dev/nst0} with the appropriate tape drive |
| 306 |
device name for your machine. |
| 307 |
|
| 308 |
\section{Installing \postgresql{} from RPM} |
| 309 |
\index[general]{\postgresql{}!Installing from \acsp{RPM}} |
| 310 |
\index[general]{Installing \postgresql{} from \acsp{RPM}} |
| 311 |
If you are installing \postgresql{} from \acsp{RPM}, you will need to install |
| 312 |
both the \postgresql{} binaries and the client libraries. The client |
| 313 |
libraries are usually found in a \textbf{devel} or \textbf{dev} package, so you must |
| 314 |
install the following for rpms: |
| 315 |
|
| 316 |
\begin{bVerbatim} |
| 317 |
postgresql |
| 318 |
postgresql-devel |
| 319 |
postgresql-server |
| 320 |
postgresql-libs |
| 321 |
\end{bVerbatim} |
| 322 |
|
| 323 |
|
| 324 |
and the following for debs: |
| 325 |
|
| 326 |
\begin{bVerbatim} |
| 327 |
postgresql |
| 328 |
postgresql-common |
| 329 |
postgresql-client |
| 330 |
postgresql-client-common |
| 331 |
libpq5 |
| 332 |
libpq-dev |
| 333 |
\end{bVerbatim} |
| 334 |
|
| 335 |
|
| 336 |
These will be similar with most other package managers too. After |
| 337 |
installing from rpms, you will still need to run the scripts that set up |
| 338 |
the database and create the tables as described above. |
| 339 |
|
| 340 |
\section{Converting from MySQL to PostgreSQL} |
| 341 |
\index[general]{PostgreSQL!Converting from MySQL to } |
| 342 |
\index[general]{Converting from MySQL to PostgreSQL } |
| 343 |
|
| 344 |
There are various scripts available that allow you to convert from MySQL |
| 345 |
to PostgreSQL database format. The first step is to backup your existing |
| 346 |
MySQL database, then shutdown Bacula. |
| 347 |
|
| 348 |
\smallskip |
| 349 |
One such conversion program from MySQL to PostgreSQL can be found at: |
| 350 |
\bulink{MySQL to PostgreSQL Conversion Scripts} |
| 351 |
{https://github.com/wanderleihuttel/bacula-utils/tree/master/convert_mysql_to_postgresql} |
| 352 |
|
| 353 |
\section{Upgrading \postgresql{}} |
| 354 |
\index[general]{Upgrading \postgresql{}} |
| 355 |
\index[general]{Upgrading!\postgresql{}} |
| 356 |
\index[general]{Upgrading} |
| 357 |
\baculaenterprise{} is built with the \postgresql{} that is appropriate for each Operating System platform. Each time that \postgresql{} is updated, the \bacula{} binaries must be updated. |
| 358 |
If you upgrade PostgreSQL, on older PostgreSQLs, you needed to |
| 359 |
rebuild Bacula for the specific version of PostgreSQL. However, |
| 360 |
on more recent versions, this is no longer necessary. |
| 361 |
If you experience problems after upgrading PostgreSQL, you might |
| 362 |
try rebuilding Bacula. |
| 363 |
|
| 364 |
\section{Tuning \postgresql{}} |
| 365 |
\index[general]{Tuning} |
| 366 |
|
| 367 |
If you despool attributes for many jobs at the same time, you can tune the |
| 368 |
sequence object for the \texttt{FileId} field. |
| 369 |
\begin{bVerbatim} |
| 370 |
psql -Ubacula bacula |
| 371 |
|
| 372 |
ALTER SEQUENCE file_fileid_seq CACHE 1000; |
| 373 |
\end{bVerbatim} |
| 374 |
|
| 375 |
\section{Credits} |
| 376 |
\index[general]{Credits } |
| 377 |
Many thanks to Dan Langille for writing the \postgresql{} driver. This will |
| 378 |
surely become the most popular database that \bacula{} supports. |