How to embed PostgreSQL in my application ?
19/11/04 08:53
We develop an application requiring a database and want to use PostgreSQL for technical reason, but we want to make the life of our users as easy as possible when installing our product. We would like to embed the PostgreSQL server in your application bundle, is that possible ?
Of course this is possible : click here to download an example project on how to achieve this.
In order to use this project you need a PostgreSQL 7.4.x distribution on your hard disk. Once you will have opened the project in XCode, you will need to update the paths to the PostgreSQL binaries (postgres, pg_id, ...) to point to their location on your hard disk.
There is a pgsql/data directory containing only the template0 and template1 databases.
If you plan to use this example with PostgreSQL 8, you will have to replace it with a new one (see the PostgreSQL documentaion about how to do this using the initlocation tool).
The important point about running an embedded PostgreSQL postmaster is to minimize its requirements relative to system resources. A "normal" PostgreSQL server process may request too much system memory for the needs of an individual user.
System requirements may be adjusted by editing the postgresqL.conf file.
In the example given, we have reduced the max_connections parameter to 5, shared_buffers to 16, sort_mem to 64, vacuum_mem to 1024, max_fsm_ages to 2000, max_fsm_relations to 100 and max_files_per_process to 50.
One problem may arrise when the end-user uses more than one application using an embedded PostgreSQL server.
In this kind of situation, a conflict may happen both on system memory requirements and on the TCP/IP port used to connect to the server.
In your shipping product, you should offer an interface allowing users to change some settings in order to solve these conflicts.
Note that you could also connect to your postmaster using a Unix-domain socket to avoid the latest.
Other source of concern:
pg_ctl (v7.4.x) refuses to use paths containing spaces (even correctly quoted) in either the -D parameter or the PGDATA definition.
One way to solve this annoying problem is to create a symbolic link at runtime in a location you are sure:
- there will be no space in the path name;
- the current user has always write access.
A good canditate seems to be /tmp.
The launching code can be something like:
NSString *launchCmd = [NSString stringWithFormat:@"/bin/ln -fs "%@" /tmp/mypgsql 2> /dev/null ;"
" export PGDATA=/tmp/mypgsql/data ; "
""%@" -i -p 9876 >> /tmp/mypgsql/logs/logfile 2>&1 &", pgsqlFolder, postmasterPath] ;
NSArray *args = [NSArray arrayWithObjects:@"-c",launchCmd,nil] ;
[[NSTask launchedTaskWithLaunchPath:@"/bin/sh" arguments:args] waitUntilExit] ;
And the code to stop the postmaster when application exits:
NSString *launchCmd = [NSString stringWithFormat:@"export PGDATA=/tmp/mypgsql/data ; "%@" stop ", pgctlPath] ;
NSArray *args = [NSArray arrayWithObjects:@"-c",launchCmd,nil] ;
[[NSTask launchedTaskWithLaunchPath:@"/bin/sh" arguments:args] waitUntilExit] ;
(You can also use other ways to launch a task like popen...)
In order to use this project you need a PostgreSQL 7.4.x distribution on your hard disk. Once you will have opened the project in XCode, you will need to update the paths to the PostgreSQL binaries (postgres, pg_id, ...) to point to their location on your hard disk.
There is a pgsql/data directory containing only the template0 and template1 databases.
If you plan to use this example with PostgreSQL 8, you will have to replace it with a new one (see the PostgreSQL documentaion about how to do this using the initlocation tool).
The important point about running an embedded PostgreSQL postmaster is to minimize its requirements relative to system resources. A "normal" PostgreSQL server process may request too much system memory for the needs of an individual user.
System requirements may be adjusted by editing the postgresqL.conf file.
In the example given, we have reduced the max_connections parameter to 5, shared_buffers to 16, sort_mem to 64, vacuum_mem to 1024, max_fsm_ages to 2000, max_fsm_relations to 100 and max_files_per_process to 50.
One problem may arrise when the end-user uses more than one application using an embedded PostgreSQL server.
In this kind of situation, a conflict may happen both on system memory requirements and on the TCP/IP port used to connect to the server.
In your shipping product, you should offer an interface allowing users to change some settings in order to solve these conflicts.
Note that you could also connect to your postmaster using a Unix-domain socket to avoid the latest.
Other source of concern:
pg_ctl (v7.4.x) refuses to use paths containing spaces (even correctly quoted) in either the -D parameter or the PGDATA definition.
One way to solve this annoying problem is to create a symbolic link at runtime in a location you are sure:
- there will be no space in the path name;
- the current user has always write access.
A good canditate seems to be /tmp.
The launching code can be something like:
NSString *launchCmd = [NSString stringWithFormat:@"/bin/ln -fs "%@" /tmp/mypgsql 2> /dev/null ;"
" export PGDATA=/tmp/mypgsql/data ; "
""%@" -i -p 9876 >> /tmp/mypgsql/logs/logfile 2>&1 &", pgsqlFolder, postmasterPath] ;
NSArray *args = [NSArray arrayWithObjects:@"-c",launchCmd,nil] ;
[[NSTask launchedTaskWithLaunchPath:@"/bin/sh" arguments:args] waitUntilExit] ;
And the code to stop the postmaster when application exits:
NSString *launchCmd = [NSString stringWithFormat:@"export PGDATA=/tmp/mypgsql/data ; "%@" stop ", pgctlPath] ;
NSArray *args = [NSArray arrayWithObjects:@"-c",launchCmd,nil] ;
[[NSTask launchedTaskWithLaunchPath:@"/bin/sh" arguments:args] waitUntilExit] ;
(You can also use other ways to launch a task like popen...)