Re: Rebuild streaming replication

Chander Ganesan <chander@xxxxxxxxxx> · Thu, 12 Mar 2015 17:28:11 -0400



    Hi Dara,

      
      A few things:

      
      1.  Replication is a binary process - that means that you can
      never set it up with using any of the pg_dump type tools (i.e.,
      pg_dumpall/pg_dump can never be used.)

      2.  PostgreSQL provides a special tool to make this process
      super-simple:
      http://www.postgresql.org/docs/9.1/static/app-pgbasebackup.html

      
      So the steps should be as follows:

      
      1.  Don't touch the master (you really should never have to touch
      the master's config/run state once you have replication setup,
      except for performance tuning, etc.)

      2.  Install PostgreSQL on the slave (if it already exists there,
      stop the slave server.)

      3. On the slave server run the pg_basebackup tool (note: you may
      need to delete the contents of the data directory on the slave
      first):

      
      pg_basebackup -D <DATA DIRECTORY ON SLAVE> -c -P -h
      <MASTER IP ADDRESS> -U <REPLICATION USERNAME>

      
      4. If your config files are in your data directory of the master,
      they'll all be replicated over as well - if they are not then
      you'll need to copy them over by hand.

      
      Hope that helps!

      
      Chander

      
      On 3/12/15 5:05 PM, Dara Unglaube wrote:

    
      We had two servers set up with streaming
        replication. I believe when we did a full vacuum analyze to the
        database on the master, it caused the slave to not be able to
        catch up (wal_keep_segments = 32, but still not enough and had
        archive off). I am trying to re-set up the slave to streaming
        replication. Below are the steps I took (attempted a couple
        times now). Does anyone have any idea what I am doing wrong? The
        service is unable to start after I copy and paste the files
        over.

        
          Uninstalled and re-installed postgres on slave and master
            (v9.1.10)
        
      
          Stopped slave postgres service and added files and
            settings for streaming replication to the slave
            (postgresql.cof, recovery.conf - which all worked in the
            past set up)
          Loaded the master with a pg_dumpall backup file.
          Stopped the master postgres service.

          
          Copied the data directory from master to slave minus
            pg_xlog folder, postgresql.conf and postgresql.pid files
            using rsync

          
          Attempted to start postgres on the slave and it is unable
            to start 
          
            Postgres log:
            
              CDT LOG: entering standby mode

              
              CDT FATAL: the database is starting up (multiple
                times)
              CDT FATAL: could not connect to the primary server...
                (assume this is ok because master has not been started
                yet)

              
            Event Viewer, Administrative Events
            
              Timed out waiting for server start up

              
        Any help, thoughts, comments, tips, etc would be greatly
          appreciated.

        
        Thanks!

        
        Dara

          
    -- 
Chander Ganesan
Open Technology Group, Inc.
11010 Lake Grove Blvd Ste. 100-307
Morrisville, NC  27560
919-463-0999/877-258-8987
http://www.otg-nc.com