Обсуждение: Cannot change archive_command with a reload

Hi all;

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

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


Thoughts?

Thanks in advance

What version of Postgres are you using? What OS? Have you tried editing the archive_command in postgresql.conf and then reload?

Strahinja Kustudić | Lead System Engineer | Nordeus

On Sun, Feb 2, 2014 at 4:52 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

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


Thoughts?

Thanks in advance


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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.

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/

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)

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/

On Sun, Feb 2, 2014 at 9:56 PM, CS DBA <cs_dba@consistentstate.com> 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)

'cp %p /data/wal_tmp/%f && mv /data/wal_tmp/%f /data/wal/&f',

Seems you are missing something here, you have two action commands in your archive_command. One is copy "cp" and another is "mv" move. Once you are copying file to the directory you are moving that entire directory and not keeping the place available for archiver process to copy next file in that location on its next arrival.

Because, every transaction logs first go to archive directory as you mentioned "cp %p /data/wal_tmp/%f". Next it will execute the other command too since its one command string, i.e, move "mv /data/wal_tmp/%f /data/wal&f".

Hence, you need to have unique place for archives and it should not be disturbed in any case. If you want to ship a copy to standby server then have another directory with same set of copies at OS level.

--Raghav

On Feb 2, 2014, at 9:51 AM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:

> Once you are copying file to the directory you are moving that entire directory and not keeping the place available
forarchiver process to copy next file in that location on its next arrival. 

No, he's just mv'ing the file, not the directory.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

My issue is the &f instead of %f at the end.  However here's the intent:

if the first command is true (cp %p /data/wal_tmp/%f) then I want the second command to execute (mv /data/wal_tmp/%f /data/wal/%f)

In this case archiver process fail, because there won't be directory called /data/wal_tmp anymore with your your "mv" command.

since both directories are in the same file system the mv is immediate so any process watching for files to show up in /data/wal will never see a partial file

"partial file" ?

Its 16MB file in any case there won't be any partial files, because after completion of writing 16mb file in xlog it will come to archive location else not.

--Raghav

 

On Sun, Feb 2, 2014 at 5:57 PM, CS DBA <cs_dba@consistentstate.com> wrote:
> On 2/2/14, 9:51 AM, Raghavendra wrote:
> My issue is the &f instead of %f at the end.  However here's the intent:
>
> if the first command is true (cp %p /data/wal_tmp/%f) then I want the second
> command to execute (mv /data/wal_tmp/%f /data/wal/%f)
>

I suggest you to wrap the commands in a shell script and to get a log
of the activity, at least in the beginning, to see what is not
working. Moreover, having a script will allow you to extend the logic.


> since both directories are in the same file system the mv is immediate so
> any process watching for files to show up in /data/wal will never see a
> partial file

As explained by others, the issue of having partial files is not an
issue at all.

Luca

On Feb 3, 2014, at 8:15 AM, Luca Ferrari <fluca1978@infinito.it> wrote:

> As explained by others, the issue of having partial files is not an
> issue at all.

It most certainly is an issue--the cp command is not atomic.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On Feb 3, 2014, at 8:15 AM, Luca Ferrari <fluca1978@infinito.it> wrote:

> As explained by others, the issue of having partial files is not an
> issue at all.

I apologize for the tone of my post--early AM and as soon as I hit send, I realized how harsh it might seem.


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice