39

If it's possible, I'm interested in being able to embed a PostgreSQL database, similar to sqllite. I've read that it's not possible. I'm no database expert though, so I want to hear from you.

Essentially I want PostgreSQL without all the configuration and installation. If it's possible, tell me how.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
pc1oad1etter
  • 8,549
  • 10
  • 49
  • 64
  • 1
    No random caps there. Apparently I was mistaken on the g though, I see. – pc1oad1etter Sep 13 '08 at 03:58
  • 2
    Instead of literally embedding it, could you install it alongside your program and only run it while your program is running? – Serafina Brocious Sep 13 '08 at 03:04
  • The question for me is "What is 'embeded' and what not". You can always run a process in background like the answer from user brcha below. This is not a question about programming or databases. It is about package management and configuration management. I guess, you have all you want if it is easy to install postgres N times on one machine (maybe with different versions) and run N postgres server processes in background. Each not interfering with each other. Sometimes it is better to not eat the low hanging fruits (sqlite). You have more in the long run if you choose a different solution :-) – guettli Jan 05 '17 at 13:27
  • see also this "Embedded PostgreSQL Component" here: https://stackoverflow.com/a/41119298/363573 – Stephan May 28 '22 at 13:13

8 Answers8

16

Run postgresql in a background process.

Start a separate thread in your application that would start a postgresql server in local mode either by binding it to localhost with some random free port or by using sockets (does windows support sockets?). That should be fairly easy, something like:

system("C:\Program Files\MyApplication\pgsql\postgres.exe -D C:\Documents and Settings\User\Local Settings\MyApplication\database -h 127.0.0.1 -p 12345");

and then just connect to 127.0.0.1:12345.

When your application quits, you can always send a SIGTERM to your thread and then wait a few seconds for postgresql to quit (ie join the thread).

PS: You can also use pg_ctl to control your "embedded" database, even without threads, just do a "pg_ctl start" (with appropriate options) when starting the application and "pg_ctl stop" when quitting it.

Community
  • 1
  • 1
brcha
  • 161
  • 1
  • 4
  • 2
    No, I was not being funny, in any way in which I was aware, in my question. – pc1oad1etter Nov 24 '10 at 15:37
  • Thank you. This solution looks good. Yes, starting postgres is more work than using sqlite, but this way you have a real database, not just a toy :-) – guettli Jan 05 '17 at 13:06
13

You cannot embed it, nor should you try.

For embedding you should use sqlite as you mentioned or firebird rdbms.

Brian R. Bondy
  • 339,232
  • 124
  • 596
  • 636
  • 2
    I am also currently considering learning Firebird as its the only database which comes in both embedded and server/client forms and has a totally free, open source , non-commercial agenda. – explorer Aug 06 '11 at 18:21
  • 1
    _"... nor should you try ..."_ Why not? Even Oracle is embeddable. – Valentin H Jun 25 '15 at 21:40
  • I have been using Firebird Emdedded Server for over 15 years and in that period I have had over 5000 databases installed. I'm satisfied with that. – Nelson H C Nepomuceno Jun 02 '21 at 15:47
10

Unless you do a major rewrite of code, it is not possible to run Postgres "embedded". Either run it as a separate process or use something else. SQLite is an excellent choice. But there are others. MySQL has an embedded version. See it at http://mysql.com/oem/. Also several java choices, and Mac has Core Data you can write too. Hell, you can even use FoxPro. What OS you on and what services you need from the database?

Crag
  • 124
  • 1
  • 4
  • We already have an application that uses PGSQL (Windows XP), - I don't know what the ramifications of switching to a different database - but I assumed they would be less significant if there was an embedded version of PGSQL. – pc1oad1etter Sep 16 '08 at 03:05
  • 1
    The environment is Windows XP, and one of the biggest concerns is that the environment's policies requires extra hoops to jump through if an new package is being installed, whereas simply dropping a zero-config, no installation file on it would be simpler. – pc1oad1etter Sep 16 '08 at 03:07
  • @pc1oad1etter your problem (no easy way to install new package) could be solved at a different level. If you provide **one** package which installs postgres and your code ... would this help? – guettli Jan 05 '17 at 13:03
7

You can't embed it as a in process type thing like sqlite etc, but you can easily embed it into your application setup using Inno setup at http://www.innosetup.org. Search their mailing list archive and you will find someone did most of the work for you and all you have to to is grab the zipped distro and you can easily have postgresql installed when the user installs your app. You can then use the pg_hba.conf file to restrict the server to local host only. Not a true embedded DB, but it would work.

  • Have you been success on using innosetup to do postgresql silent install? that would be great! – swdev Apr 20 '12 at 09:28
1

PostgreSQL is intended to run as a stand-alone server; it's probably possible to embed it if you hack at it hard and long enough, but it would be much easier to just run it as intended in a separate process.

John Millikin
  • 197,344
  • 39
  • 212
  • 226
1

Well, I know this is a very very very old post, but if anyone has nowadays this question, I would refer to:

elikesprogramming
  • 2,506
  • 2
  • 19
  • 37
1

HSQLDB (http://hsqldb.org/) is another db which is easily embedded. Requires Java, but is an excellent and often-used choice for Java applications.

Karel Vervaeke
  • 339
  • 3
  • 2
0

Anyone tried on Mac OS X:

http://pagesperso-orange.fr/bruno.gaufier/xhtml/prod_postgresql.xhtml

http://www.macosxguru.net/article.php?story=20041119135924825

(Of course sqlite would be my embedded db of choice as well)