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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Richard Broersma Jr <rabroersma(at)yahoo(dot)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
>