Re: Cannot change archive_command with a reload

CS DBA <cs_dba@xxxxxxxxxxxxxxxxxxx> · Sun, 02 Feb 2014 09:57:14 -0700



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

    
          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
              
                
    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)

    
    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