Getting started with PostgreSQL. ubuntu debian mint

PostgreSQL database server installation and configuration in debian / ubuntu

Introduction :
PostgreSQL is a fully featured object-relational database management system. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects. Some of the features are: ACID transactions, foreign keys, views, sequences, subqueries, triggers, user-defined types and functions, outer joins, multiversion concurrency control. Graphical user interfaces and bindings for many programming languages
are available as well.

Postgresql Database Installation in Debian

If you want to install the PostgreSQL base system as well as a textual clientrun the following command from your shell

apt-get update
apt-get install postgresql-8.3 postgresql-client-8.3 postgresql-contrib-8.3 postgresql-common

This will install a lot of package dependencies as well. The version of PostgreSQL installed should be 8.3.7 on a debian lenny (5.0.1 in my case) system. If you want to check the official documentation click here.

NOTE: After installation if you will try to start postgresql then it may not start if you have not created database cluster.
it is simple to initialize database cluster [It is being used to store databases].

#pg_createcluster 8.3 main –start That will display some information on your screen. once it completes successfully, you will able to login to postgresql database using default way to login as mentioned below.

The only (default) way to log into your newly created database system is to log as root, then to log as user postgres, by issuing

#su postgres
#psql template1
#template1=# \q

However, you would probably like to be able to login as your unix user, or any other user you choose. There are several ways to set up these accesses, so let’s decomposeAlowing local users to login is done by editing the /etc/postgresql/pg_hba.conf file (a complete documentation of this file is available here). There, you have to retrieve a series of lines like the following ones

local all postgres ident sameuser
# All other connections by UNIX sockets
local all all ident sameuser
# All IPv4 connections from localhost
host all all md5
# All IPv6 localhost connections
host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff ident sameuser
host all all ::ffff: ident sameuser
# reject all other connection attempts
host all all reject

This file has limited accesses but we will come back here later.Now what we have told PostgreSQL is that all users on the local machine should be able to access all databases, using their UNIX login and passwords. You need to restart your database to take new changes effect

Restart postgresql using the following command

#/etc/init.d/postgresql restart

Now the problem is that you have authorized users to log into the database, but they will only be able to log in if they also exist in the database. So you need to create accounts with their usernames. This is done by issuing the createuser command as user postgres as follows

#su postgres
#createuser firstuser

The createuser command will ask you if the user should be able to create databases or create new users. Answer truly.

Now you have a new user in the database, and you should be able to connect as this user (let’s call him firstuser) like this:

#su – firstuser
#firstuser@localhost:~$ psql -W template1
#template1=# \q

The -W parameter is to ask for a password prompt. You should enter your system password when asked.
So that’s it, we got the first part of this installation running. Now let’s prepare this for the web.

If some of your web scripts are going to need a connection to the database, you will need to grant them some access via a new user, reserved for this web usage.

Let’s first create this new user (as user postgres). Let’s call him webuser.

#su postgres
#createuser webuser

Now you want to give him access to one or several databases (let’s say you will let him access the databases which name is web). To do this, you need to edit once more the pg_hba.conf file. Retrieve the first line below and write the second one just beneath the first.

host all all md5
host web webuser md5

Would you like to give this user access from any computer on the sub-network, you would have to add the following line

host web webuser md5

You have to grant him access to the host as it will probably use the TCP/IP protocol to get a connection to the database, and that’s what host is for. But as you have given him an md5 authentication type, you need to give him a password as well. In order to do this, you need to connect to the database and issue a special command, all as postgres user:

#su postgres
#postgres@localhost:~$ psql template1
#template1=# alter user webuser password ‘some_password’;
#template1=# \q

Now the user should have a nice ‘some_password’ password that you can use after restarting PostgreSQL to make it remember your changes to pg_hba.conf.

Restart postgresql using the following command

#/etc/init.d/postgresql restart

And you should be able to create his own database if you gave him the permission

#createdb -h localhost -U webuser -W web

And connect to this newly-created database using the following command

#psql -h localhost -U webuser -W web

As you might have noticed, we use -h localhost here. This is to force the PostgreSQL client to use TCP/IP instead of UNIX sockets. If you don’t want is, you need to add a new line into the “local” category of pg_hba.conf for the user webuser to connect via sockets.The line would be something like

local web webuser md5

Using SQL

The first important command you might want to know is

#psql -h localhost -U webuser -W -l

This will give you a list of available databases.

Now connect to the PostgreSQL database using the psql client, and let’s create a small table, just after a few tests

web=> SELECT version();
web=> SELECT current_date;
web=> SELECT 2+2;
web=> \h
web=> \d
web=> \dt
web=> CREATE TABLE weather(
web=> city varchar(80),
web=> temp_lo int, — low temperature
web=> temp_hi int, — high temperature
web=> prcp real, — precipitation
web=> date date
web=> );
web=> INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
web=> VALUES (‘London’, 12, 13, 0.0, ’2005-05-29′);
web=> INSERT INTO weather (date, city, temp_hi, temp_lo)
web=> VALUES (’2005-05-19′, ‘Miltons’, 10, 25);
web=> SELECT city, temp_lo as ‘Lowest Temperature’, temp_hi as ‘Highest Temperature’ FROM weather;
web=> SELECT city FROM weather WHERE temp_lo < 30;
web=> SELECT max(temp_lo) FROM weather;
web=> UPDATE weather SET temp_lo = 18 WHERE city = ‘London’;
web=> SELECT * FROM weather;
web=> DELETE FROM weather WHERE prcp IS NULL;
web=> SELECT * FROM weather;
web=> \q

Now you’ve had a nice overview of several SQL statements and how to use them in PostgreSQL.

Later, you might want to create a table with a numerical sequence as an index. This link will help you get through this step which I find counter-intuitive and user-unfriendly at will. You might as well know of a shortcut. If you want to create a default auto-incremental key that will be used by your table, you need to define (for every table) a SEQUENCE. This id done, for the weather table above, like this (with the help of user comments here):

web=> CREATE SEQUENCE my_table_pk_sequence;
web=> CREATE TABLE weather(
web=> id int not null default nextval(‘my_table_pk_sequence’) PRIMARY KEY,
web=> city varchar(80),
web=> temp_lo int, — low temperature
web=> temp_hi int, — high temperature
web=> prcp real, — precipitation
web=> date date
web=> );

And then insert elements as before, ignoring the id field

web=> INSERT INTO weather (date, city, temp_hi, temp_lo)
web=> VALUES (’2009-06-20′, ‘London’, 14, 27);
web=> SELECT * FROM weather;

This should have inserted an auto-incremented id.

Getting started with Postgresql. Centos Redhat Fedora

How to install PostgreSQL on Centos / Redhat / Fedora.

Hi All, In this article I want to quickly show you specific steps to install PostgreSQL 8 database on CENTOS Linux 5. As we all know that we can use yum to install packages / softwares, I am using yum for this installation. To make sure that you have everything needed do:

#yum list | grep postgresql

Then verify that you see:

postgresql.i386, postgresql-server.i386 and postgres-libs.i386. if you have 64 bit version then you will see x86_64.

Centos installation comes with postgresql-lib installed. If it is not installed then please follow:

#yum install postgresql-libs

Now, the general installation. As root install postgres core and postgresql server:

#yum install postgresql postgresql-server

We need a seperate user as owner of postgresql database owner so create postgres user:

#adduser postgres

A directory to store datafiles caleed the ‘datafile’ for the database:

#mkdir -p /usr/local/pgsql/data

It is necessary to change ownership of the data files to the postgres user:

#chown postgres /usr/local/pgsql/data

now change your role to postgres user from root user:

#su – postgres

Initialize the datafiles for the database:

#/usr/bin/initdb -D /usr/local/pgsql/data

In most of the cases it will starStart the database with initialized datafiles as the background process (&) and log all messages and errors (2&1) in the logfile:

#/usr/bin/postgres -D /usr/local/pgsql/data > logfile 2>&1 &

Create the test database:

#/usr/bin/createdb firstdb

Log in to the test database:

$/usr/bin/psql firstdb
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit


Setup PostgreSQL database (RDBMS) on Centos-Redhat-Fedora.
Install and configure PostgreSQL database (RDBMS) using rpm. (redhat packages)
One of the best open source RDBMS system – PostgreSQL (pgsql)
Powerful yet free RDBMS system with active development – PostgreSQL.