Re: Backup error

Adalkonda Harshad <adalkondaharshad@xxxxxxxxx> · Sat, 04 Oct 2014 11:21:23 +0530



    On 03-10-2014 15:07, Celso Vieira
      wrote:

    
        Hello, I have online backup done as follows: 

             - pg_start_backup ... 

             - scp datafiles ... 

             - pg_stop_backup ... 

             - scp data archives needed 

             - Adjust recovery.conf 

             - Start postgres 
        The backup takes 4 hours.
        Bank goes live, but after several mistakes, as do routine
          reindex on all tables. 
        But I had followed errors duplication of primary key in the
          tables, and this prevents the reindex. Already tried vacuumdb
          these cases, but does not solve. 

          The index remains active and to query the table, the sql uses
          the index and returns only one line, but when reading the
          table without index (use id + 1 so it will read the table
          without index), two values appear. How can one have 2
          primary key values in the table? 

          I wonder if it is acceptable to do online backup using
          pg_start_backup, scp, pg_stop_backup in version 9.3.4? This
          type of backup is correct? Inform you that the same error
          occurred in the use of pg_basebackup in version 9.2.4. I
          thought it was bug application. 

            Summarizing the questions: 

             - Is right back up with pg_start_backup, scp,
          pg_stop_backup? 

             - Why postgres duplicates primary keys when the backup is
          done via scp, and even pg_basebackup? 
        This operation is made to duplicate the database, but my
          concern is that our model is not reliable backup.
      
    
    I suspect your scp operation is not
        completed and your performing pg_stop_backup before completing
        scp operation.

      
      Use -Cpv option with scp for increasing performance.

      
      Example:                

                      scp -Cpv example.log 
        username@192.168.0.100:.

      
      (pg_start_backup,scp, pg_stop_backup) is proper direction
        for taking backup.

      
      I suggest you should use pg_basebackup for avoiding any
        errors.

      
      I suspect your primary key is corrupted, so it might have
        allowed duplication while inserting records.

      
      Postgres does not duplicates primary key when backup is
        done via pg_basebackup.
    -- 

      
      Harshad Adalkonda

        Database Administrator

         harshad.adalkonda@xxxxxxxxxxxxxx
      
         
        Office:  +919552687400/8400
       

        http://www.shreeyansh.com