how to find correct command parameter of pg_resetxlog (9.4.5)

Поиск
Список
Период
Сортировка
От leo
Тема how to find correct command parameter of pg_resetxlog (9.4.5)
Дата
Msg-id 1472441721231-5918242.post@n5.nabble.com
обсуждение исходный текст
Ответы Patch Set for v9.3 (RedHat)  ("Ferrell, Denise SDC" <denise.ferrell@sdc-world.com>)
Список 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.


В списке pgsql-admin по дате отправления:

Предыдущее
От: jaime soler
Дата:
Сообщение: Re: Does CLUSTER generate WAL data?
Следующее
От: "Marchello Lippi"
Дата:
Сообщение: run copy of database on another (backup) server