473,890 Members | 2,038 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

serverless postgresql

For ease of configuration and other reasons, I would like for my
single-user GUI app to be able to use postgresql in-process as a library
accessing a database created in the users home directory. I think I
could possibly get what I want by launching a captive copy of postmaster
with appropriate args but it seems conceptually cleaner to not have a
seperate process at all. Has anyone tried to do anything like this?

I've looked at sqlite and it might be workable for my project but I
would prefer the more standard and featureful SQL of postgres. In
particular sqlite lacks date arithmetic and has some funny type issues
(it claims to be typeless, except when it isn't, or something).

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 22 '05
59 7243
Hi Rick;

I have been looking for a few years at a similar situation with my CRM
software and soon (once I have the SOAP interfaces done, I will start on a
stand-alone client. I have eventually decided on a very different structure
than you have, though the result is more or less the same.

Rather than using PostgreSQL as an embedded database manager, I have decided
that it makes more sense for my app to "cache objects" in XML documents and
then use those on trips to customers' sites. The XML documents can then be
checked and the objects re-sync'd when the app goes online again. The
cacheing would be automatic and cached objects would expire unless
explicitly saved.

Why not use XML for this sort of problem? Or berkeley DB? Is there any
reason that PostgreSQL would be better? I am not aware of any functional
replication systems which would work in this way, so I am guessing your app
will have to handle all the logic for the syncronization anyway.

Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #41
Hi Tom and others;

I agree with your reasoning insofar as it pertains to dedicating core
PostgreSQL time into developing such a library. I also agree insofar as it
pertains to the PostgreSQL core distribution.

I do think, however, that given the current demand for a serverless solution
to PostgreSQL (whether or not it is the right tool for the job), I would
suggest that those who are interested and have the necessary skills set up a
gborg project. Perhaps at some point if PostgreSQL distributions become
more commonplace, we will see it used, and this will open up new
oportunities for PostgreSQL. It should be remembered that more options in
critical areas (such as Win32) may mean more community, and hopefully in the
long run, more developers.

Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #42

On Fri, 16 Jan 2004, Jeff Bowden wrote:
Nigel J. Andrews wrote:
I don't see what the problem is. PostgreSQL is perfectly able to be installed
and run as any old user. Even if one is distributing via a package system, such
as RPM perhaps, which has problems installing as non-root user if one sees the
package already installed then there's no need to try to install it
again. Even with the package installations there's bugger all stopping any old
user doing an initdb -D myownplace.

The standard postgres user on a system is only special in that it is generally
the user the postmaster is run as. If you have other non-priviledged users and
there's no need for them to share a cluster why try to make them?


I'm sorry, maybe I didn't state my ideas clearly enough. I was
following on to Tom Lane's suggestion that the Debian and RPM packages
make configuration "automatic" . I was trying to explain that even after
installation, some administrator configuration is required to make the
server usable for non-privelidged users. Either in the form of creating
and dropping databases or in giving the users rights to do it themselves.

The way I'd like to make it work for my app is to run a seperate
postmaster as the user to avoid involving the machine administrator or
interfering with other users. If a shared install can be made to work
in an equivalent way, then I would rather go with that. I'm not clear
on whether that's possible though. My current understanding is that it
is not and that my original notion is the only way for my app.


I think I probably didn't do the explanation clearly enough. This does depend
on what you mean by shared install. I was assuming you just wanted to manage
data independently only using the shared binaries installed from the
package. If that's not the case then obviously this isn't what you want. If
that is the case then you could run the following as any old user, provided the
environment has been set appropiately of course:

initdb -D $HOME/pgdata
sed -e 's/^#port = 5432/port = 12345/' $HOME/pgdata/postgresql.conf > /tmp/p
mv /tmp/p $HOME/pgdata/postgresql.conf
pg_ctl -D $HOME/pgdata start

then access the cluster using:

pgsql -P 12345 template1

That's it. That's access as the db superuser for that cluster. Sure if the
system admin upgrades the shared software then you could have problems.

My apologies if I've got the wrong end of things again, I've got a lot of
unread email and I keep reading things in a very haphazard order.
--
Nigel J. Andrews
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #43
On Fri, 2004-01-16 at 16:00, Jonathan Bartlett wrote:
Linux has no real embedded database. PG would be an excellent
embedded database.


SQLite works great for these kinds of applications, FWIW.

b.g.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #44
Christopher Browne wrote:
jl*@houseofdis traction.com (Jeff Bowden) wrote:

Oh yeah, that brings me to another question. I was looking at the
postmaster command-line switches and I couldn't find any that would
allow me to point it at an arbitrary config file but then I had a
look around and it seems that the .conf files are already used by
the various startup scripts. Does postmaster itself ever read the
.conf files or is it controlled strictly by switches?


All of the .conf files are in one directory, and that directory is
controlled by either the value of environment variable PGDATA or the
"-D" command parameter.

It seems not-overly-valuable to have the .conf files be able to be
specified in random other locations.

Is there some particular reason you have in mind why you would want to
_ignore_ the configuration in $HOME/DBDIR and instead use
configuratio n in some other random location? I would think that
collecting the config into one directory, as is done right now, is a
_good_ thing.

If the existing user configuration could be reliably determined to be
sane and have sufficient permissions or could be made so, that would be
fine.

I am writing an app for users who are not necessarily knowledgeable
about databases. If they don't have an existing useable postgres
configuration, I would like for my app to be able to create one without
forcing the user to have to be root or learn about postgres
configuration. The data for this app should remain private to the user
who created it and there is no need for concurrent access.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 22 '05 #45
After a long battle with technology, jl*@houseofdist raction.com (Jeff Bowden), an earthling, wrote:
Christopher Browne wrote:
jl*@houseofdi straction.com (Jeff Bowden) wrote:
Oh yeah, that brings me to another question. I was looking at the
postmaster command-line switches and I couldn't find any that would
allow me to point it at an arbitrary config file but then I had a
look around and it seems that the .conf files are already used by
the various startup scripts. Does postmaster itself ever read the
.conf files or is it controlled strictly by switches?
All of the .conf files are in one directory, and that directory is
controlled by either the value of environment variable PGDATA or the
"-D" command parameter.

It seems not-overly-valuable to have the .conf files be able to be
specified in random other locations.

Is there some particular reason you have in mind why you would want to
_ignore_ the configuration in $HOME/DBDIR and instead use
configurati on in some other random location? I would think that
collecting the config into one directory, as is done right now, is a
_good_ thing.


If the existing user configuration could be reliably determined to be
sane and have sufficient permissions or could be made so, that would
be fine.


I ask again:

Is there some particular reason why you would expect configuration in
$HOME/DBDIR/postgresql.conf to be unreliable/non-sane in some way that
would be "fixed" by putting the configuration in some other location?

You haven't said anything that even _suggests_ that sticking the
configuration somewhere other than the "PGDATA" directory would have
the slightest effect.
I am writing an app for users who are not necessarily knowledgeable
about databases. If they don't have an existing useable postgres
configuration, I would like for my app to be able to create one
without forcing the user to have to be root or learn about postgres
configuration. The data for this app should remain private to the
user who created it and there is no need for concurrent access.


I cannot say this emphatically enough:
_NOTHING REQUIRES THAT THE USER HAVE ROOT ACCESS._

Watch the process of creating a database without:
a) Forcing the user to be root, or
b) Forcing the user to learn about PostgreSQL configuration.

Here is a shell script that will do this, given a directory and a port
number, sets up a database for the current user.

#!/bin/bash
DIRECTORY=$1
PORT=$2
mkdir -p $DIRECTORY
user=`whoami`
/usr/lib/postgresql/bin/initdb -D $DIRECTORY > /dev/null 2> /dev/null
sed -e "s/#port = 5432/port=$PORT/g" -i $DIRECTORY/postgresql.conf
sed -e "s/max_connections = 100/max_connections = 10/g" -i $DIRECTORY/postgresql.conf
/usr/lib/postgresql/bin/pg_ctl -D $DIRECTORY -l $DIRECTORY/pglogs.log start
echo "# empty out access control list" > $DIRECTORY/pg_hba.conf
echo "local all $user trust" >> $DIRECTORY/pg_hba.conf
/usr/lib/postgresql/bin/pg_ctl -D $DIRECTORY reload

The preferred paths to the PostgreSQL programs may vary; I'm using,
here, the instance installed as part of the Debian Linux distribution.
If you include the PostgreSQL software as part of what you install,
then you'll have no problem controlling where it runs from.

That script actually works...

[cbbrowne@wolfe: cbbrowne] ~/bin/mkpg ~/PostgreSQL/cbbrowneInstall PGNotAsRoot 7981
postmaster successfully started
postmaster successfully signalled
[cbbrowne@wolfe: cbbrowne] psql -p 7981 -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
template0 | cbbrowne | SQL_ASCII
template1 | cbbrowne | SQL_ASCII
(2 rows)

You could certainly have your application fiddle a bit more with the
configuration in order to degrade the capabilities further.

None of the things the script configures are esoteric or particularly
elaborate; what can you get in 12 lines of code, after all?

I have made no attempt to make it particularly robust; rewriting the
config files would presumably be safer if you used regular expressions
that permitted variations in whitespace and such, on the off chance
that a user might just discover that you were installing PostgreSQL,
and might change some bit of configuration.

I can't see an "embedded library" database being materially simpler to
configure; you'll still need to point to various software and database
paths, configure socket access, and integrate code to "open" and
"close" the database into your application's code.

The configuration satisfies your requirements:

- Does it create the DB without forcing the user to be root?
It sure does.

- Does it force the user to rummage through configuration files?
No, it does not.

- Is the data private to the user?

The access controls are pretty restrictive; another user would have
to go to _some_ effort to get at the data.

As for "there is no need for concurrent access," that isn't really a
constraint.
--
(format nil "~S@~S" "cbbrowne" "ntlug.org" )
http://www3.sympatico.ca/cbbrowne/spiritual.html
"I've seen estimates that 10% of all IDs in the US are phony. At least
one-fourth of the president's own family has been known to use phony
IDs." -- Bruce Schneier CRYPTO-GRAM, December 15, 2001
Nov 22 '05 #46
> On Fri, Jan 16, 2004 at 02:00:35PM -0800, Jonathan Bartlett wrote:
having PG as an embedded database have never used one. I think Tom, for
example, has been focusing on enterprise-level applications too much to
think about the personal applications, and Chris, I'm going to guess
This seems to suggest that Postgres should focus on something other
than enterprise-level applications, and (presumably) on something
else.


Yes but sometimes an enterprise level application may need to be put on a
laptop and taken off-line. Having an embedded database that is compatible
with the one on the server makes this a bit easier to do.
Frankly, I am _mighty glad_ that the developers are focussed on
enterprise-level applications. That's what makes Postgres eligible
to back enterprise-level software. Applications that try to do
everything for everyone tend to end up doing nothing very well. I
don't think one needs to look too hard to discover examples of that
phenomenon.


Well, this very well may be true. It does seem as if many of the features
that add the reliability and performance necessary for an enterprise server
application are what makes it difficult to make a good embedded solution.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #47
> Hi Rick;

I have been looking for a few years at a similar situation with my CRM
software and soon (once I have the SOAP interfaces done, I will start on a
stand-alone client. I have eventually decided on a very different structure than you have, though the result is more or less the same.

Rather than using PostgreSQL as an embedded database manager, I have decided that it makes more sense for my app to "cache objects" in XML documents and then use those on trips to customers' sites. The XML documents can then be checked and the objects re-sync'd when the app goes online again. The
cacheing would be automatic and cached objects would expire unless
explicitly saved.

Why not use XML for this sort of problem? Or berkeley DB? Is there any
reason that PostgreSQL would be better? I am not aware of any functional
replication systems which would work in this way, so I am guessing your app will have to handle all the logic for the syncronization anyway.


Well since the desktop and server app are using almost exactly the same code
it is nice to not have to recode everything that accesses the database.
While data syncronization code will have to be written either way I want the
existing code to "just work" once taken off line.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #48
On Wed, Jan 21, 2004 at 11:23:40AM -0700, Rick Gigger wrote:

Yes but sometimes an enterprise level application may need to be put on a
laptop and taken off-line. Having an embedded database that is compatible
with the one on the server makes this a bit easier to do.


Why can't you just run a postgres instance for this? What is magic
about "embedded" for this sort of application? Sounds like a clever
wrapper script is all that's necessary for something like that, no?

A

--
Andrew Sullivan | aj*@crankycanuc k.ca
My friend, you can put wheels on your mama but that doesn't make her a bus.
--Joel Spolsky

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #49
On Wed, 21 Jan 2004, Andrew Sullivan wrote:
On Wed, Jan 21, 2004 at 11:23:40AM -0700, Rick Gigger wrote:

Yes but sometimes an enterprise level application may need to be put on a
laptop and taken off-line. Having an embedded database that is compatible
with the one on the server makes this a bit easier to do.


Why can't you just run a postgres instance for this? What is magic
about "embedded" for this sort of application? Sounds like a clever
wrapper script is all that's necessary for something like that, no?


That's what I still don't get. Embedded means something running on [and
probably running] your wrist watch _not_ something running on a full blown
system.

I understand in this thread's context that embedded is used to mean embedded
within an application on an ordinary system, took me a while to realise that
though, but just means I can't see why it is wanted like that. Imagine if there
was a DB around that was used by direct library calls from an application. What
would be one of the first things that would be programmed using it? A server
perhaps?
--
Nigel Andrews
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 22 '05 #50

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
12736
by: codepirate | last post by:
Hi All, I am trying to connct to a serverless/embedded PostgreSQL database from a C# application using VS 2005. First of all, is this possible with PostgreSQL? I know this can be done with some other database providers (sqllite?). If anyone has connected to such a database using some other method/language I'd be greatful for the information. Thank you in advance for any information.
0
9810
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9612
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8000
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7153
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5830
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6031
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4652
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3259
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.