Category Archives: PostgreSQL

PostgreSQL, Red Hat/CentOS, Server project

PostgreSQL on CentOS 7 – Howto

Leave a comment

In this post I’ll try to show you how to install PostgreSQL 9.6 on CentOS 7.3 which is the current versions of PostgreSQL and CentOS.

The default PostgreSQL version on CentOS 7.3 is PostgreSQL 9.2 which is still maintained, but in case you’re more for a “cutting edge” technology, try to follow the next steps

First, remove the already installed version (in case you installed the default version)

yum remove postgresql-server postgresql-contrib

Install official PostgreSQL Yum repo with

yum -y install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

which will create a new repo file /etc/yum.repos.d/pgdg-96-centos.repo with the next content

[pgdg96]
name=PostgreSQL 9.6 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
 
[pgdg96-source]
name=PostgreSQL 9.6 $releasever - $basearch - Source
failovermethod=priority
baseurl=https://download.postgresql.org/pub/repos/yum/srpms/9.6/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
 
[pgdg96-updates-testing]
name=PostgreSQL 9.6 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/testing/9.6/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
 
[pgdg96-source-updates-testing]
name=PostgreSQL 9.6 $releasever - $basearch - Source
failovermethod=priority
baseurl=https://download.postgresql.org/pub/repos/yum/srpms/testing/9.6/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96

Now install PostgreSQL with

yum -y groupinstall "PostgreSQL Database Server 9.6 PGDG"

and initialize it with

/usr/pgsql-9.6/bin/postgresql96-setup initdb

Start and enable service with

systemctl start postgresql-9.6.service
systemctl enable postgresql-9.6.service

Switch to the postgres user with

su postgres -

and connect to the server (currently running only on localhost)

psql

Check the installed version with

SELECT version();

You should get something like

...
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)
....

The default PostgreSQL installation listens only on localhost so if you want to connect from the remote host you’ll need to change a few things.

Open /var/lib/pgsql/9.6/data/postgresql.conf and find the line #listen_addresses = ‘localhost’… and replace it with the listen_addresses = ‘*’

Before

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
 
# - Connection Settings -
 
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)

after

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
 
# - Connection Settings -
 
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)

Now open /var/lib/pgsql/9.6/data/pg_hba.conf and add at the end

host    all             all             YOUR_CLIENT_IP_ADDRESS/32            md5

save the file and restart service with

systemctl restart postgresql-9.6.service

The last step before you test it is to change the password for postgres user with

su postgres -
 
bash-4.2$ psql
psql (9.6.1)
Type "help" for help.
 
postgres=# \password
Enter new password:
Enter it again:
postgres=# \q
exit

Now add new server in pgAdmin and test it

PostgreSQL, Tips & Tricks

PostgreSQL books which you should consider

Leave a comment

Since the Oracle bought Sun (including MySQL), I wanted to improve my knowledge about other databases and to check the alternatives (in case “something” happens to MySQL).

Luckily, MySQL is still here and it still works fine but in the last couple of months, I wanted to dig a little bit about PostgreSQL. I thought that it would be easy to find online tutorials about this topic but I noticed that online tutorials and forums about PostgreSQL are not so popular. Don’t get me wrong, there are several blogs which fits perfectly in my learning habits but spending so much time just to find the basic things is not what I wanted.

I found a link about Packt Publishing free PostgreSQL backup e-book and I didn’t wait (LINK). I posted a comment and couple days later received Packt account with two books

Instant PostgreSQL Starter by Daniel K. Lyons was exactly what I was looking for. A small (48 pages) book was right away uploaded to my DropBox so I can have it where ever I go. The book covers everything you need to start with Postgresql.
You will learn how to install Postgresql, how to connect and create your first database. The book also covers basic SQL queries for creating tables, CRUD (create, read, update and delete records), etc. The last part of the book contains some features you should also know (how to store passwords, working with XML, Full text search and basic options for improving speed and security). Couple advices about backups are also there.
Using pgAdmin III as a GUI tool won’t be a problem because the book contains pgAdmin screenshots.
Don’t get me wrong…. The book has only 48 pages and it can be compared with a cheat sheet but it is a very good portable book. Learning Postgresql can take only one afternoon and in less than a weekend you’ll became a “usable” DBA.

After the Postgresql starter book, I switched to PostgreSQL Backup and Restore How-to. We usually like to say that backup doesn’t have the price and every DBA should know everything about creating and restoring backups.
This (55 page) book is a step-by-step guide to backing up and restoring your database which covers:

  • basic and partial exports,
  • simple restore,
  • creating and restoring binary backups,
  • compressing backups
  • taking snapshots
  • sync backups
  • point in time recovery
  • warm and hot standby restore
  • streaming replication

Just like the first book, this one is also uploaded to my DropBox. Having those books with you is a nice idea and I highly recommend them to all Postgresql beginners. You can buy them as an e-book and as a hard copy.

PostgreSQL, Red Hat/CentOS, Server project, Tips & Tricks

Why PostgreSQL is not so popular? (howto part 2)

3 Comments

So… After the first part (Link) where we talk about the installation,
the next step would be to create root user and to change postgres and root password.

[root@XTdata init.d]# su postgres
bash-3.2$ createuser -s root
bash-3.2$ createdb root --owner=root
exit
 
[root@XTdata data]# psql
psql (9.2.4)
Type "help" for help.
 
root=# ALTER USER postgres WITH PASSWORD 'SomePAASWDe348';
ALTER ROLE
root=# ALTER USER root WITH PASSWORD 'SomePAASWDe3489898';
ALTER ROLE
root=# \q

Now, the next step would be to allow remote connections.

postgresql.conf is the main PostgreSQL config file. To be able to reach the server remotely, find the commented line

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

uncomment the line and replace the localhost with the servers IP address. (or replace it with * which means – listen on all interfaces)

listen_addresses = '*'         # what IP address(es) to listen on;

PostgreSQL, by default, refuses all connections it receives from any remote host. The remote hosts can be controled via pg_hba.conf file (located in the same dir like postgresql.conf).

Add the next line

host    all             all             192.168.10.57/32         md5

where 192.168.10.57 is the remote host IP address.

Also, you can allow any host by replacing the 192.168.10.57/32 with 0.0.0.0/0.

The line syntax is

local      DATABASE  USER  METHOD  [OPTIONS]
host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

which is documented inside the pg_hba.conf. Save the file and restart the server.

I prefer the pgAdmin III tool which can be used for remote management. Fire it up, select File, Add Server… Enter name, host, Username and password.

This should be enough for now…

PostgreSQL

Why PostgreSQL is not so popular?

1 Comment

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