PGFortress 🛡️

A comprehensive Node.js library for PostgreSQL database backup and restoration operations. Built with TypeScript, PGFortress provides enterprise-grade backup solutions with multiple formats, compression, scheduling, and robust error handling.

Features

🔧 Core Functionality

• Multiple Backup Formats: SQL dump, PostgreSQL custom format, and TAR archive
• Complete Restore System: Automatic format detection and restoration
• Compression Support: Built-in gzip compression with configurable levels
• Progress Tracking: Real-time progress updates with event-driven architecture
• Connection Pooling: Efficient database connection management
• SSL Support: Secure connections with proper SSL configuration

🎯 Advanced Features

• Selective Backups: Table-specific, schema-only, or data-only backups
• Scheduled Backups: Automated backup scheduling with cron expressions
• Retention Policies: Automatic cleanup of old backups
• Streaming Support: Memory-efficient streaming for large databases
• Error Recovery: Comprehensive error handling and retry mechanisms
• Verbose Logging: Detailed logging with configurable levels

🛠️ Developer Experience

• TypeScript First: Full TypeScript support with complete type definitions
• Dual Interface: Both programmatic API and command-line interface
• Event-Driven: EventEmitter pattern for progress tracking
• Production Ready: Extensive testing and performance optimization
• Cross-Platform: Works on Windows, macOS, and Linux

Installation

Prerequisites

• Node.js 20.x or higher
• PostgreSQL client tools (pg_dump, pg_restore, psql)
• Database connection with appropriate permissions

Install PGFortress

npm install pg-fortress

Install PostgreSQL Client Tools

Ubuntu/Debian:

sudo apt-get install postgresql-client

macOS:

brew install postgresql

Windows: Download from PostgreSQL official website

Quick Start

Command Line Interface

# Basic backup
npx pg-fortress backup -f sql -o backup.sql

# Compressed backup
npx pg-fortress backup -f custom -o backup.dump -c

# Restore database
npx pg-fortress restore -i backup.sql -f sql

# Help
npx pg-fortress --help

Recent Improvements

• ✅ Dotenv Integration: Automatic loading of environment variables from .env files
• ✅ Enhanced Configuration: Support for both DATABASE_URL and individual environment variables
• ✅ PostgreSQL Version Compatibility: Supports PostgreSQL 16+ with proper version handling
• ✅ Cloud Database Support: Optimized for Neon, Supabase, and other cloud PostgreSQL providers
• ✅ Robust Error Handling: Improved connection validation and error reporting

Programmatic API

const { PgFortress } = require('pg-fortress');

async function example() {
  const config = {
    host: 'localhost',
    port: 5432,
    database: 'mydb',
    username: 'user',
    password: 'password',
    ssl: false
  };

  const fortress = new PgFortress(config);

  // Create backup
  const result = await fortress.backup({
    format: 'sql',
    outputPath: './backup.sql',
    compress: true
  });

  console.log('Backup created:', result.filePath);
  
  await fortress.close();
}

Configuration

Environment Variables

# Database connection
DATABASE_URL=postgresql://user:password@host:port/database
PGHOST=localhost
PGPORT=5432
PGDATABASE=mydb
PGUSER=username
PGPASSWORD=password
PGSSLMODE=require

Using .env Files

PGFortress automatically loads environment variables from .env files. Create a .env file in your project root:

# Database Connection Configuration
PGHOST=localhost
PGPORT=5432
PGDATABASE=postgres
PGUSER=postgres
PGPASSWORD=your_password_here

# Alternative: Use DATABASE_URL (overrides individual parameters)
# DATABASE_URL=postgresql://username:password@host:port/database

# SSL Configuration (optional)
# PGSSLMODE=require

# Connection Pool Settings (optional)
# PGCONNECTIONTIMEOUT=30000
# PGIDLETIMEOUT=30000
# PGMAX=10

Note: The .env file is automatically loaded by the application. Make sure to add .env to your .gitignore file to avoid committing sensitive credentials.

Configuration File

Create pg-fortress.config.json:

{
  "database": {
    "host": "localhost",
    "port": 5432,
    "database": "mydb",
    "username": "user",
    "password": "password",
    "ssl": true
  },
  "backup": {
    "format": "custom",
    "compress": true,
    "compressionLevel": 6
  },
  "logger": {
    "level": "info",
    "file": "./logs/pg-fortress.log"
  }
}

API Reference

PgFortress Class

Constructor

const fortress = new PgFortress(config, logger);

Methods

backup(options)

const result = await fortress.backup({
  format: 'sql',           // 'sql', 'custom', 'tar'
  outputPath: './backup.sql',
  compress: true,
  compressionLevel: 6,     // 1-9
  schemaOnly: false,
  dataOnly: false,
  tables: ['users', 'orders'],
  excludeTables: ['logs'],
  verbose: true
});

restore(options)

const result = await fortress.restore({
  inputPath: './backup.sql',
  format: 'sql',
  createDatabase: false,
  dropExisting: false,
  verbose: true
});

close()

await fortress.close();

PgScheduler Class

const { PgScheduler } = require('pg-fortress');

const scheduler = new PgScheduler(config, {
  cron: '0 2 * * *',        // Daily at 2 AM
  retentionDays: 7,
  backupPath: './backups',
  enabled: true
});

// Schedule backup
scheduler.schedule('daily-backup', {
  format: 'custom',
  compress: true
});

// List schedules
console.log(scheduler.list());

// Stop scheduler
await scheduler.stop();

Command Line Interface

Backup Command

pg-fortress backup [options]

Options:
  -f, --format <format>            Backup format (sql, custom, tar)
  -o, --output <path>              Output file path
  -c, --compress                   Compress the backup
  -l, --compression-level <level>  Compression level (1-9)
  -s, --schema-only               Backup schema only
  -d, --data-only                 Backup data only
  -t, --table <tables...>         Include specific tables
  -T, --exclude-table <tables...> Exclude specific tables
  -v, --verbose                   Verbose output
  --config <path>                 Configuration file path

Restore Command

pg-fortress restore [options]

Options:
  -i, --input <path>              Input backup file
  -f, --format <format>           Backup format (sql, custom, tar)
  -c, --create-database           Create target database
  -d, --drop-existing             Drop existing data
  -v, --verbose                   Verbose output
  --config <path>                 Configuration file path

Schedule Command

pg-fortress schedule [options]

Options:
  -a, --add <name>                Add new schedule
  -r, --remove <name>             Remove schedule
  -l, --list                      List all schedules
  -s, --start                     Start scheduler
  -S, --stop                      Stop scheduler
  --config <path>                 Configuration file path

Examples

Basic Backup and Restore

const { PgFortress } = require('pg-fortress');

async function basicExample() {
  const config = {
    host: 'localhost',
    port: 5432,
    database: 'myapp',
    username: 'postgres',
    password: 'password'
  };

  const fortress = new PgFortress(config);

  try {
    // Create backup
    const backup = await fortress.backup({
      format: 'sql',
      outputPath: './myapp_backup.sql',
      verbose: true
    });

    console.log(`Backup created: ${backup.filePath}`);
    console.log(`Size: ${backup.size} bytes`);
    console.log(`Duration: ${backup.duration}ms`);

    // Restore backup
    const restore = await fortress.restore({
      inputPath: './myapp_backup.sql',
      format: 'sql',
      verbose: true
    });

    console.log(`Restore completed: ${restore.tablesRestored} tables`);

  } catch (error) {
    console.error('Operation failed:', error.message);
  } finally {
    await fortress.close();
  }
}

Scheduled Backups

const { PgScheduler } = require('pg-fortress');

async function scheduledExample() {
  const config = {
    host: 'localhost',
    port: 5432,
    database: 'myapp',
    username: 'postgres',
    password: 'password'
  };

  const scheduleConfig = {
    cron: '0 2 * * *',        // Daily at 2 AM
    retentionDays: 30,
    backupPath: './backups',
    enabled: true
  };

  const scheduler = new PgScheduler(config, scheduleConfig);

  // Schedule different types of backups
  scheduler.schedule('daily-full', {
    format: 'custom',
    compress: true,
    outputPath: './backups/daily-full.dump'
  });

  scheduler.schedule('weekly-sql', {
    format: 'sql',
    compress: true,
    outputPath: './backups/weekly-sql.sql'
  });

  // Start scheduler
  await scheduler.start();
  console.log('Scheduler started');

  // Stop after some time
  setTimeout(async () => {
    await scheduler.stop();
    console.log('Scheduler stopped');
  }, 60000);
}

Advanced Backup Options

const { PgFortress } = require('pg-fortress');

async function advancedExample() {
  const fortress = new PgFortress(config);

  // Schema-only backup
  const schemaBackup = await fortress.backup({
    format: 'sql',
    outputPath: './schema.sql',
    schemaOnly: true
  });

  // Data-only backup
  const dataBackup = await fortress.backup({
    format: 'custom',
    outputPath: './data.dump',
    dataOnly: true,
    compress: true
  });

  // Table-specific backup
  const tableBackup = await fortress.backup({
    format: 'sql',
    outputPath: './users.sql',
    tables: ['users', 'user_profiles'],
    compress: true
  });

  // Exclude specific tables
  const excludeBackup = await fortress.backup({
    format: 'custom',
    outputPath: './app_without_logs.dump',
    excludeTables: ['logs', 'temp_data'],
    compress: true
  });

  await fortress.close();
}

Error Handling

const { PgFortress } = require('pg-fortress');

async function errorHandlingExample() {
  const fortress = new PgFortress(config);

  try {
    const result = await fortress.backup({
      format: 'sql',
      outputPath: './backup.sql'
    });

    if (result.success) {
      console.log('Backup successful');
    } else {
      console.error('Backup failed:', result.error);
    }

  } catch (error) {
    console.error('Unexpected error:', error.message);
    
    // Check specific error types
    if (error.message.includes('connection')) {
      console.error('Database connection failed');
    } else if (error.message.includes('permission')) {
      console.error('Insufficient permissions');
    }

  } finally {
    await fortress.close();
  }
}

Progress Tracking

const { PgFortress } = require('pg-fortress');

async function progressExample() {
  const fortress = new PgFortress(config);

  // Listen to progress events
  fortress.on('progress', (info) => {
    console.log(`${info.phase}: ${info.percentage}%`);
    if (info.totalTables) {
      console.log(`Tables: ${info.totalTables}`);
    }
  });

  fortress.on('error', (error) => {
    console.error('Error:', error.message);
  });

  const result = await fortress.backup({
    format: 'custom',
    outputPath: './backup.dump',
    verbose: true
  });

  await fortress.close();
}

Testing

# Run all tests
npm test

# Run with coverage
npm run test:coverage

# Run specific test suite
npm run test:backup
npm run test:restore
npm run test:cli

Performance Optimization

Large Databases

• Use custom format for better performance
• Enable compression to reduce I/O
• Use table-specific backups for large datasets
• Consider schema-only backups for structure analysis

Connection Pooling

const config = {
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  username: 'user',
  password: 'password',
  // Connection pool settings
  max: 10,
  connectionTimeoutMillis: 30000,
  idleTimeoutMillis: 30000
};

Security Considerations

• Store credentials in environment variables
• Use SSL connections for production
• Implement proper access controls
• Regular security updates
• Audit backup access

Troubleshooting

Common Issues

Connection Errors:

# Check PostgreSQL tools
pg_dump --version
pg_restore --version

# Test database connection
psql -h localhost -U username -d database -c "SELECT 1"

Permission Errors:

-- Grant necessary permissions
GRANT USAGE ON SCHEMA public TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

SSL Connection Issues:

# Set SSL mode
export PGSSLMODE=require

Contributing

1. Fork the GitLab repository
2. Create a feature branch
3. Make your changes
4. Add tests
5. Submit a merge request

Author

Kurubaran Anandhan

• Email: [email protected]
• GitLab: @kurubarana
• Project Repository: pgfortress

License

MIT License - see LICENSE file for details

Support

• GitLab Issues: Report bugs
• Documentation: Wiki
• Email: [email protected]

Changelog

v1.0.0

• Initial release
• Basic backup and restore functionality
• CLI interface
• TypeScript support
• Comprehensive test suite