Re: URGENT issue: pg-xlog growing on master!

[bricklen <bricklen@xxxxxxxxx>]

On Mon, Jun 10, 2013 at 8:51 AM, bricklen <bricklen@xxxxxxxxx> wrote:

On Mon, Jun 10, 2013 at 8:35 AM, Niels Kristian Schjødt <nielskristian@xxxxxxxxxxxxx> 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'