> Update - I have two hot replication slaves of this db, both have the problem. > I took one out of recovery and ran REINDEX table session_session and it > fixed the errors about this row. Now Im going to run vacuum and see if > there are other tables that complain, but Im guessing if so I will need to see > if there is a way to force vacuum to continue on error, worst case I might > have to script a table by table vacuum script I guess.. If anyone has a > better > suggestion for determining the extent of the damage Id appreciate it.
Oh man. I'm sorry, Mike. One of the cardinal rules I have is to disconnect any replication following a database crash. It's just too easy for damaged replicated rows to be propagated unless you're on 9.3 and have checksums enabled. If you want to perform a table-by-table check, don't vacuum the database, but the individual tables. I'd go with a DO loop and have it raise notices into the log so you can investigate further: COPY ( SELECT 'VACUUM ' || oid::regclass::text || ';' FROM pg_class WHERE relkind = 'r' ) to '/tmp/vac_all.sql'; Run the /tmp/vac_all.sql through psql and pipe the contents into a log file. Any table that doesn't vacuum successfully will need to be repaired manually. One way you can do this if there are dupes, is by checking the ctid value after disabling index scans: SET enable_indexscan TO False; SELECT ctid, * FROM [broken_table] WHERE ...; Just construct the WHERE clause based on the error output, and you should get all rows if there are dupes. You'll need to figure out which row to keep, then delete the bad row based on the ctid. Do this as many times as it takes, then reindex to make sure the proper row versions are indexed. It's also a good idea to dump any table that came back with an error, just in case. After you've done all of that, you should re-base your replicas once you've determined your production system is usable. In the meantime, I highly recommend you set up a VIP you can assign to one of your replicas if your production system dies again, and remove any autostart code. If your production system crashes, switch the VIP immediately to a replica, and invalidate your old production system. Data corruption is insidious when streaming replication is involved. Look into tools like repmgr to handle managing your replicas as a cluster to make forced invalidation and re-basing easier. Good luck! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
