Script to migrate from old database to new database.

[bpawnzZ]

Between me and Claude 3.5 Sonnet we put our minds to it this afternoon and had this thing whooped in about an hour.

This script is 100% successful in migrating from the old database structure to the new database structure. Link to original issues thread LINK

Just posting it here. Hopefully it helps someone. should be self explanatory.

import psycopg2
from psycopg2.extras import execute_values, Json
import json

# Source database connection details
SRC_DB = {
    'host': '127.0.0.1',
    'port': '5431',
    'database': 'defaultdb',
    'user': 'teldrive',
    'password': 'secret'
}

# Destination database connection details
DEST_DB = {
    'host': '127.0.0.1',
    'port': '5432',
    'database': 'teldrive',
    'user': 'teldrive',
    'password': 'secret'
}

# List of tables to transfer (in order, considering foreign key dependencies)
TABLES = [
    'teldrive.users',
    'teldrive.channels',
    'teldrive.bots',
    'teldrive.uploads',
    'teldrive.sessions',
    'teldrive.files',
    'public.goose_db_version'
]

def adapt_dict(python_dict):
    return Json(python_dict)

psycopg2.extensions.register_adapter(dict, adapt_dict)

def connect_db(db_config):
    return psycopg2.connect(**db_config)

def get_table_data(cursor, table):
    cursor.execute(f"SELECT * FROM {table}")
    return cursor.fetchall()

def get_column_names(cursor, table):
    cursor.execute(f"""
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = %s AND table_name = %s 
        ORDER BY ordinal_position
    """, table.split('.'))
    return [col[0] for col in cursor.fetchall()]

def transfer_table(src_cursor, dest_cursor, table):
    print(f"Transferring data for table: {table}")
    
    # Get column names and types
    src_cursor.execute(f"""
        SELECT column_name, data_type 
        FROM information_schema.columns 
        WHERE table_schema = %s AND table_name = %s 
        ORDER BY ordinal_position
    """, table.split('.'))
    columns_info = src_cursor.fetchall()
    columns = [col[0] for col in columns_info]
    column_str = ', '.join(columns)
    
    # Fetch data from source
    src_cursor.execute(f"SELECT {column_str} FROM {table}")
    data = src_cursor.fetchall()
    
    if not data:
        print(f"No data to transfer for table: {table}")
        return
    
    # Prepare the INSERT statement with type casting for JSONB and numbered placeholders
    insert_columns = []
    for i, (col, data_type) in enumerate(columns_info, start=1):
        if data_type == 'jsonb':
            insert_columns.append(f"CAST(%s AS JSONB)")
        else:
            insert_columns.append(f"%s")
    insert_column_str = ', '.join(insert_columns)
    
    insert_stmt = f"INSERT INTO {table} ({column_str}) VALUES ({insert_column_str}) ON CONFLICT DO NOTHING"
    
    # Convert any JSON strings to dicts for proper handling
    processed_data = []
    for row in data:
        processed_row = list(row)
        for i, (col, data_type) in enumerate(columns_info):
            if data_type == 'jsonb':
                if isinstance(processed_row[i], str):
                    try:
                        processed_row[i] = json.dumps(json.loads(processed_row[i]))
                    except json.JSONDecodeError:
                        processed_row[i] = json.dumps({})  # Default to empty JSON object if parsing fails
                elif processed_row[i] is None:
                    processed_row[i] = json.dumps({})  # Default to empty JSON object if None
                else:
                    processed_row[i] = json.dumps(processed_row[i])  # Ensure it's a JSON string
        processed_data.append(tuple(processed_row))
    
    # Execute the INSERT
    dest_cursor.executemany(insert_stmt, processed_data)
    
    print(f"Transferred {len(data)} rows for table: {table}")

def main():
    src_conn = connect_db(SRC_DB)
    dest_conn = connect_db(DEST_DB)
    
    src_cursor = src_conn.cursor()
    dest_cursor = dest_conn.cursor()
    
    try:
        for table in TABLES:
            transfer_table(src_cursor, dest_cursor, table)
            dest_conn.commit()
        
        print("Data transfer completed successfully!")
    
    except Exception as e:
        print(f"An error occurred: {e}")
        dest_conn.rollback()
    
    finally:
        src_cursor.close()
        dest_cursor.close()
        src_conn.close()
        dest_conn.close()

if __name__ == "__main__":
    main()

[RamNikhileshNunna]

Between me and Claude 3.5 Sonnet we put our minds to it this afternoon and had this thing whooped in about an hour.

This script is 100% successful in migrating from the old database structure to the new database structure. Link to original issues thread LINK

Just posting it here. Hopefully it helps someone. should be self explanatory.

import psycopg2
from psycopg2.extras import execute_values, Json
import json

# Source database connection details
SRC_DB = {
    'host': '127.0.0.1',
    'port': '5431',
    'database': 'defaultdb',
    'user': 'teldrive',
    'password': 'secret'
}

# Destination database connection details
DEST_DB = {
    'host': '127.0.0.1',
    'port': '5432',
    'database': 'teldrive',
    'user': 'teldrive',
    'password': 'secret'
}

# List of tables to transfer (in order, considering foreign key dependencies)
TABLES = [
    'teldrive.users',
    'teldrive.channels',
    'teldrive.bots',
    'teldrive.uploads',
    'teldrive.sessions',
    'teldrive.files',
    'public.goose_db_version'
]

def adapt_dict(python_dict):
    return Json(python_dict)

psycopg2.extensions.register_adapter(dict, adapt_dict)

def connect_db(db_config):
    return psycopg2.connect(**db_config)

def get_table_data(cursor, table):
    cursor.execute(f"SELECT * FROM {table}")
    return cursor.fetchall()

def get_column_names(cursor, table):
    cursor.execute(f"""
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = %s AND table_name = %s 
        ORDER BY ordinal_position
    """, table.split('.'))
    return [col[0] for col in cursor.fetchall()]

def transfer_table(src_cursor, dest_cursor, table):
    print(f"Transferring data for table: {table}")
    
    # Get column names and types
    src_cursor.execute(f"""
        SELECT column_name, data_type 
        FROM information_schema.columns 
        WHERE table_schema = %s AND table_name = %s 
        ORDER BY ordinal_position
    """, table.split('.'))
    columns_info = src_cursor.fetchall()
    columns = [col[0] for col in columns_info]
    column_str = ', '.join(columns)
    
    # Fetch data from source
    src_cursor.execute(f"SELECT {column_str} FROM {table}")
    data = src_cursor.fetchall()
    
    if not data:
        print(f"No data to transfer for table: {table}")
        return
    
    # Prepare the INSERT statement with type casting for JSONB and numbered placeholders
    insert_columns = []
    for i, (col, data_type) in enumerate(columns_info, start=1):
        if data_type == 'jsonb':
            insert_columns.append(f"CAST(%s AS JSONB)")
        else:
            insert_columns.append(f"%s")
    insert_column_str = ', '.join(insert_columns)
    
    insert_stmt = f"INSERT INTO {table} ({column_str}) VALUES ({insert_column_str}) ON CONFLICT DO NOTHING"
    
    # Convert any JSON strings to dicts for proper handling
    processed_data = []
    for row in data:
        processed_row = list(row)
        for i, (col, data_type) in enumerate(columns_info):
            if data_type == 'jsonb':
                if isinstance(processed_row[i], str):
                    try:
                        processed_row[i] = json.dumps(json.loads(processed_row[i]))
                    except json.JSONDecodeError:
                        processed_row[i] = json.dumps({})  # Default to empty JSON object if parsing fails
                elif processed_row[i] is None:
                    processed_row[i] = json.dumps({})  # Default to empty JSON object if None
                else:
                    processed_row[i] = json.dumps(processed_row[i])  # Ensure it's a JSON string
        processed_data.append(tuple(processed_row))
    
    # Execute the INSERT
    dest_cursor.executemany(insert_stmt, processed_data)
    
    print(f"Transferred {len(data)} rows for table: {table}")

def main():
    src_conn = connect_db(SRC_DB)
    dest_conn = connect_db(DEST_DB)
    
    src_cursor = src_conn.cursor()
    dest_cursor = dest_conn.cursor()
    
    try:
        for table in TABLES:
            transfer_table(src_cursor, dest_cursor, table)
            dest_conn.commit()
        
        print("Data transfer completed successfully!")
    
    except Exception as e:
        print(f"An error occurred: {e}")
        dest_conn.rollback()
    
    finally:
        src_cursor.close()
        dest_cursor.close()
        src_conn.close()
        dest_conn.close()

if __name__ == "__main__":
    main()

I want to migrate to another pc how does this script works with that?

[doomwithdon]

Between me and Claude 3.5 Sonnet we put our minds to it this afternoon and had this thing whooped in about an hour.
This script is 100% successful in migrating from the old database structure to the new database structure. Link to original issues thread LINK
Just posting it here. Hopefully it helps someone. should be self explanatory.

import psycopg2
from psycopg2.extras import execute_values, Json
import json

# Source database connection details
SRC_DB = {
    'host': '127.0.0.1',
    'port': '5431',
    'database': 'defaultdb',
    'user': 'teldrive',
    'password': 'secret'
}

# Destination database connection details
DEST_DB = {
    'host': '127.0.0.1',
    'port': '5432',
    'database': 'teldrive',
    'user': 'teldrive',
    'password': 'secret'
}

# List of tables to transfer (in order, considering foreign key dependencies)
TABLES = [
    'teldrive.users',
    'teldrive.channels',
    'teldrive.bots',
    'teldrive.uploads',
    'teldrive.sessions',
    'teldrive.files',
    'public.goose_db_version'
]

def adapt_dict(python_dict):
    return Json(python_dict)

psycopg2.extensions.register_adapter(dict, adapt_dict)

def connect_db(db_config):
    return psycopg2.connect(**db_config)

def get_table_data(cursor, table):
    cursor.execute(f"SELECT * FROM {table}")
    return cursor.fetchall()

def get_column_names(cursor, table):
    cursor.execute(f"""
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = %s AND table_name = %s 
        ORDER BY ordinal_position
    """, table.split('.'))
    return [col[0] for col in cursor.fetchall()]

def transfer_table(src_cursor, dest_cursor, table):
    print(f"Transferring data for table: {table}")
    
    # Get column names and types
    src_cursor.execute(f"""
        SELECT column_name, data_type 
        FROM information_schema.columns 
        WHERE table_schema = %s AND table_name = %s 
        ORDER BY ordinal_position
    """, table.split('.'))
    columns_info = src_cursor.fetchall()
    columns = [col[0] for col in columns_info]
    column_str = ', '.join(columns)
    
    # Fetch data from source
    src_cursor.execute(f"SELECT {column_str} FROM {table}")
    data = src_cursor.fetchall()
    
    if not data:
        print(f"No data to transfer for table: {table}")
        return
    
    # Prepare the INSERT statement with type casting for JSONB and numbered placeholders
    insert_columns = []
    for i, (col, data_type) in enumerate(columns_info, start=1):
        if data_type == 'jsonb':
            insert_columns.append(f"CAST(%s AS JSONB)")
        else:
            insert_columns.append(f"%s")
    insert_column_str = ', '.join(insert_columns)
    
    insert_stmt = f"INSERT INTO {table} ({column_str}) VALUES ({insert_column_str}) ON CONFLICT DO NOTHING"
    
    # Convert any JSON strings to dicts for proper handling
    processed_data = []
    for row in data:
        processed_row = list(row)
        for i, (col, data_type) in enumerate(columns_info):
            if data_type == 'jsonb':
                if isinstance(processed_row[i], str):
                    try:
                        processed_row[i] = json.dumps(json.loads(processed_row[i]))
                    except json.JSONDecodeError:
                        processed_row[i] = json.dumps({})  # Default to empty JSON object if parsing fails
                elif processed_row[i] is None:
                    processed_row[i] = json.dumps({})  # Default to empty JSON object if None
                else:
                    processed_row[i] = json.dumps(processed_row[i])  # Ensure it's a JSON string
        processed_data.append(tuple(processed_row))
    
    # Execute the INSERT
    dest_cursor.executemany(insert_stmt, processed_data)
    
    print(f"Transferred {len(data)} rows for table: {table}")

def main():
    src_conn = connect_db(SRC_DB)
    dest_conn = connect_db(DEST_DB)
    
    src_cursor = src_conn.cursor()
    dest_cursor = dest_conn.cursor()
    
    try:
        for table in TABLES:
            transfer_table(src_cursor, dest_cursor, table)
            dest_conn.commit()
        
        print("Data transfer completed successfully!")
    
    except Exception as e:
        print(f"An error occurred: {e}")
        dest_conn.rollback()
    
    finally:
        src_cursor.close()
        dest_cursor.close()
        src_conn.close()
        dest_conn.close()

if __name__ == "__main__":
    main()

I want to migrate to another pc how does this script works with that?

2 Ways as far as I understand

Solution 1: Use supabase as intermediary for migration.
Step 1: Use script to move from current PC to supabase
Step 2: Use standard psql export/ import commands to import from supabase to new PC

Solution 2: Use local export import
Step 1: Export existing schema from existing PC using psql export command
Step 2: Import schema into old DB schema on new PC
Step 3: Create new DB on new PC
Step 4: Use script to migrate from old DB to new DB