How to install PostgreSQL 9.1.3 with pgAdmin3 on Ubuntu 11.10 / Linux Mint 12

ADVERTISEMENT

PostgreSQL is an object-relational database management system (ORDBMS) available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of community volunteers employed and supervised by companies such as Red Hat and EnterpriseDB. It implements the majority of the SQL:2008 standard, is ACID-compliant, is fully transactional (including all DDL statements), has extensible data types, operators, and indexes, and has a large number of extensions written by third parties.

The vast majority of Linux distributions have PostgreSQL available in supplied packages. Mac OS X starting with Lion has PostgreSQL server as its standard default database in the server edition and PostgreSQL client tools in the desktop edition.

ADVERTISEMENT
Article will continue after the ad

New features in PostgreSQL 9.1:

PostgreSQL moving beyond the traditional relational-database feature set with new, ground-breaking functionality that is unique to PostgreSQL. The streaming replication feature introduced in release 9.0 is significantly enhanced by adding a synchronous-replication option, streaming backups, and monitoring improvements. Major enhancements include:

  • Allow synchronous replication
  • Add support for foreign tables
  • Add per-column collation support
  • Add extensions which simplify packaging of additions to PostgreSQL
  • Add a true seriaizable isolation level
  • Support unlogged tables using the UNLOGGED option in CREATE TABLE
  • Allow data-modification commands (INSERT/UPDATE/DELETE) in WITH clauses
  • Add nearest-neighbor (order-by-operator) searching to GiST indexes
  • Add a SECURITY LABEL command and support for SELinux permissions control
  • Update the PL/Python server-side language

Here is the tutorial about installing PostgreSQL 9.1.3 and pgAdmin on Linux Mint 12 / Ubuntu 11.10.

Open Terminal ( Ctrl + Alt + T).

Installing PosgreSQL 9.1.3:

By default the PostgreSQL packages are available  in repository; please issue the following command to install.

raj@geeksite~/$ sudo apt-get install postgresql postgresql-contrib

Configuring PostgreSQL 9.1.3 server:

PostgreSQL normally listens on the localhosts only, if would you like to enable the PostgreSQL to listen on all ip addresses; edit the /etc/postgresql/9.1/main/postgresql.conf.

raj@geeksite~/$ sudo gedit /etc/postgresql/9.1/main/postgresql.conf

Go to Connections and Communications section, find the “listen_address” variable. Uncomment the “listen_addresses” and place “*” instead of “localhost”

Before editing:

#listen_addresses = "localhost"

After editing:

listen_addresses = "*"

Add your network to access database remotely; Edit /etc/postgresql/9.1/main/pg_hba.conf.

raj@geeksite~/$ sudo gedit /etc/postgresql/9.1/main/pg_hba.conf

Add the following line according to your network configuration with md5 password authentication ((Enable remote access of database).

# Local networks
host	all	all	xx.xx.xx.xx/xx	md5
# Example
host	all	all	192.168.0.0/24	md5
host    all     all     127.0.0.0/32    md5

Restart the PostgreSQL server.

raj@geeksite~/$ sudo /etc/init.d/postgresql restart

Confirm the PostgreSQL listening.

raj@geekSite ~ $ netstat -ant | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
tcp6       0      0 :::5432                 :::*                    LISTEN

Creating Database:

Login as postgres user.

raj@geeksite~/$ su postgres

create the database called “test”

postgres@geeksite~/$ createdb test

Login into the database.

postgres@geeksite~/$ psql test

Create a new user called “raj” to manage the databases.

test=# CREATE USER raj WITH SUPERUSER LOGIN PASSWORD 'raj';

Login with the superuser.

sam@geeksite~/$ psql -h geekdbserver -d test -U raj

The command line administration will be good for the people who has full experience on PostgreSQL, but for the beginner pgAdmin will be best option to manage the databases.

Installing pgAdmin:

pgAdmin3 stable does not supports all the features of PostgreSQL 9.0 administration, to get the full features of administration; you might requires to install pgadmin3 beta packages from the ppa repository.

To add repository, edit the /etc/apt/sources.list file.

raj@geeksite:~$ sudo gedit /etc/apt/sources.list

Add the following line at the end of the repository list.

deb http://ppa.launchpad.net/rhonda/pgadmin3/ubuntu natty main

Update the system repository.

raj@geeksite:~$  sudo apt-get update

Install pgadmin3 beta ( Note: In Linux Mint 12, you might require to manually verify the signature and have to press Y for the installation).

raj@geeksite:~$  sudo apt-get install pgadmin3

Start pgAdmin3.

raj@geeksite:~$ pgadmin3

Connect to the database server using pgAdmin.

Screen shot of the pgAdmin after connected to the PosrgreSQL server.

Now you can manage the databases through the GUI-administration console.

That’s all!.

ADVERTISEMENT

POSTS YOU MAY LIKE -:)

Share This Post

  • Tom Anderson

    Take a look on a free tool Valentina Studio, is the best way to transform your data into meaningful information; create, administer, query and explore Valentina DB, MySQL, Postgre and SQLite databases for free on 32/64 bit Windows, Linux and Mac OS X. http://www.valentina-db.com/en/valentina-studio-overview

Shares