Feedback-ID: i76984098:Fastmail
Message-ID: <c3e27dc2-343d-48dd-bf05-a1404e6939ec@aklaver.com>
Date: Sat, 14 Jun 2025 08:21:19 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_restore ERROR: permission denied to change default privileges
To: Rachel Roch <rroch@tutanota.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 Pgsql General <pgsql-general@lists.postgresql.org>
References: <OSeT_Gx--F-9@tutanota.de> <1407215.1749838987@sss.pgh.pa.us>
 <e12c9b8d-0d77-4462-9f52-06376d5f9d65@aklaver.com-OSehXyh----9>
 <OShaniJ--F-9@tutanota.de>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <OShaniJ--F-9@tutanota.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/c3e27dc2-343d-48dd-bf05-a1404e6939ec%40aklaver.com>
Precedence: bulk

On 6/14/25 01:42, Rachel Roch wrote:
> 
> 
> 
> 13 Jun 2025, 20:13 by adrian.klaver@aklaver.com:
> 
>>
>> To get at an editable script you can do something like:
>>
>> pg_restore -f my_database_txt.sql  my_database.dump
>>
>> This will give you a plain text version of the dump that you can feed back to psql to load into remote database.
>>
> 
> Thanks Adrian !
> 
> I had thought maybe maybe I could do a "pg_restore -l my_database.dump" and ignore the relevant line using whatever the other flag is, but sadly there doesn't appear to be enough flexibility, i.e.
> 
> pg_restore -l my_database.dump | fgrep -F postgres
> gives:
> 2067; 826 16607 DEFAULT ACL public DEFAULT PRIVILEGES FOR TABLES postgres
> 
> But
> 
> pg_restore -l my_database.dump | fgrep -F my_database_ro
> gives nothing.   :(

That is because the lines returned from pg_restore -l are not the full 
commands, they represent(generally) a summary of the object, its name 
and the owner.

The error message and your first example above show that the command is 
there. See at here:

https://www.postgresql.org/docs/current/app-pgrestore.html

in the Examples section how you can comment out the line. Then you could 
use -L to feed the list back to pg_restore.

Isn't fgrep -F redundant? As I understand it fgrep = grep -F

> 
> So either your solution or Tom's "just ignore it" sound like they'll work.

-- 
Adrian Klaver
adrian.klaver@aklaver.com