Last week, I accidentally stumbled upon blog stating features of new version of posgreSQL, and I found it pretty interesting, couple of useful features that does not exist in MySQL, are now implemented in PostgreSQL 9.3 (I am especially interested in Materialized views). so I wanted to learn more about this database.
Unfortunately, I haven’t used Postgres before ( although I have several years experience as MySQL developer and administrator), so I had to learn the basics about postgres, and I wanted to share this experience:
Installing PostgreSQL
In order to get the latest version on my Centos machine, I compiled Postgres from the source as following:
first, I got the desired version source files from Postgres site ( I used V9.3):
wget http://ftp.postgresql.org/pub/source/v9.3.2/postgresql-9.3.2.tar.bz2
then, uncompress the file:
tar xvjf postgresql-9.3.2.tar.bz2
then, compile the source files using this simple command inside the extracted folder:
./configure && make && make install
now postgres files should be placed at /usr/local/pgsql.
Postgres operates by default under user named postgres, so we should create the user, create data directory and assign folder ownership to created user:
adduser postgres mkdir /usr/local/pgsql/data chown postgres:postgres /usr/local/pgsql/data
Then we should initialize the data storage “database cluster” for the server by calling initdb, but first I switched to postgres user because you cannot run this command as a root:
[root@sub bin]# su - postgres -bash-4.1$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/
now, I can start postgres server by typing:
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &-D parameter specify the data directory location which also contains the configuration file of postgres, which is named by default postgresql.conf (analogous to my.cnf in mysql).
Now Postgres server is running and we can begin working with sql commands.
PostgreSQL Client
now let us enter the postgres client program by executing psql program, which is the interactive terminal for postgres:
/usr/local/pgsql/bin/psql -hlocalhost -U postgres -wHere I am using the database using its super user “postgres”.
I will issue \list command to see the databases installed:
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
as shown in the snippet, there are three databases here:
- Postgres database: which is the default database for postgres (same as mysql database in mysql)
- template0 and template1: which are two template databases.
I created a new database:
postgres=# create database test_pg; CREATE DATABASE
create database test_pg2 template template_database;
now if you run \list command, you will see the new database there
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test_pg | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | test_usr=CTc/postgres
(4 rows)
then, I created a new user and granted him a permission to use that database:
postgres=# create user test_usr with password 'test_pass'; CREATE ROLE postgres=# grant all privileges on database test_pg to test_usr; GRANT
Now, I will exit (by typing \q), then connect to the new database using the user I have created in the previous step:
-bash-4.1$ /usr/local/pgsql/bin/psql test_pg -Utest_usr -W Password for user test_usr: psql (9.3.2) Type "help" for help. test_pg=>
create a table I will use for testing:
test_pg=# create table test_tbl( id serial primary key, name varchar(255) ); CREATE TABLE
Serial keyword is similar to auto-increment attribute in other databases, and used to create unique identifier for the table records
and I will insert sample data to the table
test_pg=# insert into test_tbl(name) values('test1'), ('test2'), ('test3');
INSERT 0 3
PHP Script
I will use PDO in order to test php connectivity to postgres, but first php-postgres package must be installed:
yum install php-pgsql.x86_64
then I wrote this simple script:
<?php
try{
$dbh = new \PDO('pgsql:host=localhost;dbname=test_pg', 'test_usr', 'test_pass');
}catch(Exception $ex){
die('Error in connecting: '.$ex->getMessage());
}
$stmt = $dbh->prepare("select * from test_bg");
$stmt->execute();
echo $stmt->rowCount(). " records fetched.".chr(10);
while($row = $stmt->fetch()){
echo "id:".$row['id'].', name:'.$row['name'].chr(10);
}now you can run the script to see the results:
[root@sub pg_test]# php -f test.php 3 records fetched. id:1, name:test1 id:2, name:test2 id:3, name:test3
Now you can use Postgres as your data store in a very similar way to MySQL.
Conclusion
In this post, I explained quick introduction to Postgres database, from installation, and creating database and roles, to writing simple PHP script that retrieves data from Postgres. PostgreSQL has great features that I intend to learn more about in order to get most of value out of it.