PostgreSQL 7.x

Help
jester
2006-10-24
2013-04-09
  • jester
    jester
    2006-10-24

    Is there any saying in what will not work when using PostgreSQL 7.4 with LedgerSMB, will there be big trouble?! I'm using SME Server (contribs.org / smeserver.org) that's based on CentOS 4.3, a great server distro (worth looking at btw). But because it is based on CentOS 4 (RHEL4) it comes with PostgreSQL 7.4 and i'm to much of a Linux noob (that's why SME Server is so interesting) to upgrade this, so i'm stuck.

    I really need LedgerSMB for my small company, if i use ver.1.0.0 will i be able to use it without problems with PostgreSQL 7.4 ?!

    Regards,
    ArtixDesign.

     
    • Chris Travers
      Chris Travers
      2006-10-24

      1.0 will work with LedgerSMB 1.0.0.  1.1 was the release that abandoned PostgreSQL 7.4 support.

      Please note that PostgreSQL 7.4 will likely be unsupported in terms of security and data integrity fixes in another year or so, thus you might want to consider looking for help upgrading.  Note that upgrading PostgreSQL is not extremely easy at the moment though it is getting better.  If you would like help, I would suggest contacting us on IRC (freenode, channel #ledgersmb) or getting on the email lists.

      Best Wishes,
      Chris Travers

       
    • I run CentOS 4.3 on my development box and built Postgres (and PHP) rpms for it based on Fedora Core 5. I'm currently running 8.1.4 without any problems. If you would like, I could send you the RPMs or some simple instructions on how to build your own. Postgres 8.1 is definitely worth the upgrade.

      Cheers,

      Chris

       
    • jester
      jester
      2006-11-09

      Hi Chris,

      Thanx for the offer, but i've got more stuff on that box that needs the current PHP version. I don't wanna break it or the Contribs.org templating system, i'm to much of a linux noob to correct it.

      Kind Regards.

       
    • Chris Travers
      Chris Travers
      2006-11-10

      Hi Jester;

      Also, upgrading to 8.x can be a little bit difficult under certain circumstances.  But we are more than willing to walk you through the process and be there to help you if things go wrong.  You don't need to upgrade PHP.  Just PostgreSQL.

      Especially of you are a newbie, I would suggest planning to upgrade to 8.2 as soon as it comes out.  The main benefit of upgrading to 8.1 or 8.2 if you are not used to PostgreSQL is that these releases integrate Autovacuum into the backend.  Let me explain why this is important:

      Old-style databases (MS SQL 6.5, MySQL w/My-ISAM, etc) stored data in tables that were structured sort of like big spreadsheets.  A little more complicated than that but you get the idea.  Handling concurrent updates to the table meant that the entire table or a portion of it had to be locked.  ("Portions" range from individual rows to blocks known as "pages" depending on the implementation.)  Obviously this limits the number of applications that can write to the database at once.

      PostgreSQL solves this problem by storing a special value in the row called the transaction id.  It is easy to handle concurrency because different applications will write different transaction id's to their rows.  When you need a view of the data, you can use that value to make sure that you see the state of the data at a specific point in time (when you start the query or when you start the transaction, depending on your settings).  To make this work, old data is not deleted when the delete or update operation occurs.

      This causes two problems.  The first is that performance will slowly go down as you continue to write to the database unless you remove the old rows through a database command called "vacuum."  When this happens, old rows are actually deleted and that space can be used to store new data.  Unvacuumed databases can become pretty slow over time.

      The second issue is far more serious.  The snapshot capability involves comparing transaction id's.  Higher transaction id's are considered to be in the future and are generally ignored when searching.  If you fail to vacuum, after a few billion transactions, the transaction id counter will roll over (and become 0) and all operations since the last vacuum will sudenly be invisible!  This means that data is suddenly inaccessible.  Vacuuming not only removes old rows, but current visible rows of committed transactions are set to a special transaction id that makes them always visible until superceded.

      By integrating autovacuum into the PostgreSQL back-end, it means that you have far less to worry about.  Performance management is much more hands-off, and although you probably won't hit transaction id wraparound either way, the system is much better at avoiding the problem (and will now refuse to accept any commands other than vacuum if you get too close).

      Finally, the backup programs are a lot more reliable in PostgreSQL 8.x.

      If you want help migrating, you can contact me through the Sourceforge interface, or find me on IRC (Freenode channel #ledgersmb) in the evenings.

      Best Wishes,
      Chris Travers

       
    • jester
      jester
      2006-11-14

      I might take you up on this migrating help Chris! I really like to stay up to date and safe with LedgerSMB. I've first got to find me a IRC client and a test server, but that won't take to long.

      I'll be back... (in a day or two)   ;)

      Kind regards,
      jester.

       
      • Chris Travers
        Chris Travers
        2006-11-14

        Also, the people on the PostgreSQL email lists are extremely helpful (if you don't mind the volume of posts) and extremely knowledgeable.  Just mentioning all resources.

         
    • llahuntsic
      llahuntsic
      2007-05-27

      I'm in the same situation as Jester and I need to stick with version 7.x for some time.
      Do I need version 8 to install LedgerSMB 1.2.5 ?
      I have troubles with some sql statements and I wonder if this is a compatbility issue.

       
      • Yes, we have dropped support for PostgreSQL 7.x, and we strongly
        recommend at least 8.1 as we expect to drop 8.0 sometime shortly too.

        Cheers,

        Chris