Postgres SQL
From Blue-IT.org Wiki
Contents
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
ODBC, Postgres and Openoffice
According to PostgreSQL-Datenbanken in OpenOffice.org 2.0 unter Linux.
Ubuntu Gutsy - not working
Not possible until now --Apos 17:32, 28 September 2007 (CEST).
See the TODO 's at the end of this section.
- libodbcpsql.so is not installed
- 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
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