Postgresql Backproblem

Question

I taken a copy of whole pgsql folder as a backup. Now I tring to restore from that but it is not working.

It gives the error

ERROR: index "mytable_pkey17" contains unexpected zero page at block 295

Hint: Please REINDEX it.

So I tried to Reindex by using the following query. and got the error as following

REINDEX INDEX mytable_pkey17;

ERROR:  invalid page header in block 2056 of relation base/11259/1923672

Please help me...

"*I taken a copy of whole pgsql folder as a backup*" - this is your first problem. You can't just "copy" a database folder. See the manual for details: http://www.postgresql.org/docs/current/static/backup.html — , Oct 01 '15 at 12:32
Did you follow the manual's advice and stop PostgreSQL first, or use `pg_start_backup()`? Or `pg_basebackup`? — Craig Ringer, Oct 01 '15 at 12:46
I have no other backups now. I stopped pgsql before file backup. — apm, Oct 01 '15 at 13:07

score 0 · Answer 1 · edited Feb 01 '17 at 20:42

on master:

postgres=# select * fro m bt_page_items('idx_t',100); itemoffset | ctid | itemlen | nulls | vars | data ------------+-----------+---------+-------+------+------------------------- 1 | (160,75) | 16 | f | f | 8b 8d 00 00 00 00 00 00 2 | (158,161) | 16 | f | f | 1d 8c 00 00 00 00 00 00 3 | (158,162) | 16 | f | f | 1e 8c 00 00 00 00 00 00 4 | (158,163) | 16 | f | f | 1f 8c 00 00 00 00 00 00 5 | (158,164) | 16 | f | f | 20 8c 00 00 00 00 00 00 6 | (158,165) | 16 | f | f | 21 8c 00 00 00 00 00 00 7 | (158,166) | 16 | f | f | 22 8c 00 00 00 00 00 00 8 | (158,167) | 16 | f | f | 23 8c 00 00 00 00 00 00 9 | (158,168) | 16 | f | f | 24 8c 00 00 00 00 00 00 10 | (158,169) | 16 | f | f | 25 8c 00 00 00 00 00 00 11 | (158,170) | 16 | f | f | 26 8c 00 00 00 00 00 00 12 | (158,171) | 16 | f | f | 27 8c 00 00 00 00 00 00 13 | (158,172) | 16 | f | f | 28 8c 00 00 00 00 00 00 14 | (158,173) | 16 | f | f | 29 8c 00 00 00 00 00 00

update tbl set xx=xx wher e ctid in (select t ctid from bt_page_items('idx_t',100) );

Postgresql Backproblem

1 Answers1