0

Currently, I have an application that uses Firebird in embedded mode to connect to a relatively simple database stored as a file on my hard drive. I want to switch to using PostgreSQL to do the same thing (Yes, I know it's overkill). I know that PostgreSQL cannot operate in embedded mode and that is fine - I can leave the server process running and that's OK with me.

I'm trying to figure out a connection string that will achieve this, but have been unsuccessful. I've tried variations on the following:

jdbc:postgresql:C:\myDB.fdb
jdbc:postgresql://C:\myDB.fdb
jdbc:postgresql://localhost:[port]/C:\myDB.fdb

but nothing seems to work. PostgreSQL's directions don't include an example for this case. Is this even possible?

starblue
  • 55,348
  • 14
  • 97
  • 151
Chris
  • 3,400
  • 1
  • 27
  • 41
  • 2
    Postgres won't be able to store a database in one file. Depending on your operating system, you can have a disk image inside of a single file. What is the issue with continuing to use Firebird, or another embedded database? I like Postgres too - but if it doesn't fit your requirements... – Steve K Oct 26 '09 at 20:15
  • I was afraid of this. We were switching because of security concerns raised by end-users, but we will have to find another option. – Chris Oct 26 '09 at 21:47
  • @Chris : I think that no database is really secure when you can access the database file(s). So embedded mode is not secure. – Hugues Van Landeghem Oct 26 '09 at 22:13

3 Answers3

1

You can trick it. If you are running PostGRESQL on a UNIXlike system, then you should be able to create a RAMDISK and use that for the database storage. Here's a pretty good step by step guide for RAMdisks on Linux.

In general though, I would suggest using SQLITE for an SQL db in RAM type of application.

Michael Dillon
  • 31,973
  • 6
  • 70
  • 106
  • Thanks for the comment. However, it would seem that a RAM disk is memory that behaves like a disk whereas I would need a disk that behaves like memory. – Chris Oct 26 '09 at 19:37
  • The main behavioral difference between a disk and memory is that memory is very fast, and every location is accessed equally fast. The only way to get a disk that behaves like memory is to use memory. In other words an SSD disk or flash memory. PostgreSQL can store its database on either of those two as well. – Michael Dillon Oct 26 '09 at 20:08
0

Postgres databases are not a single file. There will be one file for each table and each index in the data directory, inside a directory for the database. All files will be named with the object ID (OID) of db / table / index.

The JDBC urls point to the database name, not any specific file: jdbc:postgresql:foodb (localhost is implied)

If by "disk that behaves like memory", you mean that the db only exists for the lifetime of your program, there's no reason why you can't create a db at program start and drop it at program exit. Note that this is just DDL to create the DB, not creating the data dir via the init-db program. You could connect to the default 'postgres' db, create your db then connect to it.

AngerClown
  • 6,149
  • 1
  • 25
  • 28
  • I'd like to mark Steve K's answer as the solution, but since it was a comment, this is the next best thing. – Chris Oct 26 '09 at 21:55
0

Firebird 2.1 onwards supports global temporary tables, which only exist for the duration of the database connection.

Syntax goes something like CREATE GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS

Niels Thomsen
  • 46
  • 2