我用的是postgresql 9.6。我尝试用pg_dump创建一个备份文件:pg_dump -U postgres -O -f file。备份数据库
但是我得到了错误:SQL错误[XX000]: Error: could not read block 270 in file "base/16393/1249": Invalid argument
使用这个查询:
select
c.tableoid,
c.oid,
c.relname,
(
select
pg_catalog.array_agg(acl
order by
row_n)
from
(
select
acl,
row_n
from
pg_catalog.unnest(coalesce(c.relacl, pg_catalog.acldefault(case when c.relkind = 'S' then 's' else 'r' end::"char", c.relowner))) with ordinality as perm(acl,
row_n)
where
not exists (
select
1
from
pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault(case when c.relkind = 'S' then 's' else 'r' end::"char", c.relowner))) as init(init_acl)
where
acl = init_acl)) as foo) as relacl,
(
select
pg_catalog.array_agg(acl
order by
row_n)
from
(
select
acl,
row_n
from
pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault(case when c.relkind = 'S' then 's' else 'r' end::"char", c.relowner))) with ordinality as initp(acl,
row_n)
where
not exists (
select
1
from
pg_catalog.unnest(coalesce(c.relacl, pg_catalog.acldefault(case when c.relkind = 'S' then 's' else 'r' end::"char", c.relowner))) as permp(orig_acl)
where
acl = orig_acl)) as foo) as rrelacl,
null as initrelacl,
null as initrrelacl,
c.relkind,
c.relnamespace,
(
select
rolname
from
pg_catalog.pg_roles
where
oid = c.relowner) as rolname,
c.relchecks,
c.relhastriggers,
c.relhasindex,
c.relhasrules,
c.relhasoids,
c.relrowsecurity,
c.relforcerowsecurity,
c.relfrozenxid,
c.relminmxid,
tc.oid as toid,
tc.relfrozenxid as tfrozenxid,
tc.relminmxid as tminmxid,
c.relpersistence,
c.relispopulated,
c.relreplident,
c.relpages,
case
when c.reloftype <> 0 then c.reloftype::pg_catalog.regtype
else null
end as reloftype,
d.refobjid as owning_tab,
d.refobjsubid as owning_col,
(
select
spcname
from
pg_tablespace t
where
t.oid = c.reltablespace) as reltablespace,
array_remove(array_remove(c.reloptions, 'check_option=local'), 'check_option=cascaded') as reloptions,
case
when 'check_option=local' = any (c.reloptions) then 'LOCAL'::text
when 'check_option=cascaded' = any (c.reloptions) then 'CASCADED'::text
else null
end as checkoption,
tc.reloptions as toast_reloptions,
exists (
select
1
from
pg_attribute at
left join pg_init_privs pip on
(c.oid = pip.objoid
and pip.classoid = 'pg_class'::regclass
and pip.objsubid = at.attnum)
where at.attrelid = c.oid
and ((
select
pg_catalog.array_agg(acl
order by
row_n)
from
(
select
acl,
row_n
from
pg_catalog.unnest(coalesce(at.attacl, pg_catalog.acldefault('c', c.relowner))) with ordinality as perm(acl,
row_n)
where
not exists (
select
1
from
pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('c', c.relowner))) as init(init_acl)
where
acl = init_acl)) as foo) is not null
or (
select
pg_catalog.array_agg(acl
order by
row_n)
from
(
select
acl,
row_n
from
pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('c', c.relowner))) with ordinality as initp(acl,
row_n)
where
not exists (
select
1
from
pg_catalog.unnest(coalesce(at.attacl, pg_catalog.acldefault('c', c.relowner))) as permp(orig_acl)
where
acl = orig_acl)) as foo) is not null
or null is not null
or null is not null))as changed_acl
from
pg_class c
left join pg_depend d on
(c.relkind = 'S'
and d.classid = c.tableoid
and d.objid = c.oid
and d.objsubid = 0
and d.refclassid = c.tableoid
and d.deptype = 'a')
left join pg_class tc on
(c.reltoastrelid = tc.oid)
left join pg_init_privs pip on
(c.oid = pip.objoid
and pip.classoid = 'pg_class'::regclass
and pip.objsubid = 0)
where
c.relkind in ('r', 'S', 'v', 'c', 'm', 'f')
order by
c.oid;
我想有些表损坏了。有没有一种方法可以让我创建一个备份而不经过这个查询?
您的pg_attribute表中有数据损坏。该表包含所有数据库表、索引、复合类型等的列定义。
检查base/16393/1249的大小是否大于2220032字节(271块)。