Kenhuang Academy
  • English

      • PostgreSQL Drop Database

      Overview

      Dropping (deleting) a database is a permanent action that removes the database and all its contents from the PostgreSQL cluster. This operation cannot be undone, so it should be performed with extreme caution.

      Before dropping a database:

      Basic Syntax

      DROP DATABASE database_name;

      Simple Examples

      -- Drop a database
DROP DATABASE myapp;

-- Drop database with IF EXISTS (recommended)
DROP DATABASE IF EXISTS myapp;

-- Drop database with explicit connection
-- (This will fail if there are active connections)

      Complete DROP DATABASE Syntax

      DROP DATABASE [IF EXISTS] database_name
    [WITH (FORCE = true | false)];

      Using dropdb

      ### Important Warnings
dropdb myapp

### Prerequisites
dropdb myapp 2>/dev/null || true

## DROP DATABASE Statement
dropdb --force myapp

### Basic Syntax
dropdb -U postgres myapp

### Simple Examples
dropdb -h localhost -p 5432 -U postgres myapp

### Complete DROP DATABASE Syntax
dropdb "postgresql://postgres@localhost/myapp"

      Using Command Line Tools

      Using psql

      ### Using dropdb
psql -U postgres -c "DROP DATABASE myapp;"

# Drop with IF EXISTS
psql -U postgres -c "DROP DATABASE IF EXISTS myapp;"

# Force drop with termination
psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'myapp' AND pid <> pg_backend_pid();"
psql -U postgres -c "DROP DATABASE myapp;"

      Using pgAdmin

      1. Connect to PostgreSQL server in pgAdmin
      2. Expand the server in the browser tree
      3. Right-click on the database you want to drop
      4. Select "Delete/Drop"
      5. Confirm the action
      6. Click "Yes" to confirm

      Checking Active Connections

      -- Check active connections to database
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    query_start
FROM pg_stat_activity
WHERE datname = 'myapp'
ORDER BY query_start;

      Terminating Connections

      -- Terminate all connections except current
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp' 
AND pid <> pg_backend_pid();

-- Terminate connections by user
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp' 
AND usename = 'specific_user';

-- Terminate idle connections
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp' 
AND state = 'idle'
AND pid <> pg_backend_pid();

-- Wait for connections to close naturally
-- Use WITH FORCE to terminate immediately

      Using FORCE Option

      -- PostgreSQL 13+ supports FORCE option
DROP DATABASE myapp WITH (FORCE = true);

-- This will terminate all connections and drop the database

      Connection Scripts

      #!/bin/bash
# drop_database.sh - Drop database with connection management

DB_NAME=$1

echo "Checking for active connections..."
CONN_COUNT=$(psql -U postgres -t -c "SELECT COUNT(*) FROM pg_stat_activity WHERE datname = '$DB_NAME';")

if [ "$CONN_COUNT" -gt 1 ]; then
    echo "Terminating $CONN_COUNT active connections..."
    psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME' AND pid <> pg_backend_pid();"
    sleep 2
fi

echo "Dropping database $DB_NAME..."
dropdb $DB_NAME

echo "Database $DB_NAME dropped successfully."

      1. Connect to Server: Open pgAdmin and connect to your PostgreSQL server

      2. Navigate to Databases: Expand the server in the browser tree and click on "Databases"

      3. Select Database: Right-click on the database you want to drop

      4. Access Delete Option: Select "Delete/Drop" from the context menu

      5. Confirm Deletion: A dialog will appear asking for confirmation

      6. Review Impact: pgAdmin may show objects that will be deleted

      7. Confirm Action: Click "Yes" to proceed or "No" to cancel

      8. Verify: Refresh the database list to confirm deletion

      pgAdmin Options

      pgAdmin provides options during database deletion:

      Template Databases

      -- Cannot drop template databases
-- Error: "cannot drop a template database"

-- Check if database is a template
SELECT datname, datistemplate FROM pg_database WHERE datname = 'myapp';

-- Unmark as template before dropping
ALTER DATABASE myapp IS_TEMPLATE FALSE;
DROP DATABASE myapp;

      System Databases

      -- Cannot drop system databases
-- These are protected:
-- - postgres
-- - template0
-- - template1

-- Attempting to drop these will result in error
DROP DATABASE postgres;  -- Error

      Owner Permissions

      -- Only owner or superuser can drop database
-- As database owner:
DROP DATABASE myapp;  -- Works

-- As non-owner without privileges:
DROP DATABASE myapp;  -- Error: must be owner

-- Grant ownership first
ALTER DATABASE myapp OWNER TO new_owner;
-- Then drop as new owner

      Database Restrictions

      -- Cannot drop database with active connections
-- Error: "database is being accessed by other users"

-- Solution: Terminate connections first
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp' 
AND pid <> pg_backend_pid();

-- Or use FORCE option (PostgreSQL 13+)
DROP DATABASE myapp WITH (FORCE = true);

      Backup Before Dropping

      ### Using psql
pg_dump -U postgres myapp > myapp_backup_$(date +%Y%m%d).sql

# Or use custom format for compression
pg_dump -U postgres -Fc myapp > myapp_backup_$(date +%Y%m%d).dump

      Verification Scripts

      #!/bin/bash
# safe_drop.sh - Safe database drop with verification

DB_NAME=$1
BACKUP_FILE="backup_${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql"

echo "=== Database Drop Safety Check ==="
echo "Database: $DB_NAME"
echo ""

### Using pgAdmin
echo "1. Checking if database exists..."
if psql -U postgres -t -c "SELECT 1 FROM pg_database WHERE datname = '$DB_NAME'" | grep -q 1; then
    echo "   ✓ Database exists"
else
    echo "   ✗ Database does not exist"
    exit 1
fi

## Managing Connections Before Dropping
echo "2. Checking for active connections..."
CONN_COUNT=$(psql -U postgres -t -c "SELECT COUNT(*) FROM pg_stat_activity WHERE datname = '$DB_NAME' AND pid <> pg_backend_pid();" | tr -d ' ')
echo "   Active connections: $CONN_COUNT"

### Checking Active Connections
echo "3. Checking database size..."
SIZE=$(psql -U postgres -t -c "SELECT pg_size_pretty(pg_database_size('$DB_NAME'));" | tr -d ' ')
echo "   Database size: $SIZE"

### Terminating Connections
echo "4. Counting tables..."
TABLE_COUNT=$(psql -U postgres -d $DB_NAME -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';" | tr -d ' ')
echo "   Tables in public schema: $TABLE_COUNT"

echo ""
echo "=== Creating Backup ==="
pg_dump -U postgres $DB_NAME > $BACKUP_FILE
echo "Backup saved to: $BACKUP_FILE"

echo ""
echo "=== Summary ==="
echo "Database: $DB_NAME"
echo "Size: $SIZE"
echo "Tables: $TABLE_COUNT"
echo "Backup: $BACKUP_FILE"
echo ""
echo "Ready to drop. Continue? (yes/no)"
read CONFIRM

if [ "$CONFIRM" = "yes" ]; then
    echo "Dropping database..."
    dropdb $DB_NAME
    echo "Database dropped successfully."
else
    echo "Drop cancelled."
fi

      Confirmations and Logging

      Confirmations and Logging

      ### Using FORCE Option
### Connection Scripts

DB_NAME=$1

# Log the action
echo "$(date '+%Y-%m-%d %H:%M:%S') - Attempting to drop database: $DB_NAME" >> /var/log/db_operations.log

## Dropping Databases with pgAdmin
read -p "Are you sure you want to drop database '$DB_NAME'? (type 'yes' to confirm): " CONFIRM

if [ "$CONFIRM" = "yes" ]; then
    echo "Dropping database $DB_NAME..."
    dropdb $DB_NAME
    
    if [ $? -eq 0 ]; then
        echo "Database dropped successfully."
        echo "$(date '+%Y-%m-%d %H:%M:%S') - Database dropped: $DB_NAME" >> /var/log/db_operations.log
    else
        echo "Failed to drop database."
        echo "$(date '+%Y-%m-%d %H:%M:%S') - Failed to drop: $DB_NAME" >> /var/log/db_operations.log
    fi
else
    echo "Drop cancelled."
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Drop cancelled by user: $DB_NAME" >> /var/log/db_operations.log
fi

      Error: "database is being accessed by other users"

      Error: "database is being accessed by other users"

      -- Solution 1: Terminate all connections
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp' 
AND pid <> pg_backend_pid();

-- Solution 2: Use FORCE option (PostgreSQL 13+)
DROP DATABASE myapp WITH (FORCE = true);

-- Solution 3: Wait for connections to close
-- (no new connections allowed)

      Error: "must be owner"

      Error: "must be owner"

      -- Solution 1: Connect as owner
psql -U database_owner -d postgres
DROP DATABASE myapp;

-- Solution 2: Change owner
ALTER DATABASE myapp OWNER TO postgres;
DROP DATABASE myapp;

-- Solution 3: Use superuser
psql -U postgres
DROP DATABASE myapp;

      Error: "cannot drop a template database"

      Error: "cannot drop a template database"

      -- Solution: Unmark as template
ALTER DATABASE myapp IS_TEMPLATE FALSE;
DROP DATABASE myapp;

      Error: "does not exist"

      Error: "does not exist"

      -- Solution: Use IF EXISTS
DROP DATABASE IF EXISTS myapp;

      Error: "dependent objects exist"

      Error: "dependent objects exist"

      -- Database drops should cascade automatically
-- If not, check for:
-- - Extensions depending on database
-- - Foreign data wrappers
-- - Replication slots

-- Check dependencies
SELECT * FROM pg_depend WHERE refobjid = (
    SELECT oid FROM pg_database WHERE datname = 'myapp'
);

      Example 1: Development Database Cleanup

      Example 1: Development Database Cleanup

      -- Drop old development databases
DROP DATABASE IF EXISTS myapp_dev_old;
DROP DATABASE IF EXISTS myapp_dev_v1;
DROP DATABASE IF EXISTS test_db_2023;

-- Create fresh development database
CREATE DATABASE myapp_dev;

      Example 2: Test Database Management

      Example 2: Test Database Management

      -- Before running tests
DROP DATABASE IF EXISTS myapp_test;
CREATE DATABASE myapp_test;

-- Run tests...

-- After tests
DROP DATABASE myapp_test;

      Example 3: Staging Database Rotation

      Example 3: Staging Database Rotation

      -- Rotate staging database
-- Step 1: Rename current staging to old
ALTER DATABASE myapp_staging RENAME TO myapp_staging_old;

-- Step 2: Create new staging from production
CREATE DATABASE myapp_staging TEMPLATE myapp_production;

-- Step 3: After verification, drop old staging
DROP DATABASE IF EXISTS myapp_staging_old;

      Example 4: Archive Old Data

      Example 4: Archive Old Data

      -- Instead of dropping, archive first
-- Create backup
\! pg_dump -U postgres myapp_old > /backups/myapp_old_$(date +%Y%m%d).sql

-- Create archive database
CREATE DATABASE myapp_archive;
\! pg_restore -d myapp_archive /backups/myapp_old_$(date +%Y%m%d).sql

-- Now safe to drop original
DROP DATABASE myapp_old;

      Cron-based Cleanup

      Cron-based Cleanup

      ### Step-by-Step Guide
### pgAdmin Options
0 2 * * * /usr/local/bin/cleanup_test_dbs.sh >> /var/log/test_db_cleanup.log 2>&1
      ## Important Considerations
### Template Databases

### System Databases
psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datname LIKE 'test_%' AND pg_stat_get_db_dboid(datname) IS NOT NULL;" | while read db; do
    db=$(echo $db | tr -d ' ')
    if [ -n "$db" ]; then
### Owner Permissions
        echo "Dropping old test database: $db"
        dropdb $db
    fi
done

      Database Restrictions

      Docker Environment

      Docker Environment

      ## Safety Measures
### Backup Before Dropping

CONTAINER_NAME="postgres_container"
DB_NAME=$1

echo "Dropping database $DB_NAME in container $CONTAINER_NAME"

# Terminate connections
docker exec $CONTAINER_NAME psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME' AND pid <> pg_backend_pid();"

# Drop database
docker exec $CONTAINER_NAME psql -U postgres -c "DROP DATABASE IF EXISTS $DB_NAME;"

echo "Database $DB_NAME dropped in container."

      Naming Conventions

      Naming Conventions

      -- Use clear naming to avoid accidental drops
CREATE DATABASE myapp_production;    -- Clear it's production
CREATE DATABASE myapp_staging;       -- Clear it's staging
CREATE DATABASE myapp_development;   -- Clear it's development
CREATE DATABASE myapp_test_sprint_1; -- Specific test database

-- Avoid
CREATE DATABASE app;                 -- Too generic
CREATE DATABASE db1;                 -- Not descriptive
CREATE DATABASE temp;                -- Too generic

      Protection Measures

      Protection Measures

      -- Rename database to indicate protected status
ALTER DATABASE myapp RENAME TO myapp_PROTECTED_DO_NOT_DROP;

-- UseREVOKE to prevent accidental drops
REVOKE DROP ON DATABASE myapp FROM PUBLIC;

-- Add protective comments
COMMENT ON DATABASE myapp IS 'PROTECTED: Contains critical production data. Contact DBA before any modifications.';

      Documentation

      Documentation

      -- Document database purpose and dependencies
COMMENT ON DATABASE myapp IS '
    Purpose: Main production database for application X
    Owner: Development Team
    Backup: Daily at 2 AM
    Retention: 30 days
    Contact: dba@example.com
    Last modified: 2024-01-15
';

      Verification Scripts

      Dropping databases in PostgreSQL requires careful consideration:

      Always backup before dropping and use IF EXISTS to prevent errors.