Skip to content

Pepys Database Backup and Restoration Procedure

Jump to bottom
Arunlal Venugopal edited this page Jul 21, 2022 · 5 revisions

This document lists the procedure to create backups of and restore Pepys database.

Database requirements

Pepys’ primary database is PostgreSQL RDBMS. Below are the version details with which Pepys’ is being developed now.

Item Version Description
PostgreSQL 11.7 Core Database
PostGis 2.5.4 For GIS functions
pg_dump 11.16 For database backup

Create data dump using pg_dump utility

Creating a data dump is as straight forward as issuing a pg_dump command with the necessary arguments. Below is an example.

pg_dump -h trac-store.com -p 5432 -U tracstor_view -n pepys tracstor_trial1 > ~/Desktop/pepys_dump_20220721.txt
#tracstor_trial1 is the name of the database

Input the password when prompted for, or setup a .pgpass file as necessary.

Database system setup

Once PostgreSQL and PostGis is installed, the following setup has to be done.

Users to be created

The following users are to be created.

  • tracstor
  • tracstor_admin
  • tracstor_dev
  • tracstor_view
create user tracstor with password 'password';
create user tracstor_admin with password 'password';
create user tracstor_dev with password 'password';
create user tracstor_view with password 'password';

Database to be created

Ensure that the database name is same as the one that was imported using pg_dump, if restoring.

CREATE DATABASE tracstor_trial1 WITH TEMPLATE = template0 ENCODING = 'UTF8';
ALTER DATABASE tracstor_trial1 OWNER TO tracstor;

PostGis extensions to be enabled

PostGis extensions have to be enabled in the database created as part of the previous step.

Connect to the database created in the previous step

\connect tracstor_trial1

Enable PostGis extensions

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION postgis_tiger_geocoder;

Search path to be set

Ensure PostGis related domains and functions are set to search path

ALTER DATABASE tracstor_trial1 SET search_path TO '$user', 'public', 'topology', 'tiger';

Privileges to be granted

The new database is required to be access by other users that were previously created.

GRANT ALL ON DATABASE tracstor_trial1 TO tracstor_admin;
GRANT ALL ON DATABASE tracstor_trial1 TO tracstor_dev;
GRANT ALL ON DATABASE tracstor_trial1 TO tracstor_view;

Restore the exported pg_dump file

Restoration is as simple as issuing a psql command with necessary arguments.

psql -h localhost -p 5432 -U tracstor_admin  -d tracstor_trial1 -f ~/Desktop/pepys_dump_20220721.txt
Clone this wiki locally