Sfrgttk

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?

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.