On Mon, Jun 10, 2013 at 8:51 AM, bricklen <brick...@gmail.com> wrote:
> > On Mon, Jun 10, 2013 at 8:35 AM, Niels Kristian Schjødt < > nielskrist...@autouncle.com> wrote: > >> I can't seem to figure out which steps I need to do, to get the standby >> server wiped and get it started as a streaming replication again from >> scratch. I tried to follow the steps, from step 6, in here >> http://wiki.postgresql.org/wiki/Streaming_Replication but the process >> seems to fail when I reach the point where I try to do a psql -c "SELECT >> pg_stop_backup()". It just says: >> > > > If you use pg_basebackup you don't need to manually put the master into > backup mode. > Be aware that if you are generating a lot of WAL segments and your > filesystem backup is large (and takes a while to ship to the slave), you > will need to set "wal_keep_segments" quite high on the master to prevent > the segments from disappearing during the setup of the slave -- or at least > that's the case when you use "--xlog-method=stream". > > For what its worth, I took some notes when I set up Streaming Replication the other day and the process worked for me. There might have been some tweaks here and there that I negelected to write down, but the gist of the steps are below. If anyone has any corrections, please chime in! ##On the hot standby, create the staging directory to hold the master's log files mkdir /pgdata/WAL_Archive chown postgres:postgres /pgdata/WAL_Archive # master, $PGDATA/postgresql.conf wal_level = hot_standby archive_mode = on ## /pgdata/WAL_Archive is a staging directory on the slave, outside of $PGDATA archive_command = 'rsync -W -a %p postgres@SLAVE_IP_HERE :/pgdata/WAL_Archive/' max_wal_senders = 3 wal_keep_segments = 10000 # if you have the room, to help the pg_basebackup # not fail due to the WAL segment getting removed from the master. ## Modify the master $PGDATA/pg_hba.conf and enable the replication lines for the IPs of the slaves. ## Issue "pg_ctl reload" on the master after the changes have been made. # TYPE DATABASE USER ADDRESS METHOD hostssl replication replication SLAVE_IP_HERE/32 md5 ## On the hot standby, $PGDATA/postgresql.conf hot_standby = on #off # "on" allows queries during recovery max_standby_archive_delay = 15min # max delay before canceling queries, set to hours if backups will be taken from here max_standby_streaming_delay = 15min # max delay before canceling queries hot_standby_feedback = on #off ## On the master, create the replication role, which will be replicated to the slave via pg_basebackup psql -d postgres -c "CREATE USER replication WITH replication ENCRYPTED PASSWORD 'CHANGEME' LOGIN" ## Restart the master, to pick up the changes to postgresql.conf ## On the slave, from $HOME, issue the pg_basebackup command to start setting up the hot standby from the master ## --host=IP_OF_MASTER -> The master's IP ## --pgdata=$PGDATA -> The slave's $PGDATA directory ## -- xlog-method=stream -> Opens a second connection to the master to stream the WAL segments rather than pulling them all at the end ## --password will prompt for the replication role's password ## Without compression, "stream" gets the changes via the same method as Streaming Replication time pg_basebackup --pgdata=$PGDATA --host=IP_OF_MASTER --port=5432 --username=replication --password --xlog-method=stream --format=plain --progress --verbose -- Alternate version with compression #time pg_basebackup --pgdata=$PGDATA --host=IP_OF_MASTER --port=5432 --username=replication --password --xlog --gzip --format=tar --progress --verbose ##On the standby, create $PGDATA/recovery.conf: standby_mode = on ## To promote the slave to a live database, issue "touch /tmp/promote_db" trigger_file = '/tmp/promote_db' ## Host can be the master's IP or hostname primary_conninfo = 'host=IP_OF_MASTER port=5432 user=replication password=CHANGEME' ## Log the standby WAL segments applied to a standby.log file ## TODO: Add the standby.log to a log rotator restore_command = 'cp /pgdata/WAL_Archive/%f "%p" 2>>/pgdata/9.2/data/pg_log/standby.log' ## XXX: If there are multiple slaves, do not use pg_archivecleanup (WAL segments could be removed before being applied to other slaves) archive_cleanup_command = '/usr/pgsql-9.2/bin/pg_archivecleanup /pgdata/WAL_Archive %r' ## On hot standby clusters, set to 'latest' to switch to the newest timeline in the archive recovery_target_timeline = 'latest'
