| From: | leo <dazhoufei(at)gmail(dot)com> |
|---|---|
| To: | pgsql-admin(at)postgresql(dot)org |
| Subject: | how to find correct command parameter of pg_resetxlog (9.4.5) |
| Date: | 2016-08-29 03:35:21 |
| Message-ID: | 1472441721231-5918242.post@n5.nabble.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Marchello Lippi | 2016-08-29 07:13:25 | run copy of database on another (backup) server |
| Previous Message | jaime soler | 2016-08-25 08:36:57 | Re: Does CLUSTER generate WAL data? |