[GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows
Hi Everyone I have a question re using postgres as a "embedded" database on Windows. First a little background: I have been using Postgres for a number of years on Linux, and it is great. I am now working with a company which develops Windows software using Delphi. This has been successful and they have a number of users (1000's). This currently uses a Paradox database. For various reasons (some I am sure you can guess!) we would like to move away from paradox, and Postgres is a good option. The main problem(s) I see with Postgres in this environment related to: - The fact that the users are very non computer savy - these are general office workers or Farmers - most will have little interest in the computer beyond getting a job done. - Installation (Yes there is a silent install) - running as a service What happens in the unlikely event that they already have a version of Postgres installed? - I guess I can see that we could create a installation which works for most users, but end up spending a lot of time getting the last few computers updated, as we deal with some obscure problem between windows and services and Postgres (I know welcome to Windows), and we would like to be able to manage these updates remotely I would like to hear feed back from others, has anyone else deployed Postgres in this type of environment? Are there options for running postgres, maybe as a process (maybe the wrong term) within the main application - this could be started and stopped by the application? I am thinking some type of options similar to SQLLite would be great for a single user install - but we also want to be able to A) use the full power of Postgres, B) set-up as a full server in some other cases. I look forward to hearing your views Thanks Craig
Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows
On 10/23/07, Craig Hawkes <[EMAIL PROTECTED]> wrote: > Hi Everyone I have a question re using postgres as a "embedded" database on > Windows. This question gets asked about once every three months. Searching the archives should turn up some previous discussions on the subject. > First a little background: > I have been using Postgres for a number of years on Linux, and it is great. > I am now working with a company which develops Windows software using > Delphi. This has been successful and they have a number of users (1000's). > This currently uses a Paradox database. For various reasons (some I am sure > you can guess!) we would like to move away from paradox, and Postgres is a > good option. > I would like to hear feed back from others, has anyone else deployed > Postgres in this type of environment? > Are there options for running postgres, maybe as a process (maybe the wrong > term) within the main application - this could be started and stopped by the > application? No, and it's not likely to happen. this would make postgresql subject to silent corruption if the application crashed, and no one wants to support that on top of the version we already have. > I am thinking some type of options similar to SQLLite would be great for a > single user install - but we also want to be able to A) use the full power > of Postgres, B) set-up as a full server in some other cases. Sadly, there ain't no such thing as a free lunch here. Either you install the real thing, and deal with the issues of administration and such, or you go with sqllite or it's brethren. I'd look into building an appliance computer for the customers. Something in a 1U rack mount with a pair of SATA or SAS drives mounted in a sw RAID-1 would do nicely for this kind of thing. Think of it as delivering the whole solution. Deliver it with a super simple custom gui on top to create accounts or phpmyadmin or something. The further away from a database on every desktop you get, generally the better off you'll be. If embedded fits this project better, then look elsewhere, it's really not pgsql's strong suit. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows
Craig Hawkes, 24.10.2007 05:14: - running as a service What happens in the unlikely event that they already have a version of Postgres installed? As far as I know you can happily install different versions in parallel. You just need to make sure the service name is unique (e.g. by using your application's name), that you initialize the PGDATA directory somewhere that is app specific and finally make sure you are not using the standard port for the communication. I don't think an already installed Postgres would be "harmed" then. Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows
Hi OK, Sorry but I did search, and did not find anything useful. Maybe putting forward a embedded solution as part of the question was the wrong option. If I could reword: Given that we have a large estiblished client base running a Delphi/Paradox solution, and that we would like to replace Paradox with a much better SQL engine, I was looking for comments as to how Postgres maybe suitable. We will be implementing more multi-user features - it really is only single user at this stage, plus we would like to be able to use better SQL - espically for Reporting - where currently we have hundreds of lines of Delphi code which could be replaced with a one or two reasonable SQL Queries. There will really be two targets, replacing the existing single user solutions, and providing a larger solution with multi-user etc. There is however large base of similar code between these solutions, and it would be perferable if they could use the same back-end engine or something very simliar. I know some people will aruge that you should use the right tool for the job, and I guess I am tring to see if there is a right tool which will work for both these. My main/only real hesiation with Postgres is around supporting non IT users, mainly around installation. I am keen to here peoples comments on this, and what steps could be taken to mitigate these. Currently the main alternate which is being considered is SQL Server, using SQL Express, SQL Mobile/Compact. But I see this as limiting use to only features supported by the Compact edition, or having to install SQL Express - which I can see as being worst than supporting Postgres Installs. Thank you for your comments Craig On 10/24/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > On 10/23/07, Craig Hawkes <[EMAIL PROTECTED]> wrote: > > Hi Everyone I have a question re using postgres as a "embedded" database > on > > Windows. > > This question gets asked about once every three months. Searching the > archives should turn up some previous discussions on the subject. > > > First a little background: > > I have been using Postgres for a number of years on Linux, and it is > great. > > I am now working with a company which develops Windows software using > > Delphi. This has been successful and they have a number of users > (1000's). > > This currently uses a Paradox database. For various reasons (some I am > sure > > you can guess!) we would like to move away from paradox, and Postgres is > a > > good option. > > > I would like to hear feed back from others, has anyone else deployed > > Postgres in this type of environment? > > Are there options for running postgres, maybe as a process (maybe the > wrong > > term) within the main application - this could be started and stopped by > the > > application? > > No, and it's not likely to happen. this would make postgresql subject > to silent corruption if the application crashed, and no one wants to > support that on top of the version we already have. > > > I am thinking some type of options similar to SQLLite would be great for > a > > single user install - but we also want to be able to A) use the full > power > > of Postgres, B) set-up as a full server in some other cases. > > Sadly, there ain't no such thing as a free lunch here. Either you > install the real thing, and deal with the issues of administration and > such, or you go with sqllite or it's brethren. > > I'd look into building an appliance computer for the customers. > Something in a 1U rack mount with a pair of SATA or SAS drives mounted > in a sw RAID-1 would do nicely for this kind of thing. Think of it as > delivering the whole solution. Deliver it with a super simple custom > gui on top to create accounts or phpmyadmin or something. The further > away from a database on every desktop you get, generally the better > off you'll be. > > If embedded fits this project better, then look elsewhere, it's really > not pgsql's strong suit. >
Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows
On 10/25/07, Craig Hawkes <[EMAIL PROTECTED]> wrote: > There will really be two targets, replacing the existing single user > solutions, and providing a larger solution with multi-user etc. There is > however large base of similar code between these solutions, and it would be > perferable if they could use the same back-end engine or something very > simliar. I know some people will aruge that you should use the right tool > for the job, and I guess I am tring to see if there is a right tool which > will work for both these. I guess you could be looking at an intermediate abstraction layer that will allow your application to communicate with whatever database that newly added tier can talk to. > My main/only real hesiation with Postgres is around supporting non IT users, > mainly around installation. I am keen to here peoples comments on this, and > what steps could be taken to mitigate these. The "immediate" option that springs to mind would be to offer consultancy and installation support. > Thank you for your comments > Craig Cheers, Andrej P.S.: Please, do not top post :} ... see my sig for reasons. -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/24/07 15:04, Craig Hawkes wrote: > Hi > > OK, Sorry but I did search, and did not find anything useful. > > Maybe putting forward a embedded solution as part of the question was the > wrong option. > > If I could reword: > > Given that we have a large estiblished client base running a Delphi/Paradox > solution, and that we would like to replace Paradox with a much better SQL > engine, I was looking for comments as to how Postgres maybe suitable. > > We will be implementing more multi-user features - it really is only single > user at this stage, plus we would like to be able to use better SQL - > espically for Reporting - where currently we have hundreds of lines of > Delphi code which could be replaced with a one or two reasonable SQL > Queries. Hmmm. Is Paradox that bad? Or are you pinning too many hopes on SQL? > There will really be two targets, replacing the existing single user > solutions, and providing a larger solution with multi-user etc. There is > however large base of similar code between these solutions, and it would be > perferable if they could use the same back-end engine or something very > simliar. I know some people will aruge that you should use the right tool > for the job, and I guess I am tring to see if there is a right tool which > will work for both these. > > My main/only real hesiation with Postgres is around supporting non IT users, > mainly around installation. I am keen to here peoples comments on this, and > what steps could be taken to mitigate these. Install PostgreSQL on your box, run the app and see how much maintenance it needs, and how much can be scripted. If PG is installed as a service (which it should be, no?), then certainly there is an API call to start the postgresql service at the beginning of your app and another to stop the service during app termination. > Currently the main alternate which is being considered is SQL Server, using > SQL Express, SQL Mobile/Compact. But I see this as limiting use to only > features supported by the Compact edition, or having to install SQL Express > - which I can see as being worst than supporting Postgres Installs. I think that you're just going to have to create a pilot project to see how it fits your individual needs. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHIAehS9HxQb37XmcRApZYAJ9uI5NHgVF19MytK2M+7+6xHGhlNACfR1bL qRhO+gSrIF5ow7lbQUbkWqo= =5vCS -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows
Craig Hawkes, 24.10.2007 22:04: If I could reword: Given that we have a large estiblished client base running a Delphi/Paradox solution, and that we would like to replace Paradox with a much better SQL engine, I was looking for comments as to how Postgres maybe suitable. Not sure if I'll be banned from this list for saying this :) For embedding with possible scaling to a multi-user environment you might want to take a look at Firebird (www.firebirdsql.org). It's not as feature reach as Postgres and the documentation is very poor compared to PG (because of licensing issues everything is spread in different places). But it is a good engine and does integrate well with Delph as far as I know. Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows
Craig, just reporting my own experience; distributing PostgreSQL on Windows into around 15 companies on Servers AND Laptop-Clients with various restrictive in-House-Rules: - the windows installer is robust and scriptable - the installer uses standard .msi technology, so it can be integrated into nearly every self respecting sofware distribution system - challenges usually concerned user permissions for the postgres user: it definitely needs the "logon_as_service" permission, and Windows Installer creates it reliably with that permission. Problems only appeared when inhouse Windows permission systems (Active Directory and thelike) were configured wrongfully to take those permissions away. - PostgreSQL is very very robust even on Laptop-Usage and within virtual machines. Laptop-Usage being things like "Power Outage", "no clean shutdown" etc. etc.; even BlueScreening Servers did not lead to data corruption - challenges on Computer Systems came from sloppy Antivirus-Systems and Firewalls, which decided to: a) lock database data-files b) interfere with local TCP/IP communcation from Client <-> Client and PostgreSQL postmaster to per-connection-backend - challenges with System Administrators arose from multiple postgres.exe processes running on each server (just starting postgres leads to 5 postgres.exe in taskmanagers process-view; that is correct but unusual for Windows) Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows
Ron Johnson wrote:
Hmmm. Is Paradox that bad?
Yes. I'm also a Delphi developer, who has already converted a paradox
program (was just before pg on windows.. bummer)
Paradox is a file share'd database (like dbase), and when the program
crashes, it'll hose up your pdox tables too. Especially if they are
shared on the network.
You can do pdox apps in two ways, with table operations, or sql.
By table operations I mean:
table.open;
table.IndexName := 'name';
table.findFirst('bob');
table.edit;
table.fieldByName('pay').asInteger := 4;
table.post;
You'd say, why do that when there is sql? Because its not exactly
stable. I did code in sql to update some photos, by looking at the code
you'd think it would work.. but it didnt. (was a delete statement, then
an insert). Sometime it worked, sometimes not. I replaced it with
table operations and it always worked.
We ported over to firebird, it is so much more stable now (We used to
have to tell people to restore from backup all the time, just cuz pdox
is not stable) and is much simpler to maintain the db because we get to
fire off sql. Our database upgrade was a huge program that would
restructure the database... now its just a little sql script. Its so
much nicer.
-Andy
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
