If something happened and you PostgreSQL database crashed and corrupted data or your filesystem crashed and corrupted data files and you must check what is missing then you may appreciate this procedure which checks data files for every table on all databases (except for templates).
Of course even if data file is present it might be corrupted. Therefore the only save way how to rescue crashed database is to restore it from backup.
But still procedure like this can be sometimes useful. It is not exactly brilliant but it is working.
CREATE OR REPLACE FUNCTION check_data_files()
RETURNS TABLE(_database text, _databaseoid text, _tablespacename text, _relname text, _reloid text, _relkindid text, _relkind text, _tablespacelocation text, _file text, _size text, _last_accessed text, _last_modified text, _last_filepath_status_change_unix_only text, _file_creation_windows_only text, _filepath_status text, _details_status text, _select_catalog_status text) AS
$BODY$
declare
_db record;
_data_dir text;
_classes record;
_pgfiles record;
_detail text[];
_count bigint;
begin
begin
select setting into _data_dir from pg_settings where name = 'data_directory';
exception when others then
_database := current_database();
_relname := 'pg_settings';
_select_catalog_status := sqlstate||'-'||sqlerrm;
return next;
return;
end;
begin
select count(*) into _count from pg_database;
exception when others then
_database := current_database();
_relname := 'pg_database';
_select_catalog_status := sqlstate||'-'||sqlerrm;
return next;
return;
end;
for _db in select oid::text, datname::text from pg_database where datistemplate is not true order by datname loop
_databaseoid := _db.oid;
_database := _db.datname;
begin
select a into _count from dblink('dbname='||_db.datname, $query$select count(*) from pg_class$query$) as t(a bigint);
exception when others then
_relname := 'pg_class';
_select_catalog_status := sqlstate||'-'||sqlerrm;
return next;
return;
end;
for _classes in select * from dblink('dbname='||_db.datname,
$query$select
c.oid as class_oid,
c.reltablespace as tablespace_oid,
c.relname,
c.relkind as relkindid,
case c.relkind
when 'r' then 'ordinary table'
when 'i' then 'index'
when 'S' then 'sequence'
when 'v' then 'view'
when 'm' then 'materialized view'
when 'c' then 'composite type'
when 't' then 'TOAST table'
when 'f' then 'foreign table'
else c.relkind||' ?' end as relkind,
ts.spcname as tablespacename
from pg_class c
left join pg_tablespace ts
on c.reltablespace=ts.oid
where c.relkind not in ('v','c','i')
order by relname$query$) as t( class_oid oid, tablespace_oid oid, relname name, relkindid char, relkind text, tablespacename name)
loop
_relname := _classes.relname::text;
_relkindid := _classes.relkindid::text;
_relkind := _classes.relkind::text;
_reloid := _classes.class_oid::text;
_tablespacename := coalesce(_classes.tablespacename::text, 'public');
begin
select a into _tablespacelocation from dblink('dbname='||_db.datname, 'select pg_tablespace_location('||_classes.tablespace_oid||')::text;') as t(a text);
exception when others then
_tablespacelocation := (sqlstate||'-'||sqlerrm)::text;
end;
_file := null::text;
_size := null::text;
_last_accessed := null::text;
_last_modified := null::text;
_last_filepath_status_change_unix_only := null::text;
_file_creation_windows_only := null::text;
_filepath_status := null::text;
_details_status := null::text;
begin
select a into _file from dblink('dbname='||_db.datname, 'select '''||_data_dir||'/''||pg_relation_filepath('||_classes.class_oid||')') as t(a text);
begin
select a into _detail from dblink('dbname='||_db.datname,
'select string_to_array(replace(replace(pg_stat_file('''||_data_dir||'/''||pg_relation_filepath('||_classes.class_oid||'))::text,''('',''''),'')'',''''),'','')')
as t(a text[]);
_size := _detail[1]::text;
_last_accessed := _detail[2]::text;
_last_modified := _detail[3]::text;
_last_filepath_status_change_unix_only := _detail[4]::text;
_file_creation_windows_only := _detail[5]::text;
exception when others then
_details_status := sqlstate||'-'||sqlerrm;
end;
exception when others then
_filepath_status := sqlstate||'-'||sqlerrm;
end;
return next;
end loop; --classes
end loop; --databases
return;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;