Why PostgreSQL is not so popular?

Good tutorials about PostgreSQL can be found but not so easy like for MySQL. I decided to install PostgreSQL on my own server just to see what is the main benefit and how it can be used as a MySQL replacement.

I’m using CentOS 6.x and I removed the default PostgreSQL installation with yum remove postgresql* so I can install the latest PostgreSQL (9.2.1). As I noticed on http://yum.postgresql.org/repopackages.php, there is a yum repo which has the latest rpms.

If you want to do the same, chech your distro version and platform with uname -a. In my case it was CentOS 6.2 x86_64 which means I need to download http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm.

Open /etc/yum.repos.d/CentOS-Base.repo and add

exclude=postgresql*

inside [base] and [updates] sections.

so it looks a like:

....
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
 
#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
.....

The added lines will help you to avoid the default PostgreSQL rpms which are usually very old.

Install downloaded rpm with

rpm -ivh pgdg-centos92-9.2-6.noarch.rpm

Now you can install PostgreSQL with

yum install postgresql92-libs postgresql92 postgresql92-devel postgresql92-server postgresql92-docs php-pgsql

(php-pgsql is required only if you plan to use PHP with PostgreSQL)

The first step after installation is to init database with

service postgresql-9.2 initdb

After this, start server with

service postgresql-9.2 start

WTF ??? … Don’t you think that postgresql-9.2 a little bit to long for typing ? Especially everyday…

To skip this problem, rename the postgresql-9.2 to something similar like other PostgreSQL commands. For example psqld (similar like mysqld).

[root@XT init.d]# service psqld restart
Stopping psqld service:                                    [  OK  ]
Starting psqld service:                                    [  OK  ]

works… Great…

The first time in my life, I’m trying to use PostreSQL…

[root@XT init.d]# psql
psql: FATAL:  role "root" does not exist

No problem… Let’s Google…

The result is the createuser command which defines a new PostgreSQL user account. Lets type a little…

[root@XT init.d]# su postgres
bash-4.1$ createuser root
bash-4.1$ man createuser
bash-4.1$ createuser -s root
createuser: creation of new role failed: ERROR:  role "root" already exists
bash-4.1$ man createuser
bash-4.1$ drop
dropdb    droplang  dropuser
bash-4.1$ dropuser root
bash-4.1$ createuser --interactive root
Shall the new role be a superuser? (y/n) y
bash-4.1$ exit

As you probably noticed… We need a superuser account (that’s why I had to delete the created user and to do the same again but with “–interactive” part)…

Lets try again…

[root@XT share]# psql
psql: FATAL:  database "root" does not exist
 
.....
 
[root@XT share]# man createdb
 
.....
 
[root@XT share]# createdb root --owner=root

Finally…

Lets try again…

[root@XT share]# psql
psql (9.2.1)
Type "help" for help.
 
root=# quit
ERROR:  syntax error at or near "quit"
LINE 1: quit
        ^
root=# exit
root-# exit;
ERROR:  syntax error at or near "exit"
LINE 1: exit
        ^
root=# quit;
ERROR:  syntax error at or near "quit"
LINE 1: quit;
        ^
root=# close
root-# close;
ERROR:  syntax error at or near "close"
LINE 3: close;
        ^
root=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
root=# \q

Huh … Finally I succeed to exit… This is very stupid… “help” command is Ok, but quit, exit, close and similar commands are not…

Now, lets try to find some config files to see what’s inside them… Hm…. /etc… Nothing… /usr/…. Nothing…. Try here and there… Nothing… Back to Google…

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

Lets try…

[root@XT share]# psql
psql (9.2.1)
Type "help" for help.
 
root=# SELECT name, setting
root-# FROM pg_settings
root-# WHERE category = 'File Locations';
       name        |                 setting
-------------------+-----------------------------------------
 config_file       | /var/lib/pgsql/9.2/data/postgresql.conf
 data_directory    | /var/lib/pgsql/9.2/data
 external_pid_file |
 hba_file          | /var/lib/pgsql/9.2/data/pg_hba.conf
 ident_file        | /var/lib/pgsql/9.2/data/pg_ident.conf
(5 rows)
...

Ahaaaa….

The first problem with PostgreSQL is the name… PostgreSQL is a to long name for normal usage. That’s why the service name can’t be postgresql-9.2.
Also, the big problem is a complete disorder in command names…. To connect you’ll need psql, to start/stop service postgresql-9.2, to create user – createuser, to create db – createdb, to drop user dropupser, to drop database dropdb, etc etc… Some config files starts with pg_… some with postgresql…. Dir names are mostly pqsql…

It would be much better idea to use mysql naming system… Every mysql command starts with mysql and you can get all commands with

[root@XT init.d]# mysql [TAB]
mysql                       mysqlbug                    mysqld_multi                mysql_find_rows             mysqlimport                 mysqlshow                   mysql_upgrade
mysqlaccess                 mysqlcheck                  mysqld_safe                 mysql_fix_extensions        mysql_install_db            mysqlslap                   mysql_waitpid
mysqladmin                  mysql_config                mysqldump                   mysql_fix_privilege_tables  mysql_secure_installation   mysqltest                   mysql_zap
mysqlbinlog                 mysql_convert_table_format  mysqldumpslow               mysqlhotcopy                mysql_setpermission         mysql_tzinfo_to_sql

That’s it for now… In the next post I will try to do one more step…

In the mean time, you can check the next link
http://yum.postgresql.org/files/PostgreSQL-RPM-Installation-PGDG.pdf

One thought on “Why PostgreSQL is not so popular?”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.