pg_restore error: role XXX does not exist : <p>Trying to replicate a database from one system to another. The versions involved are 9.5.0 (source) and 9.5.2 (target). </p>

<p>Source db name is <code>foodb</code> with owner <code>pgdba</code> and target db name will be named <code>foodb_dev</code> with owner <code>pgdev</code>.</p>

<p>All commands are run on the target system that will host the replica.</p>

<p>The <code>pg_dump</code> command is:</p>

<pre><code>    pg_dump -f schema_backup.dump --no-owner -Fc -U pgdba -h $PROD_DB_HOSTNAME -p $PROD_DB_PORT -d foodb -s --clean;
</code></pre>

<p>This runs without errors.</p>

<p>The corresponding <code>pg_restore</code> is:</p>

<pre><code>    pg_restore --no-owner --if-exists -1 -c -U pgdev -d foodb_dev schema_backup.dump
</code></pre>

<p>which throws error:</p>

<pre><code>pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3969; 0 0 ACL public pgdba
pg_restore: [archiver (db)] could not execute query: ERROR:  role "pgdba" does not exist
Command was: REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM pgdba;
GRANT ALL ON SCHEMA public TO pgdba;
GRANT ...
</code></pre>

<p>If I generate the dump file in plain text format (<code>-Fp</code>) I see it includes several entries like:</p>

<pre><code>REVOKE ALL ON TABLE dump_thread FROM PUBLIC;
REVOKE ALL ON TABLE dump_thread FROM pgdba;
GRANT ALL ON TABLE dump_thread TO pgdba;
GRANT SELECT ON TABLE dump_thread TO readonly;
</code></pre>

<p>that try to set privileges for user <code>pgdba</code> who of course doesn't even exist as a user on the target system which only has user <code>pgdev</code>, and thus the errors from <code>pg_restore</code>.</p>

<p>On the source db the privileges for example of the <code>dump_thread</code> table:</p>

<pre><code># \dp+ dump_thread
Access privileges
-[ RECORD 1 ]-----+--------------------
Schema            | public
Name              | dump_thread
Type              | table
Access privileges | pgdba=arwdDxt/pgdba+
                  | readonly=r/pgdba
Column privileges |
Policies          |
</code></pre>

<p>A quick solution would be to simply add a user <code>pgdba</code> on the target cluster and be done with it. </p>

<p>But shouldn't the <code>--no-owner</code> take care of not including owner specific commands in the dump in the first place?</p>
