This database was developed within the framework of the project:

Design of a Master Plan for Sustainable Tourism Development in the Sava River Basin
Reference number: HR-ISRBC-343723-CS-QCBS
Programme: Sava and Drina River Corridors Integrated Development Program (SDIP) – Phase 1, Part 4 – Regional Cooperation
Client: International Sava River Basin Commission (ISRBC)

SavaHub is a PostgreSQL/PostGIS database for tourism and infrastructure of the Sava River.

Version: 1.0
PostgreSQL: 14+
PostGIS: 3.0+
Date: January 2025

1. Prerequisites
2. New Database Installation
3. Database Reset and Clearing
4. Project Structure
5. Installation Verification
6. Maintenance
7. Extended Content and Analytics
8. Integration with SAVA_TOURISM_DB
9. Common Errors and Solutions

• PostgreSQL 14+ installed and running
• PostGIS 3.0+ extension available
• psql client (included with PostgreSQL installation)

psql --version
# Expected: psql (PostgreSQL) 14.x or newer

psql -U postgres -c "SELECT PostGIS_Version();"
# Expected: 3.x or newer

You need a user with privileges for:

• Creating databases (CREATEDB)
• Creating extensions
• Creating schemas, tables, functions

Recommendation: use postgres superuser for initial setup, then SavaHub-specific roles for regular work.

Connect to PostgreSQL as superuser:

psql -U postgres

In the psql prompt:

-- Create database
CREATE DATABASE savahub
  WITH ENCODING 'UTF8'
       LC_COLLATE 'en_US.UTF-8'
       LC_CTYPE 'en_US.UTF-8'
       TEMPLATE template0;

-- Create administrative user (optional)
CREATE USER savahub_admin WITH PASSWORD 'your_password_here';
ALTER USER savahub_admin WITH SUPERUSER;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE savahub TO savahub_admin;

-- Exit
\q

Run the main DDL file which creates all tables, types, functions and views:

psql -U postgres -d savahub -f savahub_schema.sql

Expected output:

• BEGIN
• Multiple CREATE EXTENSION, CREATE SCHEMA, CREATE TYPE, CREATE TABLE, CREATE FUNCTION, CREATE VIEW
• COMMIT

If you see errors such as ERROR: type ... already exists, this means the database was not empty. Check whether this is a fresh installation or you need a reset (see below).

psql -U postgres -d savahub -f savahub_patch.sql

What it does:

• Fixes audit.log_change() function (client_ip type)
• Adds tables tourism.attraction, tourism.event
• Adds views tourism.v_monthly_nights, nautical.v_marina_overview, cycling.v_eurovelo_sava, cycling.v_daily_traffic

Expected output:

• BEGIN
• DROP FUNCTION, CREATE FUNCTION
• CREATE TABLE for attraction/event
• CREATE VIEW for several views
• COMMIT

psql -U postgres -d savahub -f savahub_patch2.sql

What it does:

• Adds sample record to tourism.accommodation_stats
• Creates audit triggers for key tables (core.country, core.admin_unit, core.location, tourism.attraction, tourism.event, nautical.marina, cycling.route)

Expected output:

• BEGIN
• INSERT 0 1 (accommodation_stats)
• DO (trigger creation)
• COMMIT

psql -U postgres -d savahub -f savahub_seed.sql

What it does:

• Populates SBTCS classification (domains, categories, subcategories, classes)
• Adds 4 Sava countries (SI, HR, BA, RS)
• Adds admin units (one per country)
• Adds sample location (Zagreb)
• Adds sample accommodation (Hotel SavaLux)
• Adds sample marina (Marina Zagreb)
• Adds sample cycling route (EuroVelo 6 - Sava)
• Creates partitions for 2025 (vessel_position, counter_reading)

Expected output:

• BEGIN
• Series of INSERT 0 X (where X is the number of rows)
• Several NOTICE: relation ... already exists, skipping (partitions)
• SELECT results at the end (demo queries)
• COMMIT

IMPORTANT: If you see ROLLBACK instead of COMMIT, something went wrong – check previous steps or see Common Errors.

Connect to the database:

psql -U postgres -d savahub

Check row counts in key tables:

SELECT COUNT(*) FROM reference.sbtcs_class;       -- Expected: 4
SELECT COUNT(*) FROM core.country;                -- Expected: 4
SELECT COUNT(*) FROM core.admin_unit;             -- Expected: 8 (2 per country)
SELECT COUNT(*) FROM core.location;               -- Expected: 1
SELECT COUNT(*) FROM tourism.accommodation;       -- Expected: 1
SELECT COUNT(*) FROM tourism.accommodation_stats; -- Expected: 1
SELECT COUNT(*) FROM tourism.attraction;          -- Expected: 1
SELECT COUNT(*) FROM tourism.event;               -- Expected: 1
SELECT COUNT(*) FROM nautical.marina;             -- Expected: 1
SELECT COUNT(*) FROM cycling.route;               -- Expected: 1

Check views:

SELECT * FROM tourism.v_accommodation_by_country;
-- Should show 1 hotel in Croatia

SELECT * FROM tourism.v_monthly_nights;
-- Should show statistics for Hotel SavaLux, July 2025

If all the above returns expected values, installation is successful.

Use the ready-made reset script:

psql -U postgres -d savahub -f savahub_reset.sql

What it does:

1. Drops all SavaHub schemas (core, tourism, nautical, cycling, reference, audit) along with all objects within them
2. Re-runs savahub_schema.sql
3. Applies savahub_patch.sql
4. Applies savahub_patch2.sql
5. Runs savahub_seed.sql

Duration: ~10-30 seconds depending on disc speed.

Result: Database restored to "fresh install" state with initial seed data.

If you want a completely clean slate:

psql -U postgres

In psql:

-- CAUTION: deletes EVERYTHING in savahub database
DROP DATABASE IF EXISTS savahub;

-- Recreate
CREATE DATABASE savahub
  WITH ENCODING 'UTF8'
       LC_COLLATE 'en_US.UTF-8'
       LC_CTYPE 'en_US.UTF-8'
       TEMPLATE template0;

\q

Then run installation from step 2 (above).

If you want to keep tables and views but delete only data:

psql -U postgres -d savahub

In psql:

BEGIN;

-- Delete data from tables (reverse order from seed, due to FK constraints)
TRUNCATE TABLE audit.change_log CASCADE;
TRUNCATE TABLE audit.import_log CASCADE;

TRUNCATE TABLE cycling.counter_reading CASCADE;
TRUNCATE TABLE cycling.counter CASCADE;
TRUNCATE TABLE cycling.route CASCADE;

TRUNCATE TABLE nautical.vessel_position CASCADE;
TRUNCATE TABLE nautical.vessel CASCADE;
TRUNCATE TABLE nautical.marina CASCADE;

TRUNCATE TABLE tourism.guest_origin CASCADE;
TRUNCATE TABLE tourism.accommodation_stats CASCADE;
TRUNCATE TABLE tourism.accommodation CASCADE;
TRUNCATE TABLE tourism.event CASCADE;
TRUNCATE TABLE tourism.attraction CASCADE;

TRUNCATE TABLE core.location CASCADE;
TRUNCATE TABLE core.admin_unit CASCADE;
TRUNCATE TABLE core.sava_river_reference CASCADE;
TRUNCATE TABLE core.country CASCADE;

TRUNCATE TABLE reference.sbtcs_class CASCADE;
TRUNCATE TABLE reference.sbtcs_subcategory CASCADE;
TRUNCATE TABLE reference.sbtcs_category CASCADE;
TRUNCATE TABLE reference.sbtcs_domain CASCADE;

COMMIT;

Then re-run only seed:

psql -U postgres -d savahub -f savahub_seed.sql

After basic installation, you can add extended data and analytical views.

Adds more locations, hotels, marinas, attractions and events:

psql -U postgres -d savahub -f savahub_extended_seed.sql

What it adds:

• 3 additional locations (Ljubljana, Sarajevo, Belgrade)
• 3 additional hotels (one per new location)
• Statistics for Q2 2025
• 2 additional marinas (Ljubljana, Belgrade)
• 3 attractions (one per new location)
• 3 events (EkoSava, Sava Sports Cup, Sava Music Fest)
• 2 additional cycling routes

Adds advanced views for BI and reporting:

psql -U postgres -d savahub -f savahub_analytics_views.sql

What it adds:

• analytics.v_tourism_kpi – key tourism indicators
• analytics.v_occupancy_trends – occupancy trends
• analytics.v_seasonal_patterns – seasonal patterns
• analytics.v_cross_border_comparison – cross-country comparison

Script for automatic daily backup:

chmod +x savahub_daily_backup.sh
./savahub_daily_backup.sh

What it does:

• Creates complete backup (pg_dump)
• Retains backups for the last 30 days
• Organises by date
• Adds timestamp to filename

pg_dump -U postgres -d savahub -F c -f savahub_backup_$(date +%Y%m%d).dump

pg_dump -U postgres -d savahub -s -f savahub_schema_backup_$(date +%Y%m%d).sql

pg_dump -U postgres -d savahub -a -f savahub_data_backup_$(date +%Y%m%d).sql

pg_restore -U postgres -d savahub -c savahub_backup_20250115.dump

psql -U postgres -d savahub -f savahub_schema_backup_20250115.sql

SavaHub uses partitions for nautical.vessel_position and cycling.counter_reading.

Seed automatically creates partitions for Q1 2025. To add new partitions:

-- For February 2026
SELECT core.create_monthly_partition('nautical', 'vessel_position', 2026, 2);
SELECT core.create_monthly_partition('cycling', 'counter_reading', 2026, 2);

-- Or for the entire year (2026)
DO $$
BEGIN
    FOR month IN 1..12 LOOP
        PERFORM core.create_monthly_partition('nautical', 'vessel_position', 2026, month);
        PERFORM core.create_monthly_partition('cycling', 'counter_reading', 2026, month);
    END LOOP;
END;
$$;

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname IN ('core', 'tourism', 'nautical', 'cycling', 'reference', 'audit')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

# Full vacuum (may take time)
psql -U postgres -d savahub -c "VACUUM FULL ANALYZE;"

# Regular vacuum (faster)
psql -U postgres -d savahub -c "VACUUM ANALYZE;"

SAVA_HUB can be connected to the SAVA_TOURISM_DB analytical database using PostgreSQL Foreign Data Wrapper (FDW).

• SAVA_HUB = Operational database (individual objects, events, real-time tracking)
• SAVA_TOURISM_DB = Analytical database (aggregated statistics, BI panels)

FDW enables:

• Access to foreign tables as if they were local
• JOINs between two databases
• Unified views combining data from both databases

# 1. Prepare credentials (edit template)
nano savahub_fdw_config_template.sql

# 2. Run FDW integration
psql -d savahub -f savahub_fdw_integration.sql

After successful integration, you will have:

1. New schema external - contains foreign tables from SAVA_TOURISM_DB:

   • external.accommodation_statistics
   • external.visitor_statistics
   • external.infrastructure_index
   • external.tourism_products_overview
   • etc.

2. New schema integration - unified views:

   • integration.v_accommodation_comparison - registry and statistics comparison
   • integration.v_country_tourism_stats - consolidated country overview
   • integration.v_visitor_statistics_enriched - visitor statistics with context
   • integration.v_sava_corridor_infrastructure - infrastructure overview
   • integration.v_tourism_products_combined - products and attractions

-- Access foreign table
SELECT * FROM external.accommodation_statistics
WHERE country_code = 'HR' AND year = 2024;

-- Unified view
SELECT 
    country_name,
    registered_accommodations,
    total_arrivals,
    avg_arrivals_per_accommodation
FROM integration.v_country_tourism_stats;

-- Join between two databases
SELECT 
    acc.name AS hotel_name,
    stats.arrivals,
    stats.occupancy_rate
FROM tourism.accommodation acc
JOIN external.accommodation_statistics stats
    ON acc.country_code = stats.country_code
WHERE stats.year = 2024;

FDW_INTEGRATION_README.md

Includes:

• Detailed architecture
• Step-by-step installation
• All available views and functions
• Use case examples
• Security and troubleshooting

Cause: PostgreSQL is configured to use peer authentication for local user.

Solution 1: Use savahub_admin instead of postgres:

psql -U savahub_admin -d savahub -f savahub_schema.sql

Solution 2: Run as Unix user postgres:

sudo -u postgres psql -d savahub -f savahub_schema.sql

Solution 3: Change pg_hba.conf to use md5 or scram-sha-256 authentication:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Change the line:

local   all   postgres   peer

to:

local   all   postgres   md5

Then restart PostgreSQL:

sudo systemctl restart postgresql

Cause: You are trying to run savahub_schema.sql in a database that already has SavaHub objects.

Solution: Use the reset script:

psql -U postgres -d savahub -f savahub_reset.sql

Or manually drop schemas before running schema DDL.

Cause: Old version of audit.log_change() function in database.

Solution: Run savahub_patch.sql which fixes the function:

psql -U postgres -d savahub -f savahub_patch.sql

Cause: You have not run savahub_patch.sql.

Solution:

psql -U postgres -d savahub -f savahub_patch.sql

Cause: Error somewhere in the seed script (most commonly missing FK or audit trigger problem).

Solution:

1. Check output – look for the first line starting with ERROR:
2. Most common problems:
   • Missing patch: run savahub_patch.sql and savahub_patch2.sql
   • Audit function not fixed: see Error: column "client_ip"
3. If it still does not work, use reset:

psql -U postgres -d savahub -f savahub_reset.sql

Cause: No records in tourism.accommodation_stats.

Solution: Run savahub_patch2.sql which adds sample statistics:

psql -U postgres -d savahub -f savahub_patch2.sql

Cause: Audit trigger is not configured for that table.

Solution: savahub_patch2.sql adds audit triggers for main tables. If you need audit on additional tables:

CREATE TRIGGER trg_<table>_audit
AFTER INSERT OR UPDATE OR DELETE ON <schema>.<table>
FOR EACH ROW EXECUTE FUNCTION audit.log_change();

Cause: PostGIS extension is not installed.

Solution:

# On Ubuntu/Debian
sudo apt-get install postgresql-14-postgis-3

# Then in database
psql -U postgres -d savahub -c "CREATE EXTENSION IF NOT EXISTS postgis;"

For questions or issues:

• Technical specification: Isporuka_V_eng.tex
• Project: SAVA_HUB
• Date: January 2025

• Initial installation
• Schemas: core, tourism, nautical, cycling, reference, audit
• SBTCS classification
• Basic seed data (4 countries, sample accommodation/marina/route)
• Audit system
• Views for reporting
• Partitioned tables (vessel_position, counter_reading)

© 2025 | Project HR-ISRBC-343723-CS-QCBS | ISRBC