Hi all,
I use pg_resetxlog to recover the pg_control file in our test lab
according to official document
(https://www.postgresql.org/docs/9.4/static/app-pgresetxlog.html). I
simulate pg_control file is corrupted and recover it.
But I feel confused after I read the document especial how to find
correct parameter, I do some test according to my understanding, but not
successfully recover the pg_control file.
My test step is:
1. Run some SQL command to change the data.
2. Stop PG
3. Check correct pg_control information by pg_controldata：
pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6323048631814781062
Database cluster state: shut down
pg_control last modified: Fri 26 Aug 2016 05:46:23 PM CST
Latest checkpoint location: 0/251CBD0
Prior checkpoint location: 0/1711F48
Latest checkpoint's REDO location: 0/251CBD0
Latest checkpoint's REDO WAL file: 000000010000000000000002
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1813
Latest checkpoint's NextOID: 116389
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1800
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint: Fri 26 Aug 2016 05:46:22 PM CST
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: minimal
Current wal_log_hints setting: off
Current max_connections setting: 100
Current max_worker_processes setting: 8
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
4, Stop PG and remove pg_control
5. According to official document to find the command parameter of
pg_control
5.1 Current the latest file is 000000010000000000000002 in pg_xlog
directory, so I use 000000010000000000000003 as parameter "-l" 's value.
5.2 Current the latest file is 0000 in pg_clog directory, so I use
0x000100000 as parameter "-x" value.
5.3 Current the latest file is 0000 in pg_multixact/offsets directory,
because I just init a PG cluster to test pg_resetxlog and only insert some
test data. According to document, I use 0x10000 as first par of "-m"
parameter. But how to define the second part of "-m" parameter? I try
0x0000, pg_resetxlog raise error"pg_resetxlog: oldest multitransaction ID
(-m) must not be 0", I have to use "-m 0x10000,0x10000"
5.4 Current the latest file is 0000 in pg_multixact/members.
According to document "looking for the numerically largest file name in the
directory pg_multixact/members under the data directory,
adding one, and then multiplying by 52352" I don't know how to choose the
correct parameter for "-O" option, try the 0x0001.
Finally, I run under command:
pg_resetxlog -l 000000010000000000000003 -x 0x000100000 -m
0x10000,0x10000 -O 0x10000 -f /data/postgresql/data/
But PG can't startup :
LOG: database system was shut down at 2016-08-29 11:08:36 CST
FATAL: could not access status of transaction 65536
DETAIL: Could not read from file "pg_multixact/members/0001" at offset
65536: Success.
LOG: startup process (PID 11217) exited with exit code 1
LOG: aborting startup due to startup process failure
Maybe some values of command parameter are incorrect, please help me and
explain the which step is error.
--
View this message in context:
http://postgresql.nabble.com/how-to-find-correct-command-parameter-of-pg-resetxlog-9-4-5-tp5918242.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
