Difference between revisions of "Колдонуучу:DockerIssue WNU PostgreSQL Container is Huge"

Кыргыз Энциклопедия Жана Терминология Борбору дан
Jump to navigation Jump to search
(Created page with "Slm")
 
1 -сап: 1 -сап:
Slm
=how to check if the PostgreSQL database contains something=
 
== **Multiple Ways to Check PostgreSQL Database Contents:** ==
 
=== **Method 1: Quick Size Check (Best First Step)** ===
```bash
# Replace with your actual PostgreSQL container name
PG_CONTAINER="wnu_postgres_1"
 
echo "=== DATABASE SIZE CHECK ==="
docker exec $PG_CONTAINER psql -U postgres -c "
SELECT
    datname as database,
    pg_size_pretty(pg_database_size(datname)) as size,
    pg_database_size(datname) as bytes
FROM pg_database
WHERE datname NOT IN ('template0', 'template1', 'postgres')
ORDER BY bytes DESC;
" 2>/dev/null || echo "Cannot connect to PostgreSQL"
```
 
=== **Method 2: Check If Any User Tables Exist** ===
```bash
echo "=== USER TABLE CHECK ==="
docker exec $PG_CONTAINER psql -U postgres -c "
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as table_size,
    n_live_tup as row_count
FROM pg_tables
LEFT JOIN pg_stat_user_tables USING (schemaname, tablename)
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
" 2>/dev/null | head -20
```
 
=== **Method 3: Comprehensive Content Check Script** ===
```bash
cat > ~/check_pg_content.sh << 'EOF'
#!/bin/bash
# PostgreSQL Content Check Script
 
PG_CONTAINER=${1:-wnu_postgres_1}  # Use first argument or default
 
echo "=== POSTGRESQL CONTENT ANALYSIS ==="
echo "Container: $PG_CONTAINER"
echo ""
 
# Check if container exists and is running
if ! docker ps | grep -q $PG_CONTAINER; then
    echo "❌ Container '$PG_CONTAINER' not running!"
    echo "Try: docker start $PG_CONTAINER"
    exit 1
fi
 
# 1. List all databases
echo "1. 📊 DATABASES:"
echo "---------------"
docker exec $PG_CONTAINER psql -U postgres -c "\l" 2>/dev/null || {
    echo "Failed to list databases. Testing connection..."
    docker exec $PG_CONTAINER pg_isready -U postgres
}
 
# 2. Check for user databases (excluding system DBs)
echo -e "\n2. 👤 USER DATABASES (non-system):"
echo "---------------------------------"
docker exec $PG_CONTAINER psql -U postgres -t -c "
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) as size,
    CASE
        WHEN pg_database_size(datname) > 1000000 THEN '⚠️ HAS DATA'
        WHEN pg_database_size(datname) > 100000 THEN '📦 SOME DATA'
        ELSE '✅ EMPTY/TINY'
    END as status
FROM pg_database
WHERE datname NOT IN ('template0', 'template1', 'postgres')
ORDER BY pg_database_size(datname) DESC;
" 2>/dev/null
 
# 3. Check tables in each user database
echo -e "\n3. 📋 TABLES IN USER DATABASES:"
echo "--------------------------------"
docker exec $PG_CONTAINER psql -U postgres -t -c "
SELECT datname
FROM pg_database
WHERE datname NOT IN ('template0', 'template1', 'postgres')
AND datistemplate = false;
" 2>/dev/null | while read db; do
    if [ ! -z "$db" ]; then
        echo -e "\n  Database: $db"
        docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "
        SELECT
            schemaname || '.' || tablename as table_name,
            pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as size,
            (SELECT n_live_tup FROM pg_stat_user_tables WHERE schemaname = t.schemaname AND tablename = t.tablename) as rows
        FROM pg_tables t
        WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
        ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
        LIMIT 5;
        " 2>/dev/null | while read line; do
            [ ! -z "$line" ] && echo "    $line"
        done
    fi
done
 
# 4. Sample data from largest table
echo -e "\n4. 🔍 SAMPLE DATA CHECK:"
echo "------------------------"
LARGEST_TABLE=$(docker exec $PG_CONTAINER psql -U postgres -t -c "
SELECT schemaname || '.' || tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 1;
" 2>/dev/null | tr -d ' ')
 
if [ ! -z "$LARGEST_TABLE" ]; then
    echo "  Largest table: $LARGEST_TABLE"
    echo "  First 2 rows:"
    docker exec $PG_CONTAINER psql -U postgres -c "
    SELECT * FROM $LARGEST_TABLE LIMIT 2;
    " 2>/dev/null | tail -n +3 | head -5
else
    echo "  No user tables found"
fi
 
# 5. Check for common CMS tables (WordPress, Django, etc.)
echo -e "\n5. 🏗️ CMS DETECTION:"
echo "-------------------"
for db in $(docker exec $PG_CONTAINER psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres');" 2>/dev/null); do
    echo "  Checking $db:"
   
    # WordPress
    if docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = 'wp_posts');" 2>/dev/null | grep -q t; then
        echo "    ✅ WordPress detected (wp_posts table exists)"
        POST_COUNT=$(docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "SELECT COUNT(*) FROM wp_posts;" 2>/dev/null | tr -d ' ')
        echo "    Posts: $POST_COUNT"
    fi
   
    # Django
    if docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = 'django_migrations');" 2>/dev/null | grep -q t; then
        echo "    ✅ Django detected"
    fi
   
    # Laravel
    if docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = 'migrations');" 2>/dev/null | grep -q t; then
        echo "    ✅ Laravel detected"
    fi
done
 
echo -e "\n=== SUMMARY ==="
TOTAL_USER_DATA=$(docker exec $PG_CONTAINER psql -U postgres -t -c "
SELECT SUM(pg_database_size(datname))
FROM pg_database
WHERE datname NOT IN ('template0', 'template1', 'postgres');
" 2>/dev/null | tr -d ' ')
 
if [ ! -z "$TOTAL_USER_DATA" ] && [ "$TOTAL_USER_DATA" -gt 0 ]; then
    echo "📦 Total user data: $(echo $TOTAL_USER_DATA | numfmt --to=iec)"
    if [ "$TOTAL_USER_DATA" -lt 1000000 ]; then  # < 1MB
        echo "✅ Minimal data - safe to recreate fresh"
    else
        echo "⚠️  Significant data - consider backup"
    fi
else
    echo "✅ No user data found - PostgreSQL is empty"
fi
EOF
 
chmod +x ~/check_pg_content.sh
./check_pg_content.sh wnu_postgres_1
```
 
=== **Method 4: Quick One-Liner Check** ===
```bash
# Ultra-quick: Just check if any user databases exist
docker exec wnu_postgres_1 psql -U postgres -t -c "
SELECT 'Found: ' || COUNT(*) || ' user databases'
FROM pg_database
WHERE datname NOT IN ('template0', 'template1', 'postgres')
AND pg_database_size(datname) > 10000;" 2>/dev/null || echo "Cannot check"
```
 
=== **Method 5: Interactive Check** ===
```bash
# Interactive script
cat > ~/interactive_pg_check.sh << 'EOF'
#!/bin/bash
read -p "Enter PostgreSQL container name: " PG_CONTAINER
 
echo "Checking $PG_CONTAINER..."
echo "1. Testing connection..."
if docker exec $PG_CONTAINER pg_isready -U postgres; then
    echo "✅ Connected"
   
    echo -e "\n2. What would you like to check?"
    echo "  a) List all databases"
    echo "  b) Check table sizes"
    echo "  c) Sample table contents"
    echo "  d) Everything"
    read -p "Choice (a/b/c/d): " choice
   
    case $choice in
        a|A)
            docker exec $PG_CONTAINER psql -U postgres -c "\l"
            ;;
        b|B)
            docker exec $PG_CONTAINER psql -U postgres -c "
            SELECT
                datname,
                pg_size_pretty(pg_database_size(datname)) as size
            FROM pg_database
            ORDER BY pg_database_size(datname) DESC;"
            ;;
        c|C)
            read -p "Enter database name: " dbname
            docker exec $PG_CONTAINER psql -U postgres -d "$dbname" -c "\dt"
            read -p "Enter table name to see sample: " tablename
            docker exec $PG_CONTAINER psql -U postgres -d "$dbname" -c "SELECT * FROM $tablename LIMIT 3;"
            ;;
        d|D)
            ./check_pg_content.sh $PG_CONTAINER
            ;;
    esac
else
    echo "❌ Cannot connect to PostgreSQL"
fi
EOF
 
chmod +x ~/interactive_pg_check.sh
./interactive_pg_check.sh
```
 
=== **Method 6: Check for Specific Content (WordPress, etc.)** ===
```bash
# Check for WordPress tables
docker exec wnu_postgres_1 psql -U postgres -t -c "
SELECT
    'WordPress tables: ' || COUNT(*)
FROM pg_tables
WHERE tablename LIKE 'wp_%';" 2>/dev/null
 
# Check for any user data at all
docker exec wnu_postgres_1 psql -U postgres -t -c "
SELECT
    CASE
        WHEN COUNT(*) > 0 THEN '⚠️ Database contains user data'
        ELSE '✅ Database is empty (no user tables)'
    END
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');" 2>/dev/null
```
 
== 📊 **Interpretation Guide:** ==
 
=== **If you see:** ===
```
database | size  | bytes 
----------+-------+--------
appdb    | 15 MB | 15728640
```
→ **Has data** (15MB of user data)
 
=== **If you see:** ===
```
database | size | bytes 
----------+------+--------
(0 rows)
```
→ **No user databases** (only system DBs exist)
 
=== **If you see:** ===
```
datname | size  |  status 
---------+-------+----------
mydb    | 728kB | ✅ EMPTY/TINY
```
→ **Minimal data** (likely just schema, no real content)
 
== 🎯 **For Your Specific Case (Static Landing Page):** ==
 
**Run this definitive check:**
```bash
# Replace with your actual container name
CONTAINER="wnu_postgres_1"
 
echo "=== DEFINITIVE CHECK FOR STATIC SITE ==="
echo "If this shows empty/minimal data → safe to remove"
echo ""
 
docker exec $CONTAINER psql -U postgres -c "
WITH user_dbs AS (
    SELECT datname, pg_database_size(datname) as size_bytes
    FROM pg_database
    WHERE datname NOT IN ('template0', 'template1', 'postgres')
)
SELECT
    CASE
        WHEN COUNT(*) = 0 THEN '✅ NO user databases found'
        WHEN SUM(size_bytes) < 1000000 THEN CONCAT('✅ MINIMAL data: ', COUNT(*), ' databases, total ', pg_size_pretty(SUM(size_bytes)))
        ELSE CONCAT('⚠️  HAS DATA: ', COUNT(*), ' databases, total ', pg_size_pretty(SUM(size_bytes)))
    END as verdict
FROM user_dbs;
" 2>/dev/null || echo "Cannot check - container may not be running"
```
 
== 💡 **Quick Decision Flow:** ==
 
1. **Run Method 1** (size check)
2. **If size > 10MB** → run Method 2 (table check)
3. **If tables exist** → run Method 3 (detailed analysis)
4. **If < 1MB or empty** → safe to remove/recreate fresh
 
**For your static landing page PostgreSQL, I predict it will show as empty or < 1MB!**

11:46, 10 Февраль (Бирдин айы) 2026 -деги абалы

how to check if the PostgreSQL database contains something

**Multiple Ways to Check PostgreSQL Database Contents:**

**Method 1: Quick Size Check (Best First Step)**

```bash

  1. Replace with your actual PostgreSQL container name

PG_CONTAINER="wnu_postgres_1"

echo "=== DATABASE SIZE CHECK ===" docker exec $PG_CONTAINER psql -U postgres -c " SELECT

   datname as database,
   pg_size_pretty(pg_database_size(datname)) as size,
   pg_database_size(datname) as bytes

FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres') ORDER BY bytes DESC; " 2>/dev/null || echo "Cannot connect to PostgreSQL" ```

**Method 2: Check If Any User Tables Exist**

```bash echo "=== USER TABLE CHECK ===" docker exec $PG_CONTAINER psql -U postgres -c " SELECT

   schemaname,
   tablename, 
   pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as table_size,
   n_live_tup as row_count

FROM pg_tables LEFT JOIN pg_stat_user_tables USING (schemaname, tablename) WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC; " 2>/dev/null | head -20 ```

**Method 3: Comprehensive Content Check Script**

```bash cat > ~/check_pg_content.sh << 'EOF'

  1. !/bin/bash
  2. PostgreSQL Content Check Script

PG_CONTAINER=${1:-wnu_postgres_1} # Use first argument or default

echo "=== POSTGRESQL CONTENT ANALYSIS ===" echo "Container: $PG_CONTAINER" echo ""

  1. Check if container exists and is running

if ! docker ps | grep -q $PG_CONTAINER; then

   echo "❌ Container '$PG_CONTAINER' not running!"
   echo "Try: docker start $PG_CONTAINER"
   exit 1

fi

  1. 1. List all databases

echo "1. 📊 DATABASES:" echo "---------------" docker exec $PG_CONTAINER psql -U postgres -c "\l" 2>/dev/null || {

   echo "Failed to list databases. Testing connection..."
   docker exec $PG_CONTAINER pg_isready -U postgres

}

  1. 2. Check for user databases (excluding system DBs)

echo -e "\n2. 👤 USER DATABASES (non-system):" echo "---------------------------------" docker exec $PG_CONTAINER psql -U postgres -t -c " SELECT

   datname,
   pg_size_pretty(pg_database_size(datname)) as size,
   CASE 
       WHEN pg_database_size(datname) > 1000000 THEN '⚠️ HAS DATA'
       WHEN pg_database_size(datname) > 100000 THEN '📦 SOME DATA'
       ELSE '✅ EMPTY/TINY'
   END as status

FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres') ORDER BY pg_database_size(datname) DESC; " 2>/dev/null

  1. 3. Check tables in each user database

echo -e "\n3. 📋 TABLES IN USER DATABASES:" echo "--------------------------------" docker exec $PG_CONTAINER psql -U postgres -t -c " SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres') AND datistemplate = false; " 2>/dev/null | while read db; do

   if [ ! -z "$db" ]; then
       echo -e "\n  Database: $db"
       docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "
       SELECT 
           schemaname || '.' || tablename as table_name,
           pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as size,
           (SELECT n_live_tup FROM pg_stat_user_tables WHERE schemaname = t.schemaname AND tablename = t.tablename) as rows
       FROM pg_tables t
       WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
       ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
       LIMIT 5;
       " 2>/dev/null | while read line; do
           [ ! -z "$line" ] && echo "    $line"
       done
   fi

done

  1. 4. Sample data from largest table

echo -e "\n4. 🔍 SAMPLE DATA CHECK:" echo "------------------------" LARGEST_TABLE=$(docker exec $PG_CONTAINER psql -U postgres -t -c " SELECT schemaname || '.' || tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC LIMIT 1; " 2>/dev/null | tr -d ' ')

if [ ! -z "$LARGEST_TABLE" ]; then

   echo "  Largest table: $LARGEST_TABLE"
   echo "  First 2 rows:"
   docker exec $PG_CONTAINER psql -U postgres -c "
   SELECT * FROM $LARGEST_TABLE LIMIT 2;
   " 2>/dev/null | tail -n +3 | head -5

else

   echo "  No user tables found"

fi

  1. 5. Check for common CMS tables (WordPress, Django, etc.)

echo -e "\n5. 🏗️ CMS DETECTION:" echo "-------------------" for db in $(docker exec $PG_CONTAINER psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres');" 2>/dev/null); do

   echo "  Checking $db:"
   
   # WordPress
   if docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = 'wp_posts');" 2>/dev/null | grep -q t; then
       echo "    ✅ WordPress detected (wp_posts table exists)"
       POST_COUNT=$(docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "SELECT COUNT(*) FROM wp_posts;" 2>/dev/null | tr -d ' ')
       echo "    Posts: $POST_COUNT"
   fi
   
   # Django
   if docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = 'django_migrations');" 2>/dev/null | grep -q t; then
       echo "    ✅ Django detected"
   fi
   
   # Laravel
   if docker exec $PG_CONTAINER psql -U postgres -d "$db" -t -c "SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = 'migrations');" 2>/dev/null | grep -q t; then
       echo "    ✅ Laravel detected"
   fi

done

echo -e "\n=== SUMMARY ===" TOTAL_USER_DATA=$(docker exec $PG_CONTAINER psql -U postgres -t -c " SELECT SUM(pg_database_size(datname)) FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres'); " 2>/dev/null | tr -d ' ')

if [ ! -z "$TOTAL_USER_DATA" ] && [ "$TOTAL_USER_DATA" -gt 0 ]; then

   echo "📦 Total user data: $(echo $TOTAL_USER_DATA | numfmt --to=iec)"
   if [ "$TOTAL_USER_DATA" -lt 1000000 ]; then  # < 1MB
       echo "✅ Minimal data - safe to recreate fresh"
   else
       echo "⚠️  Significant data - consider backup"
   fi

else

   echo "✅ No user data found - PostgreSQL is empty"

fi EOF

chmod +x ~/check_pg_content.sh ./check_pg_content.sh wnu_postgres_1 ```

**Method 4: Quick One-Liner Check**

```bash

  1. Ultra-quick: Just check if any user databases exist

docker exec wnu_postgres_1 psql -U postgres -t -c " SELECT 'Found: ' || COUNT(*) || ' user databases' FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres') AND pg_database_size(datname) > 10000;" 2>/dev/null || echo "Cannot check" ```

**Method 5: Interactive Check**

```bash

  1. Interactive script

cat > ~/interactive_pg_check.sh << 'EOF'

  1. !/bin/bash

read -p "Enter PostgreSQL container name: " PG_CONTAINER

echo "Checking $PG_CONTAINER..." echo "1. Testing connection..." if docker exec $PG_CONTAINER pg_isready -U postgres; then

   echo "✅ Connected"
   
   echo -e "\n2. What would you like to check?"
   echo "   a) List all databases"
   echo "   b) Check table sizes"
   echo "   c) Sample table contents"
   echo "   d) Everything"
   read -p "Choice (a/b/c/d): " choice
   
   case $choice in
       a|A)
           docker exec $PG_CONTAINER psql -U postgres -c "\l"
           ;;
       b|B)
           docker exec $PG_CONTAINER psql -U postgres -c "
           SELECT 
               datname,
               pg_size_pretty(pg_database_size(datname)) as size
           FROM pg_database 
           ORDER BY pg_database_size(datname) DESC;"
           ;;
       c|C)
           read -p "Enter database name: " dbname
           docker exec $PG_CONTAINER psql -U postgres -d "$dbname" -c "\dt"
           read -p "Enter table name to see sample: " tablename
           docker exec $PG_CONTAINER psql -U postgres -d "$dbname" -c "SELECT * FROM $tablename LIMIT 3;"
           ;;
       d|D)
           ./check_pg_content.sh $PG_CONTAINER
           ;;
   esac

else

   echo "❌ Cannot connect to PostgreSQL"

fi EOF

chmod +x ~/interactive_pg_check.sh ./interactive_pg_check.sh ```

**Method 6: Check for Specific Content (WordPress, etc.)**

```bash

  1. Check for WordPress tables

docker exec wnu_postgres_1 psql -U postgres -t -c " SELECT

   'WordPress tables: ' || COUNT(*) 

FROM pg_tables WHERE tablename LIKE 'wp_%';" 2>/dev/null

  1. Check for any user data at all

docker exec wnu_postgres_1 psql -U postgres -t -c " SELECT

   CASE 
       WHEN COUNT(*) > 0 THEN '⚠️ Database contains user data'
       ELSE '✅ Database is empty (no user tables)'
   END

FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema');" 2>/dev/null ```

📊 **Interpretation Guide:**

**If you see:**

```

database | size  | bytes  

+-------+--------

appdb    | 15 MB | 15728640

``` → **Has data** (15MB of user data)

**If you see:**

```

database | size | bytes  

+------+--------

(0 rows) ``` → **No user databases** (only system DBs exist)

**If you see:**

```

datname | size  |  status   

+-------+----------

mydb    | 728kB | ✅ EMPTY/TINY

``` → **Minimal data** (likely just schema, no real content)

🎯 **For Your Specific Case (Static Landing Page):**

    • Run this definitive check:**

```bash

  1. Replace with your actual container name

CONTAINER="wnu_postgres_1"

echo "=== DEFINITIVE CHECK FOR STATIC SITE ===" echo "If this shows empty/minimal data → safe to remove" echo ""

docker exec $CONTAINER psql -U postgres -c " WITH user_dbs AS (

   SELECT datname, pg_database_size(datname) as size_bytes
   FROM pg_database 
   WHERE datname NOT IN ('template0', 'template1', 'postgres')

) SELECT

   CASE 
       WHEN COUNT(*) = 0 THEN '✅ NO user databases found'
       WHEN SUM(size_bytes) < 1000000 THEN CONCAT('✅ MINIMAL data: ', COUNT(*), ' databases, total ', pg_size_pretty(SUM(size_bytes)))
       ELSE CONCAT('⚠️  HAS DATA: ', COUNT(*), ' databases, total ', pg_size_pretty(SUM(size_bytes)))
   END as verdict

FROM user_dbs; " 2>/dev/null || echo "Cannot check - container may not be running" ```

💡 **Quick Decision Flow:**

1. **Run Method 1** (size check) 2. **If size > 10MB** → run Method 2 (table check) 3. **If tables exist** → run Method 3 (detailed analysis) 4. **If < 1MB or empty** → safe to remove/recreate fresh

    • For your static landing page PostgreSQL, I predict it will show as empty or < 1MB!**