Re: Stuck (again) trying to move a 7.x app and database to 8.2 - Mailing list pgsql-novice

Howard Eglowstein wrote:
> Hi all. I asked this question back on January and got a lot of helpful
> advice, but I never did get the thing up and running. Now that I have
> time again, I need to get an application and database running on a
> Debian machine with PSQL 7.x running on a Fedora machine running 8.2.
>
> Here's what I've done:
>
> 1) I installed 8.2 on the new machine. The installation doesn't complain
> about anything, and 'initdb' says it inited stuff.
>
> 2) The old database makes daily backups with a command like 'pg_dump -U
> backup -Ft -b data > 20070615.tar'
>
> 3) I copied the 20070615.tar file over to the new machine and tried
> restoring it with 'pg_restore 20070615.tar'
>
> I will admit I don't know SQL enough to know how to view the data in the
> interactive terminal, but my app knew how to extract data and it says
> there's no data in the database.
>
> Did I miss some obvious, critical step? I would appreciate any
> suggestions you may have, or better yet, if there's one of you out there
> that would like to consult for a few hours, I would gladly pay for your
> time (in the Boston, MA area) to fix this stupid thing.
>
> Thank you in advance for your help.
>
> Howard
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

FYI - For what it's worth, as part of our app upgrade we go from
v7.2->v8.1 on RH Linux.

The C installer calls out to a shell and installs v81 into a separate
directory. The import routine then starts v72 on the standard port and
v81 on 6543.

I found that I had to exclude the "CREATE ROLE postgres" command from
the v72 dump to get a clean install. We use the STOP_ON_ERROR to catch
any import errors. The two versions are then stopped, and v81 is brought
up using port 5432 (standard port). We do not use standard/stock RH rpm
installs anymore to allow us to install the binaries in our own app
directory.


import_72_into_81 ()
{
    start_72_database
    start_81_database

    echo ""
    echo "Importing 7.2 data into v8.1 database..."

    ECHO='--echo-all'

    #
    # create a .psqlrc file so that on psql startup the timing
    # command is executed
    # to get times of how long things are taking
    #

    #echo '\timing' >> ~/.psqlrc

    PSQL="$DB_INSTALL_DIR/bin/psql"
    PG_DUMPALL="$DB_INSTALL_DIR/bin/pg_dumpall"

    #
    # note: that the postgres role alredy exist in the v8.1 database     #
cluster
    # created by the initdb, so exclude it from the migration
    #

    $PG_DUMPALL --verbose -U postgres |grep -v 'CREATE ROLE postgres;' |
$PSQL -U postgres -d postgres -p 6543 -v "ON_ERROR_STOP=1" $ECHO
    if [ $? -ne 0 ]; then
        echo ""
        echo "ERROR: import failed."
        echo ""
        exit 1
    fi

    stop_72_database
    stop_81_database
}