Loading the WAMEX pgsql database

Let’s run through the restoration of the pgsql backup file supplied by GSWA and expedio for all those times when you just want to have some nice data to play with.

Let’s first download the backup file from aws, change the path of the file mdhdb_data_and_structure.backup.gz to suit your setup.

curl https://exp-gswa-mdhdb-bkt01.s3.ap-southeast-2.amazonaws.com/mdhdb_data_and_structure.backup.gz --output mdhdb_data_and_structure.backup.gz

Of course you have already downloaded and installed postgres if not follow the process here to sort yourself out if you are trying this on windows…(hahaha) you will need to install sed and something to unzip.

As an FYI the restored database is going to require ~200Gb of space on a drive somewhere.

Once the file is downloaded we need to configure pgsql for loading i.e. configuring some schemas and extensions. I just run this in dbeaver because that is easy.

-- I assume that you don't have this schema
create schema production2;
-- you need postgis installed
CREATE EXTENSION postgis with schema production2;

Once we have the schema configured we can now load the .backup file.

The process streams the output of the compressed file into sed where we remove the lines starting with pg_dump and replace the user names that you might not have on your database: postgres and expedio. Replace the text userXXX with an appropriate user name for your configuration.

gunzip -c mdhdb_data_and_structure.backup.gz |  sed 's/postgres/userXXX/g;/^pg_dump/d;s/expedio/userXXX/g' | psql postgres -v ON_ERROR_STOP=1

If you cook the restore somehow this will delete all the tables that the restore function would of created, which I did more than once.

drop schema production2 cascade;
DROP TABLE public.tenements CASCADE;
DROP TABLE public."250k_mapsheet" CASCADE;

Cheers,

Ben.