TurnKey Linux Virtual Appliance Library

LAPP Postgres Version

Jason Adams's picture

I can't seem to find the documented convent version of PostgreSQL in the LAPP. Poking around, it seems to be 8.x. Is there any intent to move to 9.x?

I wanted to connect to the DB using the PgAdmin III application, as it has some nice tools. But I can't seem to connect for what appears versioning differences. Has anyone successfully connected to LAPP using pgAdmin III?

Otherwise is there a means of upgrading to 9.x?

The default means of connecting via TCP is through port 22, correct? I can't connect even by my own programming, as I get "Expected Authentication request from server, but received S" — which is the same error I get from pgAdmin III. :(

Thanks! :)

Jeremy's picture

I guess you're unfamilar with the way generally Linux works

In the world of Linux stability and security are generally favoured over the latest version of software. When a new version of Ubuntu (the basis of TKL) is released generally the software version does not change for the life of the OS. Security patches and major bugfixes are backported to the version that is included with the software. In TKL these security fixes are auto-installed as they become available.

And yes you are correct re version. If you have a look at the changelog (linked to from the appliance page) you can see that it includes version 8.4.5. I know nothing of pgAdmin III but according to this it should work fine with the version included (although only the options relevant to v8.4.5 will work).

As for the TKL LAPP appliance, I have no experience with it, but I imagine that the PostgreSQL component is bound to localhost and as such will require a little config to get it to listen for external connections. TBH I'm not sure exactly what that will involve (I have no experience with PostgreSQL at all). AFAIK it is not documented here anywhere (MySQL is, but that won't be much use to you I don't think - although the theory may be similar). You may find the PostgreSQL secion of the Ubuntu Server Guide a useful place to start (actually probably not - I just had a look and you're probabaly better off looking on the PostgreSQL website or at the generic Ubuntu PostrgrelSQL page).

Port 22 is for SSH (or SFTP) connection. You won't get anything else to connect via that port. I imagine that you can define the port you use to connect to PostgrelSQL with, but unless you have a good reason I'd just stick with the default (which is apparently TCP port 5432). The appliance should have PHPPgAdmin installed already and I'd guess that'd be available via port 12322 (ie http://<appliandefaultce-ip>:12322).

The next major version of TKL (v12.x) will be based on Ubuntu 12.04/Precise and thus will include v9.1 as the default version (see here). You could install a newer version if you wanted (this is Linux after all) but I strongly recommend against it.

Good luck.

Jason Adams's picture

Nope, I'm not

 

Hello Jeremy,

You're quite right. I'm a developer, but I've very little experience with the Linux OS. The philosophy, as it seems, is lost on me as well. But I have a need for a server, and Linux seemed the best route to go and it seemed a good time to get my feet wet.

I'm debating between MySQL and PostgreSQL. I like the power of Postgres, but MySQL, from what it seems, is considerably easier to install and maintain. More than that, it appears more widely supported. I may try installing the MySQL turnkey as well, and see if it's easier to work with. I'm open to a rhetoric as to why I should use MySQL.

Otherwise I've opened the 5432 port on the Linux Firewall, and added my static address to the Postgres Allowed Host list, but that's about it. I've ran Postgres before, but I always configured the files myself, so I feel more like I'm getting confused with the Webmin interface and Linux methodology. Does it sound like I'm missing something? or going in a wrong direction?

Thanks again. It's always appreciated.

Jeremy's picture

I only know MySQL because it's so widely supported

But since Oracle bought out Sun (who owned MySQL) there have been concerns about the future direction and whether MySQL will become crippleware in the future (ie the core of it will remain but access to all future development advances will require purchase). Oracle is not known as a good open source citizen and especially when databases are their core buisness many have openly worried about the future of MySQL under their leadership. The way development of OpenOffice became quite muddied is an example of what can happen to an otherwise thriving project when a change of management occurs. Anyway I digress...

As I say I don't know a lot about SQL in general, just a bit of MySQL because it is so commonly used as a DB backend for a myriad of apps. PostgreSQL on the other hand is a much less commercial DB (by my understanding anyway) and so the full features are available to all. I have read that it i a much more powerful DB but I guess that as MySQL is so prevelant many choose to use it because they don't need the power and it's easy. So at the end of the day it comes down to what will work best for you. If you are seriously looking at transitioning to TKL then another factor may be the current lack of TKLBAM support for PostgreSQL. I have little doubt that that situation will be rectified at some point in the near future but when is anybody's guess.

You shouldn't need to muck around with firewall ports and I just checked the documentation I alluded to in my previous post and it actually does contain instructions on PostgreSQL so you're in luck! Have a look at docs : Help (top of screen) >> Documentation >> Tutorials/How tos >> connecting to remote database (or something like that - sorry no direct link, I'm using Firefox on an android device and copy/paste doesn't work how I want)

Hello,   I just installed

Hello,

 

I just installed the LAPP appliance, and it works great.  The only problem I have, is the development environment we have been working with for Postgre is on Version 9.1 and trying to restore a backup to the new LAPP install will not work - due to incompatibility between pgAdminIII working with the 9.1 version and the Webmin restore tool.  The exact failure is

Failed to restore database : Restore failed :

pg_restore: [archiver] unsupported version (1.12) in file header

 

So, it appears that I have to upgrade the version of Postgre from the version supplied in the LAPP stack.  Can either of you suggest a painless method for achieving this?  I am afraid of asking Linux to do an update/upgrade since I have seen before where it simply adds another version, and leaves the existing installed. And am concerned that the setup that just "works" as provided by TK, will no longer work.

Thoughts anyone?

Thank you
Tim


Jeremy's picture

Possible - yes; painless - perhaps...

You will not get an updated version of PostgreSQL using apt unless you do some tweaking. The standard repos only contains the same version so no matter how you use it, it will not update to a newer version (but there may be backported bug fixes that will install - backported security updates are installed automatically).

So that leaves you with a couple of options - which may or may not break your system and/or the way that TKL "just works". [update prior to posting this: After a little research it looks like the middle PPA option may be your best but for completeness I have included the other info].   

  1. Probably the preferred option would be to build the version that you want from source. The upside of this method is that the likelihood of it interfereing with the current system is lower. However by default it will leave the current setup as it is and unless you wish to keep both versions side-by-side you will probably want to remove the currently installed version anyway. This will involve some trial and error modification of settings etc to get it to all "just work" again. Another serious downfall of this setup is that all security fixes will need to be manually applied (ie need to rebuild from source each time) and in my (very limited experience) may require you hunting for the fixes yourself to patch your existing install, or rebuild from source each time. If your install is not internet exposed, this would be less of an issue as you have limited chances of attack.
  2. PPA - A quick hunt using google reveals that this may be your best option in this instance (ie for PostgreSQL). Ubuntu PPAs are Private Package Archives (provided by other people, not official Ubuntu packages) and can often contain backported versions of software (ie newer versions of software for older versions of Ubuntu). Sometimes you can strike it lucky and the developers of software have an 'official' PPA with backports of the desired software but usually these are by random 3rd party users, often for their own purposes. My experience has been gernerally good, but you still really need to trust the source. You also have no control over if/when security patches are applied. This method is probably the easiest but unless it is an 'official' PPA then security and reliability may be issues - ones which you will have no control over. As it turns out an Ubuntu developer, Martin Pitt provides a PostgreSQL PPA which includes a backported version of PostgreSQL 9.1 (for Ubuntu 10.04/Lucid - the version TKL v11.x is based on). I can't vouch for him/it but to my eye it looks legitimate and reliable. Although I don't know what level of support he provides for security patches/upgrades. If you wish to auto apply updates from this PPA then you'll need to add it to the security sources.list. Otherwise you'll need to manually update on occasion (assuming he provides updated versions with security patches included). If you need a hand and/or further details on this ask away.
  3. Using packages from a newer Ubuntu version. This method in its rawest form is theoretically also quite easy to implement. But in practice may open a whole can of worms. This is something that must be done with extreme caution as it has the potential to completely destroy your system. Having said that, if PostgreSQL has minimal dependancies then you may be able to get away with it and if it were to work ok, may actually be a great way to set this up. With a little tweaking (ie pinning) you may even be able to get security updates automatically installing by leveraging the existing TKL auto security updates.

Bottomline, you may have to tweak some stuff to get it all working as you'd like and you will probably want to remove the default version of PostgreSQL that is included with TKL. So good luck with that and it'd be useful to hear how you go as others may be interested in acheiving the same ends.

Thank Jeremy!  That was quite

Thank Jeremy!  That was quite a response. I appreciate all the time you took to do that. I will tread lightly on this matter!

Thanks

Tim


Jeremy's picture

No worries

Let me know if you need a hand at all as I'm happy to do what I can to help. I certainly won't have all the answers but I'll do what I can :)

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account, used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr> <br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong> <font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <strike> <caption>

More information about formatting options

Leave this field empty. It's part of a security mechanism.
(Dear spammers: moderators are notified of all new posts. Spam is deleted immediately)