Why is pg_dumpall throwing an "OID does not exist'' error?

Question

When I run pg_dumpall I'm now getting this:

$ pg_dumpall -f may2012
pg_dump: schema with OID 7549789 does not exist
pg_dumpall: pg_dump failed on database "dealermade", exiting

How do I resolve it?

UPDATE

Here is some of the data requested by @depesz:

select relname from pg_class where relnamespace = 7549789;
   relname    
--------------
 lotdata
 lotdata_pkey
(2 rows)

Answer 1

do:

select * from pg_class where relnamespace = 7549789;

and:

select * from pg_proc where pronamespace = 7549789;

These are the most likely suspects (there are other objects that can be related to schema).

Make sure you run the queries in dealermade database.

After you will see the results - answer can be obvious (for example: delete from ... where ..), or more tricky - depending on what is the bad object. But at least you will have some data.

Also - try enabling logging of all queries, and rerun pg_dump to see what was the exact query that failed.

Answer 2

This has been reported a number of times over the years, but the root cause has never been really identified. The one thing which seems to be clear is that it is caused when a schema is dropped but some references to it are not. There is evidence that at least in some cases this is a "special" schema created for a user's temporary tables.

The traditional fix is to log in as a database superuser and delete the lingering references to the schema from system tables. pg_depend and pg_type seem to be the main tables where these are found. Some people prefer to create a schema and update its oid to match the missing schema and poke around first, but I haven't seen that yield much additional information or recover any data.

This may not be related, but it may be a clue: I concerned by the statement that you VACUUM FULL ANALYZE whenever you do anything major. What version is this? Do you run that against the whole database, or just specific tables? Do you REINDEX afterward? Generally VACUUM FULL should be considered to be a very aggressive form of maintenance which should only be needed if there was some problem.

If you feel that you have evidence of what caused the problem, please share with the PostgreSQL community so that we can get it fixed. So far nobody has produced a reproducible test case or provided enough forensic evidence to track it down, and it has been infrequent enough to be hard to catch. You could start with an email to [email protected] if you have some evidence.

Answer 3

The other answers didn't help me but this did and wanted to share. This issue occurs when there is a corruption in the PostgreSQL system catalog. Here are the steps I used:

Delete any records that may show up in the following queries:

1. select * from pg_type where typnamespace=xxxxxxx;
2. select * from pg_class where relnamespace = xxxxxxx;
3. select * from pg_operator where oprnamespace = xxxxxxx;
4. select * from pg_conversion where connamespace = xxxxxxx;
5. select * from pg_opclass where opcnamespace = xxxxxxx;
6. select * from pg_aggregate where aggfnoid = xxxxxxx or aggtransfn = xxxxxxx or aggfinalfn = xxxxxxx;
7. select * from pg_proc where pronamespace = xxxxxxx;

Delete rows where oid xxxxxx appears and re-execute the backup in PGADMIN or pg_dump, and it should work.

Answer 4

pg_dump: error: schema with OID 514367 does not exist
pg_dumpall: error: pg_dump failed on database "postgres", exiting

So I hit this myself and this question set me on the path to a fix, but like @mikato alluded to in his comment, I could not delete the offending rows in pg_proc.

I found them with the block of commands from @Brian

select * from pg_type where typnamespace=514367;
select * from pg_class where relnamespace = 514367;
select * from pg_operator where oprnamespace = 514367;
select * from pg_conversion where connamespace = 514367;
select * from pg_opclass where opcnamespace = 514367;
select * from pg_aggregate where aggfnoid = 514367 or aggtransfn  = 514367 or aggfinalfn = 514367;
select * from pg_proc where pronamespace = 514367;

(I didn't save this tab in Datagrip, so I only have the screenshot.)

After discussion with a DBA friend, I was thinking I may have to export the other logical dbs and recreate the instance. This probably was a sane choice as I am running my DB on zolando/postgres-operator and it was the postgres db, so I didn't need it. However that also allowed me to try a wildcard.

I vacuumed the DB, and reindexed it. Then a new error came up when I tried to check pg_proc:

ERROR: missing chunk number 0 for toast value 514376 in pg_toast_1255

That error meant that definite corruption had occurred. This was likely when a full disk event hit the primary after a replica stopped following it and I didn't notice.

So I followed this genius little writeup on Github: https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a

The boiled down version is, first reindex things:

REINDEX table pg_proc;
VACUUM analyze pg_proc;

Then, select chunks of data by offsets rather than data stored in any column. That way the database just grabs chunks of rows from the table until you find the spot that's corrupt.

select * from pg_proc order by oid limit 50 offset 0;
select * from pg_proc order by oid limit 50 offset 50;
select * from pg_proc order by oid limit 50 offset 100;
select * from pg_proc order by oid limit 50 offset 150;
select * from pg_proc order by oid limit 50 offset 200;
....

Once you find a block that is corrupt, narrow the search on that block, iterating down until you find the row(s) responsible.

Once I narrowed it down to two rows.

You then select only the id (or in the case of pg_proc, oid) field, by again grabbing the row by the offset.

select oid from pg_proc order by oid limit 1 offset 3238;
select oid from pg_proc order by oid limit 1 offset 3239;

That yielded the two corrupt row ids, which I was then able to delete

 delete from pg_proc where oid = 514371;
 delete from pg_proc where oid = 514373;

The two rows that I needed to delete happened to be the two rows that I found with the commands.

Once they were finally gone, I was again able to backup the database with pg_dump

Hoping this helps save someone some pain and also document a possible cause of this issue for others.

Answer 5

Hello from 2023 on Postgresql9.4

My ghost oids were in pg_depend and in pg_default_acl

DELETE FROM pg_depend WHERE refobjid = xxx;
DELETE FROM pg_default_acl WHERE defaclnamespace = xxx;

To find them, the hell, I was kinda desperate... Because every poster told his own table where the oids were.

I made this script that

given (hardcoded, modify the script):

• the oid
• the schema

searches in every column of every table in the schema (usually pg_catalog)

Hope you like it.

you need this requirements.txt

# requirements.txt
psycopg2-binary

and a venv

sudo apt-get install -y python3-venv
python3 -m venv .venv
./.venv/bin/pip install -r ./requirements.txt                      

and the script file "oid_searcher.py" to be launched with

./.venv/bin/python3 oid_searcher.py

#!.venv/bin/python3

import psycopg2
from psycopg2 import sql
from psycopg2.extras import DictCursor

def search_value_in_schema(conn, schema, search_value):
    results = {}
    
    with conn.cursor(cursor_factory=DictCursor) as cursor:
        # Get the list of all tables in the specified schema
        query = sql.SQL("SELECT table_name FROM information_schema.tables WHERE table_schema = %s")
        cursor.execute(query, (schema,))
        tables = [row[0] for row in cursor.fetchall()]
        
        for table in tables:
            for column in table_columns(conn, schema, table):
                if should_exclude_column(conn, schema, table, column):
                    continue
                
                query = sql.SQL("SELECT * FROM {}.{} WHERE {} = %s").format(
                    sql.Identifier(schema),
                    sql.Identifier(table),
                    sql.Identifier(column)
                )
                try:
                    cursor.execute(query, (search_value,))
                    rows = cursor.fetchall()
                    if rows:
                        if table not in results:
                            results[table] = {}
                        results[table][column] = rows
                except psycopg2.Error as e:
                    print(f"Error executing query for table {table}, column {column}: {e}")
                    
    return results

def table_columns(conn, schema, table):
    with conn.cursor() as cursor:
        query = sql.SQL("SELECT column_name FROM information_schema.columns WHERE table_schema = %s AND table_name = %s")
        cursor.execute(query, (schema, table))
        return [row[0] for row in cursor.fetchall()]



def should_exclude_column(conn, schema, table, column):
    with conn.cursor() as cursor:
        query = sql.SQL("SELECT data_type FROM information_schema.columns WHERE table_schema = %s AND table_name = %s AND column_name = %s")
        cursor.execute(query, (schema, table, column))
        data_type = cursor.fetchone()[0]
        return data_type != 'oid'


def main():
    dbname = "your_db"
    user = "postgres"
    password = "pwd"
    host = "localhost"
    port = "5432"
    
    schema = "pg_catalog"  # Specify the schema you want to search in
    search_value = 2865812  # Specify the integer value you want to search for
    
    try:
        conn = psycopg2.connect(
            dbname=dbname,
            user=user,
            password=password,
            host=host,
            port=port
        )
        results = search_value_in_schema(conn, schema, search_value)
        
        for table, columns in results.items():
            print(f"Table: {table}")
            for column, rows in columns.items():
                print(f"Column: {column}")
                if len(rows) > 0:
                    for row in rows:
                        print(row)
                else:
                    print (f"value not in table {table}")
                print("===")
            print("\n")
    except psycopg2.Error as e:
        print("Error:", e)
    finally:
        if conn is not None:
            conn.close()

if __name__ == "__main__":
    main()