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@alvh.no-ip.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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