CODEWITHSUNDEEP
ruby-on-railspostgresql
pvskisteak5
pvskisteak5

Reputation: 4262

Postgres drop database in rails app with rake command

I am having trouble dropping my postgres database for a rails app - switched recently from sqlite after deploying to heroku. When running my rake task to aggregate data and populate the database, rake db:create db:migrate all works, but db:drop is not working. I am getting the following error in my terminal after running rake db:drop db:migrate db:create all. 'all' is a rake task for an aggregator.

Couldn't drop something_development : #<PG::Error: FATAL:  database "postgres" does not exist
>
Couldn't drop something_test : #<PG::Error: could not connect to server: No such file or directory
    Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
>
something_development already exists
could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

'This is a look at my database.yml file...` 

development:
  adapter: postgresql
  host: localhost
  database: something_development

  username: username


test:
  adapter: postgresql
  database: something_test
 pool: 5
 timeout: 5000

production:
 adapter: postgresql
 host: localhost
 username: username
 database: something_production

Also here is a list of databases from psql command line:

                                         List of databases
         Name         |      Owner      | Encoding | Collate | Ctype |          Access privileges          
----------------------+-----------------+----------+---------+-------+-------------------------------------
username              | username        | UTF8     | en_US   | en_US | 
something_development | username        | UTF8     | en_US   | en_US | 
template0             | username        | UTF8     | en_US   | en_US | =c/username+          |                 |          |         |       | username=CTc/username
template1             | username        | UTF8     | en_US   | en_US | =c/username    +      |                 |          |         |       | username=CTc/username
(4 rows)

EDIT - I am trying to do this in development before pushing to heroku. Do i need to add access privileges to my username? I tried GRANT UPDATE ON something_development TO username but received ERROR: relation "something_development" does not exist. Will remember that for Heroku - thanks.

Does anyone have any ideas on what might be happening or some resources that might point me in the right direction? Appreciate any help - thanks.

Upvotes: 1

Views: 2303

Answers (3)

pvskisteak5
pvskisteak5

Reputation: 4262

Figured out the problem - turns out you cannot rename the database "postgres" because it needs that to manage the database. I had tried renaming postgres to something_development. To fix this, I created a postgres database.

Upvotes: 0

An Nguyen
An Nguyen

Reputation: 126

At first, you should make sure that which kind of postgres DB using on Heroku: Shared DB or Heroku Postgres.

If you are using Shared DB (it's recommended that shouldn't use any more), unfortunately you have to remove your Heroku app and re-create new one with the same app name.

If you are using Heroku PG, you could find your DB here: https://postgres.heroku.com/databases and just destroy and add another DB, it's like drop and re-create your DB, after that you can run migration as well. With Heroku PG, I'm not sure whether we can run db:drop now (It's been a while since I was fail to use this command on Heroku)

About configuration you should follow few steps below:

install gem pg

using pgAdmin III or something like that to try to connect to your PG server. If you're successful, use this information for your database.yml file.

Notice: When you push your code to Heroku, Heroku will automatically create new database.yml basing on their PG server information, so you don't need to care about configuration database on Heroku. Remember removing sqlite gem before pushing your app to Heroku

Upvotes: 1

Jonathan Vukovich-Tribouharet
Jonathan Vukovich-Tribouharet

Reputation: 1988

On heroku you don't have privileges for drop database, you have to do

heroku pg:reset

If your database is not on heroku, check the user (of postrgesql) has privileges.

Upvotes: 0

Related Questions