Installing Postgresql On Ubuntu 12.04

Following are the variation for Ubuntu 12.04  from the book beginning PostgreSQL by Neil Mathew

adduser postgres

Creating the Database Directory
Next, you must create, as root, the directory PostgreSQL is going to use for its databases and
change its owner to be postgres:

mkdir /usr/lib/postgresql/9.1/data

chown postgres /usr/lib/postgresql/9.1/data

Here, we are using the default location for the database. You might choose to store the data
in a different location, as we discussed earlier, in the “Anatomy of a PostgreSQL Installation”
section.

Initializing the Database

You initialize the PostgreSQL database by using the initdb utility, specifying where in your file
system you want the database files to reside. This will do several things, including creating the
data structures PostgreSQL needs to run and creating an initial working database, template1.

You need to assume the identity of the postgres user to run the initdb utility. To do this,
the most reliable way is to change your identity in two steps, first becoming root with su and
then becoming postgres as follows. (As a normal user, you may not have permission to assume
another user’s identity, so you must become the superuser first.)
$ su
# su – postgres
pg$
Now the programs you run will assume the rights of the postgres user and will be able to
access the PostgreSQL database files. For clarity, we have shown the shell prompt for commands
executed as the postgres user as pg$.
■Caution Do not be tempted to shortcut the process of using the postgres user and run these programsas root. For security reasons, running server processes as root can be dangerous. If there were a problemwith the process, it could result in an outsider gaining access to your system via the network. For this reason,postmaster will refuse to run as root.
Initialize the database with initdb:

/usr/lib/postgresql/9.1/bin/initdb -D /usr/lib/postgresql/9.1/data

After Success. You can now start the database server using:
/usr/lib/postgresql/9.1/bin/postgres -D /usr/lib/postgresql/9.1/data (didnt worked)
or
/usr/lib/postgresql/9.1/bin/pg_ctl -D /usr/llib/postgresql/9.1/data -l logfile start (worked)

Starting the database server: As root. (from most to least favorite method)
$ service postgresql start
(If the database has not already been initialized with initdb, this will be performed by the command)  (Ref http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html)
Important help worked for me
(PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others.

pgAdmin III is the most popular and feature rich Open Sourceadministration and development platform for PostgreSQL, the most advanced Open Source database in the world. The application may be used on Linux, FreeBSD, OpenSUSE, Solaris, Mac OSX and Windows platforms to manage PostgreSQL 7.3 and above running on any platform, as well as commercial and derived versions of PostgreSQL such as EnterpriseDB, Mammoth PostgreSQL, Bizgres and Greenplum database.
pgAdmin III is designed to answer the needs of all users, from writing simple SQL queries to developing complex databases. The graphical interface supports all PostgreSQL features and makes administration easy. The application also includes a syntax highlighting SQL editor, a server-side code editor, an SQL/batch/shell job scheduling agent, support for the Slony-I replication engine and much more. Server connection may be made using TCP/IP or Unix Domain Sockets (on *nix platforms), and may be SSL encrypted for security. No additional drivers are required to communicate with the database server.
Install Postgresql and pgadmin3 in Ubuntu
PostgreSQL 8.2 version will be installed in Ubuntu 7.10 (Gutsy Gibbon)
sudo apt-get install postgresql-8.2 postgresql-client-8.2 postgresql-contrib-8.2
sudo apt-get install pgadmin3
This will install the database server/client, some extra utility scripts and the pgAdmin GUI application for working with the database.
Configuring postgresql in Ubuntu
Now we need to reset the password for the ‘postgres’ admin account for the server
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 ( from the unprivilged user )
sudo su postgres -c passwd (password is dh^^^^^^^^^)
Now enter the same password that you used previously.
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
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/9.1/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.
sudo gedit /etc/postgresql/9.1/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.2 restart
That’s it you can start using postgresql in Ubuntu
Create a Database from command line
You can also use pgadmin3 for all postgresql related
To create a database with a user that have full rights on the database, use the following command
sudo -u postgres createuser -D -A -P mynewuser
sudo -u postgres createdb -O mynewuser mydatabase

) http://www.ubuntugeek.com/howto-setup-database-server-with-postgresql-and-pgadmin3.html


Here, we will add an entry to allow any computer on the local network (in this case the
subnet 192.168.x.x) to connect to any database with password authentication. (If you require
a different access policy, refer to the comments in the configuration file.) We add a line to the
end of pg_hba.conf that looks like this:
host        all        all        192.168.0.0/16    md5
This means that all computers with an IP address that begins 192.168 can access all databases.
Alternatively, if we trust all of the users on all of the machines in a network, we can allow
unrestricted access by specifying trust as the authentication mechanism, like this:
host        all        all        192.168.0.0/16         trust
The PostgreSQL postmaster server process reads a configuration file, postgresql.conf
(also in the data directory) to set a number of runtime options, including (if not otherwise
specified in a -D option or the PGDATA environment variable) the location of the database data
files. The configuration file is well commented, providing guidance if you need to change any
settings. There is also a section on runtime configuration in the PostgreSQL documentation.
As an example, we can allow the server to listen for network connections by setting the
listen_addresses variable in postgresql.conf, instead of using the now deprecated -i option
to postmaster, as follows:
listen_addresses=’*’
In fact, setting configuration options in postgresql.conf is the recommended approach
for controlling the behavior of the postmaster process.


Starting the postmaster Process
Now you can start the server process itself. Again, you use the -D option to tell postmaster
where the database files are located. If you want to allow users on a network to access your
data, you can specify the -i option to enable remote clients (if you haven’t enabled
listen_addresses in postgresql.conf, as in the preceding example):
/usr/lib/postgresql/9.1/bin/postmaster -i -D /usr/llib/postgresql/9.1/data >logfile 2>&1 & (run from the unprivilged user)

for pg_ctl run
/usr/lib/postgresql/9.1/bin/pg_ctl –version (it would tell us about the version  of the postgresql running)
i was unable to connect pgadmin3 to server then i modifies the pg_hba file to the following contents

# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the “Client Authentication” section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: “local” is a Unix-domain
# socket, “host” is either a plain or SSL-encrypted TCP/IP socket,
# “hostssl” is an SSL-encrypted TCP/IP socket, and “hostnossl” is a
# plain TCP/IP socket.
#
# DATABASE can be “all”, “sameuser”, “samerole”, “replication”, a
# database name, or a comma-separated list thereof. The “all”
# keyword does not match “replication”. Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be “all”, a user name, a group name prefixed with “+”, or a
# comma-separated list thereof.  In both the DATABASE and USER fields
# you can also write a file name prefixed with “@” to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches.  It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask.  A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts.  Instead of a CIDR-address, you
# can write “samehost” to match any of the server’s own IP addresses,
# or “samenet” to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be “trust”, “reject”, “md5”, “password”, “gss”, “sspi”,
# “krb5”, “ident”, “peer”, “pam”, “ldap”, “radius” or “cert”.  Note that
# “password” sends passwords in clear text; “md5” is preferred since
# it sends encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE.  The available options depend on the different
# authentication methods — refer to the “Client Authentication”
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted.  Quoting one of the keywords
# “all”, “sameuser”, “samerole” or “replication” makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can
# use “pg_ctl reload” to do that.

# Put your actual configuration here
# ———————————-
#
# If you want to allow non-local connections, you need to add more
# “host” records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.




# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# “local” is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5



ON 22/10/12 I WAS starting the server using command service postgresql start as root user but it give the error in file /etc/postgresql/9.1/main/postgresql,conf then i changed  contents of this file by coping from the file in email account .
then i  start the server and it started working

Installing PHPpgadmin

use command sudo apt-get install phppgadmin

then configure the file /etc/phppgadmin/config.inc.conf  in that  file look ‘extra_login_security’ =true change the same to false then try to connect postgresql through phppgadmin it would work




Advertisements

One Comment Add yours

  1. This comment has been removed by the author.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s