Thread: Stuck (again) trying to move a 7.x app and database to 8.2
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
Howard Eglowstein <howard@yankeescientific.com> writes:
> 3) I copied the 20070615.tar file over to the new machine and tried
> restoring it with 'pg_restore 20070615.tar'
With no other arguments, that's just going to spew a bunch of SQL on
your screen. You could pipe its output to psql, but it'd probably make
more sense to add the switches to tell it to connect to your new
database and load the data there.
regards, tom lane
On 06/15/2007 08:07 PM, 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' You're going to run into problems if you're trying to import a dump dumped with a 7.4 pg_dump, into a v8.2 database. Try using the pg_dump on your FC machine, to dump the database from your Debian machine. I've written up a little list of what I did when I upgraded my stock Debian 7.4 postgreSQL, to the latest 8.2 version a while ago, at http://www.gildseth.com/pg74-82-migration.html Maybe that can be of some help? -- Tommy
Interesting. I did already discover (and had confirmed) that the pg_dump backups coming from the 7.x machine were useless without the globals. I went into the office where the 7.x machine is today and did full backups with pg_dumpall. Hopefully this will help. I also scrapped the 8.2 on the new box and installed 7.3 - the oldest version I could find prebuilt for FC6. Now that I have proper backups, maybe these will work. Sadly, I can't easily connect from one machine to another. The only connection possible would be through my 768K DSL and that would take days to transfer anything. Thank you for the link. I will click on it immediately if not sooner! Howard Tommy Gildseth wrote: > On 06/15/2007 08:07 PM, 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' > > > You're going to run into problems if you're trying to import a dump > dumped with a 7.4 pg_dump, into a v8.2 database. Try using the > pg_dump on your FC machine, to dump the database from your Debian > machine. > > > I've written up a little list of what I did when I upgraded my stock > Debian 7.4 postgreSQL, to the latest 8.2 version a while ago, at > http://www.gildseth.com/pg74-82-migration.html > Maybe that can be of some help? >
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
}
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
}