Rectangle 27 3

Is it possible to run a PostgreSQL database with a Django app on Elastic Beanstalk?

Yes. The dummy app setup you mention refers to the use of an Amazon Relational Database Service. At the moment PostgreSQL is not available as an Amazon RDS, but you can configure your beanstalk AMI to act as a local PostgreSQL server or set up your own PostgreSQL RDS.

If it's possible, is it worth the trouble?

This is really a question about whether it is worth using an RDS or going it alone, which boils down to questions of cost, effort, usage, required efficiency etc. It is very simple to switch database engines serving django so if you change your mind it is easy to switch set up.

Essentially you need to customise your beanstalk AMI by installing a PostgreSQL database server on an Amazon linux EB backed AMI instance.

python - PostgreSQL for Django on Elastic Beanstalk - Stack Overflow

python django postgresql elastic-beanstalk
Rectangle 27 1

Create a local postgres db to use with django app

  • sudo brew uninstall postgresql (literally, sudo solved my problem... I had read that sudo brew can be dangerous but it worked well in this case)
  • Fully uninstalled postgres.app (following instructions on site: quit, drag to the Trash, and then "Finder > Empty Trash..." as per http://postgresapp.com/documentation/install.html)
i.e., the 5 steps here:
Resetting Postgres.app
http://postgresapp.com/documentation/troubleshooting.html
/etc/profile
~/.bashrc
~/.bash_profile
# 1:
 # For postgresql postgres postgres.app:
 # Otherwise psql isn't a recognised command
 # Based on http://postgresapp.com/documentation/cli-tools.html
export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.3/bin
 # 2:
 # And this from https://devcenter.heroku.com/articles/heroku-postgresql#local-setup
export PATH="/Applications/Postgres93.app/Contents/MacOS/bin:$PATH:$PATH"
 # 3:
 # And now this based on error of file not existing
 # from http://stackoverflow.com/questions/13868730/socket-file-var-pgsql-socket-s-pgsql-5432-missing-in-mountain-lion-os-x-ser -- don't think this made a difference though
export PGHOST=localhost

I think this is overkill; the first change is definitely useful as it allows me to go straight to psql just by typing "psql" in terminal (I personally didn't even need -h localhost); I'm less sure of the need of the other two additions

In case relevant, now that it's working, here are some more steps I've finished to get fully set up and comfortable with postgres and my new heroku app. This has been my first experience with heroku, django and postgresql so I have tried to note all details which struck me!

My goal was to add this /polls app to the "getting started" django sample app

https://devcenter.heroku.com/articles/getting-started-with-python

I am running Mac OS X 10.9.5 but some of my comments may be helpful for anyone confused about local/heroku postgres integration

  • Once postgres app is running (launched by double-clicking the program in Applications -> Postgres, then look out for the elephant in the notification bar if you're on a Mac), I can log in to psql. But first straight from the command line, I made a database by command
createdb cool01db
psql: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?
DATABASES = {
     'default': {
         'ENGINE': 'django.db.backends.postgresql_psycopg2',
         'NAME': 'cool01db',
         'USER': '', 
         'PASSWORD': '',
         'HOST': 'localhost', # '127.0.0.1' probably works also
         'PORT': '5432',
     }
 }
#  DATABASES['default'] =  dj_database_url.config()
http://stackoverflow.com/a/16422414/870121
  • I navigated into the project folder (which contains the above three folders and files like manage.py) and ran (locally, not on heroku) python manage.py syncdb Here I was prompted to create a superuser with a password

Note, now the tables for the heroku sample app (tables like hello_greeting) and the django polls app (tables like polls_choice and polls_question) are all automatically created in the local postgres db, and I can view them if I log in to psql (psql cool01db from the terminal) and type \d (once you're in)

  • I ran foreman start and navigated to http://localhost:5000/admin/ and entered my superuser username and password I had just created. This successfully logged me in to the /admin site powered by my LOCAL postgres database
https://docs.djangoproject.com/en/1.6/intro/tutorial02/#make-the-poll-app-modifiable-in-the-admin
  • Now I visited http://localhost:5000/polls and sure enough it was working
  • If in the terminal I ran psql cool01db and then the sql command \d I could see the tables, and select * from question_choice showed me the updates just made. Very satisfying.
gettingstarted/settings.py
DATABASES['default'] =  dj_database_url.config()
heroku config
(awe01)moriartymacbookair13:getstartapp macuser$ heroku config
=== awe01 Config Vars
DATABASE_URL:                 postgres://aqmorelettersqz:uqry_pnFmorelettersYf@ec2-XX-XXX-XX-XX.compute-1.amazonaws.com:5432/dbmorelettersce
HEROKU_POSTGRESQL_YELLOW_URL: postgres://aqmorelettersqz:uqry_pnFmorelettersYf@ec2-XX-XXX-XXX-XX.compute-1.amazonaws.com:5432/dbmorelettersce
PAPERTRAIL_API_TOKEN:         kmorelettersx
TIMES:                        2
(awe01)moriartymacbookair13:getstartapp macuser$

I pasted all of that into the hidden file .env

Syntax note: this means the file .env looked like this:

TIMES=2
DATABASE_URL=postgres://aqmorelettersqz:uqry_pnFmorelettersYf@ec2-XX-XXX-XX-XX.compute-1.amazonaws.com:5432/dbmorelettersce
HEROKU_POSTGRESQL_YELLOW_URL=pos....

...i.e., with = signs but no spaces around them (and no comments in this file)

Note, this involved deleting the original "DATABASE_URL=" entry from the .env file

As far as I understand, the .env file is what your local machine sees, but the output of "heroku config" is what the heroku machine sees (corrections welcome), so I wanted them to match.

  • NB: Once Postgres is installed and you can connect, youll need to export the DATABASE_URL environment variable for your app to connect to it when running locally.
export DATABASE_URL=postgres:///$(whoami)

This tells Postgres to connect locally to the database matching your user account name (which is set up as part of installation).

https://devcenter.heroku.com/articles/heroku-postgresql#local-setup

Note (to self): I don't understand what happens here (I can't see a file that's changed as a result of running this, or what exactly happens). But I did a restart of computer and everything still works, I didn't need to run this again.

heroku run python manage.py syncdb

(You will be prompted to create a superuser only if you haven't already done so)

  • I went to myappname.herokuapp.com/admin and logged in with the superuser account created for heroku postgresql (which I have the same as local for convenience). There I was able to add a question, and this time this question is added to the heroku postgresql db. Visiting myappname.herokuapp.com/polls shows me that question available for votes

Now if I do foreman start, what I see at /db or /polls is the same as if I visit my locallhost:5000 or myappname.herokuapp.com, since both are using the heroku postgresdb (I don't even need my postgres server running)

\dt
SELECT * FROM...
heroku pg:psql
https://devcenter.heroku.com/articles/getting-started-with-python#provision-a-database

django - Where is data_directory for homebrew? or for postgresql or ho...

django postgresql heroku homebrew
Rectangle 27 18

That is probably because your script is running under some other user than the one you are trying to connect with (myuser here). In this case, peer authentication will fail. Your solution with HOST: "localhost" works because you are not using peer auth anymore. However, it is slower than HOST: "" because instead of using unix sockets, you use TCP connections. From django docs:

If youre using PostgreSQL, by default (empty HOST), the connection to the database is done through UNIX domain sockets (local lines in pg_hba.conf). If you want to connect through TCP sockets, set HOST to localhost or 127.0.0.1 (host lines in pg_hba.conf). On Windows, you should always define HOST, as UNIX domain sockets are not available.

If you want to keep using sockets, correct settings in pg_hba.conf are needed. The most simple is:

local   all         all                               trust

while commenting out all other local lines in the config. Note that reloading postgres is needed for this change to take effect.

But if multi-user production machine is in question, you might want to use something more secure like md5 (see here for explanation of various authentication methods).

Django connection to PostgreSQL: "Peer authentication failed" - Stack ...

django postgresql
Rectangle 27 2

None of your links appeared to be the (arguably) most useful documentation - the official postgresql docs. I recently configure a Linux Mint box with postgresql and django using a combination of those documents and these, though the latter are specific to Linux.

I'd worry more about validating the postgresql side of things, less about rails. That is to say, your question should be "How can I set up and test a postgresql server on a Windows 7 box", moreso than you need to know how to get it locked into rails.

Edit: Maybe this could also be of use to you - official postgresql wiki with detailed installation guides.

Thanks Reno. Those links looks like a much better direction to go in. I'll post back with how I get on. (Excellent, wiki.postgresql.org/wiki/ has already told me I have the wrong version of postgres as 8.4 was released before Windows 7... thanks Reno! :) )

Installing Postgres on windows for use with Ruby-on-Rails - Stack Over...

ruby-on-rails windows ruby-on-rails-3 postgresql install
Rectangle 27 12

I wanted to develop directly on the postgresql server (so don't copy the ".postgresql_psycopg2" bit if you're not also using postgresql)

  • No problem developing using the local psql db

Got that error when I uncommented the line to use heroku db

DATABASES['default'] =  dj_database_url.config(default=os.getenv('DATABASE_URL'))

Initial attempt was to add more details, such as another line,

DATABASES['default']['ENGINE'] = 'django.db.backends.postgresql_psycopg2'

This didn't work though, because then the error asked me for NAME, which it rejected.

In the end, this solved it:

postgres://user:pass@localhost/dbname
  • I updated the settings.py file to reflect those details: DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': 'your_heroku_db_name', 'USER': 'your_heroku_db_user_name', 'PASSWORD': 'your_heroku_password', 'HOST': 'ec2-23-21-133-106.compute-1.amazonaws.com', # Or something like this 'PORT': '5432', } } That tip is from https://stackoverflow.com/a/19719966/870121 Note: my next plan is to abstract these back into .env variables rather than leaving them visible in settings.py

I then commented out the later line,

# DATABASES['default'] =  dj_database_url.config(default=os.getenv('DATABASE_URL'))

so DATABASES was only specified once in the settings.py file

That way the program read everything required to connect to the postgresql heroku db

python manage.py syncdb

If you want to try developing locally, comment out everything above and instead set your local postgresql server going and uncomment the local equivalent of the above:

DATABASES = {
     'default': {
         'ENGINE': 'django.db.backends.postgresql_psycopg2',
         'NAME': 'cool01db',
         'USER': '', 
         'PASSWORD': '',
         'HOST': 'localhost', # '127.0.0.1' probably works also
         'PORT': '5432',
     }
 }

thanks, it was very helpful for me! (worked fine on heroku, but not locally...)

"settings.DATABASES is improperly configured" error performing syncdb ...

django django-models django-admin
Rectangle 27 4

IDENT authentication ended up being more hassle than it was worth. Here's what I ended up doing to avoid the use of a PostgreSQL superuser role...

postgres
sudo su - postgres

Edit the PostgreSQL host-based authentication configuration file:

nano /etc/postgresql/8.4/main/pg_hba.conf

Scroll to near the bottom of this file, looking for the line which looks like this:

local   all   all   ident

Change ident to md5, exit and save. This tells PostgreSQL to use an MD5-encrypted password for authentication on local connections. Now restart PostgreSQL:

/etc/init.d/postgresql-8.4 restart

Create a PostgreSQL user:

createuser django_user --pwprompt

Don't accept any of the special privileges when prompted. Now create a new database:

createdb -E UTF8 -O django_user django_db

Those options encode the database in UTF8 and set the owner to django_user. You can now exit back to the original linux user account:

exit
DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.postgis',
        'NAME': 'django_db', 
        'USER': 'django_user', 
        'PASSWORD': '[your password]', 
        'HOST': '', 
        'PORT': '', 
    }
}

When you run python manage.py syncdb or any other Django management commands, the settings above will be used to authenticate with the database.

django - Apache user permissions on PostgreSQL database - Stack Overfl...

django apache postgresql
Rectangle 27 2

One thing that may help you with HUGE tables is partitioning. Judging by the postgresql tag that you set for your question, take a look here: http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

But for a start I would go with a one, simple table, supported by a reasonable set of indexes. From what I understand, each data entry in the table will be identified by race session id, player id and time indicator. Those columns should be covered with indexes according to your querying requirements.

As for your two questions: 1) You store those informations as simple integers. Remember to set a proper data types for those columns. For e.g. if you are 100% sure that some values will be very small, you can use smallint data type. More on integer data types here: http://www.postgresql.org/docs/9.3/static/datatype-numeric.html#DATATYPE-INT

2) That won't be a problem if you every var list will be different row in the table. You will be able to insert as many as you'd like.

So, to sum things up. I would start with a VERY simple single table schema. From django perspective this would look something like this:

class RaceTelemetryData(models.Model):
  user = models.ForeignKey(..., index_db=True)
  race = models.ForeignKey(YourRaceModel, index_db=True)
  time = models.IntegerField()
  gas = models.IntegerField()
  speed = models.SmallIntegerField()
  # and so on...

Additionaly, you should create an index (manually) for (user_id, race_id, time) columns, so looking up, data about one race session (and sorting it) would be quick.

In the future, if you'll find the performance of this single table too slow, you'll be able to experiment with additional indexes, or partitioning. PostgreSQL is quite flexible in modifying existing database structures, so you shouldn't have many problems with it.

In the end you end up with one table, that has at least these columns: user_id - To specify which users data this row is about. race_id - To specify which race data this row is about. time - To identify the correct order in which to represent the data.

This way, when you want to get information on Joe's 5th race, you would look up rows that have user_id = 'Joe_ID' and race_id = 5, then sort all those rows by the time column.

so maybe Im not understanding, or I didnt describe the problem properly earlier, but in your django model you have (for example) gas and speed as fields that hold single values. However, in my case, for each race gas and speed each need to be a time series of their own. Because I need to record the players speed every 0.1 seconds for the entire duration of the race - this means the speed field needs to hold 1000+ ordered values (ie representation of the players speed every 0.1 seconds of that race session). How do you fit something like this into an intfield?

You don't squeeze those 1000+ values into one field. You create 1000+ RaceTelemetryData objects which results in 1000+ rows in the database table. One for every 0.1 second of the race. Then you can iterate over those objects to create an array if you need one, or query the table using an explicit SQL.

ah I see, do you end up with those 1000+ rows in a table, then the table itself represents 1 race session. This means over the entire site, you end up with hundreds of thousands of tables (one for each race that gets submitted). This is a more preferred method than just fitting an entire time series (probably json string) into a single field and having much fewer tables? I dont have much db experience so this is honestly just all new to me

I answered by editing. You have just one table, and each row in that table contains data that is needed to identify which users race it is about.

that makes perfect sense now, thanks!

python - Django database planning - time series data - Stack Overflow

python django database postgresql time-series
Rectangle 27 13

Probably the easiest way to do this is to recreate the whole database. In Ubuntu, it looks like this:

sudo su postgres
psql
drop database your_database_name;
create database your_database_name with owner user_you_use_in_django;
\q
exit

That's it. You have clean database. To make it ready to go, you have to run migrations with python manage.py migrate.

If you are working on your project alone, you can delete and recreate migrations, if you want.

If you need some data to be present after database recreation, you can use fixtures, or some automation scripts.

Simple way to reset Django PostgreSQL database? - Stack Overflow

django postgresql
Rectangle 27 4

This is not possible / a misunderstanding. A timestamp column is not in any time zone. The time zone is never saved, not even for timestamp with time zone (timestamptz), which always saves UTC time. That name is a tad bit misleading, I'll give you that.

You have two very simple options:

now()
timestamptz
  • Save now() to a timestamp column. (The time zone offset is truncated in the cast.)

If you want timestamps to be displayed for the 'PST' time zone, set the time zone setting of your session to that time zone (if it is not set already). For instance:

SET timezone='America/Anchorage'

Ample details in this related answer:

If you want to save the original time zone of input values:

I ran the SET timezone='America/Anchorage' and got a syntax error at or near the '='.

Maybe I cannot set the timezone of the session in CartoDB, although it runs off of PostgreSQL.

@cbunn: You did not mention CartoDB. I don't know it, but there should be a way to set the time zone somehow.

Set timestamp column's timezone in PostgreSQL - Stack Overflow

postgresql timezone timestamp
Rectangle 27 4

This is not possible / a misunderstanding. A timestamp column is not in any time zone. The time zone is never saved, not even for timestamp with time zone (timestamptz), which always saves UTC time. That name is a tad bit misleading, I'll give you that.

You have two very simple options:

now()
timestamptz
  • Save now() to a timestamp column. (The time zone offset is truncated in the cast.)

If you want timestamps to be displayed for the 'PST' time zone, set the time zone setting of your session to that time zone (if it is not set already). For instance:

SET timezone='America/Anchorage'

Ample details in this related answer:

If you want to save the original time zone of input values:

I ran the SET timezone='America/Anchorage' and got a syntax error at or near the '='.

Maybe I cannot set the timezone of the session in CartoDB, although it runs off of PostgreSQL.

@cbunn: You did not mention CartoDB. I don't know it, but there should be a way to set the time zone somehow.

Set timestamp column's timezone in PostgreSQL - Stack Overflow

postgresql timezone timestamp
Rectangle 27 3

Having applications connect as a superuser is almost definitely unwise. Unless the application needs to actually create and/or drop databases itself (and this is extremely unlikely), I don't think it's ever necessary. If the application connects to a database as that database's owner, it is effectively a superuser within the confines of that database, which might not be too bad.

I generally have applications access the database using an account authenticating with MD5. It's possible, for example, to set up pg_hba.conf such that the application account is the only account that can use MD5 authentication, and all other users on the local machine use ident/peer authentication.

It sounds like what you actually needed here was a role to group the Apache user and the other Django users together, so you could grant them access en masse.

Postgresql does have ways to grant permissions for all tables etc in a schema at once, and also a way to specify default permissions to be applied to new objects. This previous answer may be helpful: How do you create a read-only user in PostgreSQL?

django - Apache user permissions on PostgreSQL database - Stack Overfl...

django apache postgresql
Rectangle 27 2

You can use flush. Just run this command:

python manage.py flush

So I could make changes to my existing models and add new ones, and then just use the flush command and syncdb?

U won't need to use syncdb while using flush. Flush clears out the data from db but not the structure

django - How to "reset" postgresql database? - Stack Overflow

django postgresql
Rectangle 27 2

You can use flush. Just run this command:

python manage.py flush

So I could make changes to my existing models and add new ones, and then just use the flush command and syncdb?

U won't need to use syncdb while using flush. Flush clears out the data from db but not the structure

django - How to "reset" postgresql database? - Stack Overflow

django postgresql
Rectangle 27 0

python manage.py dumpdata > initial_data.json
python manage.py dumpdata <app_name> > initial_data.json

Second run the flush command to clean your database:

python manage.py flush

Third and last, run loaddata command to load the initial data into your database and create superuser by running createsuperuser command

python manage.py loaddata initial_data.json
python manage.py createsuperuser

What is the 'initial data' ? I am trying to remove ALL of the data, not specific app data.

django - How to "reset" postgresql database? - Stack Overflow

django postgresql
Rectangle 27 0

If you perform a raw sql, can do this:

ALTER SEQUENCE youApp_id_seq RESTART WITH 1;

database - Django + PostgreSQL: How to reset primary key? - Stack Over...

database django postgresql table primary-key
Rectangle 27 0

python manage.py help sqlsequencereset

Pipe it into psql like this to actually run the reset:

python manage.py sqlsequencereset myapp1 myapp2 | psql

Edit: here's an example of the output from this command on one of my tables:

BEGIN;
SELECT setval('"project_row_id_seq"', coalesce(max("id"), 1), max("id") IS NOT null) FROM "project_row";
COMMIT;

what if its a windows setup?

The sqlsequencereset commmand generates the SQL needed to reset the primary key counter, so on windows, instead of piping the output to psql, I would copy+paste it into whatever admin app you use. For example, if pgAdminIII is installed, paste into "execute arbitrary SQL queries".

I will check this out when I get home from work, thanks! Is there way to do this programmatically? I'd like to be able to easily stick it in a django view if possible.

Yes, I know it seems dangerous, but I'd like people using this game to be able to restart it without having to know the table structure or django commands. Really the only data "at risk" is randomized game state data for their own install of the game.

database - Django + PostgreSQL: How to reset primary key? - Stack Over...

database django postgresql table primary-key
Rectangle 27 0

I view auto-increment primary keys as purely internal identifiers for database records, and I don't like exposing them to users. Granted, it's a common design to use them as part of URLs, but even there slugs or other identifiers feel more appropriate.

I normally would too, but the objects in this case are already going to be numbered, and displayed as 1, 2, 3... etc.

database - Django + PostgreSQL: How to reset primary key? - Stack Over...

database django postgresql table primary-key
Rectangle 27 0

I don't understand why you think you need to delete either migrations or the database. The whole point of migrations is that they build on what has gone before; you should never need to delete and start again. Just do makemigrations every time you make a change, and the series of migrations should always work.

Later, you may want to squash a set of migrations into one, for the sake of speed, which is again supported natively. But never delete the migrations you have already run.

I wanted to delete migrations because I got an error when running manage.py migrate. I was not able to figure out how to fix it.

If you don't have a database on production yet, removing the migrations and creating them again does not matter much.

I think this is the wrong attitude. Developers should not be fearful of ANY code. They need to have full control over the process especially opinionated and at times unwieldy frameworks. To solve a similar issue over converting types which is a purely internal issue to Django, I went back and rewrote (migration) history. Governments do it all the time, why shouldn't developers?

Simple way to reset Django PostgreSQL database? - Stack Overflow

django postgresql
Rectangle 27 0

python manage.py dumpdata > initial_data.json
python manage.py dumpdata <app_name> > initial_data.json

Second run the flush command to clean your database:

python manage.py flush

Third and last, run loaddata command to load the initial data into your database and create superuser by running createsuperuser command

python manage.py loaddata initial_data.json
python manage.py createsuperuser

What is the 'initial data' ? I am trying to remove ALL of the data, not specific app data.

django - How to "reset" postgresql database? - Stack Overflow

django postgresql