PostgreSQL Configuration

On Debian / Ubuntu, configuration files are located in /etc/postgresql/ (check the local system documentation for information relevant to your installed version of OS and database), on Red Hat, they are by default located in /var/lib/pgsql/data.

Server Configuration

Tuning the PostgreSQL configuration is very important to have decent performance with Bacula. The default configuration values are small and not suitable for production. In several large Bacula environments, some settings were proven which result in a good performance of the Catalog database with Bacula’s specific workload.

As usual, fine-tuning here can not be generalized. However, the way PostgreSQL works is to rely on the operating system to keep data files quickly available (i. e. in cache), instead of loading all data into allocated memory. This allows the operating system to balance caching of data, buffering “dirty” data before committing to disk, and satisfying any other memory requests. Trying to balance this statically in the database configuration is most likely less flexible and will, in general, decrease the overall system performance. Thus, the most important aspect is to leave enough memory available to the OS, but give the database enough memory to do more complex work. Restricting the shared_buffers size is one of the essential elements of this configuration. For large Bacula instances, we can assume that the complete Catalog can never be held in memory, so it’s reasonable to leave more flexibility. For Catalog sizes that can be expected to completely fit into memory, leaving enough headroom for the OS and other processes, increasing the shared_buffers may be reasonable.

For a server with more than 4 GB of memory, please use the following configuration in postgresql.conf

shared_buffers = 1GB             # 128MB by default is low
                                 # 25% of RAM is generaly advised

work_mem = 64MB                  # 4MB by default is low

maintenance_work_mem = 256MB     # 1GB for systems with more than 16GB of RAM
                                 # 2GB for systems with more than 32GB of RAM

effective_cache_size = 3GB       # 75% of available RAM

min_wal_size = 1GB
max_wal_size = 4GB

checkpoint_timeout = 20min
checkpoint_completion_target = 0.9
checkpoint_warning = 90s

log_checkpoints = on
log_temp_files = 80000

autovacuum_vacuum_cost_delay = 5ms # should be 5ms at most on raid array
cursor_tuple_fraction = 1.0        # used for cursors to retrieve all rows

random_page_cost = 1.1             # if SSDs are used
                                   # the default 4.0 is good for HDDs

It is important also to adjust the max_connections parameter when running several simultaneous concurrent jobs. The default value for this parameter is max_connections=100. This value should be 2 times larger than Director’s Maximum Concurrent Jobs plus connections for each Console. Please note if you have this value already adjusted due to other applications using the same PostgreSQL environment as Bacula, you should increase the current value by the number of Maximum Concurrent Jobs you have configured for your Director.

Considering you have already max_connections=1000 and you have installed a Bacula Director with “Maximum Concurrent Jobs=300” and “Maximum Console Connections=20”, you should adjust max_connections to a value higher than 1620. For example:

max_connections = 1620

Security and Access Configuration

The pg_hba.conf file should allow your bacula user to login tn your database. (Adapt the host line for your remote access).

local   bacula bacula                             md5
host    bacula bacula       192.168.0.10/32       md5

To apply this change, you must reload the PostgreSQL postmaster process. Since PostgreSQL version 10, a more secure authentication mechanism is available, SCRAM-SHA-256. However, if md5 is specified as a method in pg_hba.conf but the user’s password on the server is encrypted for SCRAM, then SCRAM-based authentication will automatically be chosen instead. To control password-encryption set password_encryption configuration parameter before setting the password for the user. To set the bacula user password, use \password command in PSQL.

$ psql
postgres=# \password bacula
Enter new password:
Enter it again:

The next step will be to move to the PostgreSQL Catalog Administration.