Re: Rebuild streaming replication

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