0

I've not found a huge amount of guidance out there on this issue - and when I do find guidance there's not much to explain away some of the problems I'm having. I'm self-learning Python and Django, which is going fine, but deploying to a server is...hell. It would be great if there was someone out there with a bullet proof workflow for backing up a Django DB, and restoring to a server. With reference to virtual environments, migrations, --fake and -initial.

For my own sake - and for anyone reading - I'm going to go through exactly what I've done, and see if I can see where the mistakes are - because I'm at the brick wall stage.

The Problem

'It works on my machine' pretty much sums up exactly where I am at. It works fine. ON MY MACHINE.

I've managed to get to a point where the database (Postgresql) is restored to my server - Python Anywhere. And my server instance of Django is talking to the database, and some data is being served. However, there are then some migration issues which persist, and ultimately cause several fatal errors about certain tables not existing - like this:

Column podcast_show.type does not exist
LINE 1: SELECT "podcast_show"."id", "podcast_show"."type", "podcast_...

Running makemigrations / migrate gets me errors like this one

django.db.utils.IntegrityError: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, curate, 0005_item_tags, 2019-04-29 12:38:32.000168+00).

The Environment

I'm on windows, developing locally on Atom. I have a virtual environment with Django 2.1 installed. I'm using GIT for version control (which I only just understand), and I have some pypy installed django applications (in the virtualENV), which thinking about it now, I have made direct changes to.

This was the first 'aha' moment where I thought I'd cracked it. The changes I've made to pypy packages in myLocalVirtualEnv (including models) were NOT included in my GIT repo which was pushed to the server. So when I've pip installed all the dependencies from requirements.txt on the server, I've got fresh installs of these apps, rather than their modified versions.

* Question 1) What's the proper workflow to avoid this? * Once I start modifying a pypy package, which is a dependency, should I move it out of the virtualenv, and into my standard django folder structure, and work on it as though it's my own app?

The Backup

This seems to be the easy bit. I've used pgAdmin4 on my local machine - right click, backup, choose an output, and off it goes. I'm doing that live right now - from my working local project.

Done. I now have a large DB(3GB uncompressed - is this huge?) database file 'appBU'. I now use SFTP(filezilla) to send this file across to my server, putting it in the same directory as my top App level. home/user/App/app

Now in a postgresql console, I make a new database.

CREATE DATABASE app;

Then I make a user for the database. I don't always do this, if I've dropped the database and tried to backup again, I just use pre-existing users. In that case, I run the command DROP DATABASE app; followed by CREATE DATABASE app; so I'm ready with a fresh database to restore to. Anyway, the users:

CREATE USER username WITH PASSWORD 'password';

ALTER ROLE username WITH PASSWORD 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;

Now I restore the database to the newly created empty database I just made - this is from inside a bash console (not psql console - that took a while to workout!)

pg_restore -h the-host-address-for-my-postgresql-database -p 11111 -U super -W  -C app < appBU

This is where the problems start, because pg_restore is constantly unhappy as it whirrs away, these are the kinds of errors I'm getting:


pg_restore: [archiver (db)] Error from TOC entry 3347; 0 0 SEQUENCE SET django_comment_flags_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.django_comment_flags_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.django_comment_flags_id_seq...
                                 ^
    Command was: SELECT pg_catalog.setval('public.django_comment_flags_id_seq', 1, false);
pg_restore: [archiver (db)] Error from TOC entry 3348; 0 0 SEQUENCE SET django_comments_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.django_comments_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.django_comments_id_seq', 17...
                                 ^
    Command was: SELECT pg_catalog.setval('public.django_comments_id_seq', 17, true);
pg_restore: [archiver (db)] Error from TOC entry 3349; 0 0 SEQUENCE SET django_content_type_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.django_content_type_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.django_content_type_id_seq'...
                                 ^
    Command was: SELECT pg_catalog.setval('public.django_content_type_id_seq', 22, true);
pg_restore: [archiver (db)] Error from TOC entry 3350; 0 0 SEQUENCE SET django_migrations_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.django_migrations_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.django_migrations_id_seq', ...
                                 ^
    Command was: SELECT pg_catalog.setval('public.django_migrations_id_seq', 108, true);
pg_restore: [archiver (db)] Error from TOC entry 3351; 0 0 SEQUENCE SET django_site_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.django_site_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.django_site_id_seq', 1, tru...
                                 ^
pg_restore: [archiver (db)] Error from TOC entry 3352; 0 0 SEQUENCE SET podcast_category_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_category_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_category_id_seq', 1...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_category_id_seq', 1098, true);



pg_restore: [archiver (db)] Error from TOC entry 3353; 0 0 SEQUENCE SET podcast_enclosure_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_enclosure_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_enclosure_id_seq', ...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_enclosure_id_seq', 1, false);



pg_restore: [archiver (db)] Error from TOC entry 3354; 0 0 SEQUENCE SET podcast_episode_guests_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_episode_guests_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_episode_guests_id_s...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_episode_guests_id_seq', 1, false);



pg_restore: [archiver (db)] Error from TOC entry 3355; 0 0 SEQUENCE SET podcast_episode_hosts_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_episode_hosts_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_episode_hosts_id_se...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_episode_hosts_id_seq', 1, false);



pg_restore: [archiver (db)] Error from TOC entry 3356; 0 0 SEQUENCE SET podcast_episode_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_episode_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_episode_id_seq', 19...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_episode_id_seq', 1995295, true);


pg_restore: [archiver (db)] Error from TOC entry 3357; 0 0 SEQUENCE SET podcast_show_categories_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_show_categories_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_show_categories_id_...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_show_categories_id_seq', 3873, true);
pg_restore: [archiver (db)] Error from TOC entry 3358; 0 0 SEQUENCE SET podcast_show_hosts_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_show_hosts_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_show_hosts_id_seq',...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_show_hosts_id_seq', 1, false);
pg_restore: [archiver (db)] Error from TOC entry 3359; 0 0 SEQUENCE SET podcast_show_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_show_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_show_id_seq', 27121...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_show_id_seq', 27121, true);
pg_restore: [archiver (db)] Error from TOC entry 3360; 0 0 SEQUENCE SET podcast_show_itunes_categories_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_show_itunes_categories_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_show_itunes_categor...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_show_itunes_categories_id_seq', 1, false);
pg_restore: [archiver (db)] Error from TOC entry 3361; 0 0 SEQUENCE SET podcast_speaker_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.podcast_speaker_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.podcast_speaker_id_seq', 1,...
                                 ^
    Command was: SELECT pg_catalog.setval('public.podcast_speaker_id_seq', 1, false);
pg_restore: [archiver (db)] Error from TOC entry 3362; 0 0 SEQUENCE SET taggit_tag_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.taggit_tag_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.taggit_tag_id_seq', 49920, ...
                                 ^
    Command was: SELECT pg_catalog.setval('public.taggit_tag_id_seq', 49920, true);


pg_restore: [archiver (db)] Error from TOC entry 2912; 2604 34102 DEFAULT taggit_taggeditem id postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.taggit_taggeditem_id_seq" does not exist
    Command was: ALTER TABLE ONLY public.taggit_taggeditem ALTER COLUMN id SET DEFAULT nextval('public.taggit_taggeditem_id_seq'::regclass);

However - if I now go to my domain, I can see the data is there and being accessed/served by Django. The errors which persist kill certain pages which are querying some of the data mentioned above. If I try and do makemigrations/migrate, i get this:

django.db.utils.IntegrityError: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, curate, 0006_auto_20190429_1108, 2019-04-29 15:50:36.927028+00).

For the record, the 'podcast' app it's referring to is the modified app from my virtualenv. However, in this case I've moved it from the virtualenv and relocated it to the git repo inside my project - transferring all the code with it.

Does anyone know what's happening?

I should add, the codebase is updated to pythonanywhere via git. I stage the changes on Atom, then commit them. Then push them to github, then pull them down to the server with a bash command.

phil0s0pher
  • 525
  • 10
  • 21
  • Why would it find any not applied migrations if you are restoring local db where "everything worked fine"? My guess is 1) your database model is out of sync with django data model 2) you're running not the code you think – Ivan Starostin Apr 29 '19 at 16:42

2 Answers2

0

There's a big difference between pypy (a python compiler) and PyPI (the python package index). I'm assuming you mean the latter. For 3rd party packages that you need to modify, you should fork them, make your changes and push them to your fork; and finally put the link to your fork in your requirements.txt. It's a maintenance headache so if you can get the same effect by subclassing etc. then that is usually a better strategy.

Bottom line, if you're not running the same code, anything else will surely not be stable.

3GB (uncompressed) is not very big for a database. File sizes over 4GB can be problematic on some file systems, so compressing/splitting the backup might be in your future.

I don't know much about postgresql, but in general you need to be very careful about choosing formats and tools when doing a cross platform backup and restore. You should definitely not move forward until your restore executes without errors.

It looks like you might have data/constraint errors ("null value ... violates not-null constraint"). You should fix these errors in your database before backing up.

It also looks like you have some problems with the postgresql autoincrement sequences, although I have no idea how to fix that in postgresql.

I would seriously suggest taking a long look at pg_dump/pg_restore instead of some gui solution that only does half the work, cf. https://www.postgresql.org/docs/9.1/backup-dump.html and Restore a postgres backup file using the command line?.

If you want a complete gui solution, there's Navicat (https://www.navicat.com/en/products/navicat-premium - I'm a satisfied user), which at least on the databases I use makes this a select-all, copy, and paste operation. JetBrains' DataGrip looks capable too (https://www.jetbrains.com/datagrip/), although I have only played with it.

ps: on your local machine you should do a makemigrations and migrate before the backup. When you repeat these operations on the prod server there should be nothing to do (since you've "copied" the state of the database at the current version).

thebjorn
  • 26,297
  • 11
  • 96
  • 138
0

Thanks for the help everyone. I went through my project meticulously, did a full 'reset' on the migrations, so there were absolutely zero issues on my local machine.

In the end, I've discovered that PythonAnywhere are running PSQL 9.4 and I'm running PSQL 11.2. I downloaded PSQL 9.4 and tried to backup all locally, and I'm getting 150 errors each time. PythonAnywhere have no plans to upgrade their PSQL version for a while, and the data I've produced during development is fairly valuable, so I'd like to keep it.

As such, I've got two options. Either I can attempt a migration to MYSQL with MYSQL workbench, hopefully keeping my data in tact, or I can drop the data I've produced entirely and stick with PSQL 9.4 and start again.

phil0s0pher
  • 525
  • 10
  • 21