Thread: Replaying xlogs from beginning
I'm looking for answers to this question, but so far haven't turned up a usable answer. Perhaps I'm asking it the wrong way.
I want to replay the xlogs from the beginning of time up until a particular time. The problem is, the time is before the first base backup. But I have all the xlogs since the database was initialized.
To test this, I initialized a new DB and put a few xlogs in its pg_xlog directory, and I create a simple recovery.conf ("restore_command=false"). When I start it up, I get something like "DETAIL: WAL file database system identifier is 6221786353392811102, pg_control database system identifier is 6252279422905597461."
--
The most important question is: How do I recover to PIT, starting from initialization, without a base backup, provided I have all xlog files.
The secondary question is: How can I test this with a new instance?
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; }'
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.
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
$ 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?
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?
I came up with an answer to the _second_ question (how do I do this from a new instance?).[...]Again, this worked for me. What I want to be sure of is: does this really work?
I cannot definitively answer the question but it you can get the system to boot and the data looks good what I would do is pg_dump the result and then pg_restore is back into a clean cluster.
And I still want to know: can I do this without creating a new instance?
Someone considerably more informed than I would need to answer this.
David J.
otheus uibk <otheus.uibk@gmail.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
> You're assuming that the only significant aspect of initdb's output that can vary from run to run is the database system ID.
I prefer to call it "optimistic prediction". But yes. :)
> 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.
Thanks, but it's too late in retrospect. Since that is the case, to whom can I make a feature request that initdb have an implicitly enabled option to make this base backup?
OK, back to question. Provided the system is the same, the postgresql executable and libraries are identical, the locales are unchanged, we have:
1. bitwise variances in the system catalog
-- can be solved with David's suggestion of full dump/restore after the steps I listed. Confer?
2. other things you couldn't think of yet.
-- anything come to mind yet?
-- anything come to mind yet?