Recently I was trying to update a Postgres RDS instance from 10.21 to 14.8 The update failed with the following error:

Your installation contains tables declared WITH OIDS, which is not
supported anymore. Consider removing the oid column using
ALTER TABLE ... SET WITHOUT OIDS;
A list of tables with the problem is in the file:
tables_with_oids.txt

The file tables_with_oids.txt is not accessible to users since RDS is a managed service. To find the affected databases/tables you can run the following query:

SELECT current_database(),n.nspname ,c.relname, c.relkind from pg_class c, pg_attribute a, pg_namespace n where c.oid = a.attrelid and a.attname = 'oid' AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema') and c.relkind = 'r';

This needs to be ran against every database in the instance. I wrote a bash script to do this:


#!/bin/bash

# I found without setting this the script would fail
export PGPASSWORD=xxxx

# Define your query
QUERY="SELECT current_database(),n.nspname ,c.relname, c.relkind from pg_class c, pg_attribute a, pg_namespace n where c.oid = a.attrelid and a.attname = 'oid' AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema') and c.relkind = 'r';"

# Get a list of all databases in the RDS cluster
DATABASES=$(psql -h hostname.eu-west-2.rds.amazonaws.com -p 5432 -U postgres -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1');" -t)

# Loop through each database and execute the query
for DB in $DATABASES; do
    echo "Running query on database: $DB"
    psql -h qa-hostname.eu-west-2.rds.amazonaws.com -t -c "$QUERY" -U postgres -d $DB
done

This will output a list of databases and tables that need to be updated. You can then run the following query to remove the OIDS:

ALTER TABLE table_name SET WITHOUT OIDS;