Guide to Exporting and Importing DHIS2 PostgreSQL Database

dhis2mis

This guide explains how to export and import a DHIS2 PostgreSQL database while excluding specific tables to manage the size and content.

1. Export the DHIS2 Database

1.1 Export Without Excluding the `audit` Table

pg_dump --format=plain --no-owner --no-privileges \
  --exclude-table=_* \
  --exclude-table=aggregated* \
  --exclude-table=analytics* \
  --exclude-table=completeness* \
  --exclude-table=geography_columns \
  --exclude-table=geometry_columns \
  --exclude-table=raster_columns \
  --exclude-table=raster_overviews \
  --exclude-table=spatial_ref_sys \
  --schema=public \
  --dbname=YOUR_DATABASE_NAME > dhis-export.sql

1.2 Export While Excluding the `audit` Table

pg_dump --format=plain --no-owner --no-privileges \
  --exclude-table=_* \
  --exclude-table=aggregated* \
  --exclude-table=analytics* \
  --exclude-table=completeness* \
  --exclude-table=geography_columns \
  --exclude-table=geometry_columns \
  --exclude-table=raster_columns \
  --exclude-table=raster_overviews \
  --exclude-table=spatial_ref_sys \
  --exclude-table=audit \
  --schema=public \
  --dbname=YOUR_DATABASE_NAME > dhis-export.sql

2. Import the DHIS2 Database

2.1 Create Role and Database

CREATE ROLE dhis WITH LOGIN PASSWORD 'Password-goes-here';
CREATE DATABASE dhis2 OWNER dhis;
GRANT ALL PRIVILEGES ON DATABASE dhis2 TO dhis;

-- Connect to the new database
\c dhis2

-- Change ownership of the public schema to the new user
ALTER SCHEMA public OWNER TO dhis;

-- Install PostGIS extension (if required)
CREATE EXTENSION postgis WITH SCHEMA public;

2.2 Import the SQL File

psql -d dhis2 -U dhis -f dhis-export.sql

Summary

Export: Use pg_dump to export the DHIS2 database, optionally excluding tables like analytics, audit, and spatial data.
Import: Use psql to import the SQL dump into the target DHIS2 database.