26

I am working on a Linux server that is hosted on Amazon's server and I have completely set up the server. The final thing I am trying to do is host one of my old projects that I created on the server which is in the Flask framework.

I am trying to run the Python file that sets up my database that is required to run my project.

I am using a virtual machine inside the server that will run my project and every time I run the command I get the following error:

(venv) grader@ip-10-20-6-95:/var/www/catalog/catalog$ python setup_database.py
Traceback (most recent call last):
  File "setup_database.py", line 63, in <module>
    Base.metadata.create_all(engine)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/schema.py", line 2848, in create_all
    tables=tables)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1479, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1122, in _run_visitor
    **kwargs).traverse_single(element)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
    return meth(obj, **kw)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 70, in visit_metadata
    self.traverse_single(table, create_ok=True)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
    return meth(obj, **kw)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 89, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in _execute_ddl
    compiled
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) no schema has been selected to create in
 '\nCREATE TABLE users (\n\tid SERIAL NOT NULL, \n\tusername VARCHAR(100), \n\temail VARCHAR(225) NOT NULL, \n\tprofile_pic VARCHAR(225) NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n' {}

I am not sure why I get this error.
The commands I ran to set up PostgreSQL (if that should matter):

$ sudo apt-get install libpq-dev python-dev
$ sudo apt-get install postgresql postgresql-contrib
$ sudo su - postgres
$ psql
# CREATE USER catalog WITH PASSWORD 'sillypassword';
# ALTER USER catalog CREATEDB;
# CREATE DATABASE catalog WITH OWNER catalog;
# \c catalog
# REVOKE ALL ON SCHEMA public FROM public;
# GRANT ALL ON SCHEMA public TO catalog;
# \q
$ exit

How could I fix this problem?

greybeard
  • 2,102
  • 7
  • 24
  • 58
Omar_Jandali
  • 385
  • 2
  • 5
  • 15

4 Answers4

25
no schema has been selected to create in

You get this error when your search_path setting has no valid first entry (typically empty). Postgres does not know in which schema to create the table.

Fix your search_path setting, or schema-qualify object names (like: public.users). But fix your search_path in any case.
Details:

Community
  • 1
  • 1
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
  • 1
    i read in the comments that someone just entered `RESET search_path`. would something liek that work in my case – Omar_Jandali Dec 18 '16 at 20:44
  • 1
    so basically after reading the page that was linked. i can basucally just alter the permissions using `ALTER DATABASE test SET search_path = blarg,public;` and replace blarg with catalog. ot should i run the following code.. `SET search_path = blarg,public;` and replace blarg catalog.. – Omar_Jandali Dec 18 '16 at 20:45
  • @Omar_Jandali: That depends on your environment. The default is to set a default `search_path` in `postgresql.conf` for the whole DB cluster (and reload). But if you want individual settings per user / db / combination of user & db / session etc. you need settings accordingly. Your problem seems to be that you currently have no valid `search_path` at all. – Erwin Brandstetter Dec 18 '16 at 21:14
  • With that. i create a user named catalog who i set all the postgres setting with. so can i simply run the following command and it would run `SET search_path = catalog,public` or how can i fix the problem of having no valid search_path – Omar_Jandali Dec 19 '16 at 21:33
  • @Omar_Jandali: Yes, that sets the `search_path` for the current session. The linked answer has detailed instructions. – Erwin Brandstetter Dec 20 '16 at 00:00
  • I ran `SET search_path = catalog,public` in the venv environment in the posgres user with psql and then i went back and tried to run the database file, but i still get the same exact error – Omar_Jandali Dec 20 '16 at 02:59
  • It only affects the same session this way. – Erwin Brandstetter Dec 20 '16 at 03:21
11

This issue was answered already: https://dba.stackexchange.com/a/275116/114247

The fix is:

grant usage on schema public to public;
grant create on schema public to public;
Kostanos
  • 8,552
  • 4
  • 46
  • 62
  • 1
    This one worked for me. Apparently I had recreated the `public` schema and forgot to grant access. – maulik13 Feb 10 '22 at 05:16
6

I found the file created by pg_dump (under postgres 10.7) had

SELECT pg_catalog.set_config('search_path', '', false);

near the top of it. So when importing the file, it manipulated the search path, which persisted throughout the current session.

Commenting that line out (and starting a new session) fixed the problem.

Randall
  • 2,604
  • 1
  • 19
  • 22
4

You do not have a schema created. Create a schema using CREATE SCHEMA public; then you can execute grant usage on schema public to public; and grant create on schema public to public

nkosi.nuz
  • 35
  • 3