PostgreSQL configuration

I won't lie to you, setting up PostgreSQL on a Linux machine might look simple but it's a pain in the ass when you're not comfortable with using phpPgAdmin (kinda like phpMyadmin for MySQL)

PostgreSQL Installation

If you are on a Linux machine, use your favorite package manager to get the Postgresql package from your distribution

Ubuntu/Debian: apt-get install postgresql

Now we need to reset the password for the ‘postgres’ admin account for the server, so we can use this for all of the system administration tasks. Type the following at the command-line (substitute in the password you want to use for your administrator account):

$ sudo su postgres -c psql template1 template1=# ALTER USER postgres WITH PASSWORD 'password'; template1=# \q

That alters the password for within the database, now we need to do the same for the unix user ‘postgres’:

$ sudo passwd -d postgres $ sudo su postgres -c passwd

Now enter the same password that you used previously.

Finally, we need to open up the server so that we can access and use it remotely - unless you only want to access the database on the local machine. To do this, first, we need to edit the postgresql.conf file:

$ sudo vi /etc/postgresql/8.2/main/postgresql.conf

Now, to edit a couple of lines in the ‘Connections and Authentication’ section…

Change the line:

#listen_addresses = 'localhost' to listen_addresses = '*'

and also change the line:

#password_encryption = on to password_encryption = on

Save the file.

That’s it, now all you have to do is restart the server:

$ sudo /etc/init.d/postgresql-8.2 restart

And all should be working.

PostgreSQL User and Schema Creation

Someone who can try this out please log exact steps for linux:

• Create user opensim.

As the postgres user issue the following commands (note that the "psql" command starts the command line interface to the database):

psql
CREATE USER opensim WITH PASSWORD '*****' CREATEDB;
\q

Obviously, the asterisks need to be replaced by the password.

• Create database opensim with owner opensim.

psql --username opensim
#This may prompt for a password
CREATE DATABASE opensim;
\q

You will probably need to edit the pg_hba.conf and postgresql.conf files depending on your configuration and what network access you want to allow.

Setting up the OpenSim to use Postgresql

For an example how to setup the OpenSim.ini to use Postgresql:

; ##
; ## STORAGE
; ##
 
; *** Prim Storage - only leave one storage_plugin uncommented ***
storage_plugin = "OpenSim.Data.NHibernate.dll" ; for nhibernate
 
; Storage Source NHibernate example (DIALECT;DRIVER;CONNECTSTRING)
storage_connection_string="PostgreSQLDialect;NpgsqlDriver;Server=localhost;Database=opensim;User ID=opensim;Password=opensim;";
 
; Asset database provider
asset_plugin = "OpenSim.Data.NHibernate.dll" ; for nhibernate
 
; the Asset DB source.  This only works for sqlite, mysql, and nhibernate for now
; Asset Source NHibernate example (DIALECT;DRIVER;CONNECTSTRING)
asset_source = "PostgreSQLDialect;NpgsqlDriver;Server=localhost;Database=opensim;User ID=opensim;Password=opensim;";
 
; Inventory database provider
inventory_plugin = "OpenSim.Data.NHibernate.dll" ; for nhibernate
 
; Inventory Source NHibernate example (DIALECT;DRIVER;CONNECTSTRING)
inventory_source = "PostgreSQLDialect;NpgsqlDriver;Server=localhost;Database=opensim;User ID=opensim;Password=opensim;";
 
; User Data Database provider
;
; Multiple providers can be specified by separating them with commas (whitespace is unimportant)
; If multiple providers are specified then if a profile is requested, each is queried until one
; provides a valid profile, or until all providers have been queried.
; Unfortunately the order of querying is currently undefined (it may not be the order in which
; providers are specified here).  This needs to be fixed
;
userDatabase_plugin = "OpenSim.Data.NHibernate.dll" ; for nhibernate
 
; User Source NHibernate Example (DIALECT;DRIVER;CONNECTSTRING)
user_source = "PostgreSQLDialect;NpgsqlDriver;Server=localhost;Database=opensim;User ID=opensim;Password=opensim;";

Running Standalone / Grid

The rest is the normal starting of the OpenSim (standalone) or the OpenSim.Grid.Servers.

[Standalone]
OpenSim.exe
 
[Grid]
OpenSim.Grid.UserServer.exe
OpenSim.Grid.GridServer.exe
OpenSim.Grid.InventoryServer.exe
OpenSim.Grid.AssetServer.exe
OpenSim.Grid.MessagingServer.exe
OpenSim.exe