tag: postgresql

  • Creating a Spatially-Enable Database in Postgres after Upgrading

    View Comments

    Wednesday, May 25, 2011 3:47 p.m.

    This post is a reminder to myself on how to rebuild my template_postgis because I never seem to remember how to do it.

    First things first, stop the db if it’s running. For me, that’s pg_ctl -D /usr/local/var/postgres stop -s -m fast, which I have aliased as pg-stop.

    So, you’ve successfully upgraded Postgres. Congrats! Now you want to re-install Postgis. In OS X, that’s brew install postgis. Even if it’s already there, remove it first. Failing to do so will result in errors down the line. Do it now.

    Start up Postgres, pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start, or for me pg-start. Next, kill your old template_postgis. Since it’s a template, you’ll need to do:

    psql template1
template1=# update pg_database set datistemplate = FALSE
template1=# where datname = 'template_postgis';
UPDATE 1
template1=# \q
dropdb template_postgis

    Now we’re ready to create it:

    createdb template_postgis -T template1
createlang plpgsql template_postgis
cd /usr/local/Cellar/postgis/1.5.2/share/postgis/
psql -d template_postgis -f postgis.sql
psql -d template_postgis -f spatial_ref_sys.sql
psql template_postgis
template_postgis=# pg_database set datistemplate = TRUE
template_postgis=# where datname = 'template_postgis';
UPDATE 1
template_postgis=# grant all on geometry_columns to PUBLIC;
GRANT
template_postgis=# grant all on spatial_ref_sys to PUBLIC;
GRANT
template_postgis=# VACUUM FREEZE;
VACUUM
template1=# \q

    And that should be that. We now have a spatially-enabled template that we can use to create other databases with, as such createdb my_db_name -T template_postgis.

    References:

    permalink | tagged as: postgresql osx postgis template

  • Migrating a Django Project from SQLite to MySQL / PostgreSQL

    View Comments

    Tuesday, February 23, 2010 midnight

    Should you ever be tasked with migrating a django project running on an sqlite database to postgresql or mysql, the following are some of the steps I took.

    The project was already in production, so I needed all the data to come along with it. This caused some problems that django, sadly, wasn’t equipped to handle.

    ./manage.py dumpdata > mydumpfile.json

./manage.py dumpdata --indent=n > mydumpfileindent.json

grep myword mydumpfileindent.json
grep -A 10 -B 10 -n myword mydumpfileindent.json

mysql -u root -p

mysql> drop database dbname;
mysql> create database dbname
    -> character set utf8 collate utf8_general_ci;
mysql> grant all on dbname.* to dbuser@localhost
    -> identified by dbpass;
mysql> exit;

sudo -u postgres dropdb dbname;
sudo -u postgres createdb dbname -E utf8 -O dbuser;

./manage.py syncdb

./manage.py sqlflush | mysql -u dbuser -p dbname

./manage.py sqlflush | sudo -u postgre psql dbname

mysql -u dbuser -p dbname

mysql> alter table tablename modify 
    -> columnname columntype;

mysql> alter table tablename add
    -> columnname columntype after othercolumn;

./manage.py loaddata mydumpfile.json

    django-command-extensions

    osx utilities:

    permalink | tagged as: migration osx django postgresql mysql sqlite