Обсуждение: How to use pg_waldump?
Im using pgBackRest for incremental backups which, as far as I understand, use the WAL. These backups are relatively large, so I wanted to take a look at my WAL. I understand pg_waldump is the tool for this. However, I struggle with its usage. The --help output suggests all command line parameters are optional, but running it like that yields "pg_waldump: no arguments specified". So I tried "pg_waldump -z" which yields "pg_waldump: FATAL: could not find any WAL file". Ok, so apparently it doesn't know the location of the WAL files. I then tried "pg_waldump -p /var/lib/postgresql/11/main/pg_wal". Now I get "pg_waldump: no start WAL location given". And this is where I'm stuck. I don't know any WAL location. I don't even know how far back my WAL goes, that's one thing I want to find out, among other things. On https://www.postgresql.org/docs/11/pgwaldump.html it says about "--start": > WAL location at which to start reading. The default is to start reading the first valid log record found in the earliest file found. If that's the default, why does it ask me for a WAL location? What do I need to do?
Hi André, > On 01. May, 2020, at 12:47, André Hänsel <andre@webkr.de> wrote: > > I知 using pgBackRest for incremental backups which, as far as I understand, > use the WAL. These backups are relatively large, so I wanted to take a look > at my WAL. I understand pg_waldump is the tool for this. > > However, I struggle with its usage. > > The --help output suggests all command line parameters are optional, but > running it like that yields "pg_waldump: no arguments specified". So I tried > "pg_waldump -z" which yields "pg_waldump: FATAL: could not find any WAL > file". Ok, so apparently it doesn't know the location of the WAL files. I > then tried "pg_waldump -p /var/lib/postgresql/11/main/pg_wal". > > Now I get "pg_waldump: no start WAL location given". And this is where I'm > stuck. I don't know any WAL location. I don't even know how far back my WAL > goes, that's one thing I want to find out, among other things. > > On https://www.postgresql.org/docs/11/pgwaldump.html it says about > "--start": >> WAL location at which to start reading. The default is to start reading > the first valid log record found in the earliest file found. > > If that's the default, why does it ask me for a WAL location? > > What do I need to do? try: pg_waldump -p /var/lib/postgresql/11/main/pg_wal <start-wal> [<end-wal>] where <start-wal> is the name of the WAL file to start and (optionally) <end-wal> is the WAL file to stop. It reads and showsall information of the WAL files in this range. Hope, this helps. Cheers, Paul
Paul Förster wrote: > try: > > pg_waldump -p /var/lib/postgresql/11/main/pg_wal <start-wal> [<end-wal>] > > where <start-wal> is the name of the WAL file to start and (optionally) <end-wal> is the WAL file to stop. It reads andshows all information of the WAL files in this range. Picking a random WAL file and running the command gives me: pg_waldump: FATAL: could not find a valid record after 2F/6C000000
Hi André, > On 01. May, 2020, at 14:33, André Hänsel <andre@webkr.de> wrote: > > Picking a random WAL file and running the command gives me: > > pg_waldump: FATAL: could not find a valid record after 2F/6C000000 maybe try with another WAL file or files? Works for me... postgres@weasel:~$ pg_waldump -p /data/wal 0000004F0000000B00000072 0000004F0000000B00000074 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: B/E4000028, prev B/E20001C0, desc: CHECKPOINT_SHUTDOWNredo B/E4000028; tli 79; prev tli 79; fpw true; xid 0:6641; oid 37940; multi 1; offset 0; oldest xid 561in DB 16400; oldest multi 1 in DB 16401; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: B/E40000A0, prev B/E4000028, desc: RUNNING_XACTS nextXid6641 latestCompletedXid 6640 oldestRunningXid 6641 rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: B/E40000D8, prev B/E40000A0, desc: RUNNING_XACTS nextXid6641 latestCompletedXid 6640 oldestRunningXid 6641 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: B/E4000110, prev B/E40000D8, desc: CHECKPOINT_ONLINEredo B/E40000D8; tli 79; prev tli 79; fpw true; xid 0:6641; oid 37940; multi 1; offset 0; oldest xid 561in DB 16400; oldest multi 1 in DB 16401; oldest/newest commit timestamp xid: 0/0; oldest running xid 6641; online rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: B/E4000188, prev B/E4000110, desc: RUNNING_XACTS nextXid6641 latestCompletedXid 6640 oldestRunningXid 6641 rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: B/E40001C0, prev B/E4000188, desc: SWITCH rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: B/E6000028, prev B/E40001C0, desc: CHECKPOINT_SHUTDOWNredo B/E6000028; tli 79; prev tli 79; fpw true; xid 0:6641; oid 37940; multi 1; offset 0; oldest xid 561in DB 16400; oldest multi 1 in DB 16401; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: B/E60000A0, prev B/E6000028, desc: RUNNING_XACTS nextXid6641 latestCompletedXid 6640 oldestRunningXid 6641 rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: B/E60000D8, prev B/E60000A0, desc: RUNNING_XACTS nextXid6641 latestCompletedXid 6640 oldestRunningXid 6641 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: B/E6000110, prev B/E60000D8, desc: CHECKPOINT_ONLINEredo B/E60000D8; tli 79; prev tli 79; fpw true; xid 0:6641; oid 37940; multi 1; offset 0; oldest xid 561in DB 16400; oldest multi 1 in DB 16401; oldest/newest commit timestamp xid: 0/0; oldest running xid 6641; online rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: B/E6000188, prev B/E6000110, desc: RUNNING_XACTS nextXid6641 latestCompletedXid 6640 oldestRunningXid 6641 rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: B/E60001C0, prev B/E6000188, desc: SWITCH rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: B/E8000028, prev B/E60001C0, desc: CHECKPOINT_SHUTDOWNredo B/E8000028; tli 79; prev tli 79; fpw true; xid 0:6641; oid 37940; multi 1; offset 0; oldest xid 561in DB 16400; oldest multi 1 in DB 16401; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: B/E80000A0, prev B/E8000028, desc: RUNNING_XACTS nextXid6641 latestCompletedXid 6640 oldestRunningXid 6641 rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: B/E80000D8, prev B/E80000A0, desc: RUNNING_XACTS nextXid6641 latestCompletedXid 6640 oldestRunningXid 6641 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: B/E8000110, prev B/E80000D8, desc: CHECKPOINT_ONLINEredo B/E80000D8; tli 79; prev tli 79; fpw true; xid 0:6641; oid 37940; multi 1; offset 0; oldest xid 561in DB 16400; oldest multi 1 in DB 16401; oldest/newest commit timestamp xid: 0/0; oldest running xid 6641; online rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: B/E8000188, prev B/E8000110, desc: RUNNING_XACTS nextXid6641 latestCompletedXid 6640 oldestRunningXid 6641 rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: B/E80001C0, prev B/E8000188, desc: SWITCH Cheers, Paul
Paul Förster wrote: > maybe try with another WAL file or files? > > Works for me... Ok, I tried it with all the files in the pg_wal directory and it worked with one: the first one (lexicographically/hex).
Hi André, > On 01. May, 2020, at 15:46, André Hänsel <andre@webkr.de> wrote: > > Ok, I tried it with all the files in the pg_wal directory and it worked with one: the first one (lexicographically/hex). strange. Bug? I don't know. What is your PostgreSQL version? Mine is 12.2 compiled from source on the machine it runs on. Cheers, Paul
Paul Förster wrote: > strange. Bug? I don't know. > > What is your PostgreSQL version? Mine is 12.2 compiled from source on the machine it runs on. 11.7-2.pgdg18
Hi André, > On 01. May, 2020, at 16:03, André Hänsel <andre@webkr.de> wrote: > > 11.7-2.pgdg18 is this Ubuntu? A pre-packaged version? I use openSUSE as a dedicated server and, as said, compiled on that machine. Did you try an 11.7 compiled from source onthat machine? I always recommend this. :-) Cheers, Paul
On 2020-May-01, André Hänsel wrote: > Paul Förster wrote: > > > maybe try with another WAL file or files? > > > > Works for me... > > Ok, I tried it with all the files in the pg_wal directory and it > worked with one: the first one (lexicographically/hex). The other files have not yet been written, so pg_waldump cannot read them. If you can find out what was the name of the file that it had before the recycling, you can rename (a copy of) the file to that, and then pg_waldump will be able to read it. (You can try brute-forcing the search for the correct file name). If you have an archive_command-generated archive of WALs, or pg_receivewal, those files should all be readable by pg_waldump. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services