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

On Mon, Jun 10, 2013 at 8:51 AM, bricklen <bricklen(at)gmail(dot)com> wrote:

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