3. Our plan

You have seen the four parts of the migration. This chapter is how you do each one. Seven more sections take you from "PostgreSQL is installed" to "the weather cache is running on PostgreSQL with versioned schema migrations".

Chapter plan

What you will build

  • first_postgres.py — your first connection to a running PostgreSQL server
  • migrate_weather.py — a Python migration script that moves SQLite data to PostgreSQL with progress tracking and row-count verification
  • weather_app.py — application code converted from sqlite3 to psycopg2, with connection pooling
  • alembic/ — a version-controlled schema migrations directory backed by alembic.ini

What you will learn

  • Plan a database migration using the four-phase workflow: Preparation, Setup, Execution, Validation
  • Map SQLite types to appropriate PostgreSQL types: SERIAL, VARCHAR, NUMERIC, TIMESTAMPTZ, JSONB
  • Write Python migration scripts with progress tracking, type conversion, and row-count verification
  • Convert applications from sqlite3 to psycopg2: placeholders, explicit commits, connection pooling
  • Use Alembic to version-control schema changes the way Git version-controls code

Migration skills matter because real applications must change without losing data or breaking existing behaviour. By the end of this chapter, you will have carried out that work once, with a working example that demonstrates the process clearly.

Next, you will install PostgreSQL, create a database, and run your first query from Python. Proof before theory.