Difference between revisions of "Postgres SQL"

From Blue-IT.org Wiki

(ODBC, Postgres and Openoffice)
(Ubuntu Gutsy - not working)
Line 72: Line 72:
 
According to [http://de.openoffice.org/doc/howto_2_0/base/ooopsql.html PostgreSQL-Datenbanken in OpenOffice.org 2.0 unter Linux].
 
According to [http://de.openoffice.org/doc/howto_2_0/base/ooopsql.html PostgreSQL-Datenbanken in OpenOffice.org 2.0 unter Linux].
  
==Ubuntu Gutsy - not working==
+
==Ubuntu Gutsy (7.10)==
 
'''Not possible until now --[[User:Apos|Apos]] 17:32, 28 September 2007 (CEST)'''.
 
'''Not possible until now --[[User:Apos|Apos]] 17:32, 28 September 2007 (CEST)'''.
  
Line 89: Line 89:
 
     unixodbc-bin - Graphical tools for ODBC management and browsing
 
     unixodbc-bin - Graphical tools for ODBC management and browsing
 
   
 
   
  apt-get install unixodbc
+
  apt-get install unixodbc unixodbc-bin
 +
 
 +
With the command ''ODBCConfig'' you can manage the configuration files (part of the package unixodbc-bin). Start it as superuser with ''gksu'' or use it to manage your local database configuration files - stored in your home directory.
  
 
===2. ODBC <-> Postgresql===
 
===2. ODBC <-> Postgresql===

Revision as of 11:37, 29 September 2007

General

This guide is tested on ubuntu feisty with postgresql 8.2. It should work for former and future versions of both too.

General documentation you will find here: postgresql.org - interactive (!) manual with comments.

You should read this in advance to make shure the concepts behind postgresql configuration, startup and security (e.g. 'roles).

User Management

See postgresql-org - user management.

Ubuntu Feisty

Thanks to the article on hocuspok.us, it was very easy to set up postgresql-8.2 running on ubuntu.

Installation

Install postgresql

sudo apt-get install postgresql-8.2 postgresql-client-8.2

For administration we install pgadmin3

sudo apt-get install pgadmin3 pgadmin3-data

Set database admin account

Alter the password for the standard postgres user account (as root)

sudo su
postgres -c psql template1

Inside the database:

template1=# ALTER USER postgres WITH PASSWORD 'new_password';
template1=# \q

/etc/postgresql/8.2/main/postgresql.conf

Assure that the server is accessible on localhost

sudo gedit /etc/postgresql/8.2/main/postgresql.conf
listen_addresses = 'localhost'
password_encryption = on

/etc/postgresql/8.2/main/pg_hba.conf

Security settings

sudo gedit /etc/postgresql/8.2/main/pg_hba.conf
# Allow any user on the local system to connect to any database under
# any user name using Unix-domain sockets (the default for local
# connections).
#
# Database administrative login by UNIX sockets
local all all trust

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all md5

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

# Connections for all PCs on the subnet
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# e.g. host all all 192.168.0.0/24 md5
# e.g. host all all 192.168.0.10 255.255.255.0 md5
host all all [ip address] [subnet mask] md5

Restart postgresql

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

Initial registration with pgAdminIII

Working with pgAdminIII

ODBC, Postgres and Openoffice

According to PostgreSQL-Datenbanken in OpenOffice.org 2.0 unter Linux.

Ubuntu Gutsy (7.10)

Not possible until now --Apos 17:32, 28 September 2007 (CEST).

See the TODO 's at the end of this section.

  1. libodbcpsql.so is not installed
  2. Cannot compile unixodbc manually due to compile error

Don't read further.


1. Openoffice <-> ODBC

For connection between openoffice and odebc:

apt-cache search odbc | grep unix
   unixodbc - ODBC tools libraries
   unixodbc-bin - Graphical tools for ODBC management and browsing

apt-get install unixodbc unixodbc-bin

With the command ODBCConfig you can manage the configuration files (part of the package unixodbc-bin). Start it as superuser with gksu or use it to manage your local database configuration files - stored in your home directory.

2. ODBC <-> Postgresql

For connection between the odbc layer and the database:

apt-cache search odbc
   odbc-postgresql - ODBC driver for PostgreSQL

apt-get install odbc-postgresql

3. Edit the configuration files

Ubuntu installs its odbc libraries in /usr/lib/odbc. There are two versions of the postgres odbc driver: an ascii version (psqlodbca.so) and a unicode version (psqlodbcw.so). We are using the latter.

vim /etc/odbcinst.ini
[PostgreSQL]
Description = PostgreSQL ODBC Driver
Driver = /usr/lib/odbc/psqlodbcw.so
Setup = /usr/lib/odbc/libodbcpsqlS.so

For system wide database location you have to edit the /etc/odbc.ini file.

There is a section Driver. You have to insert exactly the same name as defined in the odbcinst.ini between the braces, e.g. use PostgreSQL, if your odbcinst.ini is defined as [PostgreSQL]:

vim /etc/odbc.ini
[pgTestDBSource]
Description = PostgreSQL Test Database
Driver = PostgreSQL
Servername = localhost
Database = pgtest
Port = 5432
ReadOnly = No