Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WA0HM-000785-B1 for pgsql-admin@arkaria.postgresql.org; Sun, 02 Feb 2014 16:52:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WA0HL-0006W2-Q1 for pgsql-admin@arkaria.postgresql.org; Sun, 02 Feb 2014 16:52:19 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WA0HJ-0006Tr-Iy for pgsql-admin@postgresql.org; Sun, 02 Feb 2014 16:52:17 +0000 Received: from mail-ie0-f169.google.com ([209.85.223.169]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WA0HF-0002RD-Jk for pgsql-admin@postgresql.org; Sun, 02 Feb 2014 16:52:17 +0000 Received: by mail-ie0-f169.google.com with SMTP id to1so5779823ieb.0 for ; Sun, 02 Feb 2014 08:52:12 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:message-id:date:from:user-agent:mime-version:cc :subject:references:in-reply-to:content-type; bh=WUw7uWvlX/o3Lr9SWQDCzYJHxWZpA2WriKEvrKYfoMQ=; b=Q+HnIiYXGe1FZ6xH0qL0d+e7DDASxiX081SBWQEyDC8YPxK59N3tfWM/IelS3gRQW/ SlUqiKNxzBhVpnv/rk5AbtagraeJei5mbpXizVO4HgTWACApv28px7TaESyuACFEijSD PPoOTC3HrUjv4iF74ectrFVUGi8XW7jOomVyaJ6iN9UlWtnMl4u9SPErjCJFnxA/KV/n 2E6MyWgrmosMi+eP3z676+hkyxugoC2yujyAw0Lj2Ig8RcllxvWXq+ibWqnCjxe08gm2 eSJ6VTAHrBcsuhJD0tKau6Xr1YL/ikxBc+kal9h2a9GUCq5gWDpTSVPPrJ68zwtLSXOj doNQ== X-Gm-Message-State: ALoCoQnmDRHa+jCDnxqGovpDgTHObs33cbxxvvW/P9WoZZinBRoq1sJF1Aa9tK6FM6TvBmLLViwt X-Received: by 10.50.78.229 with SMTP id e5mr8068950igx.49.1391359932199; Sun, 02 Feb 2014 08:52:12 -0800 (PST) Received: from Kevins-MacBook-Pro.local ([67.148.186.194]) by mx.google.com with ESMTPSA id x13sm20982298igp.2.2014.02.02.08.52.11 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Sun, 02 Feb 2014 08:52:11 -0800 (PST) Message-ID: <52EE77BA.4080609@consistentstate.com> Date: Sun, 02 Feb 2014 09:52:10 -0700 From: CS DBA User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:24.0) Gecko/20100101 Thunderbird/24.0.1 MIME-Version: 1.0 CC: pgsql-admin Subject: Re: Cannot change archive_command with a reload References: <52EE69AF.4030709@consistentstate.com> <52EE71B9.3090204@consistentstate.com> In-Reply-To: <52EE71B9.3090204@consistentstate.com> Content-Type: multipart/alternative; boundary="------------030001060603020907080600" X-Pg-Spam-Score: -0.6 (/) 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 This is a multi-part message in MIME format. --------------030001060603020907080600 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 2/2/14, 9:26 AM, CS DBA wrote: > On 2/2/14, 9:19 AM, Raghavendra wrote: >> >> >> On Sun, Feb 2, 2014 at 9:22 PM, CS DBA > > wrote: >> >> Hi all; >> >> we have a cluster running with an archive command that is failing. >> I tried a reload and the value does not change >> >> >> Reload with new value won't work until you fix the failing >> archive_command. >> >> Eg:- >> >> ps -ef | grep arch >> postgres 29743 29736 0 Jan30 ? 00:00:00 postgres: archiver process >> failed on 00000001000000010000003D >> >> and it might cause same error you are experiencing as >> >> postgres=# select set_config('archive_command','cp %p >> /opt/PostgreSQL/9.3/a93/%f',false); >> ERROR: parameter "archive_command" cannot be changed now >> >> So, fix the archive_command first and then give new changes. >> >> Tried a set_config and I get this error: >> >> select set_config ('archive_command', 'cp %p /data/wal_tmp/%f && >> mv /data/wal_tmp/%f /data/wal/&f', 'false'); >> ERROR: parameter "archive_command" cannot be changed now >> >> >> If your archive_command pointing to "/data/wal_tmp" then please >> ensure that directory exists there. If that directory not present >> then archive process fail to copy the transaction logs. When archiver >> process in failed state you cannot apply any new changes to the >> archive_command it will fail. >> >> Also, am surprised to see "mv" command, basically the archive_command >> meant to have a copies of pg_xlogs but not any OS related directory >> movements. > > we want to copy the file to /data/wal_tmp, then do a mv to /data/wal > so our process that ships a copy to the standby servers never see's a > partial file (since it watches /data/wal) > >> >> Link on archive_command: >> http://www.postgresql.org/docs/9.2/static/runtime-config-wal.html >> >> >> --- >> Regards, >> Raghavendra >> EnterpriseDB Corporation >> Blog: http://raghavt.blogspot.com/ >> > So, I found that the archive command has a typeo (using a &f instead of a %f at the end): cp %p /data/wal_tmp/%f && mv /data/wal_tmp/%f /data/wal/*&*f I don't see any way to make the command as is "work" so I suspect the only option is a restart. Question: as a growing stack of archive commands fails (see log entry example below), is it going to take an increasingly longer and longer time to do a restart? Log entry: sh: f: command not found 11631 2014-02-02 09:49:41 MST [2014-02-02 01:04:54 MST] [3820] LOG: archive command failed with exit code 127 11631 2014-02-02 09:49:41 MST [2014-02-02 01:04:54 MST] [3821] DETAIL: The failed archive command was: test ! -f /data/wal/00000001000006E50000001C && cp pg_xlog/00000001000006E50000001C /data/wal_tmp/00000001000006E50000001C && mv /data/wal_tmp/00000001000006E50000001C /data/wal/&f 11631 2014-02-02 09:49:41 MST [2014-02-02 01:04:54 MST] [3822] WARNING: transaction log file "00000001000006E50000001C" could not be archived: too many failures --------------030001060603020907080600 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 2/2/14, 9:26 AM, CS DBA wrote:
On 2/2/14, 9:19 AM, Raghavendra wrote:


On Sun, Feb 2, 2014 at 9:22 PM, CS DBA <cs_dba@consistentstate.com> wrote:
Hi all;

we have a cluster running with an archive command that is failing.
I tried a reload and the value does not change


Reload with new value won't work until you fix the failing archive_command.

Eg:-

ps -ef | grep arch
postgres 29743 29736  0 Jan30 ?        00:00:00 postgres: archiver process   failed on 00000001000000010000003D

and it might cause same error you are experiencing as 

postgres=# select set_config('archive_command','cp %p /opt/PostgreSQL/9.3/a93/%f',false);
ERROR:  parameter "archive_command" cannot be changed now

So, fix the archive_command first and then give new changes.

Tried a set_config and I get this error:

select set_config ('archive_command', 'cp %p /data/wal_tmp/%f && mv /data/wal_tmp/%f /data/wal/&f', 'false');
ERROR:  parameter "archive_command" cannot be changed now


If your archive_command pointing to "/data/wal_tmp" then please ensure that directory exists there. If that directory not present then archive process fail to copy the transaction logs. When archiver process in failed state you cannot apply any new changes to the archive_command it will fail. 

Also, am surprised to see "mv" command, basically the archive_command meant to have a copies of pg_xlogs but not any OS related directory movements.

we want to copy the file to /data/wal_tmp, then do a mv to /data/wal so our process that ships a copy to the standby servers never see's a partial file (since it watches /data/wal)



---
Regards,
Raghavendra
EnterpriseDB Corporation




So, I found that the archive command has a typeo (using a &f instead of a %f at the end):
cp %p /data/wal_tmp/%f && mv /data/wal_tmp/%f /data/wal/&f


I don't see any way to make the command as is "work" so I suspect the only option is a restart.

Question:  as a growing stack of archive commands fails (see log entry example below), is it going to take an increasingly longer and longer time to do a restart?

Log entry:
sh: f: command not found
11631    2014-02-02 09:49:41 MST [2014-02-02 01:04:54 MST] [3820] LOG:  archive command failed with exit code 127
11631    2014-02-02 09:49:41 MST [2014-02-02 01:04:54 MST] [3821] DETAIL:  The failed archive command was: test ! -f /data/wal/00000001000006E50000001C && cp pg_xlog/00000001000006E50000001C /data/wal_tmp/00000001000006E50000001C && mv /data/wal_tmp/00000001000006E50000001C /data/wal/&f
11631    2014-02-02 09:49:41 MST [2014-02-02 01:04:54 MST] [3822] WARNING:  transaction log file "00000001000006E50000001C" could not be archived: too many failures



--------------030001060603020907080600--