Sqlite3 to Postgres Migration?

[talex]

I have a functioning Django application that is based on sqlite3,
but I want to change to using Postgres.
I have altered the DATABASES clause in settings.py to use Postgres.
and I am using Django version 1.7.

Am I correct that use of "python makemigrations" followed by
"python migrate", should copy the existing data into Postgres
and leave me with a working application?

Instead what is happening is that "python migrate" gives
the following error and hint:

  django.db.utils.ProgrammingError: column "date" cannot be cast automatically to type integer
  HINT:  Specify a USING expression to perform the conversion.

How can I apply this hint to make this work?

My models.py file is as follows:
----------------
from django.db import models
import datetime

# Create your models here.

class Location(models.Model):

   class Meta:
      unique_together = ("lat", "lng")

   lat  = models.DecimalField(max_digits=8, decimal_places=5)
   lng  = models.DecimalField(max_digits=8, decimal_places=5)
   name = models.CharField(max_length=200,  unique=True)

   def __str__(self):
      return "%s: %d %d" % (self.name, self.lat, self.lng)

class Observation(models.Model):

   date     = models.DateField()
   location = models.ForeignKey(Location)
   observer = models.CharField(max_length=50)
   temperature     = models.FloatField(default=0.0)
   photo    = models.ImageField(default="tower.jpg", upload_to="uploaded_photos")

   def __str__(self):
      return self.observer
---------------

Thanks.

[Andrew Pinkham]

On Feb 19, 2015, at 1:04 AM, talex <bkiel...@gmail.com> wrote:
> Am I correct that use of "python makemigrations" followed by
> "python migrate", should copy the existing data into Postgres
> and leave me with a working application?

I'm afraid not.

Migration allows for predictable and reversible alterations to the database structure. It's an excellent tool for modifying the schema (tables, columns) of a database. It is possible to use migrations for data (rows), but you typically need to modify the migration to achieve your goal.

Migrations are not intended for moving data from one database to another. You might be able to use Django to help achieve your goal, but it's not magic. To start with, you will need both both databases to be connected to your Django site - if you disconnect your SQLite database, then all the data in it is unavailable to you. Django won't know how to get to it anymore.

You might find my article about migrations interesting:
http://afrg.co/updj17/a2

> Instead what is happening is that "python migrate" gives
> the following error and hint:
>
> django.db.utils.ProgrammingError: column "date" cannot be cast automatically to type integer
> HINT: Specify a USING expression to perform the conversion.
>
> How can I apply this hint to make this work?

I'm unfamiliar with this error, but I suspect your problem lies in the migration files. What files exist in `/app_name/migrations/` ? Those are the files that `./manage.py migrate` execute with.

Andrew

[Tom Lockhart]

On Feb 18, 2015, at 11:04 PM, talex <bkiel...@gmail.com> wrote:

> I have a functioning Django application that is based on sqlite3,
> but I want to change to using Postgres.

I’ll point out for the benefit of others who might be getting started: Postgres is a full featured database and supports the full range of Django features and add-ons including GeoDjango. Starting development with a production-quality database already in place is not much more work and will save grief later on.

Usually I’d end with “hth”, but this is no help at all for talex…

- Tom

[Mike Dewhirst]

On 20/02/2015 3:07 AM, Tom Lockhart wrote:
> On Feb 18, 2015, at 11:04 PM, talex <bkiel...@gmail.com> wrote:
>
>> I have a functioning Django application that is based on sqlite3,
>> but I want to change to using Postgres.
>

> I’ll point out for the benefit of others who might be getting started: Postgres is a full featured database and supports the full range of Django features and add-ons including GeoDjango. Starting development with a production-quality database already in place is not much more work and will save grief later on.
>
> Usually I’d end with “hth†, but this is no help at all for talex…
>
> - Tom
>

I agree with Tom.

You are looking for a pair of dump/load scripts. Check this post ...

http://stackoverflow.com/questions/4581727/convert-sqlite-sql-dump-file-to-postgresql

I haven't studied that SO post but at first glance it seems the sqlite
dump file might be directly usable by postgres. If so, you need to
create the new database in Postgres and load it from the sqlite dump file.

You need to look at the Postgres docs for psql which will load the
sqlite3 data dump.

Here is an example I use regularly ...

psql --port=5432 --file=/home/mike/dumps/pg/ssds/pq3.dump --dbname=ssds
--username=miked

hth

Mike

[talex]

Thanks for your helpful comments.

Andrew, thanks for your migration article at http://afrg.co/updj17/a2 .  It is slightly daunting for me at this point, but I may need to come back to it later. Your book looks interesting.

Mike, thanks for the dump/load load suggestion.

For the moment, I think the easiest thing for me is to follow Thomas, and just start over with Postgres, but let me explain more about what I am doing and ask two more questions.

I am leading a project to create a web application for uploading certain scientific data into a database.  No one in the group has experience with web development.  We decided on Django, and I wrote a prototype using sqlite3.  Sqlite3 will not be used going forward.  Instead either Postgres or MySql will be used.  Personally I am in favor of Postgres, but I have been telling the group, perhaps incorrectly, that Django is "database agnostic" and that we can easily change databases at any time in the future should we need to do so.  So let me ask two more questions:

In the life of a Django application, is it simple and easy to change the database flavor (say from Postgres to MySql), even after significant data has accumulated in the model's database?  

Does the answer change if we are willing to forgo the use of the version-control-like capabilities represented by Django's makemirations/migrate tools?

Thanks.

[Andrew Farrell]

One approach may be to use django-import-export to add to the admin interface the capability to export your data to CSV (or JSON or Excel) and likewise to import it.

Then, simply run two instances of your app: one on top of postgres, one on top of sqlite. Export from one and import into the other.

Modifying the admin interface is probably not the fastest route to victory, but if your application is centered around uploading and downloading data,

then you might decide that adding an "export to CSV" button is already useful.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/458baa37-5387-4784-97c2-c7cbb50fc480%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Mike Dewhirst]

On 21/02/2015 9:55 AM, talex wrote:
> Thanks for your helpful comments.
>
> Andrew, thanks for your migration article at http://afrg.co/updj17/a2 .
> It is slightly daunting for me at this point, but I may need to come
> back to it later. Your book looks interesting.
>
> Mike, thanks for the dump/load load suggestion.
>
> For the moment, I think the easiest thing for me is to follow Thomas,
> and just start over with Postgres, but let me explain more about what I
> am doing and ask two more questions.
>
> I am leading a project to create a web application for uploading certain
> scientific data into a database. No one in the group has experience
> with web development. We decided on Django, and I wrote a prototype
> using sqlite3. Sqlite3 will not be used going forward. Instead either
> Postgres or MySql will be used. Personally I am in favor of Postgres,

Good call. IMO.

> but I have been telling the group, perhaps incorrectly, that Django is
> "database agnostic"

Correctly. https://docs.djangoproject.com/en/1.7/ref/databases/

and that we can easily change databases at any time
> in the future should we need to do so. So let me ask two more questions:
>
> In the life of a Django application, is it simple and easy to change the
> database flavor (say from Postgres to MySql), even after significant
> data has accumulated in the model's database?

Probably easy but depends on your data. You need to do a comparison of
data types supported to see if your task is covered by both. But even if
not, you should be able to work around it with sleight of hand. This is
independent of Django.

>
> Does the answer change if we are willing to forgo the use of the
> version-control-like capabilities represented by Django's
> makemirations/migrate tools?

Not sure I understand the question. Switching from one database to
another is not migrating in the sense that word is used in Django.

You probably wouldn't even need to use migrations anyway unless your
models change. And you wouldn't want to conflate a model change with a
database switch.

You would use PG Admin for "dumbing down" Postgres data types so it will
become dump/loadable into Mysql.

Your real alternatives in switching databases are to do it with a
shutdown or do it live. Django can handle multiple databases
simultaneously so your options are legion!

I think the only valid reason for switching databases is if the one you
chose is not doing the job.

Maybe your team is familiar with Mysql. If so, go with that. It is
usually better for a leader to ask the team to do the research then
choose the stack so that they have buy-in.

Cheers

Mike

>
> Thanks.
>
>
>
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-users...@googlegroups.com

> <mailto:django-users...@googlegroups.com>.

> To post to this group, send email to django...@googlegroups.com

> <mailto:django...@googlegroups.com>.

> Visit this group at http://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/458baa37-5387-4784-97c2-c7cbb50fc480%40googlegroups.com

> <https://groups.google.com/d/msgid/django-users/458baa37-5387-4784-97c2-c7cbb50fc480%40googlegroups.com?utm_medium=email&utm_source=footer>.