Django本地PostgreSQL迁移至服务器的可靠方案及问题排查

Hey there, let's tackle your Django deployment and database headaches one by one. I've been in similar spots when moving local Django projects to servers, so I know how frustrating this can be.

1. Modified Pip Dependencies (Your Questions 1 & 2) #

First, let's address the dependency modification problem—this is a common pitfall when starting out, and fixing it will eliminate a lot of future sync issues.

Answer to Question 2 #

Absolutely yes—you should move any modified pip packages into your project's standard Django directory structure and treat them as your own apps. When you modify a pip-installed package in your virtual environment, those changes live only on your local machine and won't be tracked by Git or deployed to the server. By moving the code into your project, you can commit all changes to version control and ensure consistency across environments.

Answer to Question 1 #

To avoid this issue entirely:

• Never edit packages directly in your virtual environment: Instead, either:
  • Fork the original package's repository, make your changes in the fork, then update your requirements.txt to point to your forked repo (e.g., git+https://github.com/yourusername/forked-podcast-package.git@your-modified-branch).
  • Copy the package's full code into your project's apps folder, rename it if needed (to avoid conflicts), update INSTALLED_APPS in settings.py to reference this local app instead of the pip-installed version, and commit it to Git.
• Always track migration files: Make sure every makemigrations run locally generates files that are committed to Git—these are critical for keeping your database schema in sync across environments.

2. Database Backup/Restore & Migration Troubles #

Your current errors stem from two main issues: inconsistent backup/restore commands, and mismatched migration history between your local and server environments.

Fixing the Column podcast_show.type does not exist Error #

This happened because your server was running the unmodified version of the podcast package (from requirements.txt) while your restored database had the schema from your locally modified version. Now that you've moved the modified podcast app into your Git repo, double-check your server's settings.py to ensure INSTALLED_APPS lists your local podcast app (not the pip-installed one) and that you've pulled the latest code from Git.

Fixing pg_restore Sequence Errors #

The sequence errors occur because:

• You used the -C flag in pg_restore, which tries to create the database again (but you already created it manually).
• The backup includes commands to reset sequences, but those sequences don't exist yet in the new server database.

Fix this by re-running the restore command without -C, and explicitly targeting the public schema:

pg_restore -h the-host-address-for-my-postgresql-database -p 11111 -U super -W -d app -n public appBU

After restoring, fix all sequence mismatches with Django's built-in command:

python manage.py sqlsequencereset podcast [other-app-names] | python manage.py dbshell

Replace podcast with all your Django app names that use auto-incrementing IDs (like django_comments if you're using that app).

Fixing the IntegrityError During Migrate #

This error happens because your server's django_migrations table has conflicting or corrupted entries (from restoring the local database). Since your restored database already has the full schema (including all migrations applied locally), you don't need to run migrations again—you just need to mark them as applied on the server:

python manage.py migrate --fake

This command updates the django_migrations table to show all migrations as completed without modifying the database schema. After running this, use python manage.py showmigrations to confirm all migrations are marked with [X].

To avoid these issues in the future, follow this step-by-step workflow:

Pre-Deployment: Code Consistency #

1. Lock in dependencies: Ensure all modified packages are either forked and referenced in requirements.txt or moved into your project's local apps directory.
2. Sync migrations: Run python manage.py makemigrations locally, test them, commit all migration files to Git, and pull the latest code to your server.
3. Activate the server's virtual environment: Make sure you're using the correct virtual env on the server and install any new dependencies with pip install -r requirements.txt.

Local Database Backup #

Use the command-line pg_dump tool for a consistent backup (avoid relying solely on pgAdmin GUI):

pg_dump -h localhost -U your-local-db-user -d your-local-db-name -Fc -f app_backup.dump

• -Fc creates a custom-format dump that's efficient and easy to restore.
• Test restoring this backup locally first to confirm it works before uploading to the server.

Server Database Setup & Restore #

1. Create the database and user:

   CREATE DATABASE app;
   CREATE USER username WITH PASSWORD 'password';
   ALTER ROLE username SET client_encoding TO 'utf8';
   ALTER ROLE username SET default_transaction_isolation TO 'read committed';
   ALTER ROLE username SET timezone TO 'UTC';
   GRANT ALL PRIVILEGES ON DATABASE app TO username;
   GRANT ALL PRIVILEGES ON SCHEMA public TO username; -- Critical for sequences/tables
   

2. Restore the backup:

   pg_restore -h your-server-db-host -p your-port -U super-user -W -d app -n public app_backup.dump
   

3. Reset sequences:

   python manage.py sqlsequencereset your-app-names | python manage.py dbshell
   

Migrations Sync #

1. Mark migrations as applied:

   python manage.py migrate --fake
   

2. Verify:
   • Run python manage.py showmigrations to confirm all migrations are applied.
   • Test your app thoroughly to ensure all pages load and database queries work as expected.

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