Debian Codegraph @

manuals/en/main/postgresql.tex

raw
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.