Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cVxAs-0004pp-DT for pgsql-admin@arkaria.postgresql.org; Tue, 24 Jan 2017 09:13:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cVxAs-0006Hh-0N for pgsql-admin@arkaria.postgresql.org; Tue, 24 Jan 2017 09:13:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cVx9A-0004Oh-6y for pgsql-admin@postgresql.org; Tue, 24 Jan 2017 09:12:12 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cVx96-00052K-LJ for pgsql-admin@postgresql.org; Tue, 24 Jan 2017 09:12:11 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id v0O9C6Ck023398 for ; Tue, 24 Jan 2017 11:12:06 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) To: pgsql-admin@postgresql.org From: Achilleas Mantzios Subject: Strange times in WAL files in archive directory (9.3) Message-ID: <58871A66.306@matrix.gatewaynet.com> Date: Tue, 24 Jan 2017 11:12:06 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org Hello, we are running PostgreSQL 9.3 on a new VM running Linux 3.16.0-4-amd64 #1 SMP, Debian 8.6, and we setup WAL archiving yesterday. We were planing to keep a window of 15 days worth of WAL files, so we thought of writing a script to delete files older than 15 days. The settings used were rather common : archive_mode = on archive_command = '/usr/bin/scp %p sma:/smadb/pgsql/pitr/%f' archive_timeout=240 However, this morning, to my surprise I saw this by doing ls on the archive directory : # ls -l /smadb/pgsql/pitr/ | less total 12680296 -rw------- 1 postgres postgres 16777216 Jan 24 03:44 000000020000070100000000 -rw------- 1 postgres postgres 16777216 Jan 24 03:50 000000020000070100000001 -rw------- 1 postgres postgres 16777216 Jan 24 03:54 000000020000070100000002 -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:04 000000020000070100000005 -rw------- 1 postgres postgres 16777216 Jan 24 04:09 000000020000070100000006 -rw------- 1 postgres postgres 16777216 Jan 24 04:14 000000020000070100000007 -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:25 00000002000007010000000A -rw------- 1 postgres postgres 16777216 Jan 24 04:29 00000002000007010000000B -rw------- 1 postgres postgres 16777216 Jan 24 04:34 00000002000007010000000C -rw------- 1 postgres postgres 16777216 Jan 24 04:39 00000002000007010000000D -rw------- 1 postgres postgres 16777216 Jan 24 04:39 00000002000007010000000E -rw------- 1 postgres postgres 16777216 Jan 24 04:44 00000002000007010000000F ........... -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 <------ -rw------- 1 postgres postgres 16777216 Jan 23 13:04 0000000200000701000000F2 <------ time jumps -rw------- 1 postgres postgres 16777216 Jan 23 13:05 0000000200000701000000F3 -rw------- 1 postgres postgres 16777216 Jan 23 13:06 0000000200000701000000F4 So IMHO either the naming of the WAL filename got damaged somehow or the one of the servers got wrong time or something I miss. "Jan 23 13:04" is indeed the time that we started the server for WAL archiving. By sorting by time I get : ls -ltr /smadb/pgsql/pitr/ | less -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 23 13:09 0000000200000701000000F8 -rw------- 1 postgres postgres 16777216 Jan 23 13:10 0000000200000701000000F9 -rw------- 1 postgres postgres 16777216 Jan 23 13:11 0000000200000701000000FA -rw------- 1 postgres postgres 16777216 Jan 23 13:12 0000000200000701000000FB -rw------- 1 postgres postgres 16777216 Jan 23 13:13 0000000200000701000000FC ........ -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 <----- problem begins -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 ........ (same pattern goes on 0000000200000703 mixing with 0000000200000701 ) -rw------- 1 postgres postgres 16777216 Jan 24 09:42 0000000200000701000000EE -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 <----- problem ends -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 Any thoughts about any explanation on this? -- 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