db_query

delete from pg_attribute where attrelid = 105113;
INSERT INTO pg_catalog.pg_attribute select 105113, q.* from ( select attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod, attbyval, attstorage,attalign, attnotnull, atthasdef, attisdropped, attislocal, attinhcount, attcollation, attacl, attoptions, attfdwoptions  from pg_attribute where attrelid::regclass = 'path_periodic_rollup_data_20160711'::regclass)  as q





servcie postgresql-9.0 stop
mv /data/databases/db001 /data/databases/db001.dead
cp -ar /backup/databases/week1/ /data/databases/db001/
chown -R postgres:postgres /data/databases/db001
chmod 700 /data/databases/db001
rm /data/databases/db001/pg_xlog/*
rm /data/databases/db001/pg_xlog/archive_status/*
rm /data/databases/db001/postmaster.pid
echo -e "restore_command = '/opt/scripts/pg_wal_archiver.sh restore %p %f db001 /backup'\nrecovery_target_timeline = 'latest'" > /data/databases/db001/recovery.conf
chown postgres:postgres /data/databases/db001/recovery.conf
service postgresql-9.0 start








select t.relname, pg_relation_filepath(t.oid) from pg_class t join pg_namespace ns on ns.oid = t.relnamespace where ns.nspname = 'public';


select
    t.relname as table_name,
    i.relname as index_name,
    ix.indexrelid as index_oid,
    t.oid as table_oid
from
    pg_class t, pg_class i, pg_index ix where t.oid = ix.indrelid and i.oid = ix.indexrelid and t.relkind = 'r' and t.relname like 'path_rt_%' order by 3;





select n.nspname AS tableschema, c.relname AS tablename
from pg_class c
inner join pg_namespace n on (c.relnamespace = n.oid)
where c.relfilenode = 11597;

SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'path_rt_data_2016%' ORDER BY table_name DESC LIMIT 1;
SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'path_rt_periodic_rollup_data_2016%' ORDER BY table_name DESC LIMIT 1;


echo "psql -U postgres -h localhost -d appnetdb <<EOF" >> /tmp/db_script1.sh;
echo "CREATE OR REPLACE FUNCTION query_earliest_path_rt_rollup_timestamp (rollupType int)  " >> /tmp/db_script1.sh;
        echo "RETURNS path_rt_periodic_rollup_data.rollup_timestamp%TYPE " >> /tmp/db_script1.sh;
echo "AS $$  " >> /tmp/db_script1.sh;
echo "DECLARE " >> /tmp/db_script1.sh;
        echo "oldestTime timestamp; " >> /tmp/db_script1.sh;
echo "BEGIN  " >> /tmp/db_script1.sh;
        echo "IF (rollupType = 0) THEN " >> /tmp/db_script1.sh;
echo "--               SELECT MAX(ROLLUP_TIMESTAMP) FROM PATH_RT_PERIODIC_ROLLUP_DATA INTO oldestTime;" >> /tmp/db_script1.sh;
echo "--    temproarry workaround and get data from hourly instead of corrupted periodic" >> /tmp/db_script1.sh;
     echo "SELECT MAX(ROLLUP_TIMESTAMP) FROM PATH_RT_HOURLY_ROLLUP_DATA  INTO oldestTime; " >> /tmp/db_script1.sh;
        echo "ELSIF (rollupType = 1) THEN " >> /tmp/db_script1.sh;
                 echo "SELECT MAX(ROLLUP_TIMESTAMP) FROM PATH_RT_HOURLY_ROLLUP_DATA  INTO oldestTime; " >> /tmp/db_script1.sh;
        echo "ELSIF (rollupType = 2) THEN  " >> /tmp/db_script1.sh;
                 echo "SELECT MAX(ROLLUP_TIMESTAMP) FROM PATH_RT_DAILY_ROLLUP_DATA  INTO oldestTime;" >> /tmp/db_script1.sh;
        echo "ELSE " >> /tmp/db_script1.sh;
                echo "RAISE EXCEPTION 'Error: query_earliest_path_rt_rollup_timestamp unsupported rollupType value: %', rollupType;" >> /tmp/db_script1.sh;
        echo "END IF;" >> /tmp/db_script1.sh;
        echo "RETURN oldestTime;" >> /tmp/db_script1.sh;
echo "END; " >> /tmp/db_script1.sh;
echo "$$ LANGUAGE plpgsql; " >> /tmp/db_script1.sh;
echo "EOF" >> /tmp/db_script1.sh;