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

Thank you - it worked beautifully.
Dara

On Thu, Mar 12, 2015 at 4:28 PM, Chander Ganesan <chander@otg-nc.com> wrote: