postgresql - pg_dump: error: role with OID nnnnn does not exist - Stack Overflow

I'm upgrading a long out-of-date server from v11 to v16 (I've upgraded another similar server from v11 to v16 and had no issue). However, for this one I get an error when doing the following upgrade command:

"C:\Program Files\PostgreSQL\16\bin\pg_upgrade.exe" -U postgres -j 16 -d "F:\MonikaCloudData\PG11\data" -D "E:\PostgreSQL\16\data" -b "C:\Program Files\PostgreSQL\11\bin" -B "C:\Program Files\PostgreSQL\16\bin"

Here's the error:

pg_dump: error: role with OID 21338 does not exist
command: "C:/Program Files/PostgreSQL/16/bin/pg_dump" --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="E:/PostgreSQL/16/data/pg_upgrade_output.d/20250131T013503.421/dump/pg_upgrade_dump_16393.custom" ^"dbname^=monika2x^" >> "E:/PostgreSQL/16/data/pg_upgrade_output.d/20250131T013503.421/log/pg_upgrade_dump_16393.log" 2>&1

Everything runs perfectly fine as v11 without this missing role. How can I...

1. create a role with a specific OID, or
2. fix the relations which have this odd OID to something defined

I'm upgrading a long out-of-date server from v11 to v16 (I've upgraded another similar server from v11 to v16 and had no issue). However, for this one I get an error when doing the following upgrade command:

"C:\Program Files\PostgreSQL\16\bin\pg_upgrade.exe" -U postgres -j 16 -d "F:\MonikaCloudData\PG11\data" -D "E:\PostgreSQL\16\data" -b "C:\Program Files\PostgreSQL\11\bin" -B "C:\Program Files\PostgreSQL\16\bin"

Here's the error:

pg_dump: error: role with OID 21338 does not exist
command: "C:/Program Files/PostgreSQL/16/bin/pg_dump" --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="E:/PostgreSQL/16/data/pg_upgrade_output.d/20250131T013503.421/dump/pg_upgrade_dump_16393.custom" ^"dbname^=monika2x^" >> "E:/PostgreSQL/16/data/pg_upgrade_output.d/20250131T013503.421/log/pg_upgrade_dump_16393.log" 2>&1

Everything runs perfectly fine as v11 without this missing role. How can I...

1. create a role with a specific OID, or
2. fix the relations which have this odd OID to something defined

• postgresql

Share Follow asked Jan 30 at 14:58 fluffy_martfluffy_mart 11111 silver badge99 bronze badges 3

• Can you successfully dump it with the v11 pg_dump? If so, a dump and restore with v11 might "clean" it and let you dump the cleaned one with v16 pg_dump. – Richard Huxton Commented Jan 30 at 15:10
• That seems to be some kind of data corruption. – Laurenz Albe Commented Jan 30 at 17:04
• Thank you. It's a bit of a mess and should have been upgraded years ago. Backed it out in the end as we had issues with the WAL files for some reason. It's happy again with v11 for now. Another battle for another day... – fluffy_mart Commented Feb 1 at 0:48

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

It appears like I've found a solution.

Somewhere, I found a list of queries to run to track this down. One of these was SELECT * FROM pg_largeobject_metadata WHERE lomowner = OID;

So if I loop-through all large objects and reassign ownership it sort this for me!

DO $$ 
DECLARE 
    lo_id OID;
BEGIN
    FOR lo_id IN 
        SELECT oid FROM pg_largeobject_metadata WHERE lomowner = 21338
    LOOP
        EXECUTE format('ALTER LARGE OBJECT %s OWNER TO postgres;', lo_id);
    END LOOP;
END $$;