473,889 Members | 1,692 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 7242
Quoting Rick Gigger <ri**@alpinenet working.com>:
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.


Rick,

I'm not sure what OS you're using (I might have missed it in the thread) but
I've never even thought about an embedded database because PG runs very nice on
my laptop (P III - 850Mhz but I did NOT it new). Now, I'm running Linux so
maybe that is the difference. In fact, I'm found it to be invaluable to take it
to a client meeting and show not only the high level application gloss but I can
also show the guts of a application/web site.

Now I do understand the sync'ning issue with the database and I haven't had that
issue yet but since you'd have two Pg server's, I would think this issue could
be handled one of the replication project that is available or by something you
custom build (like a perl script that connects to both systems and syncs by
checking for missing pkey's in both systems).

If you have the ability, you might want to invest in building a laptop with
Linux on it until the features you want are available in the methods you want to
use them in.

-$0.02

--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

_______________ _______________ ______
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(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 #51
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?


The "magic" seems to be that the application appears to be managing the
database by its own, without the help of any separate database product.
I've seen commercial product that uses embedded Firebird and changes the
default extension of the database file from *.fdb into *.dat or something.

Of course, with the current cluster/database directory layout, it's
still easy to spot PostgreSQL footprints all over the place
(pg_hba.conf, pg_xlog/, PG_VERSION, etc).

--
dave
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #52
David Garamond wrote:
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?

The "magic" seems to be that the application appears to be managing
the database by its own, without the help of any separate database
product. I've seen commercial product that uses embedded Firebird and
changes the default extension of the database file from *.fdb into
*.dat or something.

Of course, with the current cluster/database directory layout, it's
still easy to spot PostgreSQL footprints all over the place
(pg_hba.conf, pg_xlog/, PG_VERSION, etc).


It's not so important to hide the fact that postgres is being used as it
is to spare the user from having to know anything about general database
administration when the all the app really needs from the db is for it
to be a more powerful way of storing and retreiving information from
user-owned files.

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

Nov 22 '05 #53
On Thu, Jan 22, 2004 at 09:03:13AM -0800, Jeff Bowden wrote:
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?

The "magic" seems to be that the application appears to be managing
the database by its own, without the help of any separate database
product. I've seen commercial product that uses embedded Firebird and
changes the default extension of the database file from *.fdb into
*.dat or something.

Of course, with the current cluster/database directory layout, it's
still easy to spot PostgreSQL footprints all over the place
(pg_hba.conf , pg_xlog/, PG_VERSION, etc).


It's not so important to hide the fact that postgres is being used as it
is to spare the user from having to know anything about general database
administration when the all the app really needs from the db is for it
to be a more powerful way of storing and retreiving information from
user-owned files.


Installation and upgrades are another issue.

To 'install' a typical embedded database requires no action at all -
all the infrastructure is embedded within the application, or at most
a single external dynamica library.

I deploy and support applications that use PostgreSQL as a backend
database and a huge amount of support effort is consumed by PostgreSQL
issues. (We're just upgrading from 7.2.* to 7.4.1, and walking even
very unix literate users through the pain of a dump, upgrade, restore
process is very painful).

Other differences between an embedded database and a typical RDBMS is
that the embedded database is likely to be between twice as fast and
ten times as fast, depending on the details of the application. One
tradeoff, of course, is that the embedded database is likely to be
single-user, or at most single-writer, multiple-reader. The other
advantage that the embedded DB likely has is that it probably accepts
queries and returns results as language native types via direct
calls, avoiding all the overhead of translating queries into SQL
and translating the result sets back again.

Another difference between the two is that an RDBMS like PostgreSQL
is that PostgreSQL will make more requirements of the underlying OS
(for instance shared-memory and semaphores) - meaning that the kernel
may need to be reconfigured and the machine rebooted.

If what you need is a single-user database then, nice as it is to
have a full RDBMS, you really don't need all that infrastructure,
and the users will (likely) be far happier without it. If it'd
be nice to have SQL support, then there are embedded, single-user,
databases with some level of SQL support.

All of this explains why an embedded PostgreSQL isn't a great idea. It
being a true multi-user database means that even if you went though
all the work needed to turn it into an embedded database you wouldn't
get most of the advantages.

Short term, if you want embedded SQL, use something like SQLite that's
designed to work that way. If you want to share code between an
embedded DB and PostgreSQL then use a database agnostic API (such as
DBI or libdbi) and restrict yourself to fairly vanilla SQL. If you
must have PostgreSQL used by a non DB aware user then you'll have to
do what I've done, which is to build some automated maintenance code
around PostgreSQL to handle installation and day-to-day maintenance
and monitoring automagically, or patch something similar together
based on the vacuum daemon and so on.

Now, while I think that an embedded fork of PostgreSQL is completely
missing the point I do think that a low maintenance fork or
configuration option would be a very useful feature. I'd love to
be able to ship an application that would

o Have a private installation of PostgreSQL

o That would run semi-persistently - if the DB isn't running, the
application will transparently start it, and if the DB is idle
for some length of time it gracefully shuts down

o Is zero-maintenance - all vacuuming, analysing etc is handled
automatically. So are database version upgrades.

o That runs under the permissions of the user running the application

o And that could, by tweaking an application configuration variable
could swap out the private PostgreSQL installation and instead
access a standard installation

But (apart from the easy DB version upgrade bit) I'd far rather have
point-in-time or incremental backups, easy replication, richer SQL
support, easier reporting and easier to use replication. So, much as
I'd like it, a low maintenance build is a long way down my list of
priorities.

Cheers,
Steve

---------------------------(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 #54
> I'm not sure what OS you're using (I might have missed it in the thread)
but
I've never even thought about an embedded database because PG runs very nice on my laptop (P III - 850Mhz but I did NOT it new). Now, I'm running Linux so maybe that is the difference. In fact, I'm found it to be invaluable to take it to a client meeting and show not only the high level application gloss but I can also show the guts of a application/web site.
If it were just my laptop that would be fine. But I just can't really
install a full on database server (nor would I feel comfortable doing so) on
every machine that I would need it on.
Now I do understand the sync'ning issue with the database and I haven't had that issue yet but since you'd have two Pg server's, I would think this issue could be handled one of the replication project that is available or by something you custom build (like a perl script that connects to both systems and syncs by checking for missing pkey's in both systems).


This is not an issue for postgres at this point. I will most likely just
handle the syncing of specific parts of the database on my own.
---------------------------(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 #55
> > It's not so important to hide the fact that postgres is being used as it
is to spare the user from having to know anything about general database
administration when the all the app really needs from the db is for it
to be a more powerful way of storing and retreiving information from
user-owned files.
Installation and upgrades are another issue.

To 'install' a typical embedded database requires no action at all -
all the infrastructure is embedded within the application, or at most
a single external dynamica library.


This is by far (other than reliability and compatibility) the most important
thing for me.
All of this explains why an embedded PostgreSQL isn't a great idea. It
being a true multi-user database means that even if you went though
all the work needed to turn it into an embedded database you wouldn't
get most of the advantages.
Is it true that postgres is not suited for this and should not be used as
such or is it just a matter of spending the time to allow you maybe compile
an embedded version?
Short term, if you want embedded SQL, use something like SQLite that's
designed to work that way. If you want to share code between an
embedded DB and PostgreSQL then use a database agnostic API (such as
DBI or libdbi) and restrict yourself to fairly vanilla SQL.
This is exactly the approach that I have been using and it has worked very,
very well. The only problems that I have come across have occured when my
sql just wasn't quite "vanilla" enough. For instance sqlite doesn't have
date functions. We have not come across any show stoppers for this approach
but we do have to always consider that we are coding for 2 different dbs and
sometimes we do have to write db specific sql when there are differences and
test on both dbs. I understand that if that's what I have to do then that's
what I have to do. It would just be very, very nice to have postgres in
both situations, if it were possible.
Now, while I think that an embedded fork of PostgreSQL is completely
missing the point I do think that a low maintenance fork or
configuration option would be a very useful feature. I'd love to
be able to ship an application that would

o Have a private installation of PostgreSQL

o That would run semi-persistently - if the DB isn't running, the
application will transparently start it, and if the DB is idle
for some length of time it gracefully shuts down

o Is zero-maintenance - all vacuuming, analysing etc is handled
automatically. So are database version upgrades.

o That runs under the permissions of the user running the application

o And that could, by tweaking an application configuration variable
could swap out the private PostgreSQL installation and instead
access a standard installation
This just might work fine for me. :-)
But (apart from the easy DB version upgrade bit) I'd far rather have
point-in-time or incremental backups, easy replication, richer SQL
support, easier reporting and easier to use replication. So, much as
I'd like it, a low maintenance build is a long way down my list of
priorities.


point-in-time, incremental backups, easy replication would be big for me as
well. Especially point-in-time and incremental backups
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #56
"Rick Gigger" <ri**@alpinenet working.com> writes:
All of this explains why an embedded PostgreSQL isn't a great idea. It
being a true multi-user database means that even if you went though
all the work needed to turn it into an embedded database you wouldn't
get most of the advantages.
Is it true that postgres is not suited for this and should not be used as
such or is it just a matter of spending the time to allow you maybe compile
an embedded version?


I think that Steve has it exactly right here. Postgres isn't designed
to be an embedded database in that sense, and none of the developers are
interested in moving it in that direction. It would require too many
compromises versus the full-fledged-server situation.

This is definitely a case where one size does not fit all. Rather
than trying to force-fit Postgres to an application it's not suited for,
you should use another product that is designed for that application.
In short: your time would be better spent on upgrading SQLite to do what
you need.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #57
> "Rick Gigger" <ri**@alpinenet working.com> writes:
All of this explains why an embedded PostgreSQL isn't a great idea. It
being a true multi-user database means that even if you went though
all the work needed to turn it into an embedded database you wouldn't
get most of the advantages.
Is it true that postgres is not suited for this and should not be used
as such or is it just a matter of spending the time to allow you maybe compile an embedded version?


I think that Steve has it exactly right here. Postgres isn't designed
to be an embedded database in that sense, and none of the developers are
interested in moving it in that direction. It would require too many
compromises versus the full-fledged-server situation.

This is definitely a case where one size does not fit all. Rather
than trying to force-fit Postgres to an application it's not suited for,
you should use another product that is designed for that application.
In short: your time would be better spent on upgrading SQLite to do what
you need.


How about the following comment from an earlier post:
Now, while I think that an embedded fork of PostgreSQL is completely
missing the point I do think that a low maintenance fork or
configuration option would be a very useful feature. I'd love to
be able to ship an application that would

o Have a private installation of PostgreSQL

o That would run semi-persistently - if the DB isn't running, the
application will transparently start it, and if the DB is idle
for some length of time it gracefully shuts down

o Is zero-maintenance - all vacuuming, analysing etc is handled
automatically. So are database version upgrades.

o That runs under the permissions of the user running the application

o And that could, by tweaking an application configuration variable
could swap out the private PostgreSQL installation and instead
access a standard installation


Is this something that could make sense for postgres?
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 22 '05 #58
On Thu, Jan 22, 2004 at 01:04:29PM -0700, Rick Gigger wrote:

[PostgreSQL ill-suited to embedded use]
How about the following comment from an earlier post:
Now, while I think that an embedded fork of PostgreSQL is completely
missing the point I do think that a low maintenance fork or
configuration option would be a very useful feature. I'd love to
be able to ship an application that would

o Have a private installation of PostgreSQL

o That would run semi-persistently - if the DB isn't running, the
application will transparently start it, and if the DB is idle
for some length of time it gracefully shuts down

o Is zero-maintenance - all vacuuming, analysing etc is handled
automatically. So are database version upgrades.

o That runs under the permissions of the user running the application

o And that could, by tweaking an application configuration variable
could swap out the private PostgreSQL installation and instead
access a standard installation


Is this something that could make sense for postgres?


It is something that could be done without touching the PostgreSQL
core code - for instance, by dropping a transparent shim in front of
libpg that maintains a little state and intercepts some of the calls
to libpg, yet presents the same API to the application.

Add a flag to the connect string to identify an attempt to connect to
a private installation (passing the top-level path so the shim can
find the binaries, database directory and socket). On a failed attempt
to connect to the private installation try and fork/exec the postmaster,
wait for it to spin up and then continue. Once connected, kill -TERM
the postmaster (so that it'll shutdown gracefully once everyone is
disconnected).

That'll provide everything needed for a private installation, optional
connection to a real installation, automatic spin-up and spin-down.

Vacuuming and analysing can be handled using the algorithms (and the code,
come to that) from pg_autovacuum.

That provides everything on my wish-list apart from automated version
upgrades with only a trivial amount of coding needed - probably no more
than a hundred lines of new code for a minimalist version.

Cheers,
Steve

---------------------------(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 #59
Steve Atkins <st***@blighty. com> writes:
[ much snipped ]
Vacuuming and analysing can be handled using the algorithms (and the code,
come to that) from pg_autovacuum.


Autovacuum will undoubtedly migrate into the core. I'm not sure how
soon, or whether the end result will look much like the present
contrib code, but everybody agrees that this is something we need for
main-line development. Accordingly, I don't see the point in expending
extra effort on this in a (hypothetical) embedded-like wrapper for PG.

regards, tom lane

---------------------------(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 #60

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
9967
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
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
11202
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10443
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9611
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
7998
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
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...
1
4650
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
3
3256
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.