Postgres + Ruby / Ruby on Rails

01_check_schema_sql.sh

# This one-liner compares your local RoR's dump (db/structure.sql) with a remote DB's schema (Postgres)
#
# It ignores:
#   - ownership & privileges information
#   - empty lines
#   - SQL comments
#   - data in the "migrations" table from db/structure.sql

pg_dump --no-owner --no-privileges --schema-only -U username -h hostname dbname \
  | egrep -v '^--|^$' | diff -b <(egrep -v '^INSERT INTO|^--|^$' db/structure.sql) -

02_refresh_all_matviews.sql

-- Use this to do the very 1st REFRESH for your matviews
set statement_timeout to 0;
set client_min_messages to info;

do $$
declare
  matview text;
  sql text;
  iter int2; -- how many iterations 
  done_cnt integer; -- how many matviews refreshed
  curts timestamptz;
begin
  -- uncomment this, if ALL matviews need to be refreshed,
  -- even those that already have data
  /*for matview in
    select format('"%s"."%s"', schemaname::text, matviewname::text)
    from pg_matviews
  loop
    sql := format('refresh materialized view %s with no data;', matview);
    raise notice '[%] SQL:    %', '-', sql;
    execute sql;
  end loop;*/

  iter := 1;
  done_cnt := 0;
  loop
    for matview in
      select format('"%s"."%s"', schemaname::text, matviewname::text)
      from pg_matviews
      where not ispopulated
    loop
      begin
        sql := format('refresh materialized view %s', matview);
        raise notice '[%] SQL:    %', iter, sql;
        curts := clock_timestamp();
        execute sql;
        raise notice '[%] % refreshed, it took %', iter, matview, (clock_timestamp() - curts)::text;
        done_cnt := done_cnt + 1;
      exception
        when others then
          raise warning '[%] Cannot update view %, skip and try again later.', iter, matview;
      end;
    end loop;
    
    exit when 0 = (select count(*) from pg_matviews where not ispopulated);
    iter := iter + 1;
  end loop;
  
  raise notice 'Finished! % matviews refreshed in % iterations.', done_cnt, iter;
end;
$$ language plpgsql;