Postgresql or postgres is open-source object relational database system developed by PostgreSQL Global Development Group. 15 years active development, and until now has proven for the intregity, reliabelity and ability as the database server. It is powerfull and is very suitable for the use of large-scale database, support for all major operating system, including Linux, Unix, Mac OS X and Windows.
In this tutorial we will guide you about postgresql installation and configuration on Unix operating system FreeBSD 10.2. And then will install a phppgadmin as the premier web-based administration tool for postgresql that required apache and php.
- FreeBSD 10.2 - 64bit
- Root privileges
Step 1 - Update the Repository Database
Log in to the freebsd server with the ssh credential, and the gain the sudo/root privileges with command "sudo su", and update the repository :
Step 2 - Install PostgreSQL Database
In this tutorial we will install all the package from the freebsd repository with pkg command, run pkg command as sudo user to install postgresql database :
pkg install postgresql93-server postgresql93-client
If the installation finished, next add postgresql to start at the booti time with sysrc command :
Next, run command 'initdb' to initialize the database :
service postgresql initdb
And if you want the database postgresql to be accessed from the network, you must edit the postgresql.conf on pssql directory. Go to the postgresql configuration directory and edit the file with nano command.
nano -c postgresql.conf
Uncomment the listen address on line 59 and change the value to "*".
listen_addresses = '*'
On the line 63, uncomment the port option to enable postgresql listening on that port :
port = 5432
Save and exit.
And now start the postgresql with service command :
service postgresql start
Verify that postgresql is running on port 5432 with 'sockstat' command below :
sockstat -l4 | grep post
pgsql postgres 19966 4 tcp4 *:5432 *:*
Step 3 - Configure User for PostgreSQL
Once the postgresql installation, it will create new user called 'pgsql', and in this step we need to change the password for 'pgsql' that used for log in to the pgsql shell/environment.
Run command below as root/sudo privileges :
New Password: INPUT YOUR PASSWORD HERE
And try to log in to the pgsql shell with command :
NOTE : If you run that command as roo/sudo privileges, you will not be ask for 'pgsql' password, but if you run it on the normal user, you will be ask a password for 'pgsql' user.
A password for pgsql user has changed, and the next step is to create new user and new database for postgresql. This user used to log in to the postgresql databse from the phppgadmin web based tool.
Log in to the pgsql user shell/environment :
And create new user 'imnewuser' and create new databse 'newdb' with command below :
createuser -sdrP imnewuser
createdb -O imnewuser newdb
- -s : role will be superuser.
- -d : role can create new database.
- -r : role can create new roles.
- -P : assign a password for new role.
- -O : database user to own the new database.
New user and database is created, this user will be use for the next step to log in to the phppgadmin.
You can test with command below :
Step 4 - Install Apache and PHP
PhpPgAdmin is php application, web based administrative tool for postgresql, so we need this up and accessible from the browser, and we need apache and php for it.
Install apache from freebsd repository with pkg command :
pkg install apache24
Go to the apache configuration directory and edit the file 'httpd.conf' :
nano -c httpd.conf
Uncomment the ServerName on line 219, and chage it to localhost IP :
Save and exit.
And now add the apache to start at boot time, then start it :
service apache24 start
Apache Installation is done.
Next install php56 with pkg command :
pkg install php56 mod_php56 php56-curl php56-session php56-pgsql
And now you must configure php to work with apache by adding the php configuration to the apache configuration file 'httpd.conf'.
Go to the apache apache configuration directory and edit httpd.conf :
nano -c httpd.conf
Add php configuration below under line 288 :
And add the index.php on line 278 :
DirectoryIndex index.php index.html
Save and exit.
Next step is just copying the php.ini file on '/usr/local/etc/' directory :
cp php.ini-production php.ini
And restart the apache :
service apache24 restart
Apache and php is configured, and you can verify it by creating new php file on the web root directory. Go to the web directory and create the php file :
echo "<?php phpinfo(); ?>" > info.php
Visit the server IP : 192.168.1.108/info.php
Step 5 - Install and Configure PhpPgAdmin
PhpPgAdmin is web application based on PHP for managing the Postgresql database. We can install it from source, but it is now available on freebsd repository, so let's install it with pkg command :
pkg install phppgadmin-5.1_1
On the installation step you will get error :
cp: /usr/local/www/data-dist/phpPgAdmin/conf/config.inc.php: No such file or directory
It is fine, dont worry about it.
Next, PhpPgAdmin is installed on directory '/usr/local/www/phpPgAdmin', and we must make the symlink of that directory to the apache web directory '/usr/local/www/apache24/data'.
ln -s /usr/local/www/phpPgAdmin/ /usr/local/www/apache24/data/
Now visit the server IP : 192.168.1.108/phpPgAdmin/.
And you will see the phppgadmin page, try to log in with the user and password created.
After logged in, we can see the database 'newdb' taht was created.
PostgreSQL with PhpPgAdmin is installed on FreeBSD 10.2.
PostgreSQL is powerful and open-source object relational database management system (RDBMS). Developed more then 15 year, and it is very suitable for large scale database. It is Cross Platform, can run on Linux, Unix and Windows. PhpPgAdmin is web application based on php, used for manage the postgresql database from the browser. Postgresql and PhpPgAdmin is easy to install and configure.