Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Did 12.3/Postgresql 9.2 upgrade wipe my database?

  1. #1
    Join Date
    Nov 2009
    Posts
    21

    Exclamation Did 12.3/Postgresql 9.2 upgrade wipe my database?

    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!

  2. #2
    Join Date
    Jan 2012
    Location
    BC
    Posts
    178

    Default Re: Did 12.3/Postgresql 9.2 upgrade wipe my database?

    Depends on how much of an upgrade you did....

    See: PostgreSQL: Documentation: 9.1: Upgrading a PostgreSQL Cluster

    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.

  3. #3
    Join Date
    Nov 2009
    Posts
    21

    Default Re: Did 12.3/Postgresql 9.2 upgrade wipe my database?

    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:
    Code:
        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.

  4. #4
    Join Date
    Nov 2009
    Posts
    21

    Default Re: Did 12.3/Postgresql 9.2 upgrade wipe my database?

    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.

  5. #5
    Join Date
    Nov 2009
    Posts
    21

    Default Re: Did 12.3/Postgresql 9.2 upgrade wipe my database?

    So related question: how do I install postgresql 9.1 on opensuse 12.3? :S

  6. #6
    Join Date
    Jan 2012
    Location
    BC
    Posts
    178

    Default Re: Did 12.3/Postgresql 9.2 upgrade wipe my database?

    Quote Originally Posted by eean View Post
    So related question: how do I install postgresql 9.1 on opensuse 12.3? :S
    You will probably have to install via third party installers.

    Two that are listed on the postgresql site are Postgres RPM & DEB Linux Packages and Download PostgreSQL | EnterpriseDB

    By specifying a different data_dir and listening port, you can have two versions of pgsql running at the same time.

  7. #7
    Join Date
    Nov 2009
    Posts
    21

    Default Re: Did 12.3/Postgresql 9.2 upgrade wipe my database?

    I made a repo for it here: http://download.opensuse.org/reposit...ostgresql.repo

    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.

  8. #8
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: Did 12.3/Postgresql 9.2 upgrade wipe my database?

    On 2013-03-16 01:06, eean wrote:

    > 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))

  9. #9
    Join Date
    Jan 2012
    Location
    BC
    Posts
    178

    Default Re: Did 12.3/Postgresql 9.2 upgrade wipe my database?

    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.

    Quote Originally Posted by robin_listas View Post
    Having to install two versions of the package is absurd.
    It's actually very useful and you don't have to install both, it's just one of the many postgresql 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

  10. #10

    Default Re: Did 12.3/Postgresql 9.2 upgrade wipe my database?

    I'm having the same problem.

    When starting postgresql, I got the following errors after typing /etc/init.d/postgresql status
    Code:
     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.

    Regards

    Ivan

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •