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.
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
/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.