Recent change in mysql make my friend contact me. He want to learn and practice about postgresql before use it on real cpanel host that support postgresql . I agree to set new server for him to play around. The  server itself basically consists of apache, php, postgresql and phppgadmin on FreeBSD 8.1 stable.

Here the steps :

Which Version of Postgresql

I also have same hosting account that my friend use. I log in to cpanel then click phppgadmin to see postgresql version.

Ok. I can see that Postgresql 8.1.22 installed on that host.

Install Postgresql from ports

I choose to install postgresql using ports

#cd /usr/ports/databases/postgresql81-server/ && make install

Let the option as is.

Configure Postgresql

Start up option

Add postgresql_enable=”YES” in /etc/rc.conf or using echo.

#echo ‘postgresql_enable=”YES”‘ >> /etc/rc.conf

Initialize

# /usr/local/etc/rc.d/postgresql initdb

The files belonging to this database system will be owned by user “pgsql”.
This user must also own the server process.

The database cluster will be initialized with locale C.

creating directory /usr/local/pgsql/data … ok
creating directory /usr/local/pgsql/data/global … ok
creating directory /usr/local/pgsql/data/pg_xlog … ok
creating directory /usr/local/pgsql/data/pg_xlog/archive_status … ok
creating directory /usr/local/pgsql/data/pg_clog … ok
creating directory /usr/local/pgsql/data/pg_subtrans … ok
creating directory /usr/local/pgsql/data/pg_twophase … ok
creating directory /usr/local/pgsql/data/pg_multixact/members … ok
creating directory /usr/local/pgsql/data/pg_multixact/offsets … ok
creating directory /usr/local/pgsql/data/base … ok
creating directory /usr/local/pgsql/data/base/1 … ok
creating directory /usr/local/pgsql/data/pg_tblspc … ok
selecting default max_connections … 40
selecting default shared_buffers … 1000
creating configuration files … ok
creating template1 database in /usr/local/pgsql/data/base/1 … ok
initializing pg_authid … ok

enabling unlimited row size for system tables … ok
initializing dependencies … ok
creating system views … ok
loading pg_description … ok
creating conversions … ok
setting privileges on built-in objects … ok
creating information schema … ok
vacuuming database template1 … ok
copying template1 to template0 … ok
copying template1 to postgres … ok

WARNING: enabling “trust” authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

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

#

Run postgresql service

# /usr/local/etc/rc.d/postgresql start

Check the service

# ps ax | grep postgres

88563  ??  Is     0:00.24 /usr/local/bin/postmaster -D /usr/local/pgsql/data (postgres)
88565  ??  S      0:00.03 postmaster: writer process    (postgres)
88566  ??  S      0:00.01 postmaster: stats buffer process    (postgres)
88567  ??  I      0:00.01 postmaster: stats collector process    (postgres)

Ok, its time to add super user for postgresql

#su pgsql

$ createuser -sdrP alam

Enter password for new role:
Enter it again:
CREATE ROLE
$

Tips : use man createuser after su pgsql for -sdrP expalanation.

I enter user : alam and password : 123456

Install phppgadmin

Time for phppgadmin

#cd /usr/ports/databases/phppgadmin && make install

create symbolic link

# ln -sf /usr/local/www/phpPgAdmin /usr/local/www/data/phppgadmin

Visit phppgadmin page

Click on PostGreSQL link and enter login information

Done. Postgresql 8.1.22 successfully installed and ready to use.

Tips :

– To allow many simultaneous connections to your PostgreSQL server, you
should raise the SystemV shared memory limits in your kernel. Here are
example values for allowing up to 180 clients (configurations in
postgresql.conf also needed, of course):
options         SYSVSHM
options         SYSVSEM
options         SYSVMSG
options         SHMMAXPGS=65536
options         SEMMNI=40
options         SEMMNS=240
options         SEMUME=40
options         SEMMNU=120

– If you plan to access your PostgreSQL server using ODBC, please
consider running the SQL script /usr/local/share/postgresql/odbc.sql
to get the functions required for ODBC compliance.

– Please note that if you use the rc script,
/usr/local/etc/rc.d/postgresql, to initialize the database, unicode
(UTF-8) will be used to store character data by default.  Set
postgresql_initdb_flags or use login.conf settings described below to
alter this behaviour. See the start rc script for more info.

– Data location by default will be located in /usr/local/pgsql/data

Check pg_hba.conf and postgresql.conf for more custom setting of postgresql (ie: password policy, access policy etc).