pg.ddx.io pgsql-admin@postgresql.org mailing list archive help / color / mirror / Atom feed From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: Strange times in WAL files in archive directory (9.3)
Date: Wed, 25 Jan 2017 09:50:09 +0200
Message-ID: <588858B1.5080905@matrix.gatewaynet.com> (raw )
In-Reply-To: <58877B13.2000008@matrix.gatewaynet.com >
References: <58871A66.306@matrix.gatewaynet.com >
<58873609.4030004@matrix.gatewaynet.com >
<13126.1485264550@sss.pgh.pa.us >
<588759C8.3090209@matrix.gatewaynet.com >
<14804.1485267261@sss.pgh.pa.us >
<588768FF.20906@matrix.gatewaynet.com >
<20170124145556.GR18360@tamriel.snowman.net >
<5887739D.50405@matrix.gatewaynet.com >
<20170124155548.GV18360@tamriel.snowman.net >
<58877B13.2000008@matrix.gatewaynet.com >
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Just an observation on this. By looking at the timestamps of the files I see a pattern :
-rw------- 1 postgres postgres 16777216 Jan 23 13:04 0000000200000701000000F2
-rw------- 1 postgres postgres 16777216 Jan 23 13:05 0000000200000701000000F3
-rw------- 1 postgres postgres 16777216 Jan 23 13:06 0000000200000701000000F4
-rw------- 1 postgres postgres 16777216 Jan 23 13:07 0000000200000701000000F5
-rw------- 1 postgres postgres 16777216 Jan 23 13:08 0000000200000701000000F6
-rw------- 1 postgres postgres 16777216 Jan 23 13:09 0000000200000701000000F7
.....
-rw------- 1 postgres postgres 16777216 Jan 24 03:07 0000000200000702000000F7
-rw------- 1 postgres postgres 16777216 Jan 24 03:11 0000000200000702000000F8
-rw------- 1 postgres postgres 16777216 Jan 24 03:15 0000000200000702000000F9
-rw------- 1 postgres postgres 16777216 Jan 24 03:19 0000000200000702000000FA
-rw------- 1 postgres postgres 16777216 Jan 24 03:23 0000000200000702000000FB
-rw------- 1 postgres postgres 16777216 Jan 24 03:27 0000000200000702000000FC
-rw------- 1 postgres postgres 16777216 Jan 24 03:31 0000000200000702000000FD
-rw------- 1 postgres postgres 16777216 Jan 24 03:35 0000000200000702000000FE
-rw------- 1 postgres postgres 16777216 Jan 24 03:39 0000000200000702000000FF
-rw------- 1 postgres postgres 16777216 Jan 24 03:43 000000020000070300000000
-rw------- 1 postgres postgres 16777216 Jan 24 03:44 000000020000070100000000 <---- logno >> 32 == 701 file appears, now starts the pattern
-rw------- 1 postgres postgres 16777216 Jan 24 03:47 000000020000070300000001
-rw------- 1 postgres postgres 16777216 Jan 24 03:50 000000020000070100000001
-rw------- 1 postgres postgres 16777216 Jan 24 03:51 000000020000070300000002
-rw------- 1 postgres postgres 16777216 Jan 24 03:54 000000020000070100000002
-rw------- 1 postgres postgres 16777216 Jan 24 03:55 000000020000070300000003
-rw------- 1 postgres postgres 16777216 Jan 24 03:59 000000020000070300000004
-rw------- 1 postgres postgres 16777216 Jan 24 04:00 000000020000070100000003
-rw------- 1 postgres postgres 16777216 Jan 24 04:00 000000020000070100000004
-rw------- 1 postgres postgres 16777216 Jan 24 04:03 000000020000070300000005
-rw------- 1 postgres postgres 16777216 Jan 24 04:04 000000020000070100000005
-rw------- 1 postgres postgres 16777216 Jan 24 04:07 000000020000070300000006
-rw------- 1 postgres postgres 16777216 Jan 24 04:09 000000020000070100000006
-rw------- 1 postgres postgres 16777216 Jan 24 04:11 000000020000070300000007
-rw------- 1 postgres postgres 16777216 Jan 24 04:14 000000020000070100000007
-rw------- 1 postgres postgres 16777216 Jan 24 04:15 000000020000070300000008
-rw------- 1 postgres postgres 16777216 Jan 24 04:19 000000020000070300000009
-rw------- 1 postgres postgres 16777216 Jan 24 04:20 000000020000070100000008
-rw------- 1 postgres postgres 16777216 Jan 24 04:20 000000020000070100000009
-rw------- 1 postgres postgres 16777216 Jan 24 04:23 00000002000007030000000A
......
-rw------- 1 postgres postgres 16777216 Jan 24 04:43 00000002000007030000000F
-rw------- 1 postgres postgres 16777216 Jan 24 04:44 00000002000007010000000F
-rw------- 1 postgres postgres 16777216 Jan 24 04:47 000000020000070300000010
-rw------- 1 postgres postgres 16777216 Jan 24 04:50 000000020000070100000010
-rw------- 1 postgres postgres 16777216 Jan 24 04:51 000000020000070300000011
-rw------- 1 postgres postgres 16777216 Jan 24 04:55 000000020000070300000012
-rw------- 1 postgres postgres 16777216 Jan 24 04:56 000000020000070100000011
-rw------- 1 postgres postgres 16777216 Jan 24 04:56 000000020000070100000012
-rw------- 1 postgres postgres 16777216 Jan 24 04:59 000000020000070300000013
-rw------- 1 postgres postgres 16777216 Jan 24 04:59 000000020000070100000013
-rw------- 1 postgres postgres 16777216 Jan 24 05:01 000000020000070300000014
-rw------- 1 postgres postgres 16777216 Jan 24 05:02 000000020000070300000015
-rw------- 1 postgres postgres 16777216 Jan 24 05:04 000000020000070300000016
-rw------- 1 postgres postgres 16777216 Jan 24 05:05 000000020000070300000017
-rw------- 1 postgres postgres 16777216 Jan 24 05:07 000000020000070300000018
-rw------- 1 postgres postgres 16777216 Jan 24 05:08 000000020000070300000019
-rw------- 1 postgres postgres 16777216 Jan 24 05:08 000000020000070100000015
-rw------- 1 postgres postgres 16777216 Jan 24 05:08 000000020000070100000014
-rw------- 1 postgres postgres 16777216 Jan 24 05:08 000000020000070100000016
-rw------- 1 postgres postgres 16777216 Jan 24 05:08 000000020000070100000017
-rw------- 1 postgres postgres 16777216 Jan 24 05:08 000000020000070100000018
-rw------- 1 postgres postgres 16777216 Jan 24 05:08 000000020000070100000019
-rw------- 1 postgres postgres 16777216 Jan 24 05:09 00000002000007030000001A
-rw------- 1 postgres postgres 16777216 Jan 24 05:09 00000002000007030000001B
-rw------- 1 postgres postgres 16777216 Jan 24 05:10 00000002000007030000001C
-rw------- 1 postgres postgres 16777216 Jan 24 05:10 00000002000007030000001D
-rw------- 1 postgres postgres 16777216 Jan 24 05:10 00000002000007030000001E
-rw------- 1 postgres postgres 16777216 Jan 24 05:11 00000002000007030000001F
-rw------- 1 postgres postgres 16777216 Jan 24 05:11 000000020000070300000020
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 000000020000070300000021
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 000000020000070300000022
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 00000002000007010000001E
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 00000002000007010000001A
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 00000002000007010000001B
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 00000002000007010000001C
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 00000002000007010000001D
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 00000002000007010000001F
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 000000020000070100000020
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 000000020000070100000021
-rw------- 1 postgres postgres 16777216 Jan 24 05:12 000000020000070100000022
it is pretty obvious that the current logno % (2^32) = 703, and for every 703-named file that is archived, wal archiver tries to also archive all the unarchived files with filename of logno % (2^32) -
2 = 701.
I should have kept a snapshot of data/pg_xlog/archive_status/ as well, unfortunately I didn't. I didn't have time to look thoroughly into the sources or even try to reproduce via gdb. Hope this helps
a little bit.
I want to clarify once more than the pattern stopped after the last unarchived 701-file :0000000200000701000000F1 was archived . 0000000200000701000000F2 was already archived, actually was the very
first file to be archived as shown at the top of this mail.
:
-rw------- 1 postgres postgres 16777216 Jan 24 09:45 0000000200000703000000EF
-rw------- 1 postgres postgres 16777216 Jan 24 09:47 0000000200000703000000F0
-rw------- 1 postgres postgres 16777216 Jan 24 09:48 0000000200000703000000F1
-rw------- 1 postgres postgres 16777216 Jan 24 09:48 0000000200000701000000EF
-rw------- 1 postgres postgres 16777216 Jan 24 09:48 0000000200000701000000F0
-rw------- 1 postgres postgres 16777216 Jan 24 09:48 0000000200000701000000F1 <-- here
-rw------- 1 postgres postgres 16777216 Jan 24 09:50 0000000200000703000000F2
-rw------- 1 postgres postgres 16777216 Jan 24 09:52 0000000200000703000000F3
-rw------- 1 postgres postgres 16777216 Jan 24 09:56 0000000200000703000000F4
-rw------- 1 postgres postgres 16777216 Jan 24 09:59 0000000200000703000000F5
-rw------- 1 postgres postgres 16777216 Jan 24 10:03 0000000200000703000000F6
-rw------- 1 postgres postgres 16777216 Jan 24 10:07 0000000200000703000000F7
-rw------- 1 postgres postgres 16777216 Jan 24 10:11 0000000200000703000000F8
-rw------- 1 postgres postgres 16777216 Jan 24 10:14 0000000200000703000000F9
-rw------- 1 postgres postgres 16777216 Jan 24 10:17 0000000200000703000000FA
-rw------- 1 postgres postgres 16777216 Jan 24 10:20 0000000200000703000000FB
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
view thread (12+ messages)
Message-ID: <588858B1.5080905@matrix.gatewaynet.com>
Permalink: ../588858B1.5080905@matrix.gatewaynet.com/
Also on: postgresql.org/message-id/588858B1.5080905@matrix.gatewaynet.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: Strange times in WAL files in archive directory (9.3)
In-Reply-To: <588858B1.5080905@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox