0

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?

M-E
  • 168
  • 4
  • 19
  • 1
    did you run `analyze` after the upgrade (as recommended by pg_upgrade at the end)? –  Jul 29 '22 at 12:57
  • @a_horse_with_no_name No I didn't, do you think that can solve the issue? – M-E Jul 29 '22 at 13:08
  • @a_horse_with_no_name running that script did fix the issue, thank you. If you add what you said as answer I can approve it :) – M-E Jul 29 '22 at 13:14

1 Answers1

0

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.

M-E
  • 168
  • 4
  • 19