agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: jim_yates <pg@wg5jim.net>
To: pgsql-sql@postgresql.org
Subject: Re: could not access status of transaction pg_multixact issue
Date: Wed, 08 Oct 2014 11:40:55 -0700
Message-ID: <54358537.4040906@aklaver.com> (raw)
In-Reply-To: <1412791213607-5822248.post@n5.nabble.com>
References: <1412791213607-5822248.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 10/08/2014 11:00 AM, jim_yates wrote:
> I have this issue for 1 table. Version is 9.3.5 upgraded from 9.2 with
> pg_upgrade a few months ago.
>
> This issues just started in the last couple of days.
>
> acustream=# SELECT count(*) from phyorg_charges_to_invoice;
> ERROR: could not access status of transaction 267035
> DETAIL: Could not open file "pg_multixact/members/10AD6": No such file or
> directory.
>
> This error happens when I try and select or vacuum the table. Inserts still
> work. I have a hot standby database and I can recover the data from there.
> Is there any work around for this?
>
http://www.postgresql.org/docs/9.3/interactive/release-9-3-5.html
E.1.2. Changes
In pg_upgrade, remove pg_multixact files left behind by initdb
(Bruce Momjian)
If you used a pre-9.3.5 version of pg_upgrade to upgrade a database
cluster to 9.3, it might have left behind a file
$PGDATA/pg_multixact/offsets/0000 that should not be there and will
eventually cause problems in VACUUM. However, in common cases this file
is actually valid and must not be removed. To determine whether your
installation has this problem, run this query as superuser, in any
database of the cluster:
WITH list(file) AS (SELECT * FROM pg_ls_dir('pg_multixact/offsets'))
SELECT EXISTS (SELECT * FROM list WHERE file = '0000') AND
NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
NOT EXISTS (SELECT * FROM list WHERE file = 'FFFF') AND
EXISTS (SELECT * FROM list WHERE file != '0000')
AS file_0000_removal_required;
If this query returns t, manually remove the file
$PGDATA/pg_multixact/offsets/0000. Do nothing if the query returns f.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (16+ messages) latest in thread
Message-ID: <54358537.4040906@aklaver.com>
Permalink: ../54358537.4040906@aklaver.com/
Also on: postgresql.org/message-id/54358537.4040906@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, pg@wg5jim.net
Subject: Re: could not access status of transaction pg_multixact issue
In-Reply-To: <54358537.4040906@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox