Re: 9.3beta2: Failure to pg_upgrade

On Tue, Jul 30, 2013 at 10:17:52AM -0400, Jesse Denardo wrote:
> Name: Jesse Denardo
> Release: 9.2.2 -> 9.3beta2
> Test Type: Install/Upgrade Test
> Test Detail: pg_upgrade in a fresh install of 9.3beta2
> Platform: Debian Linux 6.0.5
> Installation Method: From source
> Platform Detail: Debian Linux 6.0.5, 2.6.32.45-grsec-2.2.2-r3, x86_64
> Test Procedure:
>
> I made a byte for byte copy of our exsting 9.2.2 Postgres directory (which
> includes the data directory), changed the port, and started it up. I pointed

I assume you did this while the server was down.

> command: "/home/postgres/9.3beta2/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
> "/home/postgres/9.3beta2/data" -o "-p 50432 -b -c synchronous_commit=off -c
> fsync=off -c full_page_writes=off  -c listen_addresses='' -c
> unix_socket_permissions=0700 -c unix_socket_directories='/home/postgres'" start
> >> "pg_upgrade_server.log" 2>&1
> waiting for server to start....LOG:  database system was shut down at
> 2013-07-30 09:57:58 EDT
> FATAL:  could not access status of transaction 2983
> DETAIL:  Could not read from file "pg_multixact/offsets/0000" at offset 8192:
> Success.

OK, I actually have an idea on this. Here is the pg_upgrade code:

/*
* If the old server is before the MULTIXACT_FORMATCHANGE_CAT_VER change
* (see pg_upgrade.h) and the new server is after, then we don't copy
* pg_multixact files, but we need to reset pg_control so that the new
* server doesn't attempt to read multis older than the cutoff value.
*/
if (old_cluster.controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER &&
new_cluster.controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER)
{
copy_subdir_files("pg_multixact/offsets");
copy_subdir_files("pg_multixact/members");
prep_status("Setting next multixact ID and offset for new cluster");

/*
* we preserve all files and contents, so we must preserve both "next"
* counters here and the oldest multi present on system.
*/
exec_prog(UTILITY_LOG_FILE, NULL, true,
"\"%s/pg_resetxlog\" -O %u -m %u,%u \"%s\"",
new_cluster.bindir,
old_cluster.controldata.chkpnt_nxtmxoff,
old_cluster.controldata.chkpnt_nxtmulti,
old_cluster.controldata.chkpnt_oldstMulti,
new_cluster.pgdata);
check_ok();
}

and the C comment is:

/*
* pg_multixact format changed in 9.3 commit 0ac5ad5134f2769ccbaefec73844f85,
* ("Improve concurrency of foreign key locking") which also updated catalog
* version to this value. pg_upgrade behavior depends on whether old and new
* server versions are both newer than this, or only the new one is.
*/
#define MULTIXACT_FORMATCHANGE_CAT_VER 201301231

So, first, this is new in 9.3, and second, it seems the comment "we need
to reset pg_control so that the new server doesn't attempt to read
multis older than the cutoff value" is not working. Alvaro, can you
comment on this? I think you added this code with this commit:

commit 0ac5ad5134f2769ccbaefec73844f8504c4d6182
Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Date: Wed Jan 23 12:04:59 2013 -0300

...

pg_upgrade also needs to be careful to copy pg_multixact files over from
the old server to the new, or at least part of multixact.c state,
depending on the versions of the old and new servers.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +