Re: [PERFORM] Backup taking long time !!!

От: Stephen Frost
Тема: Re: [PERFORM] Backup taking long time !!!
Дата: ,
Msg-id: 20170123064839.GT18360@tamriel.snowman.net
(см: обсуждение, исходный текст)
Ответ на: Re: [PERFORM] Backup taking long time !!!  (Vladimir Borodin)
Список: pgsql-performance

Скрыть дерево обсуждения

[PERFORM] Backup taking long time !!!  (Dinesh Chandra 12108, )
 Re: [PERFORM] Backup taking long time !!!  ("Madusudanan.B.N", )
  Re: [PERFORM] Backup taking long time !!!  (Dinesh Chandra 12108, )
   Re: [PERFORM] Backup taking long time !!!  (Pavel Stehule, )
    Re: [PERFORM] Backup taking long time !!!  ("Madusudanan.B.N", )
    Re: [PERFORM] Backup taking long time !!!  (Dinesh Chandra 12108, )
     Re: [PERFORM] Backup taking long time !!!  (Pavel Stehule, )
      Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
  Re: [PERFORM] Backup taking long time !!!  (Vladimir Borodin, )
   Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
 Re: [PERFORM] Backup taking long time !!!  (Vladimir Borodin, )
  Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
 Re: [PERFORM] Backup taking long time !!!  (Vladimir Borodin, )
  Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
   Re: [PERFORM] Backup taking long time !!!  (Jim Nasby, )
    Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
     Re: [PERFORM] Backup taking long time !!!  (Jim Nasby, )
   Re: [PERFORM] Backup taking long time !!!  (David Steele, )
  Re: [PERFORM] Backup taking long time !!!  (Torsten Zuehlsdorff, )
   Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
 Re: [PERFORM] Backup taking long time !!!  (Vladimir Borodin, )
  Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
 Re: [PERFORM] Backup taking long time !!!  (julyanto SUTANDANG, )
  Re: [PERFORM] Backup taking long time !!!  (julyanto SUTANDANG, )
   Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
    Re: [PERFORM] Backup taking long time !!!  (Jeff Janes, )
     Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
     Re: [PERFORM] Backup taking long time !!!  (Simon Riggs, )
      Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
      Re: [PERFORM] Backup taking long time !!!  (Jeff Janes, )
       Re: [PERFORM] Backup taking long time !!!  (Rick Otten, )
        Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
     Re: [PERFORM] Backup taking long time !!!  (julyanto SUTANDANG, )
  Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
 Re: [PERFORM] Backup taking long time !!!  (julyanto SUTANDANG, )
  Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
 Re: [PERFORM] Backup taking long time !!!  (julyanto SUTANDANG, )
  Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
   Re: [PERFORM] Backup taking long time !!!  (Jim Nasby, )
    Re: [PERFORM] Backup taking long time !!!  (Jeff Janes, )
     Re: [PERFORM] Backup taking long time !!!  (Stephen Frost, )
 Re: [PERFORM] Backup taking long time !!!  (julyanto SUTANDANG, )

Vladimir,

* Vladimir Borodin () wrote:
> > 20 янв. 2017 г., в 19:59, Stephen Frost <> написал(а):
> >>> How are you testing your backups..?  Do you have page-level checksums
> >>> enabled on your database?
> >>
> >> Yep, we use checksums. We restore latest backup with recovery_target = 'immediate' and do COPY tablename TO
'/dev/null’with checking exit code for each table in each database (in several threads, of course). 
> >
> > Right, unfortunately that only checks the heap pages, it won't help with
> > corruption happening in an index file or other files which have a
> > checksum.
>
> That’s fine for us because indexes could be rebuilt. The main idea is the guarantee that data would not be lost.

Fair enough, however, if you don't check that the indexes are valid then
you could end up with corruption in the database if you start operating
against such a recovered database.

Consider what happens on a 'primary key' lookup or insert- if the index
doesn't find a conflicting tuple (perhaps because the index is corrupt),
then it will happily allow the INSERT to go through, even though it
should have been prevented.

Indexes are a *really* important component to having a valid database.
If you aren't checking the validity of them, then you're running the
risk of being exposed to corruption in them, either ongoing or when
restoring.

If you *always* rebuild your indexes when restoring from a backup, then
you should be fine, of course, but if you're going to do that then you
might consider using pg_dump instead, which would do that and validate
all foreign key references too.

Thanks!

Stephen

Вложения

В списке pgsql-performance по дате сообщения:

От: Torsten Zuehlsdorff
Дата:
Сообщение: Re: [PERFORM] Backup taking long time !!!
От: Jim Nasby
Дата:
Сообщение: Re: [PERFORM] optimizing immutable vs. stable function calls?