postgresql - 如何以root身份将数据库导入PostgreSQL？

Question

我想将我的数据库导入到我服务器的数据库中。因此，我将数据库转储文件复制到服务器的根目录并登录并执行以下操作:

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"

然而，iwidget 是一个角色，并已授予此数据库的所有权限:

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)

我做错了什么？

Answer 1

来自文档:

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.

您正在执行 sudo -u postgres，但正在尝试连接为 iwidget。

您需要创建一个名为iwidget 的用户并以此用户身份登录。