Re: Couple of issues with pg_xlogdump

On 2013-04-23 14:51:05 +0530, Pavan Deolasee wrote:
> Hello,
>
> I was playing with pg_xlogdump in the HEAD and found a few issues.
>
> 1. Tried compiling pg_xlogdump via PGXS mechanism and it fails with the
> following error:
> make: *** No rule to make target
> `/home/pavan.deolasee/work/pgsql/postgresql/install/lib/pgxs/src/makefiles/../../src/backend/access/transam/xlogreader.c',
> needed by `xlogreader.c'. Stop.
>
> There are no issues if the sources are compiled directly inside the contrib
> module

Yes, its not supposed to work. In some previous thread I was suggesting
to write out an explicit error but the reactions where mixed, so I
didn't pursue it further. I guess I should submit something more than a
POC patch then...

> 2. I created a fresh database cluster, created a table and COPY IN a
> million records in the table and then stopped the server. I then tried to
> dump the xlog files from pg_xlog directory.
>
> [pavan(dot)deolasee(at)puppetserver pg_xlogdump]$ ls ~/db93head/pg_xlog/
> 000000010000000000000004 000000010000000000000005
> 000000010000000000000006 000000010000000000000007
> 000000010000000000000008 archive_status
>
> [pavan(dot)deolasee(at)puppetserver pg_xlogdump]$ ./pg_xlogdump
> ~/db93head/pg_xlog/000000010000000000000005
> pg_xlogdump: FATAL: could not find a valid record after 0/5000000
> [pavan(dot)deolasee(at)puppetserver pg_xlogdump]$ ./pg_xlogdump
> ~/db93head/pg_xlog/000000010000000000000006
> pg_xlogdump: FATAL: could not find a valid record after 0/6000000
> [pavan(dot)deolasee(at)puppetserver pg_xlogdump]$ ./pg_xlogdump
> ~/db93head/pg_xlog/000000010000000000000007
> pg_xlogdump: FATAL: could not find a valid record after 0/7000000
> [pavan(dot)deolasee(at)puppetserver pg_xlogdump]$ ./pg_xlogdump
> ~/db93head/pg_xlog/000000010000000000000008
> pg_xlogdump: FATAL: could not find a valid record after 0/8000000
>
> So pg_xlogdump gives error for all WAL files except the first one. Should
> it not have printed the WAL records from these files ?

Probably not. Those are likely renamed wal files that do not yet contain
valid data.

> The first file prints ok with this at the end:
>
> [pavan(dot)deolasee(at)puppetserver pg_xlogdump]$ ./pg_xlogdump
> ~/db93head/pg_xlog/000000010000000000000004 | tail -n 2
> pg_xlogdump: FATAL: error in WAL record at 0/4C7F208: record with zero
> length at 0/4C7F270

Which this confirms. This is likely the current end of wal. If you look
at pg_current_xlog_location() after starting the server again, it should
show an address nearby?

> rmgr: XLOG len (rec/tot): 72/ 104, tx: 0, lsn:
> 0/04C7F1A0, prev 0/04C7F170, bkp: 0000, desc: checkpoint: redo 0/4400D70;
> tli 1; prev tli 1; fpw true; xid 0/1807; oid 24576; multi 1; offset 0;
> oldest xid 1795 in DB 1; oldest multi 1 in DB 1; oldest running xid 0;
> online
> rmgr: XLOG len (rec/tot): 72/ 104, tx: 0, lsn:
> 0/04C7F208, prev 0/04C7F1A0, bkp: 0000, desc: checkpoint: redo 0/4C7F208;
> tli 1; prev tli 1; fpw true; xid 0/1807; oid 16387; multi 1; offset 0;
> oldest xid 1795 in DB 1; oldest multi 1 in DB 1; oldest running xid 0;
> shutdown
>
> But even this is a bit surprising because I would have expected the
> shutdown checkpoint WAL record in the last WAL file. Just to note, I
> stopped the server only once at the end of loading the data.

Same reasoning, we rename/recycle wal files into place before they are
used. I think its RemoveOldXlogFiles or so that does that.

> 3. The usage of pg_xlogdump shows this:
> Usage:
> pg_xlogdump [OPTION] [STARTSEG [ENDSEG]]
>
> Looking at the usage, one might feel that the STARTSEG and ENDSEG both are
> optional. But if I try to invoke pg_xlogdump without any argument, it fails.

It works without either if you use explicit options like -s STARTADDR
and -p PATH which is frequently useful to just print a few records at
the correct point. I am not sure how could put that in there without
making it too complicated. Any suggestions?

Thanks for looking at this!

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services