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
1 thought on “Why PostgreSQL is not so popular?”