Sharpe's PostgreSQL Blog

How to access WinStrom database directly

2009-12-20T17:19:00.000-08:00

WinStrom is software for book-keeping, currently supporting Czech and partially Slovak accounting rules. It is working not only on Windows, but also on Linux and Mac OS X.

It uses PostgreSQL for data storage. Sometimes you could need to access its database directly, not via WinStrom application. Here is how to accomplish this task when using WinStrom version 10.

I found access credentials in these configuration files:
/etc/winstrom/winstrom-server.xml
~/.winstrom/settings.xml

Here are settings for pgAdmin III:

Your specific maintenance DB (database name, in my case system_business__s__r__o_) you can get from this part of settings.xml:

     company-127.0.0.1:5434
    system_business__s__r__o_

Other values are from winstrom-server.xml:

WinStrom server configuration
localhost
5435
dba
Password

Alternatively, you can connect using text-based console (psql):
ostry@elitebook:~$ psql -h 127.0.0.1 -p 5435 -U dba -W -d system_business__s__r__o_
Password for user dba:
psql (8.4.1)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
system_business__s__r__o_=#

Success!

Now you are able to edit also fields, which are not writeable by WinStrom application itself, e.g. "datvyst" field of "dpolobch" table (dates of ordered items).

How to install PostgreSQL module for Python

2009-09-06T07:38:00.000-07:00

How to install psycopg2?

On Ubuntu Linux (9.04):
sudo apt-get install python-psycopg2

On Mac OS X (Leopard) - valid for PostgreSQL 8.4:
export PATH="/Library/PostgreSQL/8.4/bin:$PATH"
sudo easy_install -U setuptools
sudo easy_install psycopg2

In case of problems, install PostgreSQL first. Don't know how? Don't worry, it is pretty straightforward. For example on Linux, but also on Mac OS X or Windows.

How to install and configure PostgreSQL server on Ubuntu Linux 9.04

2009-09-05T05:32:00.000-07:00

Installation

sudo apt-get install postgresql

Create database

sudo su postgres
psql
postgres=# CREATE DATABASE dsbis_db WITH ENCODING 'utf-8';

where dsbis_db is database name.

Create database user

postgres=# CREATE ROLE dsbis_db_user LOGIN PASSWORD 'ojweod30';
postgres=# ALTER DATABASE dsbis_db OWNER TO dsbis_db_user;

Configure host-based authorization

If you need e.g. allow remote access to this database, you need to edit this configuration file: /etc/postgresql/8.3/main/pg_hba.conf.

To be able to connect locally with your newly added database user, add this line:
local all all md5
possibly as replacement of this line:
local all all ident sameuser
if you do not want to allow connecting to database to all local users of your Linux system.

Restart database server

sudo /etc/init.d/postgresql-8.3 restart

NOTE: Now, you can set your newly created database login/password in your application settings and it should work. But remember, that if you application resides on another host, probably you will need to allow connections from it in pg_hba.conf (see section above, about host baset authorization).