Linux

• Linux/BSD Installation
  • Postgres password
  • Create a data cluster
  • Start the postgresl server
  • Postgresql database/server configuration
• psql
• pgAdmin4
• PostgreSQL security
• QGIS
• manual xplordb database creation(superseded)
  • Install modules/extensions
  • Perl modules (superseded)

Linux/BSD Installation

Installation on Linux/BSD is usually fairly straight forward. You will need to install PostgreSQL, PostGIS, psql and optionally pgAdmin and QGIS. Consult the documentation of your Linux/BSD distribution. The package installer of your distro will usually take care of the dependencies. What follows below is how a typical install may be acheived but serve only as an example and may vary according to your linux/BSD distribution.

For openBSD the packages needed for PostgreSQL are

$su
$pkg_add postgis postgresql-contrib postgresql-docs

Postgres password

Usually the postgres user (database super-user or administrator) will be created without a password, so you can su to the postgres user from root

$su _postgresql (or postgres depends of disttribution)
$initdb -D /var/postgresql/data

Create a data cluster

Some operating systems create a cluster when postgres is installed and it's location may differ. For OpenBSD it needs to be created as the postgres database user as below:

$su _postgresql
$initdb -D /var/postgresql/data

Start the postgresl server

You may have to start the postgres service if the operating system has not. How this is done will depend on your distribution but is probably something like:

OpenBSD
$/etc/rc.d/postgresql start

or

$/etc/init.d/postgresql start

or if you installed from source possibly something like

$/usr/local/pgsql/bin/pg_ctl start -m smart -D /usr/local/pgsql/data/

for more info see the postgres and/or distribution documentation.

Postgresql database/server configuration

Configure the database by editing postgresql.conf and pg_hba.conf as required. Typically they are located in the PostgreSQL data directory. See the full PostgreSQL documentation http://www.postgresql.org/docs/curren.../runtime-config.html

psql

psql is the default command line front end for PostgreSQL it will usually be installed already. Documentation is here https://www.postgresql.org/docs/11/app-psql.html

To connect through psql for the first time you can su to the user postgres from root:

$su _postgresql (or postgres) 
$psql -U _postrgesql postgres

you may now create database users etc. if desired

## Install the xplordb template

Download the latest database template (.sql or .backup) from sourceforge and execute the SQL code. This can be done by executing the SQL with psql from the operating system command promt.

$psql -h localhost -U postgres -p 5432 -f xplordb_0.87.sql

The database template is now ready to take data. You may want to look at the section on [Import]ing data.

pgAdmin4

pgAdmin is a graphical open source management, development and administration tool for PostgreSQL. If you would like to use it, install it, then start it via the command line by typing the following;

$pgadmin4

Then add a connection, see the pgAdmin documentation here.

PostgreSQL security

You may want to add additional users and roles.

PostgreSQL can be configured for secure remote access. See http://www.postgresql.org/docs/11/static/ssl-tcp.html

Devising a database security strategy suitable for your organisation is also recommended.

QGIS

QGIS can be downloaded here. Or install with the operating system package management system. Documentation for QGIS is here

manual xplordb database creation(superseded)

Create the xplordb database and connect to it.

$psql# CREATE DATABASE xplordb encoding 'UTF8' template template0;
$psql# \c xplordb

Install modules/extensions

Create the Procedural Language/PostgreSQL if not already installed with the following.

$psql# create language plpgsql;

For PostgreSQL 9.1 or later and PostGIS 2 and later versions the following SQL commands will create the required extensions.

$psql# \c xplordb
$psql# CREATE EXTENSION btree_gist;
$psql# CREATE EXTENSION postgis;
$psql# CREATE EXTENSION postgis_raster

Perl modules (superseded)

You will need the DBI, DBD::Pg (postgres DBI module) and Text::CSV_XS modules installed to run the perl import script. They may be available as packages from your linux distribution e.g. for Debian

$apt-get install libdbi-perl;
$apt-get install libtext-csv-xs-perl;

or OpenBSD

$pkg_add p5-Text-CSV_XS
$pkg_add p5-DBI
$pkg_add p5-DBD-Pg