2

Moving from SQLITE3 to Postgresql (psql (PostgreSQL) 9.4.5) in Pyramid

I have been using sqlite3 to access a sqlalchemy database. I have developed a pyramid web framework and sqlite3 has been working. However, I wanted to move away from using sqlite3 and instead use postgresql for the db. I am new to this and am trying to figure out if I am missing a step.

I followed this tutorial in downloading psycopg2 (adding it to setup.py) changing the development.ini file from:
sqlalchemy.url = sqlite:////Users/ack/code/venv/NotssDB/notssdb/notssdb
to:
sqlalchemy.url = postgresql://ack:password@localhost:5432/notssdb
I installed postgres via this site using mac osx.

With that said, when I startup the app pserve development.ini ...the database is not connecting or loading. When I use the sqlite url, the database connects.

What might I be missing?

development.ini in pyramid: 

sqlalchemy.url = postgresql://ack:password@localhost:5432/notssdb

# By default, the toolbar only appears for clients from IP addresses
# '127.0.0.1' and '::1'.
# debugtoolbar.hosts = 127.0.0.1 ::1

###
# wsgi server configuration
###

[server:main]
use = egg:waitress#main
host = 0.0.0.0
port = 5432

postgresql.conf 

# - Connection Settings -

#listen_addresses = 'localhost'     # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
#port = 5432                # (change requires restart)
max_connections = 100           # (change requires restart)

postgres server log: 

LOG:  invalid length of startup packet
LOG:  invalid length of startup packet

Do I have to change the postgres config file? (A stacks question refers to it too)... I saw this in pg_hba.conf: 

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all         all           0.0.0.0/0                trust
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     ack                                trust
#host    replication     ack        127.0.0.1/32            trust
#host    replication     ack        ::1/128                 trust

Using the terminal, this connects me to the DB on postgres: 

psql -h localhost -d notssdb -p 5432

NEW

postgresql server is automatically launched

How I launch Pyramid app:

$ python setup.py develop #installs packages 
$ initialize_notssweb_db development.ini # --db Session--
$ pserve development.ini  #terminal: --db Session-- Starting server in PID 3501. serving on http://0.0.0.0:5432

Checking the server: 

$  pg_ctl -D /usr/local/var/postgres status
pg_ctl: server is running (PID: 701)
/usr/local/Cellar/postgresql/9.4.5/bin/postgres "-D" "/usr/local/var/postgres" "-r" "/usr/local/var/postgres/server.log"
Community
  • 1
  • 1
thesayhey
  • 938
  • 3
  • 17
  • 38
  • Could you please edit the question to include relevant error messages of "not loading" and also check PostgreSQL log files. – Mikko Ohtamaa Oct 15 '15 at 21:21
  • @MikkoOhtamaa I wish I got a `not loading` prompt... I am getting `No data received ERR_EMPTY_RESPONSE. Unable to load the webpage because the server sent no data.` – thesayhey Oct 15 '15 at 21:54
  • You need to get a Python traceback error, not what your web server says. It is in the console of your Pyramid application or in the log files. – Mikko Ohtamaa Oct 16 '15 at 00:36
  • Also different operating systems configure different PostgreSQL versions differently so include information about this. – Mikko Ohtamaa Oct 16 '15 at 00:37
  • 1
    Also if there is no password on PSQL the connection string should look like `postgresql://localhost/notssdb` – Mikko Ohtamaa Oct 16 '15 at 00:37
  • @MikkoOhtamaa I'm not getting a Python traceback error or anything in the logfiles. Using `psql (PostgreSQL) 9.4.5` – thesayhey Oct 16 '15 at 03:58
  • I think we need to be able to see how you initialize your application, how you create your view and how you call this view with your browser, where your log output goes, does your log output work without database setup, at least. Without good diagnosing information it is very hard to tell what could be going on here. – Mikko Ohtamaa Oct 16 '15 at 04:50
  • @MikkoOhtamaa when I use the sqlite db url (shown above) the whole system works and I am able to see my db and interact with it based on the view config... Here is a quick view of the `initilize...` and `view config`: https://gist.github.com/anonymous/f1921dd5eade586a830d – thesayhey Oct 16 '15 at 14:17
  • @MikkoOhtamaa I think it might be the conf file (see above) – thesayhey Oct 16 '15 at 19:03
  • I still don't see *the actual Python error*. Information is good, but there is no error to speak of. You need to capture the Python error on the connecition or wherever it happens and have it in the question, or helping is pretty futile. – Mikko Ohtamaa Oct 17 '15 at 04:02
  • @MikkoOhtamaa what is the best way to capture the error? It doesn't seem to be connecting with the pyramid app – thesayhey Oct 17 '15 at 04:06
  • Add the steps to the question how you launch your Pyramid and app how you follow it. – Mikko Ohtamaa Oct 17 '15 at 06:15
  • @MikkoOhtamaa added how I launch Pyramid – thesayhey Oct 17 '15 at 14:31
  • When you start a pserve and try to open a page what it prints to console? If it doesn't print anything then add some debug prints to your code to see where it stops. It should yield an error in some point if it cannot connect to database. – Mikko Ohtamaa Oct 17 '15 at 17:16
  • It just prints `: --db Session-- Start` as shown above which is from the database. – thesayhey Oct 17 '15 at 17:37
  • Ok, gotcha! Let me think. – Mikko Ohtamaa Oct 17 '15 at 17:39
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/92590/discussion-between-mikko-ohtamaa-and-thesayhey). – Mikko Ohtamaa Oct 17 '15 at 17:39
  • Yes! I can. But not by computer. Can we later today or tomorrow ? You're the best @MikkoOhtamaa! – thesayhey Oct 17 '15 at 17:40
  • Ok. I might not be on Stackoverflow but you can find me on irc.freenode.net IRC server on #pyramid channel under nick Moo (something). Mention my nick in the public chat and it should give me an alert if I am on the computer. – Mikko Ohtamaa Oct 17 '15 at 18:17
  • @MikkoOhtamaa Thank you for your continuous help... I changed the `port` today and used your recommended: `sqlalchemy.url = postgresql://localhost/NOTSSDB` and it suddenly started working. Maybe a mac os bug? idk... but seems to be working now. – thesayhey Oct 19 '15 at 21:27

1 Answers1

2

Not sure if it is the only problem, but it looks like you have port conflict. You try to launch pyramid app (via pserve) on the same port postgresql listens by default (5432).

[server:main]
use = egg:waitress#main
host = 0.0.0.0
port = 5432

Port parameter here is for pserve listen port, change it to 8000 or something.

Ondrej Slinták
  • 31,386
  • 20
  • 94
  • 126
Sergey Melekhin
  • 109
  • 1
  • 8
  • Yes a change of port allowed `postgres` to interact with pyramid. I figured that out by accident, but your explanation is better for those who may come into the same issue. Thank you Sergey. – thesayhey Oct 29 '15 at 12:53