Stuff I'm Up To

Technical Ramblings

JIRA Software and Confluence — September 14, 2018

JIRA Software and Confluence

Installing Atlassian Jira Software onto an in-house or self-hosted server is as simple as following the Jira installation guide. The only thing missing is the setup of the database.

Jira suggest that whilst other databases are available, MySQL, MSSQL etc. their preferred DB is postgresql. Primarily because it’s common in their user space and support environment, meaning that their support and documentation is likely to be more readily available for postgresql instances than other DB’s.

Let’s follow the advice and install postgresql.

$ sudo apt-get install postgresql

At the time of writing this installs postgresql version 9.6 on Debian Stretch.

In order to create the environment that we can manage there are a couple of postgresql config changes that we make to ensure you can access the DB from another system – for managing with pgadmin 4.

Enable access to postgresql from specific network/IP addresses by editing pg_hba.conf under /etc/postgresql/9.6/main.

$ sudo vi /etc/postgresql/9.6/main/pg_hba.conf

Find the line:

host    all    all    127.0.0.1/32    md5

Add a line below to match your required IP addresses/subnets eg.

host    all    all    192.168.0.0/24  md5

This allows any machine with a 192.168.0.X address to access the DB.

Now we need to listen or bind to an IP address that is available on the network. By default postgresql only listens on 127.0.0.1 port 5432, meaning it will only accept connections to the local machine from the local machine.

$ sudo vi /etc/postgresql/9.6/main/postgressql.conf

Find the line beginning:

#listen_addresses = 'localhost'

Add a new line below it:

listen_addresses = '*'

Restart the postgresql service:

$ sudo systemctl restart postgresql.service

Databases and User

Create a database and a user for Jira/Confluence to use

$ sudo -u postgres createdb jira
$ sudo -u postgres createdb confluence
$ sudo -u postgres createuser jiradb

Set the users password and grant them access to the DB’s.

$ sudo -u postgres psql 
psql (9.6.10)
Type "help" for help.

postgres=# alter user jiradb with encrypted password 'mysupersecretpassword';

postgres=# grant all privileges on database jira to jiradb;

postgres=# grant all privileges on database confluence to jiradb;

When you install Jira and confluence you can then use the database settings you’ve just created.

Database Type: PostgreSQL
Hostname:      localhost
Port:          5432
Database:      jira
Username:      jiradb
Password:      mysupersecretpassword
Schema:        public

Selection_076

 

Advertisements
PostGIS Import from PHP Session Fails — March 12, 2018

PostGIS Import from PHP Session Fails

When importing a CSV file using Laravel I found that I’d get some strange error messages relating to a called function not matching the parameters that I was sending.

local.ERROR: SQLSTATE[42883]: Undefined function: 7 ERROR: function st_makepoint(numeric, numeric) does not exist
LINE 1: SELECT ST_SetSRID(ST_MakePoint(NEW.x_coordinate, NEW.y_coord...
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I narrowed it down to the ST_SetSRID() and ST_MakePoint() functions and was 100% sure that they existed in the database, and with the right parameter types. I tried casting the values to double precision and still and the same errors.

The solution turned out to be that the function existed in the public schema NOT the schema I was using!

Continue reading

PostgreSQL Copy Data Between Servers — February 20, 2018

PostgreSQL Copy Data Between Servers

Our GIS team use a PostgreSQL server with PostGIS. They recently asked if there was any way we could display some data in a simple web form for our users. So a bit of development work was required.

I didn’t want to code against their live system so thought I’d install a local version of PostGIS and copy the data from their database.

The database they wanted to access has 28 million rows – so it’s going to take a while.

Continue reading