Migrating from sqlite to postgres

Postgres migration.md

Postgres migration

I had to migrate what had started as a toy website and has now become a real website from running on sqlite to postgres today. I use a push-to-deploy setup, so the process was a bit tricky. I only needed like 10 minutes of downtime to do it, but had to make sure I had all the steps in order. For future record:

1. Update requirements.txt to include psycopg2 and push the latest version of production branch so that any migrations get applied on the sqlite DB and psycopg2 is installed in the virtual env.
2. Go through phpPgAdmin and drop all tables and sequences from the database (if it exists -- in this case, it was a result of testing these instructions on the staging version of the site)
3. Update settings.py to use the new database parameters
4. Run python manage.py dumpdata --natural-foreign -e sessions -e contenttypes -e auth.Permission > ~/temp.json to dump the database contents
5. Bring down the production server
6. Push the new settings.py to the server. My post-receive hook will update the working directory and run migrations (creating all the tables). [If the server had been up, the hook would have reloaded it]
7. Run python manage.py loaddata ~/temp.json to load the data into the database
8. Fix sequences as described here. (Had to momentarily change the db password to something more bash-friendly)
9. Re-launch server

Details on how to fix postgres sequences

export PGPASSWORD=$db_password
psql -Atq -f reset.sql -o temp -U $db_user --dbname=$db_name
psql -f temp -U $db_user --dbname=$db_name
rm temp

-- reset.sql
SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;