1

I enrolled into a web-host plan so I could have my rails apps published. They provided me with a server and access to it. This is a shared CentOS Linux server and I can access its bash.

I was trying to load my database schema into the postgres database (v8.4) they are offering but it gives me an error when doing so. When I execute:

 RAILS_ENV=production rake db:schema:load

The following error is generated:

-- enable_extension("plpgsql")
rake aborted!
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "EXTENSION"
LINE 1: CREATE EXTENSION IF NOT EXISTS "plpgsql"
               ^
: CREATE EXTENSION IF NOT EXISTS "plpgsql"
/home/user/path-to-app/db/schema.rb:17:in `block in <top (required)>'
/home/user/path-to-app/db/schema.rb:14:in `<top (required)>'
PG::SyntaxError: ERROR:  syntax error at or near "EXTENSION"
LINE 1: CREATE EXTENSION IF NOT EXISTS "plpgsql"
               ^
/home/user/path-to-app/db/schema.rb:17:in `block in <top (required)>'
/home/user/path-to-app/db/schema.rb:14:in `<top (required)>'
Tasks: TOP => db:schema:load
(See full trace by running task with --trace)

Because the web-host provider does not allow many db privileges, and due to my unfamiliarity with PostgreSQL, I would prefer a solution changing something on the rails side...

Is there something I can do on the rails side to work around (or hopefully address) this issue?

If nothing can be really done from the rails side I would have to be specific with the privileges necessary.

This PostgreSQL EXTENSION object seems to demand privileges my db user does not have...

What would be the minimum privileges for the rails database user running the db migration in PostgeSQL?

Update

This is about PostgreSQL 8.4, witch according to the comments here do not have the "CREATE EXTENSION" command.

S-Man
  • 22,521
  • 7
  • 40
  • 63
The Fabio
  • 5,369
  • 1
  • 25
  • 55
  • 1
    `CREATE EXTENSION` was introduced with Postgres 9.1 – Roman Kiselenko Aug 26 '15 at 09:11
  • that is a relief... should i downgrade the pg gem? – The Fabio Aug 26 '15 at 09:12
  • it's is not about the `pg` gem, it's all about Postgres database itself. `pg` gem it's just the ruby wrapper for `Postgres` database. – Roman Kiselenko Aug 26 '15 at 09:13
  • possible duplicate of [Why can only a superuser CREATE EXTENSION hstore, but not on Heroku?](http://stackoverflow.com/questions/20723100/why-can-only-a-superuser-create-extension-hstore-but-not-on-heroku) – Krule Aug 26 '15 at 09:13
  • Also related to http://stackoverflow.com/questions/13410631/how-to-solve-privileges-issues-when-restore-postgresql-database – Max Williams Aug 26 '15 at 09:13
  • why you just do not do the migration? `RAILS_ENV=production bundle exec rake db:migrate` – Roman Kiselenko Aug 26 '15 at 09:16
  • I understand that Зелёный, but there is nothing i can do with the db server itself.... is there nothing i can do on rails side? – The Fabio Aug 26 '15 at 09:16
  • OK... the `rake db:migrate` command did it (some many other errors happened in this adventure with his provider that i even forgot to go simple) if you wish I can mark your answer as solution. – The Fabio Aug 26 '15 at 09:32
  • Your hosting provider should provide a less prehistoric version of PostgreSQL. If this is a new app deployment then it's absurd to use 8.4. It'd be like designing a new app to run on Windows XP. – Craig Ringer Aug 26 '15 at 13:26

1 Answers1

2

CREATE EXTENSION was introduced with Postgres 9.1, i suggest you to run migrations:

RAILS_ENV=production bundle exec rake db:migrate
Roman Kiselenko
  • 43,210
  • 9
  • 91
  • 103