Postgresql Streaming Replication Error: WAL segment removed

postgresqlpostgresql-9.3replication

I want to set up PostgreSQL streaming replication, but get the following error:

FATAL:  could not receive data from WAL stream: 
ERROR:  requested WAL segment 00000001000000000000006A has already been removed.

Master IP : 192.168.0.30

Slave IP : 192.168.0.36

On Master:

I have created a user rep which is used solely for replication.

The relevant files inside Postgres config directory (/opt/Postgres/9.3/data):

pg_hba.conf:

host    replication     rep     192.168.0.36/32   trust

postgresql.conf:

listen_addresses = 'localhost,192.168.0.30'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

I've restarted the postgres service.

On Slave:

I've stopped the postgres service, then applied the changes to the two files:

pg_hba.conf:

host    replication     rep     192.168.0.30/32  trust

postgresql.conf:

listen_addresses = 'localhost,192.168.0.36'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

For replicating the initial database I have done:

On Master:

Internal postgres backup start command to create a backup label:

psql -c "select pg_start_backup('initial_backup');"

… for transferring the database data to slave:

rsync -cva --inplace --exclude=*pg_xlog* /opt/Postgresql/9.3/data/ 192.168.0.36:/opt/Postgresql/9.3/data/

…for internal backup stop to clean up:

psql -c "select pg_stop_backup();"

On Slave:

I've created the following recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.30 port=5432 user=rep password=yourpassword'
trigger_file = '/tmp/postgresql.trigger.5432'

Starting the postgres service on the slave starts without any errors but is still waiting:

ps -ef | grep -i postgres

postgres 12959     1  0 13:39 ?        00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres 12969 12959  0 13:39 ?        00:00:00 postgres: logger process                                    
postgres 12970 12959  0 13:39 ?        00:00:00 postgres: startup process   waiting 00000001000000000000006A

Meanwhile, on master:

ps -ef | grep -i postgres

postgres  5930     1  0 13:39 ?        00:00:01 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres  5931  5930  0 13:39 ?        00:00:00 postgres: logger process                                    
postgres  5933  5930  0 13:39 ?        00:00:00 postgres: checkpointer process                              
postgres  5934  5930  0 13:39 ?        00:00:00 postgres: writer process                                    
postgres  5935  5930  0 13:39 ?        00:00:00 postgres: wal writer process                                
postgres  5936  5930  0 13:39 ?        00:00:00 postgres: autovacuum launcher process                       
postgres  5937  5930  0 13:39 ?        00:00:00 postgres: archiver process                                  
postgres  5938  5930  0 13:39 ?        00:00:00 postgres: stats collector process

psql command on the slave gives:

psql.bin: FATAL:  the database system is starting up

–> cd pg_log gives reason for waiting:-

FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment  has already been removed

00000001000000000000006A segment is not in master's pg_xlog but it is in slaves pg_xlog

How can I solve this error?

Best Answer

From Streaming Replication in the PostgreSQL documentation:

If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that's accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.

Related Question