As far as I can tell, postgresql was entirely wiped.
Did the old database end up somewhere? I tried to see if there was a log from the RPM install, but I can’t find anything.
I only use it for Quassel, but it’d be nice to have it back. I knew when I saw that openSUSE had upgraded PostgreSQL in the release press that it would be a pain. In past upgrades postgresql would just complain and not start becaues it refused to use the old database and you had to do a complicated procedure where you installed both versions of postgresql at the same time (openSUSE does not make that easy) and do an export/import. But this is worse!
Basically, if you upgrade a postgresql db from 9.1.1 to 9.1.5, that’s OK because you are just replacing a postgresql executables and restarting the service.
The internal data structure is unchanged and it’s still going to be running in 9.1 but in a version with backwards compatibility (your existing DBs will work fine without having to dump/restore).
Going from 9.1.1 to 9.2.0 is another story. Consider that a major update and the internal storage structure of the data may change, which is why the old DB may not be recognized or be able to used by the upgraded postgresql server.
Well it was from 9.1 to 9.2. I can understand the structure changing and not working anymore (like I said, that’s what happened in prior upgrades), my question is what happened to the old database?
I looked at the init script and see:
if ! -f $DATADIR/PG_VERSION ]; then
echo -n "Initializing PostgreSQL $VERSION at location ${DATADIR}"
LANG_SYSCONFIG=/etc/sysconfig/language
test -f "$LANG_SYSCONFIG" && . $LANG_SYSCONFIG
LANG=${POSTGRES_LANG:-$RC_LANG}
INITDB=/usr/bin/initdb
V=$(printf "%02d%02d" $(echo $VERSION|awk -F. '{print $1, $2}'))
AUTH="ident"; test $V -lt 0900 && AUTH="ident sameuser"
install -d -o postgres -g postgres -m 700 ${DATADIR} &&
su - postgres -c \
"$INITDB --locale=$LANG --auth=\"$AUTH\" $DATADIR &> initlog" ||
rc_failed
rc_status -v
rc_status || {
echo "You can find a log of the initialisation in ~postgres/initlog ."
rc_exit
}
I can tell from the date stamps and the initlog that for sure initdb was done during the openSUSE upgrade a couple days ago. I played with initdb myself a bit, and it refuses to do anything with a non-empty directory. So it is not the guilty party.
The question is:
did openSUSE delete or move the old database? If the latter where to? Is there logs of what happened during RPM install somewhere?
or was I using a different location entirely for the postgresql db? Can’t imagine why… but it doesn’t appear it was there.
My /var/lib/pgsql was a symlink to my magnetic drive (since root is a SSD drive). This symlink was deleted by the RPM and then it sets up a new database in its place. My confusion ensues.
Thanks for letting me figure this out on the forum.
Overall the process for postgresql upgrades is much smoother (outside of my weird problem due to the symlink I made). Someone obviously spent some time making it possible to install multiple versions of postgresql at the same time (which is required for upgrades).
…but it’s all for not without actually offering the packages!
In general I guess needing to have multiple versions of a package at the same time sort of breaks the whole metaphor of how RPM updates are supposed to work.
> In general I guess needing to have multiple versions of a package at
> the same time sort of breaks the whole metaphor of how RPM updates are
> supposed to work.
postgresql should include a program to convert databases from any
previous version to the current. Having to install two versions of the
package is absurd.
–
Cheers / Saludos,
Carlos E. R.
(from 11.4, with Evergreen, x86_64 “Celadon” (Minas Tirith))
Glad you got it figured out eean and even got your own home repo with the needed postgresql!
Do you know if -* instead of symlinking *- mounting that pgsql dir to /usr/lib/ with --bind would help with the symlink disappearance issue?
I’ve been using bind as the mount type for my pgsql installations and upgrades haven’t been causing troubles.
It’s actually very useful and you don’t have to install both, it’s just one of the manypostgresql db upgrade options =)
In a DBMS, sometimes you can’t afford too much downtime.
One reason for having two versions of postgresql running lets you leave the old instance up (and in production) until the new one is ready to go and fully tested.
One option - if you don’t want two versions running - is to remember to dump the contents of the old db (direct to disk doesn’t work) with pg_dump or pg_dumpall then restore to the new db with psql -d <database> -f <pg_dumpfile> where -f points to the pg_dump(all) output file.
There is also the pg_upgrade utility but it’s use is limited, especially if there have been major changes in the data storage format (rare), and not just the system tables.
There are lots of ways to upgrade postgresql (some take minutes) but it really depends on your environment, use-case and competency level
When starting postgresql, I got the following errors after typing /etc/init.d/postgresql status
Your database files were created by PostgreSQL version 9.1.
Could not find executables for this version.
Please install the PostgreSQL server package for version 9.1.
postgresql.service - LSB: Start the PostgreSQL master daemon
Loaded: loaded (/etc/init.d/postgresql)
Active: active (exited) since Tue, 2013-07-23 16:26:13 CEST; 10s ago
Process: 5981 ExecStop=/etc/init.d/postgresql stop (code=exited, status=0/SUCCESS)
Process: 5997 ExecStart=/etc/init.d/postgresql start (code=exited, status=5)
CGroup: name=systemd:/system/postgresql.service
Jul 23 16:26:13 virtserver.pensionarchitects.be systemd[1]: Starting LSB: Start the PostgreSQL master daemon...
Jul 23 16:26:13 virtserver.pensionarchitects.be postgresql[5997]: Your database files were created by PostgreSQL version 9.1.
Jul 23 16:26:13 virtserver.pensionarchitects.be postgresql[5997]: Could not find executables for this version.
Jul 23 16:26:13 virtserver.pensionarchitects.be postgresql[5997]: Please install the PostgreSQL server package for version 9.1.
Jul 23 16:26:13 virtserver.pensionarchitects.be systemd[1]: Started LSB: Start the PostgreSQL master daemon.
I figured out that I have to upgrade my database files to version 9.2 (I’m running opensuse 12.3). I wanted to use pg_dumpall > db.out, but this is not working because postgresql is not running. And postgresql is not running because I have version 9.1 databases. I’m running in circles.
BTW I’m not a postgresql expert, and we are using it for 1 application.
