Replication/WAL shipping. db errors on slave after stop/start master

[Bob Hatfield <bobhatfield@xxxxxxxxx>]

Reindex of triggered (slave) database showing errors.

We have replication working in 8.3.12 on two identical Windows 2008 R2 servers.  Anytime I trigger the slave, it comes up fine and doing a reindex of the slave database results in no errors.  However, when I do this *after* our nightly backup routine (which *fast* stops pg to do a full file system backup then starts it) a reindex on the salve results in errors similar to:

ERROR:  could not create unique index "xyz_pkey"

DETAIL:  Table contains duplicated values.

 

********** Error **********

 

ERROR: could not create unique index "xyz_pkey"

SQL state: 23505

Detail: Table contains duplicated values.

 

Where xyz is the table name (real name removed) and it's not always on the same table.

Note that I accidentally discovered this have attempting a reindex on the triggered slave database.

-Bob

Logs:

head of postgresql...log on slave:

-----------------------------------------------

2011-09-27 02:24:54 MST LOG:  could not open file "pg_xlog/000000010000059F000000C7" (log file 1439, segment 199): No such file or directory

2011-09-27 02:24:54 MST LOG:  redo done at 59F/C6FFDB08

2011-09-27 02:24:54 MST LOG:  last completed transaction was at log time 2011-09-26 23:55:29.746-07

2011-09-27 02:24:59 MST LOG:  restored log file "000000010000059F000000C6" from archive

2011-09-27 02:25:29 MST LOG:  selected new timeline ID: 2

2011-09-27 02:25:59 MST LOG:  archive recovery complete

2011-09-27 02:26:03 MST LOG:  database system is ready to accept connections

2011-09-27 02:26:03 MST LOG:  autovacuum launcher started

tail of STANDBY.LOG on slave after trigger:

----------------------------------------------

WAL file not present yet. Checking for trigger file...

WAL file not present yet. Checking for trigger file...

WAL file not present yet. Checking for trigger file...trigger file found

Trigger file : P:\PROGRA~1\PostgreSQL\trigger.txt

Waiting for WAL file : 000000010000059F000000C6

WAL file path : P:\PROGRA~1\PostgreSQL\archivewal\000000010000059F000000C6

Restoring to... : pg_xlog\RECOVERYXLOG

Sleep interval : 5 seconds

Max wait interval : 0 forever

Command for restore : copy "P:\PROGRA~1\PostgreSQL\archivewal\000000010000059F000000C6" "pg_xlog\RECOVERYXLOG"

Keep archive history : 000000000000000000000000 and later

running restore : OK

Trigger file : P:\PROGRA~1\PostgreSQL\trigger.txt

Waiting for WAL file : 00000002.history

WAL file path : P:\PROGRA~1\PostgreSQL\archivewal\00000002.history

Restoring to... : pg_xlog\RECOVERYHISTORY

Sleep interval : 5 seconds

Max wait interval : 0 forever

Command for restore : copy "P:\PROGRA~1\PostgreSQL\archivewal\00000002.history" "pg_xlog\RECOVERYHISTORY"

Keep archive history : 000000000000000000000000 and later

running restore :not restored : history file not found

Trigger file : P:\PROGRA~1\PostgreSQL\trigger.txt

Waiting for WAL file : 00000001.history

WAL file path : P:\PROGRA~1\PostgreSQL\archivewal\00000001.history

Restoring to... : pg_xlog\RECOVERYHISTORY

Sleep interval : 5 seconds

Max wait interval : 0 forever

Command for restore : copy "P:\PROGRA~1\PostgreSQL\archivewal\00000001.history" "pg_xlog\RECOVERYHISTORY"

Keep archive history : 000000000000000000000000 and later

running restore :not restored : history file not found

Tail of postgresql on master before/during shutdown for backups:

---------------------------------------------------------------------------------------------

2011-10-04 01:17:02 MST LOG:  unexpected EOF on client connection

2011-10-04 01:17:11 MST LOG:  received fast shutdown request

2011-10-04 01:17:11 MST LOG:  aborting any active transactions

2011-10-04 01:17:11 MST FATAL:  terminating connection due to administrator command

2011-10-04 01:17:11 MST FATAL:  terminating connection due to administrator command

2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator command

2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator command

2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator command

2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator command

2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator command

2011-10-04 01:17:12 MST LOG:  autovacuum launcher shutting down

2011-10-04 01:17:12 MST LOG:  shutting down

2011-10-04 01:17:12 MST LOG:  database system is shut down

Head of postgresql on master starting up after backups:

-----------------------------------------------------------------------------------

2011-10-04 02:19:37 MST LOG:  database system was shut down at 2011-10-04 01:17:12 MST

2011-10-04 02:19:37 MST LOG:  database system is ready to accept connections

2011-10-04 02:19:37 MST LOG:  autovacuum launcher started

2011-10-04 02:19:37 MST LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"