将Django开发数据库从默认SQLite迁移至PostgreSQL的方法及疑问

Great question! Let’s break this down into two clear parts: migrating your default SQLite setup to PostgreSQL for local development, and addressing why SQLite is the go-to default, plus whether PostgreSQL is a solid choice for your local workflow.

Here’s a practical, step-by-step guide to make the switch smoothly:

• 1. Install and set up PostgreSQL locally
  First, get PostgreSQL running on your machine:

  • For macOS: Use Homebrew with brew install postgresql, then start the service with brew services start postgresql.
  • For Ubuntu/Debian: Run sudo apt-get install postgresql postgresql-contrib, then start the service with sudo systemctl start postgresql.
  • For Windows: Grab the official installer from the PostgreSQL website, follow the setup wizard, and ensure the service is set to start automatically.
    Next, create a database and user tailored to your project:
    Open the PostgreSQL terminal with psql postgres, then run these commands:

  CREATE USER your_project_user WITH PASSWORD 'your_secure_password';
  CREATE DATABASE your_project_db OWNER your_project_user;
  GRANT ALL PRIVILEGES ON DATABASE your_project_db TO your_project_user;
  

  Exit the terminal with \q.

• 2. Export your SQLite data (and clean it for PostgreSQL)
  SQLite’s dump syntax isn’t 100% compatible with PostgreSQL, so we’ll need to tweak things:

  • Export the SQLite database to a SQL file:

    sqlite3 your_local_sqlite_db.sqlite .dump > sqlite_dump.sql
    

  • Clean up the dump file:
    • Replace SQLite’s AUTOINCREMENT with PostgreSQL’s SERIAL (or GENERATED AS IDENTITY for newer versions).
    • Convert SQLite integer-based booleans (0/1) to PostgreSQL’s BOOLEAN type.
    • Remove any SQLite-specific commands like BEGIN TRANSACTION or COMMIT if they cause issues during import.
    • Fix table/column name casing: PostgreSQL is case-sensitive by default, so ensure names match what your project expects (or wrap them in double quotes if needed).

• 3. Update your project’s database configuration
  Point your app to the new PostgreSQL database:

  • For Django: In settings.py, update the DATABASES section:

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'your_project_db',
            'USER': 'your_project_user',
            'PASSWORD': 'your_secure_password',
            'HOST': 'localhost',
            'PORT': '5432',
        }
    }
    

  • For Flask/SQLAlchemy: Update the connection string:

    app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://your_project_user:your_secure_password@localhost/your_project_db'
    

  Adjust for whatever framework or ORM you’re using—most have straightforward PostgreSQL setup docs.

• 4. Import the cleaned data into PostgreSQL
  Run this command to load your data into the new database:

  psql -U your_project_user -d your_project_db -f sqlite_dump.sql
  

  If you hit errors, double-check the cleaned dump file for remaining SQLite-specific syntax.

• 5. Test thoroughly
  Fire up your local development server and test all core features: check data loads correctly, forms submit without issues, and any custom SQL queries work as expected. Pay extra attention to things like date functions, string concatenation (PostgreSQL uses || instead of +), and constraint enforcement (PostgreSQL enforces foreign keys by default, unlike SQLite).

Why SQLite is default #

SQLite’s popularity as a local dev default boils down to simplicity:

• Zero configuration: No server to install, start, or manage—just a single file. Perfect for beginners or quick prototyping where you don’t want to mess with database setup.
• Extremely lightweight: Uses minimal system resources, so it’s great for small projects or when you’re running multiple tools locally.
• No overhead: No user permissions, port management, or maintenance required. It’s "fire and forget" for basic use cases.

Is PostgreSQL recommended locally? #

Absolutely—especially if your production environment uses PostgreSQL. Here’s why:

• Eliminates environment mismatch: SQL syntax, data types, and constraint behavior can differ between SQLite and PostgreSQL. For example, SQLite ignores foreign key constraints by default, while PostgreSQL enforces them; date/time functions also vary widely. Matching production avoids those frustrating "works on my machine" bugs.
• Full feature parity: You’ll be able to test production-specific features like JSONB columns, full-text search, or advanced query optimizations locally, which you can’t do with SQLite.
• Easy setup with modern tools: If you don’t want to manage a local PostgreSQL service, use Docker to spin up a container in seconds:

  docker run -d -p 5432:5432 -e POSTGRES_USER=your_project_user -e POSTGRES_PASSWORD=your_secure_password -e POSTGRES_DB=your_project_db postgres
  

  This skips the manual installation entirely.

The only minor downside is the initial setup overhead compared to SQLite, but that’s a tiny price to pay for aligning your local dev environment with production.

内容的提问来源于stack exchange，提问作者Zorgan