From: | Bob Hatfield <bobhatfield(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Replication/WAL shipping. db errors on slave after stop/start master |
Date: | 2011-10-04 17:27:35 |
Message-ID: | CAKikJcJdX==h3kWZMMG3nMaFfWKBLfhRCizQniERYKfNMTruPA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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"
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Hodgson | 2011-10-04 17:57:48 | Re: : PostgreSQL Online Backup |
Previous Message | Daniele Varrazzo | 2011-10-04 17:25:07 | Re: How to find freak UTF-8 character? |