Re: Replaying xlogs from beginning

otheus uibk <otheus(dot)uibk(at)gmail(dot)com> writes:
> I came up with an answer to the _second_ question (how do I do this from a
> new instance?).

> In the new instance directory:
> 1. Hack the system ID in the global/pg_control file to that of the original
> instance.
> 1a. Use pg_controlinfo to get the hex version of the control id:
> $ pg_controldata | perl -l -n -e 'if (/^Database system
> identifier:\s+(\d+)$/) { $sysid=sprintf("%x",$1);print join(" ",reverse
> $sysid=~m/../g); exit; }'
> fb fb 59 56 b9 31 58 53
> 1b. Use a hex editor (vim with xxd / xxd -r will do) to replace the
> first bytes with these values.

> 2. The new control file will be unusable because its CRC won't match. Fix
> that with pg_resetlog.
> $ pg_resetlog -f

> 3. Copy the very first pg_xlog file into place. The rest can be copied by
> hand or found using whatever you use for the "restore_command"

> 4. Create recovery.conf file.

> Start instance.

> Again, this worked for me. What I want to be sure of is: does this really
> work? And I still want to know: can I do this without creating a new
> instance?

No, and no. You're assuming that the only significant aspect of initdb's
output that can vary from run to run is the database system ID. This is
false. Quite aside from the question of whether you gave initdb the exact
same arguments each time, the resulting DB also has dependencies on the
exact set of locales installed on the system (from which the pg_collation
catalog is filled). If you're not running the exact same minor version
of PG that you were using before, there might be intentional small
differences in the initial catalog contents. There's some intentional
randomization in btree index insertion, which means that the contents
of system catalog indexes might not be bitwise the same from one initdb
run to the next, even if all else is the same. There may be still other
causes of variance that I'm not thinking of at the moment. Any one of
these differences could be harmless, but it could also mean that replaying
a WAL sequence against the database will result in inconsistencies.

If you're lucky this technique will work, but it's not reliable and not
supported. You really need to take an initial base backup after running
initdb.

regards, tom lane