无法使用其他用户名从pg_dump恢复psql数据库 [英] Unable to restore psql database from pg_dump with a different username

问题描述

我需要从computer1中以postgres用户名1转储一个postgres数据库，然后在具有postgres用户名2的computer2上恢复它.我一直遇到似乎备份文件要使用username1的错误:

I need to dump a postgres database from computer1 with postgres username1 and then restore it on computer2 with postgres username2. I keep running into the error that looks like the backup file wants to use username1:

当我在computer2上运行它时:

When I run this on computer2:

psql dbname < backupname.pgsql

我收到此错误:

ERROR:  role "username1" does not exist

我尝试过:

// Dumping from computer1:

pg_dump dbname > backupname.sql
pg_dump dbname > backupname.pgsql
pg_dump -U username1 dbname -N topology -T spacial_ref_sys > backupname.pgsql


// Restoring on computer2:

psql dbname < backupname.pgsql

是否需要对转储或还原进行修改以克服此问题?

Is it the dumping or the restoring that needs to be modified to get past this?

推荐答案

问题出在转储上.通过此帖子的见识，我能够解决此问题使用:

The problem is with the dumping. With insight from this post I was able to resolve this using:

// On Computer1

pg_dump dbname -O -x > backupname.sql


// On Computer2

psql dbname < backupname.sql

与pg_dump一起使用的选项标志是:

The option flags used with pg_dump are:

-O   <-- No owner
         Do not output commands to set ownership of objects to match the original database

-x   <-- No privileges
         Prevent dumping of access privileges (grant/revoke commands)

有关更多信息，请参见 PostgreSQL文档pg_dump 选项标志.

See the PostgreSQL docs for pg_dump for more info on the option flags.

这篇关于无法使用其他用户名从pg_dump恢复psql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！