Skip site navigation (1) Skip section navigation (2)

Re: Stuck (again) trying to move a 7.x app and database to 8.2

From: Eric Comeau <ecomeau(at)signiant(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Stuck (again) trying to move a 7.x app and database to 8.2
Date: 2007-06-19 11:27:57
Message-ID: f58ejo$15fh$1@news.hub.org (view raw or flat)
Thread:
Lists: 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
}



In response to

pgsql-novice by date

Next:From: Carol WalterDate: 2007-06-19 12:39:41
Subject: Re: [pgsql-advocacy] [PERFORM] Postgres VS Oracle
Previous:From: Eric ComeauDate: 2007-06-19 10:58:45
Subject: Re: Stuck (again) trying to move a 7.x app and database to 8.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group