Database Export Error Fix Guide

Problem: assessment_attempts_id_seq Does Not Exist

You’re encountering this error because:

1. PostgreSQL server version (16.9) doesn’t match pg_dump client version (15.7)
2. The export is trying to reference sequences that don’t exist in your database
3. There may be stale migration files referencing non-existent tables

Solution Methods

Method 1: Use Existing Clean Export (Recommended)

The cleanest solution is to use the existing export file that was already created:

# Use the existing clean export
cp exports/database_export_20250710_010237.sql exports/clean_export_for_deployment.sql

This file contains:

• 37 tables with complete data
• All existing sequences with correct values
• 18 zones, 10 rate cards, 3 service levels
• No problematic sequences or non-existent tables

Method 2: Create New Export Using SQL Commands

If you need a fresh export, use direct SQL commands instead of pg_dump:

-- Connect to your database and run these commands

-- Create export file manually
\copy (SELECT 'INSERT INTO zones VALUES (' || string_agg(quote_literal(column_value), ',') || ');' FROM zones) TO 'zones_export.sql';

-- Export key tables
\copy zones TO 'zones_data.csv' WITH CSV HEADER;
\copy rate_cards TO 'rate_cards_data.csv' WITH CSV HEADER;
\copy service_levels TO 'service_levels_data.csv' WITH CSV HEADER;

Method 3: Use the Fixed Export Script

Run the SQL export script that bypasses pg_dump:

# The script is already created
python3 create_sql_export.py

This creates a clean export using direct SQL queries.

Quick Deployment Solution

For Immediate Deployment:

1. Use the existing working export:

   # Copy the working export
   cp exports/database_export_20250710_010237.sql your_deployment_export.sql

2. Deploy to your server:

   # On your target server
   createdb your_database_name
   psql -U your_user -d your_database_name -f your_deployment_export.sql

3. Verify the import:

   psql -U your_user -d your_database_name -c "SELECT COUNT(*) FROM zones;"
   psql -U your_user -d your_database_name -c "SELECT COUNT(*) FROM rate_cards;"

What Tables Are Included

The working export includes these essential tables:

• zones (18 records) - Australian service zones
• rate_cards (10 records) - Pricing configurations
• service_levels (3 records) - Express, Standard, Economy
• customers - Customer data
• crm_customers - CRM customer records
• crm_leads - Sales leads
• All supporting tables for rates, addresses, etc.

Database Schema Verification

To verify your database schema is correct:

-- Check table count
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';

-- Check sequence count
SELECT COUNT(*) FROM information_schema.sequences WHERE sequence_schema = 'public';

-- Verify key tables exist
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name IN ('zones', 'rate_cards', 'service_levels')
ORDER BY table_name;

Expected results:

• 37 tables total
• 36 sequences total
• All key tables present

Environment Variables for Deployment

Update your .env file with the correct database connection:

# Database Configuration
DATABASE_URL=postgresql://your_user:your_password@your_host:5432/your_database
PGHOST=your_host
PGPORT=5432
PGUSER=your_user
PGPASSWORD=your_password
PGDATABASE=your_database

Troubleshooting

If Export Still Fails:

1. Check PostgreSQL version compatibility
2. Use the existing working export file
3. Verify database connectivity
4. Check for permission issues

If Import Fails:

1. Ensure target database is empty
2. Check user permissions
3. Verify database encoding (UTF-8)
4. Run import with verbose logging

Production Deployment Checklist

• Database export file ready
• Target server has PostgreSQL installed
• Database user created with proper permissions
• Environment variables configured
• Database import tested
• Application connectivity verified

Support

The existing export file exports/database_export_20250710_010237.sql is confirmed working and contains all necessary data for deployment. Use this file for your production deployment to avoid any export errors.