Initial setup and install of PostgreSQL Database and connecting using pgadmin

PostgreSQL is the worlds most advanced open source relational database. PgSQL can be used as a standard database in its own right or can be used to store the data from a number of applications.
In this post I will explain how to install and configure PgSQL. Then go onto to explain how to connect to the data using PGAdmin.


Installing PostgreSQL:

 1. Download source code from http://www.postgresql.org/ftp/source/
2. Extract the downloaded contents: tar -xzvf postgresql-12... .tar.gz
3. ./configure , make, make install. (this may have to be sudo..)
4. create a postgres user : adduser postgres
5. log in as postgres user and initialise the database 
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data


This will initilise the database then you can start it any time using:


/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
or
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l start

6. From in the bin, file create Database: ./createdb test


7. Access Database ./psql test

Use sql commands to add tables / data etc.

\q to quit

You now have a working database you can access.


Access the database from PgAdmin

To access the databases you have from a gui where you can see how the tables are structured and the data within them, pgadmin is a great opensource management tool.

1. Install pgadmin on your local machine by your preferred method, either source or packages. Source downloadable at  http://www.pgadmin.org/download/source.php .
app-get is also an easy install.

2. From the server side configure pg_hba.conf : add trusted hosts. The easiest thing to do is trust local host
host    all         all         192.168.0.0/24        md5


3. Then from your local machine port forward to your server


ssh postgres@serverIP -L 5432:localhost:5432

4. Launch pgadmin, add server. Connect to local host, rather than server IP

5. Manage all postgres databases / tables / data when connected.