Difference between revisions of "Postgres SQL"

From Blue-IT.org Wiki

(SuSE 10.1, 10.3)
(SuSE 10.1, 10.3)
Line 82: Line 82:
  
 
=SuSE 10.1, 10.3=
 
=SuSE 10.1, 10.3=
''SuSE oh SuSE ...''
+
''SuSE oh SuSE ... why not just simply as debian, redhat & Co. ... !?''
  
 
First I have to mention that it is almost impossible to get a howto to install ''postgresql'' on SuSE. I really don't now why. Finding an according ''howto'' for ubuntu was a matter of minutes. Not even a search at ''support database of SuSE'' got ANY result. This is really ''poor''. For an example see [http://www.justlinux.com/forum/showthread.php?t=150426 this thread].
 
First I have to mention that it is almost impossible to get a howto to install ''postgresql'' on SuSE. I really don't now why. Finding an according ''howto'' for ubuntu was a matter of minutes. Not even a search at ''support database of SuSE'' got ANY result. This is really ''poor''. For an example see [http://www.justlinux.com/forum/showthread.php?t=150426 this thread].
  
 +
One could draw the conclusion, that NOVELL (former SuSE) has not much experience with ''postgresql'' databases, compared with mysql. This is not very appeasing if you consider using this in a sensible environment. So be warned. You should know what you are doing. Setting up ''postgres'' on a ''debian/ubuntu'' system that support ''postgres'' from the beginning might be a better choice for inexperienced users.
  
==Installation==
+
==Installation of ''postgresql-server''==
===Prior 10.3===
+
This is easily done via yast.
* Via YaST and the previously mentioned ''packman'' repository
 
  
 
==Administration==
 
==Administration==
 
===Pgadmin III===
 
===Pgadmin III===
 
You will find them in the guru repositories:
 
You will find them in the guru repositories:
* prior to opensuse 10.3
+
* Install prior to opensuse 10.3
 
** via YaST and the previously mentioned ''packman'' repository
 
** via YaST and the previously mentioned ''packman'' repository
 
 
* opensuse 10.3 and later
 
* opensuse 10.3 and later
 
**Download the rpm package from the pgadmin site: [http://www.pgadmin.org/download/opensuse.php Direct from pgadmin.org] or [http://www.postgresql.org/ftp/pgadmin3/release/v1.8.0/opensuse/10.3/ pgadmin 10.3]
 
**Download the rpm package from the pgadmin site: [http://www.pgadmin.org/download/opensuse.php Direct from pgadmin.org] or [http://www.postgresql.org/ftp/pgadmin3/release/v1.8.0/opensuse/10.3/ pgadmin 10.3]
 +
  
 
===Post Installation===
 
===Post Installation===
Line 106: Line 106:
 
Login as user ''postgres'' and alter the table. Instead of using the ''template1'' database like in ''ubuntu/debian''
 
Login as user ''postgres'' and alter the table. Instead of using the ''template1'' database like in ''ubuntu/debian''
 
  su postgres -c psql postgres
 
  su postgres -c psql postgres
  postgres=# ALTER USER postgres WITH PASSWORD 'new_password';
+
  postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
 
  ALTER ROLE
 
  ALTER ROLE
 
  postgres=# \q
 
  postgres=# \q
  
 
===Configuration files===
 
===Configuration files===
* all configuration files are in
+
Confusing:
  cd /usr/share/postgresql
+
* all ''configuration files'' are in
 +
  /var/lib/pgsql/share
 +
and owned by ''postgres:postgres''.
  
You have to copy the ''/usr/share/postgresql/*.sample'' files to ''.conf'' and edit them according to your needs.
+
* all ''sample'' files are in
cp pg_hba.conf.sample pg_hba.conf
+
/usr/share/postgresql
cp postgresql.conf.sample postgresql.conf
+
and owned by ''root:root''.
  
 +
Solution
 +
You have to copy the ''/usr/share/postgresql/*.sample'' files to ''.conf'' and alter their permissions/owner:
 +
cd /var/lib/pgsql/share
 +
cp /usr/share/postgresql/pg_hba.conf.sample pg_hba.conf
 +
cp /usr/share/postgresql/postgresql.conf.sample postgresql.conf
 +
chown postgres:postgres pg_hba.conf postgresql.conf
 +
chmod 600 postgresql.conf pg_hba.conf postgresql.conf
  
 
I edited the '''pg_hba.conf''' (the security settings) according to a debian/ubuntu setup. Delete everything in the original file and put in this content:
 
I edited the '''pg_hba.conf''' (the security settings) according to a debian/ubuntu setup. Delete everything in the original file and put in this content:
# Database administrative login by UNIX sockets
+
  # TYPE  DATABASE       USER   IP-ADDRESS     IP-MASK        METHOD
local  all        postgres                          ident sameuser
+
  local  all             all                                     trust
 
  # TYPE  DATABASE    USER        CIDR-ADDRESS         METHOD
 
 
# "local" is for Unix domain socket connections only
 
  local  all         all                               ident sameuser
 
# IPv4 local connections:
 
host    all        all        127.0.0.1/32          md5
 
# IPv6 local connections:
 
host    all        all        ::1/128              md5
 
 
   
 
   
  # Allow Connections for certain PCs on a subnet.
+
  # Connections for all PCs on the subnet
# Alter this according to your own hosts/network configuration.
 
#
 
 
  # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
 
  # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
  host   all     all     192.168.0.200  255.255.255.0   md5
+
  #host all all 192.168.0.0/24 trust
  host    all    all    192.168.0.201  255.255.255.0    md5
+
  host    all    all    127.0.0.1      255.255.255.255 trust
  host    all    all    192.168.0.203   255.255.255.0    md5
+
  host    all    all    192.168.0.100   255.255.255.255 trust
  
 
For the postgres server settings file '''postgresql.conf''' alter the file according to the following settings:
 
For the postgres server settings file '''postgresql.conf''' alter the file according to the following settings:
 
  [...]
 
  [...]
  data_directory = '/usr/share/postgresql/'
+
  # CHANGE TO YOUR IP, comma separeted list ...
hba_file = 'ConfigDir/pg_hba.conf'
+
  listen_addresses = 'localhost,192.168.0.1'
ident_file = 'ConfigDir/pg_ident.conf'
 
 
  listen_addresses = 'localhost,192.168.0.1'   # CHANGE TO YOUR IP
 
 
  port = 5432
 
  port = 5432
 
  max_connections = 100
 
  max_connections = 100
  ssl = on
+
  #ssl = on                   # SERVER DOES NOT START WITH 'ON'
 
  password_encryption = on
 
  password_encryption = on
 
  shared_buffers = 24MB
 
  shared_buffers = 24MB
 +
 +
log_destination = 'stderr'
 +
redirect_stderr = on
 +
#silent_mode = on            # Please UNCOMMENT !!!
 +
log_line_prefix = '%t %d %u '
 
   
 
   
 
  stats_row_level = on
 
  stats_row_level = on

Revision as of 18:52, 10 November 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:

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

User Management

General Dokumentation

Ubuntu Feisty, Gutsy

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 and server settings. If you like to use the server in a network you have to pay attention to three points:

  1. Add the server ip to the listen adress
  2. Allow special clients to connect
  3. Think about using ssl, if you connect through the internet
sudo gedit /etc/postgresql/8.2/main/pg_hba.conf
# - Connection Settings -
# what IP address(es) to listen on; 
# comma-separated list of addresses;
listen_addresses = 'localhost,server_IP' 
 
# 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

SuSE 10.1, 10.3

SuSE oh SuSE ... why not just simply as debian, redhat & Co. ... !?

First I have to mention that it is almost impossible to get a howto to install postgresql on SuSE. I really don't now why. Finding an according howto for ubuntu was a matter of minutes. Not even a search at support database of SuSE got ANY result. This is really poor. For an example see this thread.

One could draw the conclusion, that NOVELL (former SuSE) has not much experience with postgresql databases, compared with mysql. This is not very appeasing if you consider using this in a sensible environment. So be warned. You should know what you are doing. Setting up postgres on a debian/ubuntu system that support postgres from the beginning might be a better choice for inexperienced users.

Installation of postgresql-server

This is easily done via yast.

Administration

Pgadmin III

You will find them in the guru repositories:

  • Install prior to opensuse 10.3
    • via YaST and the previously mentioned packman repository
  • opensuse 10.3 and later


Post Installation

Start the server for the first time:

rcpostgresql start

Login as user postgres and alter the table. Instead of using the template1 database like in ubuntu/debian

su postgres -c psql postgres
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
ALTER ROLE
postgres=# \q

Configuration files

Confusing:

  • all configuration files are in
/var/lib/pgsql/share

and owned by postgres:postgres.

  • all sample files are in
/usr/share/postgresql

and owned by root:root.

Solution You have to copy the /usr/share/postgresql/*.sample files to .conf and alter their permissions/owner:

cd /var/lib/pgsql/share
cp /usr/share/postgresql/pg_hba.conf.sample pg_hba.conf
cp /usr/share/postgresql/postgresql.conf.sample postgresql.conf
chown postgres:postgres pg_hba.conf postgresql.conf
chmod 600 postgresql.conf pg_hba.conf postgresql.conf

I edited the pg_hba.conf (the security settings) according to a debian/ubuntu setup. Delete everything in the original file and put in this content:

# TYPE  DATABASE        USER    IP-ADDRESS      IP-MASK         METHOD
local   all             all                                     trust

# Connections for all PCs on the subnet
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
#host all all 192.168.0.0/24 trust
host    all     all     127.0.0.1       255.255.255.255 trust
host    all     all     192.168.0.100   255.255.255.255 trust

For the postgres server settings file postgresql.conf alter the file according to the following settings:

[...]
# CHANGE TO YOUR IP, comma separeted list ...
listen_addresses = 'localhost,192.168.0.1'
port = 5432
max_connections = 100
#ssl = on                    # SERVER DOES NOT START WITH 'ON'
password_encryption = on
shared_buffers = 24MB
log_destination = 'stderr'
redirect_stderr = on
#silent_mode = on            # Please UNCOMMENT !!!
log_line_prefix = '%t %d %u '

stats_row_level = on
autovacuum = on
default_transaction_read_only = off

datestyle = 'iso, dmy'
lc_messages = 'de_DE.UTF-8'
lc_monetary = 'de_DE.UTF-8'
lc_numeric = 'de_DE.UTF-8'
lc_time = 'de_DE.UTF-8'

Administration with Pgadmin III

Pgadmin helps you with almost all administration tasks to manage your postgresql databases. From managing user accounts (roles), creating tables, schemes, constraints (e.g. primary keys) to an extended sql editor.

Initial registration with pgAdminIII

To be able to manage your database with pgadmin you have to configure it for the specific server:

Working with pgAdminIII

Queries with pgadminIII

A very good tool for working with the database is the integrated sql query editor of pgadmin. Select the database you want to work with, then you can choose the query tool from the menu.

PostgreSQL queries are case sensitive. So if you specified Uppercase names for columns you have to use them exactly and in quotes. It might be necessary to add the schemata name postfixed by a dot to get a proper query:

select "Prename" from myscheme."Client";

Press <F5> to start the query.

You can easily save the queries in a file for later reuse.

Backup and restore with pgadminIII

PgadminIII makes it easy to backup your database withor without all the data. Just highlite the database you like to backup and choose the backup option from the main menu.

ODBC, Postgres and Openoffice

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

Ubuntu Gutsy (7.10)

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.

But for nowe, we will - later - edit the configuration files by ourself.

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

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

Establish connection

If you like to connect to an database via openoffice you should use a string like:

odbc://servername/databasename

Don't forget to allow connections in /etc/postgresql/8.2/main/pg_hba.conf (see above) to your database server!

The rest is really self explanatory within the openoffice database assistant ;)