Re: restoring a file system backed-up data dir

... so what if the database size is above 20 GB, do we have to do
pg_dump each at periodics time to get reliable backup?

On 10/11/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Broersma Jr <rabroersma@yahoo.com> writes:
> > I found the correct log file.
>
> > 2006-10-10 04:57:45 PDT% LOG:  could not open file "pg_xlog/000000010000000000000055"
> >                                (log file 0, segment 85): No such file or directory
> > 2006-10-10 04:57:45 PDT% LOG:  could not open file "pg_xlog/000000010000000000000055"
> >                                (log file 0, segment 85): No such file or directory
> > 2006-10-10 04:57:45 PDT% LOG:  database system was shut down at 2006-09-26 17:11:35 PDT
> > 2006-10-10 04:57:45 PDT% LOG:  invalid primary checkpoint record
> > 2006-10-10 04:57:45 PDT% LOG:  invalid secondary checkpoint record
> > 2006-10-10 04:57:45 PDT% LOG:  logger shutting down
> > 2006-10-10 04:57:45 PDT% LOG:  startup process (PID 5953) was terminated by signal 6
> > 2006-10-10 04:57:45 PDT% PANIC:  could not locate a valid checkpoint record
>
> This says that pg_control is out of sync with the pg_xlog files, which
> is not real surprising for a filesystem-level backup.  You could try
> forcing the issue with pg_resetxlog, but you'll very likely end up with
> a non-self-consistent database.  The pg_dump backup is a better bet.
>
> If you are really desperate to recover the latest changes, try
> pg_resetxlog then pg_dump, and diff the dump file against your good
> pg_dump to see which changes you want to believe and apply.  But I'd
> still say you want to initdb and restore from the pg_dump backup before
> going forward.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>