PostgreSQL

1. The package must be loaded into the workspace
install.packages("RPostgreSQL")
library(RPostgreSQL)

2. A driver must be loaded
drv <- dbDriver("PostgreSQL")

3. Create a connection to a database
con <- dbConnect(drv, host="localhost",port="5432", user="postgres", dbname="test", password="blub")

4. Submits a statement (this can be also a insert, drop or update SQL statment)
rs <- dbSendQuery(con, "select * from Test")

5. Fetch all elements from the result set
fetch(rs,n=-1)

## Closes the connection
dbDisconnect(con)
## Frees all the resources on the driver
dbUnloadDriver(drv)

1. download and install the odbc driver for the postgresql database on your client from http://www.postgresql.org/ftp/odbc/versions/. The msi directory is for windows users and the src directory for *nix users.

2. set up a odbc dsn entry on the client computer. Here is a link for windows users http://support.microsoft.com/kb/305599. And here is a link for ubuntu users https://help.ubuntu.com/community/ODBC

3. Install RODBC for R. RODBC is a library which provides the necessary functions to connect to a database and modify data.

install.packages("RODBC");


4. Connecting to a odbc dsn entry is quite simple:

channel <- odbcConnect("odbc_dns_entry_name");


5. Insert Data as SQL Statement:

odbcQuery(channel,"insert ...");


6. Selecting data from a table:

sqlFetch(channel, "table_name");


Further information of the RODBC functionalities could be found here

1. set ssl to on in postgresql.conf.

2. creating a Self-Signed Certificate:

openssl req -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt
chmod og-rwx server.key

This will create a server.key certificate which not need a password in order to start up.

3. copy server.key to the server's data directory

source
developer.postgresql.org/pgdocs/postgres/ssl-tcp.html

The solution we found where file system level backups, they are faster by a factor of 5 to 10.

These have two restrictions:

• The database server must be shut down while performing the backup. This is no problem as the database is only needed during office hours, so shutting down the server at 22:00, performing the backup, and restarting the server is no problem.
• Only full backups and restorations are possible - partially restoring data does not work. - Not a problem as there is little daily activity except documents being added. The files themselves are very important (health insurance data), but if half a day is lost the files can be added again the next day and nothing important is lost.

This method can be extended by creating incremental backups (rsync is your friend) or creating snapshots, if the operating system permits it (ZFS has been reported to work well).