Install PostgreSQL in UBUNTU

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, M ac 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 TIM ESTAMP. 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 Source administration and development platform for PostgreSQL, the
most advanced Open Source 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, M ammoth 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. 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
Howto setup Database Server With postgresql and pgadmin3 -- 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 /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
sudo su postgres -c passwd
Now enter the same password that you used previously.
from here on in we can use both pgAdmin and command-line (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/8.2/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/8.2/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 M ETHOD
# “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-M ASK M ETHOD
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