Roundup Tracker

Roundup integration with Postgresql

Contents

  1. Roundup integration with Postgresql
    1. Introduction
    2. Why can't I just use the system documentation?
    3. Overview of example environment
    4. Steps required
      1. Install Postgresql
      2. psycopg
      3. Create postgresql login role for roundup
      4. Install a new tracker instance
      5. Configure tracker
      6. Initialise and away you go!
      7. OPTIONAL: move the roundup schema to separate tablespace

Introduction

Whilst the existing roundup documentation somewhat discusses back end integration with other RDBMS it was the experience of this author that alot was left as an exercise to the reader. As such, and at the risk of this document becoming obsolete as versions change, it was decided to enumerate the steps a little better to describe an example of a working environment.

This scenario may have been brought on by using Solaris and compiled from source installations instead of pac- YMMV. Also note that this document discusses creating a new tracker backed onto postgresql. Migration from an anydb source over to postgresql was not tested. However, this should be possible and straightforward using the following guide - https://www.roundup-tracker.org/docs/admin_guide.html#migrating-backends.

Why can't I just use the system documentation?

It is believed that the system documentation is missing a few helpful pointers -

This document will attempt to bridge the gap.

Overview of example environment

It is important to note that no assumptions are made about your roundup/python installation. In particular, the environment below started with a vanilla install of both roundup and python. The specifics of the environment before modification were as follows -

In addition, the use of a Solaris system installed version of Postgresql implies that the default tablespace for newly created databases is under /var. It is undesirable to have a potentially large roundup database start growing under a system directory like this. As such, this document will also discuss relocating the database to a new tablespace.

Steps required

Install Postgresql

Postgresql's documentation is extremely complete. Head to https://www.postgresql.org/docs/ and start going through whatever is required for your environment. In the example environment a new tablespace was created on a separate unix mountpoint to house roundup trackers. https://www.postgresql.org/docs/8.4/static/manage-ag-tablespaces.html

psycopg

Psycopg2 is a DB API 2.0-compliant PostgreSQL driver. It is designed for multi-threaded applications and manages its own connection pool. As such it is an obvious choice for any python-postgresql integration. It is not installed by default in your Python distribution and will thus need to be built.

psycopg can be found on https://www.psycopg.org. Download the latest and greatest. Installation is fairly straightforward under *NIX. The following changes were required in the example environment -

You should now have psycopg installed under your python/lib/python-2.x/site-packages/psycopg2

Create postgresql login role for roundup

Log in (psql) to postgresql with superuser rights -

This is assumed knowledge for the user. If you need help - https://www.postgresql.org/docs/8.4/static/sql-createuser.html. Note that you don't need a password if you are able to make secure trust connections work. CREATEDB and LOGIN are required, as roundup-admin initialise will attempt to drop any previous db and recreate.

Install a new tracker instance

This section - https://www.roundup-tracker.org/docs/installation.html#configuring-your-first-tracker - discusses the basics of initial install of a new tracker. Note that you should skip "Step 4 - Initialisation". We will be initialising later to populate postgresql. In short:

Configure tracker

Still assuming /<example>/<tracker> -

Initialise and away you go!

Assuming all the config above is correct, you should now be able to run roundup-admin initialise -i /<example>/<tracker>. This will create a postgres database in the default schema and populate it with the initial data of your tracker. You should then be able to test in the normal manner. Most likely cause of any problems will be access to the postgresql database and the permissions of the roundup role to create the database and underlying schema.

OPTIONAL: move the roundup schema to separate tablespace

Roundup 2.4.0 (July 2024) supports using a Postgres schema within a roundup database. So your admin can create a database and roundup-admin initialize can be run without have create database privileges. See: case 2 for creating a role/user in the postgresql documentation file.

In many environments, the default schema will sit on a large disk in a non-system mountpoint and this section will be irrelevant. In other cases where the OS installs the postgresql instance you may find that the default tablespace is actually sitting somewhere under your root mount and thus the roundup database could blow up and consume a lot of space in a system mount. Unfortunately, roundup-admin initialise must be allowed to create a database or it will fall over in a heap. As such, we let roundup-admin do it's thing, then shut down the tracker and move the database. To do this -

You should be all set to restart your tracker which is now housed in the new tablespace - enjoy!

Caveats and contacting the author of this document

This document is provided as-is, with no guarantees of accuracy and no association with the authors of roundup. Please use common sense and take plenty of backups before going crazy with migration to postgres. If you wish to re-use/distribute/modify/munge this document feel free. No guarantees of having all the answers, but if you have any specific questions feel free to email to roundup_postgres at lxrb dot com and I will do my best to help.