current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
add comment

3 Answers

up vote 1 down vote

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

share|improve this answer
    
No problem on heroku - everything works fine. Just not working in development using postgres, which from what I understand is quite common. Thanks for the info. –  pvskisteak5 Dec 6 '12 at 4:27
add comment
up vote 0 down vote

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.

share|improve this answer
    
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. –  pvskisteak5 Dec 6 '12 at 3:18
    
For create a database in postgresql i just do CREATE USER my_user; CREATE DATABASE my_db OWNER my_user; ALTER USER my_user WITH ENCRYPTED PASSWORD 'password'; ALTER ROLE my_user WITH CREATEDB; –  Jonathan Tribouharet Dec 6 '12 at 3:37
    
after doing this and listing databases in psql, my username is still not showing up under Access privileges for something_development. When running the CREATE commands you suggested, it said they already exist. I also granted access privilege to my_user for something_development, which shows when listing all databases in psql. –  pvskisteak5 Dec 6 '12 at 4:39
add comment
up vote 0 down vote

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.