How to import database to PostgreSQL as root?

Question

I want to import a database of mine to a database of my server. So, I copied my database dump file to my server's root directory and logged in and did this:

root@iWidgetServer1:~# sudo -u postgres psql -U iwidget -d iwidget -f iwidget_dump2.sql 
could not change directory to "/root"
psql: FATAL:  Peer authentication failed for user "iwidget"

However, iwidget is a role and has granted all priviliges for this database:

root@iWidgetServer1:~# sudo -u postgres psql -l
could not change directory to "/root"
                                     List of databases
       Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------------+----------+----------+-------------+-------------+-----------------------
 iwidget          | iwidget  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/iwidget          +
                  |          |          |             |             | iwidget=CTc/iwidget
 postgres         | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 sample_db        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
                  |          |          |             |             | postgres=CTc/postgres
 template1        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
                  |          |          |             |             | postgres=CTc/postgres
 template_postgis | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
(6 rows)

What am I doing wrong?

Quassnoi · Accepted Answer · 2014-06-26 07:16:09Z

2

From the docs:

The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

You're doing sudo -u postgres, but are trying to connect as iwidget.

You need to create a user named iwidget and login as this user.

answered Jun 26, 2014 at 7:16

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

... or just set your pg_hba.conf to use md5 authentication so you can use a password instead of having the same unix user.

Alex · Accepted Answer · 2014-06-26 07:13:43Z

1

This seems to be more a problem with the File permissions of the dump instead of Postgres. Have you tried moving the dump to a folder not owned by root?

answered Jun 26, 2014 at 7:13

Alex

881 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to import database to PostgreSQL as root?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related