PostgreSQL setup

Set up PostgreSQL with PostGIS

First you need to install PostgreSQL with PostGIS. On Debian derivatives these can be installed with a single command (version numbers might change):

apt-get install postgresql postgis postgresql-9.5-postgis-scripts pgadmin3

Next you need to set up a password for the postgres admin account, and set up a user and database for the SensorThingsServer to use.

1. First set a password for the postgres user.

   1. Run the psql command from the postgres user account: sudo -u postgres psql postgres
   2. Set the password: \password postgres
   3. Enter a password.
   4. Close psql.

2. Open pgadmin3 (postgres admin userinterface) and connect using user postgres

3. Create a user for the SensorThingsServer (user sensorthings)

   1. Right-click Login Roles -> New Login Role
   2. Enter a name (sensorthings)
   3. Enter a password (twice)
   4. Click OK

4. Create a new database (database sensorthings)

   1. Right-click Databases -> new Database
   2. Give a name (sensorthings)
   3. Set owner to "sensorthings" (if that's what you called the user in step 3.2)
   4. Do not set a Template
   5. Click ok

5. Navigate to database sensorthings as user postgres

6. Initialise PostGIS on the newly created database

   1. With the database sensorthings selected, click the SQL button in the menu bar
   2. Enter the query CREATE EXTENSION postgis;
   3. Click the green "run" button

7. (Optional) If you want to use UUIDs as ID, initialise the uuid-ossp extension

   1. Enter the query CREATE EXTENSION "uuid-ossp";
   2. Click the green "run" button

You now have PostgreSQL installed, with an empty database ready for use by the SensorThingsServer.