A comprehensive bash script for managing PostgreSQL databases with advanced backup, restore, and user management capabilities.

• 🎯 Backup & Restore Type Selection - Choose between complete migration or content-only backup/restore
• Complete Database Backup - Full database backup with all permissions and ownership
• 📊 Content-Only Backup - Data-only backup for synchronization and regular backups
• 🔐 Encrypted Password Backup - Securely backup and restore user passwords with AES-256 encryption
• Smart Restore - Restore databases with proper user and permission recreation
• Advanced User Management - Remove users with dependency cleanup
• Cross-Platform Support - Works on Ubuntu, Debian, CentOS, RHEL, Arch Linux
• Auto-Detection - Automatically detects PostgreSQL instances
• Permission Handling - Preserves all table, schema, and database permissions
• Error Recovery - Handles shared dependencies and cleanup automatically

• PostgreSQL server running
• PostgreSQL client tools (psql, pg_dump, pg_dumpall)
• OpenSSL (for password encryption/decryption)
• Bash shell
• Superuser access to PostgreSQL

git clone https://github.com/0xamirreza/pg-managex.git
cd pg-managex

chmod +x main.sh

./main.sh

1. Backup DB (Choose: Migrate or Content) - Create backups with type selection
2. Restore DB (Choose: Migrate or Content) - Restore databases with type selection
3. Connect DB - Connect to databases via psql
4. Remove DB - Remove databases and users (with dependency cleanup)
5. Change PostgreSQL Connection - Switch between PostgreSQL instances

• ✅ Database structure and data
• ✅ All user permissions (GRANT/REVOKE statements)
• ✅ Table, sequence, and function ownership
• ✅ Schema ownership
• ✅ Role definitions and memberships
• ✅ Complete permission hierarchy
• ✅ 🔐 Encrypted user passwords

• Location: ~/.pgbkre/ or /root/.pgbkre/
• Database Backup: {database}_{owner}_{timestamp}_complete.sql
• Password Backup: {database}_{owner}_{timestamp}_passwords.sql (encrypted)
• Master Key: .master_key (AES-256 encryption key)
• Metadata: Includes database info, owner, timestamp, and connection details

• ✅ All table data (INSERT statements)
• ✅ All rows and columns
• ❌ No database structure
• ❌ No users/passwords
• ❌ No permissions
• ❌ No indexes/constraints

• Content Backup: {database}_{owner}_{timestamp}_CONTENT.sql
• Metadata: {database}_{owner}_{timestamp}_CONTENT.meta
• Format: INSERT statements with column names

The complete restore process:

1. Creates the database owner (if not exists)
2. Restores role definitions and permissions
3. Drops and recreates the database
4. Restores all tables, sequences, and data
5. Restores complete ownership and permissions
6. 🔐 Decrypts and restores user passwords
7. Handles conflicts automatically

The content restore process:

1. Checks if target database exists
2. Validates database structure compatibility
3. Inserts data using INSERT statements
4. Handles duplicate data conflicts
5. Provides detailed error reporting

⚠️ Important: Content restore requires the target database structure to already exist!

When you select Backup DB or Restore DB from the main menu, you'll be presented with type selection options:

=== Backup Type Selection ===

Choose backup type:
1) 🔄 Migrate (Complete backup with users, passwords, and data)
2) 📊 Content (Data-only backup - no structure or users)
0) Back to main menu

=== Restore Type Selection ===

Choose restore type:
1) 🔄 Migrate (Complete restore with users, passwords, and data)
2) 📊 Content (Data-only restore - requires existing database structure)
0) Back to main menu

• Use for: Full database migration to new server
• Use for: Complete backup with everything
• Use for: Disaster recovery scenarios
• Includes: Structure + Data + Users + Passwords + Permissions

• Use for: Data synchronization between databases
• Use for: Regular data backups
• Use for: Development to production data transfer
• Includes: Only data (INSERT statements)
• Requires: Target database structure must exist

Choose 🔄 Migrate when:

• Moving to a new server
• Complete database migration
• Disaster recovery
• You want everything (users, passwords, structure, data)

Choose 📊 Content when:

• Syncing data between databases
• Regular data backups
• Development to production data transfer
• Target database structure already exists

The script now includes advanced password backup functionality that allows you to fully migrate database users with their original passwords intact.

1. Master Key Generation: Automatically generates a unique AES-256 encryption key
2. Password Extraction: Queries pg_authid table for user password hashes
3. Encryption: Encrypts password hashes using AES-256-CBC encryption
4. Secure Storage: Stores encrypted passwords in separate .sql files
5. Key Protection: Master key is stored with 600 permissions (owner-only access)

1. Database Restoration: Restores database structure and data
2. Password Detection: Automatically detects password backup files
3. Decryption: Decrypts password hashes using the master key
4. Password Restoration: Updates pg_authid table with original password hashes
5. Verification: Confirms successful password restoration

• AES-256 Encryption: Military-grade encryption for password protection
• Secure Key Storage: Master key stored with restrictive permissions
• Separate Files: Passwords stored separately from database backups
• No Plain Text: Passwords never stored in plain text format

~/.pgbkre/
├── .master_key                           # 🔐 AES-256 encryption key
├── database_owner_20240101_120000_complete.sql    # Database backup
├── database_owner_20240101_120000_passwords.sql   # 🔐 Encrypted passwords
└── database_owner_20240101_120000.meta           # Metadata

• 🔑 Master Key: Keep the .master_key file secure - it's required for password restoration
• 📁 Backup Security: Store backup files and master key in secure locations
• 🔒 File Permissions: Master key automatically has 600 permissions (owner-only)
• ⚠️ Backup Migration: When moving backups, include the master key file

When removing databases, the script:

1. Terminates active connections
2. Drops the database
3. Checks for user dependencies
4. Cleans up shared dependencies automatically
5. Removes the user completely
6. Handles edge cases and conflicts

• Ubuntu/Debian (with pg_lsclusters detection)
• CentOS/RHEL/Fedora
• Arch Linux
• Any Linux with PostgreSQL

• PostgreSQL 9.x and newer
• All modern versions (10, 11, 12, 13, 14, 15, 16)

export PGPASSWORD='your_password'  # Optional: avoid password prompts

• Host: localhost (default)
• Port: 5432 (default)
• User: postgres (default)
• Auto-detection: Automatically detects running instances

• Script requires superuser access to PostgreSQL
• Backups contain sensitive data - secure backup files
• 🔐 Password Encryption: User passwords are encrypted with AES-256 encryption
• 🔑 Master Key Security: Keep the .master_key file secure and backed up
• Use environment variables for passwords when possible
• Script excludes system catalogs to prevent conflicts
• ⚠️ Complete Migration: With password backup, you get 100% database migration

Contributions are welcome! Please feel free to submit issues and pull requests.

This project is open source. Feel free to use and modify as needed.

0xamirreza - GitHub Profile

Enjoy managing your PostgreSQL databases! 🚀