Re: Cannot change archive_command with a reload

Raghavendra <raghavendra.rao@xxxxxxxxxxxxxxxx> · Sun, 2 Feb 2014 22:21:00 +0530

On Sun, Feb 2, 2014 at 9:56 PM, CS DBA <cs_dba@xxxxxxxxxxxxxxxxxxx> wrote:

    On 2/2/14, 9:19 AM, Raghavendra wrote:

          On Sun, Feb 2, 2014 at 9:22 PM, CS
            DBA <cs_dba@xxxxxxxxxxxxxxxxxxx>
            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