Import .dump file

psql -f filenamed.dmp db_name

Advertisements

psql: FATAL: password authentication failed for user “postgres”

1)

 cd /etc/postgresql/9.1/main/ 

2)

 sudo vim pg_hba.conf 

3) press i for insert mode and change

 local   all             postgres                                md5 

to

 local   all             postgres                                trust 

4) press escape, write :wq! and hit enter key.
5)

 sudo service postgresql restart 

6)

 sudo -u postgres createuser --superuser $user_name 

when you will run above command, terminal will ask:
Enter name of role to add: [write any user role]
7)

 sudo -u postgres psql 

it will open the postgre command prompt.
8)

 \password [write user role which you have given above]
then it will ask to enter password two times for above created user like this:
Enter new password:
Enter it again:

9) Again

 sudo vim pg_hba.conf 

10) press i for insert mode and change

 local   all             postgres                                trust 

to

 local   all             postgres                                md5 

11)

 sudo service postgresql restart 

Now you can use above user role as username and its password to login in localhost/phppgadmin or in database.yml

PostgreSQL setup and Installation

Follow is the link to install PostgreSQL in ubuntu
https://help.ubuntu.com/community/PostgreSQL.

I faced some issues in above link that some of commands did not work. So, I have extracted those commands that work for me. These are following:

1) In your terminal run these following commands:

sudo apt-get install postgresql-client
sudo apt-get install postgresql

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

sudo apt-get install pgadmin3

Basic Server Setup:
To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

sudo -u postgres psql postgres

Set a password for the “postgres” database role using the command:

\password postgres

Type Control+D to exit the posgreSQL prompt.

Install phppgadmin:

sudo apt-get install phppgadmin

for more about phppgadmin: https://help.ubuntu.com/community/phpPgAdmin
Setup username and password for phppgadmin:

sudo -u postgres createuser --superuser write_user_name
sudo -u postgres psql
\password again_write_same_user_name

It will ask to enter password two times. Then press Ctrl + D to exit from pgsql command prompt.
In browser hit: localhost/phppgadmin
Click PostgreSQL at left of window, to connect it with server. Now it will ask for username and password. Here you will use recently created above username and password.
You will see only one database named as “postgres”.

Now we will do setup of pg in our project.

Open your project and in Gemfile of it, add:

gem 'pg', '0.15.1'

In database.yml:

common: &common
  pool: 5
  timeout: 5000
  username: write_username_that_was_used_to_loggedIn_at_phppgAdmin
  password: write_password_that_was_used_to_loggedIn_at_phppgAdmin
  adapter: postgresql
  host: localhost
  port: 5432

development:
  <<: *common
  database: datebase_name_for_development
test:
  <<: *common
  database: database_name_for_test
production:
  <<: *common
  database: database_name_for_production

In terminal goto your project directory and run following commands to create database of your project:

rake db:create
rake db:migrate
rake db:seed

Setup has completed. 🙂