Migrate postgres

[woodRock]

Goal

Migrate postgres database from wms to wellmapsrvdev02

Tasks

• [x] Move all databases over
• [x] Create mapserv user
• [ ] Test all SQL statements

Success Criteria

The postgres database works as it did on the previous server.

[woodRock]

Setup

Before we can used the pg_dumpall command. Each of the tables have to exist on the wellmapsrvdev02 postgres server. Such that we can automate this task, we write a script which creates each of the databases from the wms server on the wellmapsrvdev02 server.

That bash script is as follows:

#!/bin/bash

# We need this to use the `createdb` command
createdb -T template0 template1;

# The Databses that need to be created
declare -a DATABASES=("test" "ecovis" "trawl")

# Create each database in the list of databases
for DATABASE in ${DATABASES[@]}; do
        echo "Initializing ${DATABASE}."
        dropdb $DATABASE
        createdb $DATABASE
done
echo "Finished"

[woodRock]

Dump

After we have setup our postgres database on the wellmapsrvdev02. We are now ready to dump the files.

There are two methods for doing this with the pg_dump: --copy or --inserts. Copy is about 1000 times faster than inserts. It streams the databases from the wms to the wellmapsrvdev02 as a single atomic transaction. What we gain in speed we lose in robustness. If a single line of that stream causes an error, the failure is critical. Meaning the entire transaction does not succeed. We are left with the original database.

Despite the drop in performance, it is good practice to use --inserts. For larger databases when the pg_dumpall command takes weeks, we may have a tolerance for a few NULL values that can be fixed in post.

This can be executed with the following script:

#!/bin/bash

# The IP address of the original psql database server
WMS='192.168.16.39'

# The postgres user to perform the operation with
USER='woodj'

# An existing database on the new psql server
EXISTING='postgres'

# Performs the dump from an the old server to the new one
# The inserts flag, means that failures become non-critical to execution
# We pipe the output of the pg_dumpall from standard out into our new database

pg_dumpall -h $WMS -U $USER --inserts | psql -d $EXISTING;

[woodRock]

We create the mapserv Role. This is the role that is used in all of the Mapfiles to query the Postgres database.

First we create the role in postgres.

create role mapserv;

Then alter the roles permissions to allow it to log in to postgres.

alter role mapserv with login;

We need to whitelist the role for connections from the localhost.

Edit the pb_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             mapserv                                     trust

Restart Apache2 for the configuration to be loaded.

[woodRock]

The postgres database appeared to be empty.

select count(*) from t_trawl

This would return nothing.

[woodRock]

However this appeared to just be the tables that contained spatial data.

The following command queries a non-spatial database. In this case the table was populated. The migration was successful.

select count(*) from t_trip

This is because the postgis extension was not installed on the postgres database on the new version.

We needed to add the postgis extension to each of the tables before being populated. We can add the following command to the setup script to this for us. This connects to a specified database and adds the needed extension.

psql -d <database> -c "create extension postgis;"

[woodRock]

The new setup script looks like this:

#!/bin/bash

# We need this to use the `createdb` command
createdb -T template0 template1;

# The Databses that need to be created
declare -a DATABASES=("test" "ecovis" "trawl")

# Create each database in the list of databases
for DATABASE in ${DATABASES[@]}; do
        echo "Initializing ${DATABASE}."
        dropdb $DATABASE
        createdb $DATABASE
        psql -d $DATABASE -c "create extension postgis;"
done
echo "Finished"

[woodRock]

The tables containing spatial data have now been populated.