Configuring PostgreSQL as the Job Database

Configuring PostgreSQL Using OS Repositories / Packages

• Rocky / RHEL

• Ubuntu

Rocky / RHEL

The following steps utilize PostgreSQL version 16 as a reference. Please ensure that you replace the commands with the version of PostgreSQL you are currently using.

1. To install more recent PostgreSQL versions Rocky/RHEL Linux, you'll need to add the official PostgreSQL repository since it's not included in the standard Rocky Linux packages. You can do this by running the following command:

   sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

   NOTE: the example above uses Rocky 9. You will need to replace the version to what you are currently using.

    

2. Rocky Linux might have a default PostgreSQL module enabled. To avoid conflicts, it's recommended to stop and disable it:

   sudo systemctl stop postgresql
   sudo systemctl disable postgresql
   sudo dnf -qy module disable postgresql

    

3. Now, install the PostgreSQL 16 server package:

   sudo dnf install -y postgresql16-server

   You may also want to install the postgresql16-contrib package, which provides additional useful utilities:

   sudo dnf install -y postgresql16-contrib

    

4. Initialize the PostgreSQL database cluster by running:

   sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

    

5. Enable the PostgreSQL 16 service so that it starts up automatically whenever your system boots up:

   sudo systemctl enable postgresql-16

   Start the PostgreSQL 16 service:

   sudo systemctl start postgresql-16

    

6. Check the status of the PostgreSQL service by running:

   sudo systemctl status postgresql-16

   If there are no issues with the service the output should show that the service is active / running:

   ● postgresql-16.service - PostgreSQL 16 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled; preset: disabled)
   Active: active (running) since Sat 2025-04-12 10:43:44 UTC; 18min ago
   Docs: https://www.postgresql.org/docs/16/static/
   Process: 88189 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 88194 (postgres)
   Tasks: 7 (limit: 100412)
   Memory: 40.5M
   CPU: 568ms
   CGroup: /system.slice/postgresql-16.service
   ├─88194 /usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data/
   ├─88195 "postgres: logger "
   ├─88196 "postgres: checkpointer "
   ├─88197 "postgres: background writer "
   ├─88199 "postgres: walwriter "
   ├─88200 "postgres: autovacuum launcher "
   └─88201 "postgres: logical replication launcher "
   
   Apr 12 10:43:44 yhrprockypostgres16 systemd[1]: Starting PostgreSQL 16 database server...
   Apr 12 10:43:44 yhrprockypostgres16 postgres[88194]: 2025-04-12 10:43:44.069 UTC [88194] LOG:  redirecting log output to logging collector process
   Apr 12 10:43:44 yhrprockypostgres16 postgres[88194]: 2025-04-12 10:43:44.069 UTC [88194] HINT:  Future log output will appear in directory "log".
   Apr 12 10:43:44 yhrprockypostgres16 systemd[1]: Started PostgreSQL 16 database server.

    

7. This is an optional step. By default, PostgreSQL restricts connections to the local system. To allow connections from other computers (when the PostgreSQL database is hosted on a different machine than the job server), you must modify the configuration file found at /var/lib/pgsql/16/data/postgresql.conf. Locate the line #listen_addresses = ‘localhost', uncomment it by removing the ‘#' sign, and change 'localhost’ to '*’.

   #------------------------------------------------------------------------------
   # CONNECTIONS AND AUTHENTICATION
   #------------------------------------------------------------------------------
   # - Connection Settings -
   listen_addresses = '*'          # what IP address(es) to listen on;
   # comma-separated list of addresses;

   Note: ‘*’ will allow all available IP interfaces (IPv4 and IPv6), to only listen for IPv4 set 0.0.0.0 while ‘::’ allows listening for all IPv6 addresses.

    

8. Verify that PostgreSQL is listening on port 5432 by running the following command:

   sudo ss -tlupn | grep postgres

   The output:

   tcp   LISTEN 0      200        127.0.0.1:5432       0.0.0.0:*    users:(("postgres",pid=88194,fd=8))
   tcp   LISTEN 0      200            [::1]:5432          [::]:*    users:(("postgres",pid=88194,fd=7))

   confirms that the PostgreSQL database server, referred to as postgres in the default postgresql.service file, is actively listening on port 5432/tcp.

    

9. By default, PostgreSQL uses the postgres administrative database user account, which does not require a password. To enhance security, follow the steps below to set up password authentication for the postgres user and create standard users within PostgreSQL.

   Switch to the postgres user account and connect to the PostgreSQL database:

   sudo su - postgres
   psql

   In the open PostgreSQL prompt, execute the following commands to create the jobserver database and a corresponding user with appropriate privileges (pressing enter after each semicolon):

   create user jobserver with password '<db_password>';
   create database jobserver;
   grant all privileges on database jobserver to jobserver;
   \c jobserver postgres;
   GRANT ALL ON SCHEMA public TO jobserver;

   Replace <db_password> with your desired password for database access. Note that the single quotes around <db_password> are required.

    

10. Exit the psql shell by typing \q and pressing ENTER.

     

11. Log Out from the postgres user account by typing exit and pressing ENTER.

     

12. In order to modify the client authentication settings for PostgreSQL, you need to edit the configuration file found at /var/lib/pgsql/16/data/pg_hba.conf using sudo privileges. Start by navigating to the end of the file, right after the line that reads “# Put your actual configuration here.” The default authentication method, which is set to scram-sha-256 for host connections, does not require any changes.

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    
    # "local" is for Unix domain socket connections only
    local   all             all                                     peer
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            scram-sha-256
    # IPv6 local connections:
    host    all             all             ::1/128                 scram-sha-256
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    local   replication     all                                     peer
    host    replication     all             127.0.0.1/32            scram-sha-256
    host    replication     all             ::1/128                 scram-sha-256

    For more information, please consult the PostgreSQL documentation on the pg_hba.conf file.

     

13. Test the Connection to the PostgreSQL Database:

    To initiate the connection, enter the following command (execute all lines at once):

    psql --username=jobserver \
         --dbname=jobserver \
         --host=localhost \
         --port=5432

    1. Enter the database password <db_password> at the prompt. If the prompt changes to:

    jobserver =>

    2. This indicates that the jobserver user has successfully accessed the jobserver database. Type \q or exit and press Enter.

        

14. Adjust PostgreSQL Defaults in the Configuration File

    Edit the /var/lib/pgsql/16/data/postgresql.conf file and configure the following parameters by removing the # at the beginning of each line where applicable:

    max_connections = 500
    max_locks_per_transaction = 2048
    max_pred_locks_per_transaction = 2048

    The Job Server will employ a maximum of 32 connections to the database. This configuration is designed to ensure that PostgreSQL operates optimally alongside the Job Server’s transaction isolation mode. After implementing these changes, please restart the database by executing the following command:

    sudo systemctl restart postgresql-16
    sudo systemctl status postgresql-16

    Modifications to the postgresql.conf file are essential; otherwise, you may encounter transaction failures accompanied by the message “pq: out of shared memory” in your jobserverd.log file.

     

15. Edit the Job Server configuration file <jobserver_dir>/config/jobserver.yml

    Set database_type to "postgres". Modify the settings in the postgres_config section according to your setup. The defaults are shown in parenthesis.

    • host: select the hostname for the database server. If the jobserverd server and database server run on the same virtual or physical computer, "localhost" is sufficient. (localhost)

    • port: select the port on which Job Server communicates with the PostgreSQL database server. (5432)

    • db_user: select the user name to connect to the job database. (jobserver)

    • db_password: the password for the db_user to connect to the job database.

    • db_name: the name of the job database. (jobserver)

    • sslmode: whether or with what priority a secure SSL TCP/IP connection will be negotiated with the database server. See the PostgreSQL documentation for Database Connection Control Functions. (disable)

Ubuntu

1. We will follow the official PostgreSQL installation instructions to install PostgreSQL by running:

   sudo apt install postgresql

    

2. This is an optional step. By default, PostgreSQL restricts connections to the local system. To allow connections from other computers (when the PostgreSQL database is hosted on a different machine than the job server), you must modify the configuration file found at /etc/postgresql/*/main/postgresql.conf. Locate the line #listen_addresses = ‘localhost', uncomment it by removing the ‘#' sign, and change 'localhost’ to '*’.

   #------------------------------------------------------------------------------
   # CONNECTIONS AND AUTHENTICATION
   #------------------------------------------------------------------------------
   # - Connection Settings -
   listen_addresses = '*'          # what IP address(es) to listen on;
   # comma-separated list of addresses;

   Note: ‘*’ will allow all available IP interfaces (IPv4 and IPv6), to only listen for IPv4 set 0.0.0.0 while ‘::’ allows listening for all IPv6 addresses.

    

3. Verify that PostgreSQL is listening on port 5432 by running the following command:

   sudo ss -tlupn | grep postgres

   the output:

   tcp   LISTEN 0      1000            0.0.0.0:5432       0.0.0.0:*    users:(("postgres",pid=3456,fd=6))
   tcp   LISTEN 0      1000               [::]:5432          [::]:*    users:(("postgres",pid=3456,fd=7))

   confirms that the PostgreSQL database server, referred to as postgres in the default postgresql.service file, is actively listening on port 5432/tcp.

    

4. By default, PostgreSQL uses the postgres administrative database user account, which does not require a password. To enhance security, follow the steps below to set up password authentication for the postgres user and create standard users within PostgreSQL.

   Switch to the postgres user account and connect to the PostgreSQL database:

   sudo su - postgres
   psql

   In the open PostgreSQL prompt, execute the following commands to create the jobserver database and a corresponding user with appropriate privileges (pressing enter after each semicolon):

   create user jobserver with password '<db_password>';
   create database jobserver;
   grant all privileges on database jobserver to jobserver;
   \c jobserver postgres;
   GRANT ALL ON SCHEMA public TO jobserver;

   Replace <db_password> with your desired password for database access. Note that the single quotes around <db_password> are required.

    

5. Exit the psql shell by typing \q and pressing ENTER.

    

6. Log Out from the postgres user account by typing exit and pressing ENTER.

    

7. To adjust the PostgreSQL client authentication settings, navigate to the file located at /etc/postgresql/*/main/pg_hba.conf. At the bottom of this file, ensure that the host authentication method is set to scram-sha-256, as it is more secure compared to md5, peer, and other methods.

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
   # "local" is for Unix domain socket connections only
   local   all             all                                     peer
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            scram-sha-256
   # IPv6 local connections:
   host    all             all             ::1/128                 scram-sha-256
   # Allow replication connections from localhost, by a user with the
   # replication privilege.
   local   replication     all                                     peer
   host    replication     all             127.0.0.1/32            scram-sha-256
   host    replication     all             ::1/128                 scram-sha-256

   For more information, please consult the PostgreSQL documentation on the pg_hba.conf file.

    

8. Restart the PostgreSQL Service by running:

   sudo systemctl restart postgresql
   sudo systemctl status postgresql*

   If there are no issues with the service the output should show that the service is active / running:

   ● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
   Active: active (exited) since Mon 2025-03-24 09:07:52 UTC; 3min 56s ago
   Process: 2821 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 2821 (code=exited, status=0/SUCCESS)
   CPU: 1ms
   Mar 24 09:07:52 yp20250321ubuntu24jobserver systemd[1]: Starting postgresql.service - PostgreSQL RDBMS...
   Mar 24 09:07:52 yp20250321ubuntu24jobserver systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.
   ● postgresql@16-main.service - PostgreSQL Cluster 16-main
   Loaded: loaded (/usr/lib/systemd/system/postgresql@.service; enabled-runtime; preset: enabled)
   Active: active (running) since Mon 2025-03-24 09:07:52 UTC; 3min 56s ago
   Process: 2798 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 16-main start (code=exited, status=0/SUCCESS)
   Main PID: 2803 (postgres)
   Tasks: 6 (limit: 38493)
   Memory: 20.0M (peak: 27.4M)
   CPU: 312ms
   CGroup: /system.slice/postgresql.service
   ├─87500 /usr/bin/postmaster -D /var/lib/pgsql/data
   ├─87501 "postgres: logger "
   ├─87503 "postgres: checkpointer "
   ├─87504 "postgres: background writer "
   ├─87505 "postgres: walwriter "
   ├─87506 "postgres: autovacuum launcher "
   ├─87507 "postgres: stats collector "
   └─87508 "postgres: logical replication launcher "

    

9. Test the Connection to the PostgreSQL Database:

   To initiate the connection, enter the following command (execute all lines at once):

   psql --username=jobserver \
        --dbname=jobserver \
        --host=localhost \
        --port=5432

   1. Enter the database password <db_password> at the prompt. If the prompt changes to:

   jobserver =>

   2. This indicates that the jobserver user has successfully accessed the jobserver database. Type \q or exit and press Enter.

       

10. Adjust PostgreSQL Defaults in the Configuration File

    Edit the /etc/postgresql/*/main/postgresql.conf file and configure the following parameters by removing the # at the beginning of each line where applicable:

    max_connections = 500
    max_locks_per_transaction = 2048
    max_pred_locks_per_transaction = 2048

    The Job Server will employ a maximum of 32 connections to the database. This configuration is designed to ensure that PostgreSQL operates optimally alongside the Job Server’s transaction isolation mode. After implementing these changes, please restart the database by executing the following command:

    sudo systemctl restart postgresql
    sudo systemctl status postgresql*

    Modifications to the postgresql.conf file are essential; otherwise, you may encounter transaction failures accompanied by the message “pq: out of shared memory” in your jobserverd.log file.

     

11. Edit the Job Server configuration file <jobserver_dir>/config/jobserver.yml

    Set database_type to "postgres". Modify the settings in the postgres_config section according to your setup. The defaults are shown in parenthesis.

    • host: select the hostname for the database server. If the jobserverd server and database server run on the same virtual or physical computer, "localhost" is sufficient. (localhost)

    • port: select the port on which Job Server communicates with the PostgreSQL database server. (5432)

    • db_user: select the user name to connect to the job database. (jobserver)

    • db_password: the password for the db_user to connect to the job database.

    • db_name: the name of the job database. (jobserver)

    • sslmode: whether or with what priority a secure SSL TCP/IP connection will be negotiated with the database server. See the PostgreSQL documentation for Database Connection Control Functions. (disable)