pg_restore: [archiver (db)] connection to database “dvdrental” failed: FATAL: Peer authentication failed for user “postgres”

PostgreSQLI wanted to learn postgresql operations on real database. So, I was following online tutorial where I downloaded a sample database ‘dvdrental.tar’ and got the error ‘ FATAL: Peer authentication failed for user “postgres” ‘ while loading into postgresql. I created a new database in postgresql using the following command.

$ sudo -u postgres psql
postgres=# CREATE DATABASE dvdrental;
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------+----------+----------+-------------+-------------+-----------------------
dvdrental | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
ruby_test_app | rubynew | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)

You can see that the database has been created. Then, I logged out of the postgresql shell typing command ‘\q’ or use shortcut key ‘ctrl+d’.

FATAL: Peer authentication failed for user “postgres”

Then, I wanted to load the sample database ‘dvdrent.tar’ into the newly created database. Thus, I used the following command on Linux shell.

$ pg_restore -U postgres -d dvdrental /home/iankits/Downloads/dvdrental.tar -W -h localhost
pg_restore: [archiver (db)] connection to database "dvdrental" failed: FATAL: Peer authentication failed for user "postgres"

Solution:

I was surprised to see this error but after reseraching a bit on this error, I managed to solve this problem using following three steps.

First Step: Set a password for the user postgres.

$ sudo -u postgres psql
postgres=# \password
Enter new password:
Enter it again:
postgres=# \q

(or press ctrl+d)

Second Step: Test whether the password setup works.

$ psql -U postgres -h localhost
Password for user postgres:
psql (9.1.9)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
postgres=#

and it will open the postgresql shell.

Third Step: Load the database using the following command

$ pg_restore -U postgres -d dvdrental /tmp/dvdrental.tar -W -h localhost
password:

And it will load the sample database into the dvdrental db of postgresql.

To confirm that sample database has been loaded into the dvdrantal db, use object browser panel (I am using ‘pgAdmin3′) and it will look like below

I hope this may help you resolving error ‘ FATAL: Peer authentication failed for user “postgres” ‘. :-)

  1 comment for “pg_restore: [archiver (db)] connection to database “dvdrental” failed: FATAL: Peer authentication failed for user “postgres”

  1. January 29, 2014 at 8:18 am

    Awesome, it works like a charm :)

Comments are closed.

Social Media Icons Powered by Acurax Web Design Company