Setting up Nginx, PHP, PostgreSQL (Part-3)

In this tutorial, reader will learn –

  • How to install PostgreSQL and PHP PostgreSQL Extension.
  • Add a new user for PostgreSQL and setup cluster directory for it.
  • Initialize a database and start the server.
  • Creating a table and adding some data there.
  • Testing if PHP can access those data and show in our local site – nginxtest.dev.

To install PostgreSQL along with PHP Extension, open up terminal and run –

sudo pacman -S postgresql php-pgsql

For other Linux OS, reader should look for postgresql and php5-pgsql packages.

REMINDER : Here Archlinux is being used.

Of course you can install it from source. Go to PostgreSQL Download site to get the latest source tarball. To get PHP API (php-pgsql) go here to get further instructions.

Why PostgreSQL ?


From PostgreSQL website


"PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness."
	

Well, they said it right. Here’s a comparison between MySQL and PostgreSQL

Still the choice is yours, use whatever you feel comfortable to use. Just try to use it right. For now let’s move on.

Now time to give space for data cluster. If you have installed PostgreSQL via package manager a default directory with a default user is already set up for you. For me (as in Archlinux) it is /var/lib/postgres/data with user postgres. If reader is installing from source open up a terminal and run the following commands to create a data directory with the priviledges for a user named postgres


sudo adduser postgres
sudo mkdir -pv /var/lib/postgres/data
sudo chown -R postgres /var/lib/postgres
	

This will set up the same user with same data directory for you.

Next thing is giving the postgres user a password. To do this run –

sudo passwd postgres

You will be prompted for a password and for retyping it. Give a password as you want.

Now let’s initialize the data cluster. Fire up the terminal and run –

su - postgres
initdb -D /var/lib/postgres/data	

Keep the terminal open, because we will be using this for a while.
Screenshot from 2016-02-27 18-42-02

Now let’s start the server so that other servers(i.e php-fpm server) can connect to it.

pg_ctl -D /var/lib/postgres/data start

Here -D stands for default data directory.

Screenshot from 2016-02-27 18-55-25

Let’s create a database, add table and insert some data there –

    • Create Database(named nginxtestusers) : createdb nginxtestusers
    • Add a table using psql tool :
      
      psql -d nginxtestusers
      create table users(id serial primary key, name varchar(50) not null, email varchar(64) not null);
      		
    • Insert some data (continued from previous psql command) : insert into users(name, email) values('shimon','ams.eee09@gmail.com');
    • Run a query to see if the data is accessible (still inside psql) : select * from users;

Screenshot from 2016-02-27 19-27-22

  • Exit from psql: \q

Well, our first PostgreSQL database is up and running. Let’s see if we can access it from PHP. Remember our nginxtest.dev site ? Open a new file named /home/shimon/www/sites/nginxtest.dev/users.php and copy-paste the following –


<!DOCTYPE html>
<html>
<head>
	<title>Nginx Test Site</title>
	<style>
	table{
	align:center;border:1px solid black;
	}
	td,th{
	padding:10px;
	border: 1px solid black;
	}
	</style>
</head>
<body>
<h2>Users of this site :</h2>
<?php 

$host = "localhost"; 
$user = "postgres"; 
$pass = "postgres"; 
$db = "nginxtestusers"; 
$table = "users";

$con = pg_connect("host=$host dbname=$db user=$user password=$pass") or die ("Could not connect to server\n"); 

$query = "SELECT * FROM $table LIMIT 5"; 

$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");

echo "<table><tr><th>Id</th><th>Name</th><th>Email</th></tr>";
while ($row = pg_fetch_row($rs)) {
  echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>";
}
echo "</table>";

pg_close($con); 

?>
</body>
</html>
	

Now all we need to do is configuring PHP FPM to load the pgsql extension. To do that open /etc/php/php.ini
and look for the following lines –


;extension_dir = "/usr/lib/php/modules/"
;extension=pgsql.so
	

If they are commented out like above(using a semicolon at the beginning) then uncomment them like this –


extension_dir = "/usr/lib/php/modules/"
extension=pgsql.so
	

Now reload the PHP FPM server with this command –

sudo systemctl reload php-fpm

Open a browser and go to nginxtest.dev/users.php to see the result.
Screenshot from 2016-02-27 20-04-40

There you go. Well, this was just a beginning. Feel free to explore the configuration files (i.e php.ini, php-fpm.conf, postgresql.conf, pg_hba.conf) to see whats happens. Read the Manuals(that’s the first thing you should do for configuring).

Reader can follow the steps to set up more than one development site this way. This really helps in organizing and understanding the internals.

Happy web development.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s