Postgres Pro
Downloads
Home   >   mailing lists

Replication/WAL shipping. db errors on slave after stop/start master - Mailing list pgsql-general

From Bob Hatfield
Subject Replication/WAL shipping. db errors on slave after stop/start master
Date
Msg-id CAKikJcJdX==h3kWZMMG3nMaFfWKBLfhRCizQniERYKfNMTruPA@mail.gmail.com
Whole thread Raw
List pgsql-general
Tree view
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"


pgsql-general by date:

Previous
From: Daniele Varrazzo
Date:
Subject: Re: How to find freak UTF-8 character?
Next
From: Alan Hodgson
Date:
Subject: Re: : PostgreSQL Online Backup