PostgreSQL

PostgreSQL also called Postgres is an object-relational database management system that focuses on extensibility and standards-compliance.

Installing PostgreSQL

This quick walk-through are my notes for installing the PostgreSQL database server and the PgAdmin administration application on Ubuntu Linux, and also set up the server so it allows access to other PC's on your network.

Right for the basic installation, at the command-line, enter the following commands (or search for the listed packages in synaptic if you prefer that way of working):

$ sudo apt-get install postgresql postgresql-client postgresql-contrib
$ sudo apt-get install pgadmin3

This installs the database server/client, some extra utility scripts and the pgAdmin GUI application for working with the database.

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.

Then, from here on in we can use both pgAdmin and command-line access (as the postgres user) to run the database server. But before you jump into pgAdmin we should set-up the PostgreSQL admin pack that enables better logging and monitoring within pgAdmin. Run the following at the command-line:

$ sudo su postgres -c psql < /usr/share/postgresql/8.3/contrib/adminpack.sql

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 gedit /etc/postgresql/8.3/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

Then save the file and close gedit.

Now for the final step, we must define who can access the server. This is all done using the pg_hba.conf file.1

$ sudo gedit /etc/postgresql/8.3/main/pg_hba.conf

Comment out, or delete the current contents of the file, then add this text to the bottom of the file:

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database
# super user can access the database using some other method.
# Noninteractive
# access to all databases is required during automatic maintenance
# (autovacuum, daily cronjob, replication, and similar tasks).
#
# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser
# 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
host    all         all         [ip address]          [subnet mask]  md5

and in the last line, add in your subnet mask (i.e. 255.255.255.0) and the IP address of the machine that you would like to access your server (i.e. 138.250.192.115). However, if you would like to enable access to a range of IP addresses, just substitute the last number for a zero and all machines within that range will be allowed access (i.e. 138.250.192.0 would allow all machines with an IP address 138.250.192.x to use the database server).

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

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

And all should be working.

Q. I've installed Postgresql. I've created username / password and database. But when I try to connect it via PHP or psql using following syntax:

psql -d myDb -U username -W

It gives me an error that read as follows:

psql: FATAL: Ident authentication failed for user "username"

How do I fix this error?

A. To fix this error open PostgreSQL client authentication configuration file

 /var/lib/pgsql/data/pg_hba.conf :
# vi /var/lib/pgsql/data/pg_hba.conf

This file controls:

  1. Which hosts are allowed to connect
  2. How clients are authenticated
  3. Which PostgreSQL user names they can use
  4. Which databases they can access

By default Postgresql uses IDENT-based authentication. All you have to do is allow username and password based authentication for your network or webserver. IDENT will never allow you to login via -U and -W options. Append following to allow login via localhost only:

local    all    all    trust
host    all    127.0.0.1/32    trust

Save and close the file. Restart Postgresql server:

# service postgresql restart

Now, you should able to login using following command:

$ psql -d myDb -U username -W

Logging into PostgreSQL

To start using PostgreSQL, type the following command:

$ psql -d postgres -U postgres -W

-d is for the database name -U is for the username -W prompts the password

If you the following error message

psql: FATAL: Ident authentication failed for user "username"

edit the pg_hba.conf file and set the following to

local    all    all    ident
host    all    127.0.0.1/32    ident

to

local    all    all    trust
host    all    127.0.0.1/32    trust

Save and close pg_hba.conf and restart PostgreSQL:

 $ sudo service postgresql restart

Managing users and privileges

In PostgreSQL, users own database objects such as tables and can assign privileges on those objects to other users. Database users are conceptually separate from operating system users but they can be linked. Database users are specific to the installation, not an individual database.

Creating Users

To create a user, you can use any of the following methods:

$ psql -U postgres -W
postgres=# CREATE USER name;

or

$ createuser name

or

$ sudo su postgres -c createuser name

Your options may be limited due to the system you are using, operating system account, and your configurations.

Dropping Users

To drop (remove) a user, you have the following options:

$ psql -U postgres -W
postgres=# DROP USER name;

or

$ dropuser name

or

$ sudo su postgres -c dropuser name

Listing Users

To see a list of existing users, type the following

$ psql -U postgres -W
SELECT * FROM pg_user;

without OIDS

All you need to know is that you should choose the option "without OIDS". If you wish to understand why, continue reading. OIDS are used by PostgreSQL's system tables. They refer to tables, types of data, etc. OIDS were created for system tables only, and should not be used for user tables. Unfortunately, some coders started using OIDs as default primary keys. If PostgreSQL removes OIDs, the applications coded by these guys would crash. So for the sake of backward compatibility, there is now an option "without OID".

List all tables names

select relname from pg_stat_user_tables order by relname

PHP code

$sHost = 'localhost'; 
$sDatabase = 'aaaa';
$sUser = 'bbbb';
$sPass = 'cccc';

$conn = pg_connect("host=$sHost dbname=$sDatabase user=$sUser password=$sPassword") or die('unable to connect to database');
$q = 'select relname from pg_stat_user_tables order by relname';
$result = pg_query($conn, $q) or die ("Error in query: $q - ' . pg_last_error($q));        

while ($rw = pg_fetch_array($result)) {
    print $rw[0] . '<br>';
}

Listing tables and column names of PostgreSQL with PHP

$sHost = 'localhost';
$sDatabase = 'aaaa';
$sUser = 'bbbb';
$sPass = 'cccc';

$conn = pg_connect("host=$sHost dbname=$sDatabase user=$sUser password=$sPassword") or die('unable to connect to database');
$q = 'select relname from pg_stat_user_tables order by relname';
$result = pg_query($conn, $q) or die ("Error in query: $q - ' . pg_last_error($q));        

while ($row = pg_fetch_array($result)) {
    print $row[0] . '<br>';
    $q2 = 'select * from ' . $row[0];
    $rs = pg_query($conn, $q2) or die ("Error in query: $q2 - ' . pg_last_error($q2)); 
    $i = 0;
    while ($i < pg_num_fields($rs)) {
        $fn = pg_field_name($rs, $i);
        print $fn . ' ';
        $i++;
    }
    print '<hr>';
}

List column names in postgreSQL

To list column names of a table, simply type

select column_name
from information_schema.columns
where table_name = 'YOUR TABLE NAME'

Visualizing PostgreSQL database

DbSchema is a really cool tool which provides powerful functionalities for visualizing, browsing and running queries. It is free and can be downloaded from http://www.dbschema.com/download.html.

Installing DbSchema on Linux

$ wget http://www.dbschema.com/dbschema.zip
$ unzip dbschema.zip
$ cd DbSchema
$ chmod +x dbschema.sh

To run DbSchema

$ ./dbschema.sh
Technologies: