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. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
