I used this command to upgrade the postgresql database from v9.6 to v12:
/opt/rh/rh-postgresql12/root/usr/bin/pg_upgrade -b /opt/rh/rh-postgresql96/root/usr/bin/ -B /opt/rh/rh-postgresql12/root/usr/bin/ -d /var/opt/rh/rh-postgresql96/lib/pgsql/data -D /var/opt/rh/rh-postgresql12/lib/pgsql/data
I got no errors and the upgrade was done successfully, but when I tried to fetch some data from my database, I realized all the database tables are empty. The size of the database is somehow the same as the old database and running the below command returns all the tables but with 0 rows:
select n.nspname as table_schema,c.relname as table_name, c.reltuples as rows
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;
Can you please let me know why the tables have no rows?
running the suggested script by postgresql: '/var/lib/pgsql/analyze_new_cluster.sh'
or the command
sudo -u postgres /opt/rh/rh-postgresql12/root/usr/bin/vacuumdb --all --analyze-in-stages -p 5433
would solve the issue.