473,836 Members | 2,106 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 7228

Personally, I would use something like berkelydb for an embedded database
engine. As a general rule, if it needs to be embedded, you don't need sql.
I'm sure you could think of areas where you could use a full blown sql
server, but in practice that generally isn't the case. And like others
have already mentioned, if you are embedding a database in software that a
user has control of, you want it as SIMPLE as possible.

Chris

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa .us>
To: "Rick Gigger" <ri**@alpinenet working.com>
Cc: "Chris Travers" <ch***@travelam ericas.com>; "David Garamond"
<li***@zara.6.i sreserved.com>; "Jeff Bowden" <jl*@houseofdis traction.com>;
"pgsql-general" <pg***********@ postgresql.org>
Sent: Friday, January 16, 2004 1:14 PM
Subject: Re: embedded/"serverless " (Re: [GENERAL] serverless postgresql)

"Rick Gigger" <ri**@alpinenet working.com> writes:
... just adds potential for mucking things up. I can see the bug
reports now: "I decided I'd make the shutdown routine 'kill -9' the
postmaster because I didn't like the multi-second delay for a normal
shutdown. Now my database is corrupt."
I recall a discussion a while back where people were complaining that

some os (I think it was mac os x) would just kill all proccesses after something like 30 seconds on system shutdown if they didn't quit fast enough on their own. The response was that because of how postgres uses wal files to
prevent db corruption this was not an issue. Why does this not apply in
this situation?


Because a system shutdown doesn't leave anything else running. kill -9
on the postmaster leaves orphaned backends still running, and orphaned
shared memory segments still in existence. The latter could prevent you
from starting a fresh postmaster (because two shmem segments could
exceed the kernel's SHMMAX) until you do manual cleanup, which most
people don't know how to do. If you do succeed in starting another
postmaster with a fresh shared memory segment, you will have two
independent sets of backends modifying the database files with no
interlocking, which is a sure recipe for data corruption.

There is an interlock that is supposed to prevent this catastrophic
scenario, but I do not trust it unreservedly (and not at all on
non-Unix-derived platforms, because it depends on some rather arcane
features of the SysV shared memory API, which might not be implemented
fully on other platforms).

This is why the "don't kill -9 the postmaster" TIP is still around, BTW.
It has nothing to do with crash safety.
By saying "the client can scribble on the database anyway" do you do you
mean the client app actually writing over the db files on disk? It seems like this would only be a problem with an exceptionally stupid programmer. How could that happen on accident?


Scribbling on memory that doesn't belong to you isn't something one does
intentionally, either --- but it happens. However, my real concern here
is not so much with program bugs as clueless users. We see enough cases
already of people removing lock files or "unnecessar y" log files; I fear
it'd be a lot worse if those files were sitting in the user's home
directory.

regards, tom lane

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

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

Nov 22 '05 #31
> Personally, I would use something like berkelydb for an embedded database
engine. As a general rule, if it needs to be embedded, you don't need sql.
I'm sure you could think of areas where you could use a full blown sql
server, but in practice that generally isn't the case. And like others
have already mentioned, if you are embedding a database in software that a
user has control of, you want it as SIMPLE as possible.
But the user isn't the one accessing the database directly. Have you ever
used a program like Delphi to create applications? Using a relational
database, you can create applications using nothing but drag-and-drop and
SQL. No real code. If your application is a simple end-user app (like a
wedding planner, for example), embedded databases are your dream come
true. Linux has no real embedded database. PG would be an excellent
embedded database. I have already proposed some functions that would help
PG as an embedded database, and, if I hve time, I will implement them
(can't right now because I'm finishing a book).

Anyway, I get the feeling that those people who don't like the idea of
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
you've never built an application w/ an embedded DB.

Anyway, this thread is going nowhere, I'll come back if/when I have some
code. Perhaps with something concrete we can all see better where this is
going.

Jon

Chris

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa .us>
To: "Rick Gigger" <ri**@alpinenet working.com>
Cc: "Chris Travers" <ch***@travelam ericas.com>; "David Garamond"
<li***@zara.6.i sreserved.com>; "Jeff Bowden" <jl*@houseofdis traction.com>;
"pgsql-general" <pg***********@ postgresql.org>
Sent: Friday, January 16, 2004 1:14 PM
Subject: Re: embedded/"serverless " (Re: [GENERAL] serverless postgresql)

"Rick Gigger" <ri**@alpinenet working.com> writes:
> ... just adds potential for mucking things up. I can see the bug
> reports now: "I decided I'd make the shutdown routine 'kill -9' the
> postmaster because I didn't like the multi-second delay for a normal
> shutdown. Now my database is corrupt."

I recall a discussion a while back where people were complaining that some os (I think it was mac os x) would just kill all proccesses after something like 30 seconds on system shutdown if they didn't quit fast enough on their own. The response was that because of how postgres uses wal files to
prevent db corruption this was not an issue. Why does this not apply in
this situation?


Because a system shutdown doesn't leave anything else running. kill -9
on the postmaster leaves orphaned backends still running, and orphaned
shared memory segments still in existence. The latter could prevent you
from starting a fresh postmaster (because two shmem segments could
exceed the kernel's SHMMAX) until you do manual cleanup, which most
people don't know how to do. If you do succeed in starting another
postmaster with a fresh shared memory segment, you will have two
independent sets of backends modifying the database files with no
interlocking, which is a sure recipe for data corruption.

There is an interlock that is supposed to prevent this catastrophic
scenario, but I do not trust it unreservedly (and not at all on
non-Unix-derived platforms, because it depends on some rather arcane
features of the SysV shared memory API, which might not be implemented
fully on other platforms).

This is why the "don't kill -9 the postmaster" TIP is still around, BTW.
It has nothing to do with crash safety.
By saying "the client can scribble on the database anyway" do you do you
mean the client app actually writing over the db files on disk? It seems like this would only be a problem with an exceptionally stupid programmer. How could that happen on accident?


Scribbling on memory that doesn't belong to you isn't something one does
intentionally, either --- but it happens. However, my real concern here
is not so much with program bugs as clueless users. We see enough cases
already of people removing lock files or "unnecessar y" log files; I fear
it'd be a lot worse if those files were sitting in the user's home
directory.

regards, tom lane

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

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

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

Nov 22 '05 #32
Jeff Bowden <jl*@houseofdis traction.com> writes:
Still, the main problem I, and I suspect others, would like to solve is
installation/configuration. For my app I don't want the user to have to
understand anything about how keeping data in a shared
system-administered database is different from keeping data in local
files. Everything should "just work".
Sure, but most of the existing packagings of PG already try to make this
automatic (at least Lamar's RPMs and Oliver's Debian package do). No
doubt further work could be invested to make it even smoother, but that
doesn't mean we need a client-started database.
So kill -9 on postmaster can lead to database corruption? What happens
in a power failure?


See my last response to Rick Gigger. kill -9 is a lot different from a
power failure.

regards, tom lane

---------------------------(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 #33
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.

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.

A

--
Andrew Sullivan | aj*@crankycanuc k.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

---------------------------(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 #34
Tom Lane wrote:
Jeff Bowden <jl*@houseofdis traction.com> writes:

Still, the main problem I, and I suspect others, would like to solve is
installatio n/configuration. For my app I don't want the user to have to
understand anything about how keeping data in a shared
system-administered database is different from keeping data in local
files. Everything should "just work".


Sure, but most of the existing packagings of PG already try to make this
automatic (at least Lamar's RPMs and Oliver's Debian package do). No
doubt further work could be invested to make it even smoother, but that
doesn't mean we need a client-started database.


So maybe this is a packaging issue. On Debian when I install postgres
it is necessary to do root shit in order to enable non-priveledged users
to create and destroy databases. My understanding has alwasy been that
these operations are restricted because it can allow users to
accidentally or intentionally interefere with each other's use of the
server. If there is some way for this to be set up sanely by default to
allow users to successfully use createdb and dropdb on only their own
data, then I will file a wishlist bug against the Debian packages to add
this. Or is this all a pipe dream?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #35
> This seems to suggest that Postgres should focus on something other
than enterprise-level applications, and (presumably) on something
else.
Nope.

Frankly, I am _mighty glad_ that the developers are focussed on
enterprise-level applications.


Me too. But I think we can get it for small-scale stuff with no impact on
the rest. Just an additional function or 6 to make embedded developer's
lives easier.

Jon

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

Nov 22 '05 #36
On Fri, 16 Jan 2004, Jeff Bowden wrote:
Tom Lane wrote:
Jeff Bowden <jl*@houseofdis traction.com> writes:

Still, the main problem I, and I suspect others, would like to solve is
installatio n/configuration. For my app I don't want the user to have to
understand anything about how keeping data in a shared
system-administered database is different from keeping data in local
files. Everything should "just work".


Sure, but most of the existing packagings of PG already try to make this
automatic (at least Lamar's RPMs and Oliver's Debian package do). No
doubt further work could be invested to make it even smoother, but that
doesn't mean we need a client-started database.


So maybe this is a packaging issue. On Debian when I install postgres
it is necessary to do root shit in order to enable non-priveledged users
to create and destroy databases. My understanding has alwasy been that
these operations are restricted because it can allow users to
accidentally or intentionally interefere with each other's use of the
server. If there is some way for this to be set up sanely by default to
allow users to successfully use createdb and dropdb on only their own
data, then I will file a wishlist bug against the Debian packages to add
this. Or is this all a pipe dream?


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?
--
Nigel Andrews

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

Nov 22 '05 #37
Nigel J. Andrews wrote:
On Fri, 16 Jan 2004, Jeff Bowden wrote:

So maybe this is a packaging issue. On Debian when I install postgres
it is necessary to do root shit in order to enable non-priveledged users
to create and destroy databases. My understanding has alwasy been that
these operations are restricted because it can allow users to
accidentall y or intentionally interefere with each other's use of the
server. If there is some way for this to be set up sanely by default to
allow users to successfully use createdb and dropdb on only their own
data, then I will file a wishlist bug against the Debian packages to add
this. Or is this all a pipe dream?


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.


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

Nov 22 '05 #38
I still have to respectfully disagree. Postgresql is IMO just the wrong
software for the job, and given that there are still a number of really
important things that postgresql lacks, it should concentrate on those. I
am not against it however for technical reasons, because those things can
always be overcome. I just wouldn't want postgresql to start branching out
in different directions at this point, it makes no sense if the project
wants to keep focused and one day become comparable side by side to oracle.
IMO that should be it's main goal, and embedded functionality would be a
detour that has more chances of doing harm then good.

Chris


Frankly, I am _mighty glad_ that the developers are focussed on
enterprise-level applications.


Me too. But I think we can get it for small-scale stuff with no impact on
the rest. Just an additional function or 6 to make embedded developer's
lives easier.

Jon

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

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

Nov 22 '05 #39
Chris Ochs wrote:
I still have to respectfully disagree. Postgresql is IMO just the wrong
software for the job, and given that there are still a number of really
important things that postgresql lacks, it should concentrate on those. I
am not against it however for technical reasons, because those things can
always be overcome. I just wouldn't want postgresql to start branching out
in different directions at this point, it makes no sense if the project
wants to keep focused and one day become comparable side by side to oracle.
IMO that should be it's main goal, and embedded functionality would be a
detour that has more chances of doing harm then good.


I believe the basic mission of Postgres will stay the same [for a long
time]: providing a 1) reliable database; 2) with advanced; 3) and
standard compliant features.

However, venturing into win32 world _will_ generate demands like
embedded and other desktop app-oriented features since I expect that's
what many people will using Postgres for in win32 (currently people are
using IB/FB for this and not MySQL/Postgre; MySQL's embedded version is
GPL/commercial). And we all know it's all a matter of what itches the
developers the most. If enough people are bugging them about something,
they will do it... :-)

--
dave
---------------------------(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 #40

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

Similar topics

6
12726
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
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
9656
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
10821
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
10527
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10241
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
5642
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
4443
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
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3102
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.