Postgresql – Error on PostgreSQL streaming replication

postgresqlreplication

I have the exact same problem as Wal segment still exists on master, but logs on slave and master say it's been removed except that it is in Production !

PostgreSQL 9.4 / Debian Jessie

postgresql.conf:

wal_level = hot_standby
max_wal_senders = 5

pg_hba.conf

host    replication     repl        x.x.x.0/20               trust

On the slave replica : Recovery.conf

standby_mode = on
primary_conninfo = 'host=<master_server_ip> port=xxxx user=xxxx password=xxxx'
trigger_file = '/var/lib/postgresql/trigger_failover'

Error on Master:

ERROR:  requested WAL segment 000000030000007E00000054 has already been removed

Error on slave replica:

LOG:  started streaming WAL from primary at 7E/54000000 on timeline 3
UTC [31252-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000030000007E00000054 has already been removed

What can I do to correct it without stopping the production ?

It is not working for several months now, so I guess there is a lot to recover. I can't even find the archives in ./postgresql/9.4/main/mnt/server/archivedir/

Best Answer

It sounds like the standby is now several months out of date. It's probably not useful at this point. You can replace the standby server with a new basebackup and start up the streaming again. You may want to look into using replication slots this time to ensure that WALs won't be prematurely removed from the primary.

Related Solutions

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

The message "The database system is starting up." does not indicate an error. The reason it is at the FATAL level is so that it will always make it to the log, regardless of the setting of log_min_messages:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

After the rsync, did you really run what you show?:

pgsql -c "select pg_stop_backup();";

Since there is, so far as I know, no pgsql executable, that would leave the backup uncompleted, and the slave would never come out of recovery mode. On the other hand, maybe you really did run psql, because otherwise I don't see how the slave would have logged such success messages as:

Log: consistent recovery state reached at 0/BF0000B0

and:

Log: streaming replication successfully connected to primary

Did you try connecting to the slave at this point? What happened?

The "Success. You can now start..." message you mention is generated by initdb, which shouldn't be run as part of setting up a slave; so I think you may be confused about something there. I'm also concerned about these apparently conflicting statements:

The only ways I have restarted Postgres is through the service postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands. After I receive this error, I kill all processes and again try to restart the database...

Did you try to stop the service through the service script? What happened? It might help in understanding the logs if you prefixed lines with more information. We use:

log_line_prefix = '[%m] %p %q<%u %d %r> '

The recovery.conf script looks odd. Are you copying from the master's pg_xlog directory, the slave's active pg_xlog directory, or an archive directory?

Postgresql – Streaming Replication in PostgreSQL

PostgreSQL replicas never finish recovering. This is by design. Basically a replica is always in "recovering from disaster" mode except that it is using receiving the WAL segments from the master rather than on disk.

So what you are seeing is not cause for concern. If it is not working yet, then you will need to provide a more detailed description of what you are trying to do and what is not working. But as far as you are posting it seems normal.

Related Question