postgres without Heroku

[Larry Gates]

Hi all,

I am working on incorporating postgres into psiturk. I am doing it without Heroku.

What changes do I need to make in the config.txt file as well as any other python files to incorporate it into the experiment?

The documentation and searching the Google Group didn't initially show how to connect it without using Heroku.

Any help would be wonderful!

Can provide any information that is needed.

Thanks!

[Dave Eargle]

Just use a postgres database url in config.txt! It gets passed to sqlalchemy, which can handle psql, as long as you have postgres python libraries installed.

--
You received this message because you are subscribed to the Google Groups "PsiTurk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to psiturk+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/psiturk/fbb4c4e2-8070-4856-90b3-ca391b400d00n%40googlegroups.com.

[Larry Gates]

Thanks!

Unlike sqlite, the URL I am using in the config.txt file is having issues. It will not create the database.

The URL I am using:

database_url = postgresql://psiturk1:X@localhost:5432/pgparticipants

Password above changed to X

Below is the "Role Name | List of roles Attributes"

 psiturk1  | Create DB

But I get the error of

>

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  database "pgparticipants" does not exist

Do you have any advice on that? The solutions I have found would require me to add code to "create" the database programmatically, which seems like the wrong direction.

[Dave Eargle]

psiturk/sqlalchemy won't create the postgres database for you. You'll need to create it yourself, and then set a valid connection string in config.txt. The only magic thing about Heroku is that they set up a postgres server for you and give you its connection string as env var DATABASE_URL.

To view this discussion on the web visit https://groups.google.com/d/msgid/psiturk/a26e648b-1915-4958-bd88-0133c7eb4510n%40googlegroups.com.

[Larry Gates]

Okay, that helps!

When I figured out all the small details,  I will try to post an update for others not doing it through Heroku.

Or maybe more questions if I can't figure something out.

[Larry Gates]

Actually, now I am certain I have an issue outside of my control.

Directly from my server.log

[2022-03-03 13:40:36 -0500] [13992] [ERROR] Exception in worker process

Traceback (most recent call last):

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context

    self.dialect.do_execute(

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 608, in do_execute

    cursor.execute(statement, parameters)

psycopg2.errors.SyntaxError: type modifier is not allowed for type "text"

LINE 20:  datastring TEXT(4294967295),

[Dave Eargle]

It's still in your control -- that error tells me that your database_url doesn't have `postgres://` at the beginning for some reason. Because if it did, it wouldn't try `TEXT(number)`, which is throwing the error there: https://github.com/NYUCCL/psiTurk/blob/822d0882ced0c5ccf698b108bdd1bdebef228048/psiturk/models.py#L67-L70

To view this discussion on the web visit https://groups.google.com/d/msgid/psiturk/69d91823-51a2-40a9-a410-cb4a6dc8a092n%40googlegroups.com.

[Larry Gates]

Ah okay. I have "postgresql" at the front, instead of "postgres", it looks like it is checking explicitly for "postgres". Was following this guide about URL strings (https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) (Section 34.1.1.2.)

I will try that change when I get back to the computer and report back if I have any issues!

Thank you for linking the code, that helps me see better where I need to look before posting here!

[Larry Gates]

Sorry for the delay reply.

I was able to successfully to get it to write to the database (the first time). I did get the following error:

[2022-03-07 14:13:12 -0500] [18858] [ERROR] Exception in worker process

Traceback (most recent call last):

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context

    self.dialect.do_execute(

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 608, in do_execute

    cursor.execute(statement, parameters)

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "pg_type_typname_nsp_index"

DETAIL:  Key (typname, typnamespace)=(apscheduler_jobs, 2200) already exists.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "/usr/local/lib/python3.8/dist-packages/gunicorn/arbiter.py", line 583, in spawn_worker

    worker.init_process()

  File "/usr/local/lib/python3.8/dist-packages/gunicorn/workers/ggevent.py", line 162, in init_process

    super().init_process()

  File "/usr/local/lib/python3.8/dist-packages/gunicorn/workers/base.py", line 119, in init_process

    self.load_wsgi()

  File "/usr/local/lib/python3.8/dist-packages/gunicorn/workers/base.py", line 144, in load_wsgi

    self.wsgi = self.app.wsgi()

  File "/usr/local/lib/python3.8/dist-packages/gunicorn/app/base.py", line 67, in wsgi

    self.callable = self.load()

  File "/usr/local/lib/python3.8/dist-packages/psiturk/experiment_server.py", line 68, in load

    return util.import_app("psiturk.experiment:app")

  File "/usr/local/lib/python3.8/dist-packages/gunicorn/util.py", line 358, in import_app

    mod = importlib.import_module(module)

  File "/usr/lib/python3.8/importlib/__init__.py", line 127, in import_module

    return _bootstrap._gcd_import(name[level:], package, level)

  File "<frozen importlib._bootstrap>", line 1014, in _gcd_import

  File "<frozen importlib._bootstrap>", line 991, in _find_and_load

  File "<frozen importlib._bootstrap>", line 975, in _find_and_load_unlocked

  File "<frozen importlib._bootstrap>", line 671, in _load_unlocked

  File "<frozen importlib._bootstrap_external>", line 848, in exec_module

  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed

  File "/usr/local/lib/python3.8/dist-packages/psiturk/experiment.py", line 119, in <module>

    scheduler.start(paused=True)

  File "/usr/local/lib/python3.8/dist-packages/apscheduler/schedulers/gevent.py", line 21, in start

    BaseScheduler.start(self, *args, **kwargs)

  File "/usr/local/lib/python3.8/dist-packages/apscheduler/schedulers/base.py", line 158, in start

    store.start(self, alias)

  File "/usr/local/lib/python3.8/dist-packages/apscheduler/jobstores/sqlalchemy.py", line 68, in start

    self.jobs_t.create(self.engine, True)

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/schema.py", line 928, in create

    bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 2095, in _run_visitor

    conn._run_visitor(visitorcallable, element, **kwargs)

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1656, in _run_visitor

    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single

    return meth(obj, **kw)

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/ddl.py", line 827, in visit_table

    self.connection.execute(

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1011, in execute

    return meth(self, multiparams, params)

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection

    return connection._execute_ddl(self, multiparams, params)

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1068, in _execute_ddl

    ret = self._execute_context(

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context

    self._handle_dbapi_exception(

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception

    util.raise_(

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 182, in raise_

    raise exception

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context

    self.dialect.do_execute(

  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 608, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pg_type_typname_nsp_index"

DETAIL:  Key (typname, typnamespace)=(apscheduler_jobs, 2200) already exists.

[SQL:

CREATE TABLE apscheduler_jobs (

    id VARCHAR(191) NOT NULL,

    next_run_time FLOAT(25),

    job_state BYTEA NOT NULL,

    PRIMARY KEY (id)

)

]

(Background on this error at: http://sqlalche.me/e/13/gkpj)

After running and completing an experiment, it did not create this error. However, if I delete the database, it does this again.

For reference, here is the database URL again:

database_url = postgres://psiturk1:X@localhost:5432/pgparticipants

Doing some looking into the "postgres" versus "postgresql", I did have another question.

Why does psiturk not accept other dialects? I believe below are relevant links:

• https://github.com/sqlalchemy/sqlalchemy/issues/6083#issuecomment-801478013  (From the package that you are using)
  
• https://stackoverflow.com/questions/37910066/heroku-sqlalchemy-database-does-not-exist
  • This one shows that if I use "postgresql" it would be able to create the database, so I can't confirm it is accurate. But figure I would include it

I can work around the dialect (I think, once I can confirm everything works on my end) but was more of a long term question. If this last question about the dialect would be better as a GitHub Issue, I can do that.

I still plan on replying how I set up postgres without Heroku as for reference here, just wanted to get things wrapped up before I did that.

Thank you again!!!

[Dave Eargle]

I'm guessing that you're not actually deleting the database schema, but rather, you're deleting your `assignments` table from your database/schema, and thinking that's the same thing.  Psiturk uses several tables. Your error shows that it's trying to create the table `apscheduler_jobs`, but it already exists. IIRC, sqlalchemy is an all-or-nothing. If it finds a missing table, it tries to create all tables, or it creates none of them.

To view this discussion on the web visit https://groups.google.com/d/msgid/psiturk/27631dbc-79fb-4a25-8e81-5aaef9ef2d20n%40googlegroups.com.

[Larry Gates]

Okay, I think that makes sense! I will double check about how I was deleting them, but it looks like it works at the moment. For reference, "psiturk download_datafiles" command works as expected.

For my second point, would you like me to make a github issue about psiturk accepting "postgresql://" or other dialects or is that not possible? Figure I would ask before doing that.

[Dave Eargle]

Sure, you can open a github issue with that. That's an interesting sqlalchemy github issue thread you linked. Probably the easiest fix will be for psiturk to check for either postgres:// or postgresql://, maybe via regex.

To view this discussion on the web visit https://groups.google.com/d/msgid/psiturk/527d1d5b-d093-4670-9e50-60c1b7a353a9n%40googlegroups.com.

[Larry Gates]

Thanks and done!

As I said earlier, here are a collection of notes for anyone looking at this thread.

I had to make this for my research project for other members to install, so might as well share it here.

This is not an official guide, just something anyone can use if they need help and find this thread.

Feel free to remove this if it is not helpful or is against the guidelines.

---

Setting up postgres for Psiturk

This installation file is written for postgres for Psiturk without using Heroku.

Operating System Being Used

This installation was done on an Ubuntu version of Windows SubSystem for Linux.

Ubuntu 20.04.3 LTS

Installation

The following commands are listed in the order I installed them.

NOTES: You can probably combine them into single install statements, but making it clear which ones were done. Also, some of these might not need to be done but I can't guarantee which ones could be omitted.

sudo apt-get install python3-pip

sudo apt-get install python-dev

sudo apt-get install python-sqlalchemy

sudo apt-get install postgresql

sudo apt-get install postgresql-contrib

sudo apt-get install libpq-dev

sudo apt-get install postgresql-client

sudo apt-get install postgresql-client-common

sudo python3 -m pip install psycopg2 # Above command can be done as sudo pip3 install psycopg2

These should be all you need for installation of postgres for psiturk.

Using postgres

You now should successfully have postgres installed on your Ubuntu system.

After installing, if you try running psql, you should get an error about "could not connect to server". Which is the same thing that happened when making this tutorial.

To solve this, follow the steps below to get the database and users needed.

The first thing that needs to happen is the service for postgres needs to start.

The following command is to start the service:

sudo service postgresql start

Resource

Creating User

Once starting the server, you will get a different error message. This error message will correspond to not having a user for that in postgres. To interact with postgres, run the following command to get to the "postgres" user.

NOTE: this is not the user for the config.txt file. This is for accessing postgres interactively on your Linux system

sudo -i -u postgres

Once you type in your computer password, you will be in the postgres userspace.

You will need to create a user for the database, this is the one that you can use (you of course can create more). Type the following into the terminal :

createuser --interactive --pwprompt

This will create an interactive prompt in the terminal.

Resources for creating a user:

• https://www.a2hosting.com/kb/developer-corner/postgresql/managing-postgresql-databases-and-users-from-the-command-line
• http://manpages.ubuntu.com/manpages/bionic/man1/createuser.1.html

You will now need to alter the permissions for the user to create a database.

Create Database

Once you have that, in the terminal (through Linux, not psql) you type the following:

createdb partdb

Where partdb is the database you want to create and use in psiturk.

Resources for this section:

• https://www.a2hosting.com/kb/developer-corner/postgresql/managing-postgresql-databases-and-users-from-the-command-line

Changing Permissions for a User

You need to grant permission so that psiturk can create tables and connect to the database.

The command (in psql):

GRANT create, connect ON DATABASE partdb TO psiturkuser;

Resources for this section:

• https://www.postgresql.org/docs/9.1/sql-grant.html

Setting up config.txt for DATABASE_URL

In this postgres documentation, it describes how to do the URL for connecting to the database.

This is what you use for config.txt.

Below is an example:

database_url = postgres://psiturkuser:psiturkpass@localhost:5432/partdb

Explaining the parts:

• postgres://: Database URL to connect to the database. NOTE: you cannot use postgresql at the moment, psiturk is looking for the exact string
• psiturkuser:psiturkpass: The username / password combination you created above.
• @localhost:5432: location of the database, this example is doing it locally on the current server.
• /partdb: Name of the database at the postgres location. This database will not be created inside the folder where you project is (that is what sqlite database was doing.) I am sure you can change where it is physically located but that is not included in the database.

Helpful postgres commands

If you want to see the list of databases available (lowercase L):

\l

If you want to connect to a certain database, to do queries through the command line:

\c

Once you are in a certain database, you can do the following command to see all the available tables:

\dt

Resources used for this section:

• https://www.postgresqltutorial.com/postgresql-show-tables/
• https://www.postgresqltutorial.com/postgresql-list-users/