CentOS PostgreSQL Installation tutorial

CentOS PostgreSQL

centos postgresql :

PostgreSQL is a powerful, open source object-relational database system.
In the following tutorial I’ll show how to install PostgreSQL on your CentOS box

 

CentOS PostgreSQL Installation

We can install PostgreSQL in (at-least) two ways:

• Using YUM
• Compile from source

 

Install from repository

yum install postgresql-server

this will install the package postgresql-server, also: postgresql and postgresql-libs.

centos postgresql

Install from source

If you want to install the latest version of PostgreSQL you should compile from source. it’s recommended for advanced users and one may argue it’s recommended too for production.

Anyway, this article from DigitalOcean covers this area well (and more). If you want to compile using source you better move to that article. If you prefer or installed using repository (yum), continue…

 

PostgreSQL Service

if you’ll try to start PostgreSQL using the service command, you will see an error tells you must init the db first and create the db files in: /var/lib//pgsql/data

so,

to init on centos postgresql service use:

service postgresql initdb

This created a data folder in /var/lib/pgsql. You can’t run this command again without deleting first this folder (and all your data).

Also, when you called the initdb command above from RedHat’s init script configured permissions on the database. These configuration settings are in the pg_hba.conf file inside the data folder.

By default all permissions are ‘Ident’,

means the only user that can get in initially is user “postgres”, so if you’ll try ‘psql’ from root you’ll get error:

psql: FATAL: Ident authentication failed for user “root”

If you want to login and use postgres with other users than `postgres` you can change the permissions method in pg_hba.conf. change from ‘ident’ to ‘md5’ is recommended.

If you want to use phpPgAdmin (described later) you should change from ‘Ident’ to ‘md5’ or else it won’t login to your system.

 

Set port and Listen Addresses

If you need to change the default port (5432 by default) and Listen Addresses (localhost by default), you can set those vars inside the postgresql.conf inside /var/lib/pgsql/data folder.

#listen_addresses = 'localhost'
#port = 5432

 

Start service

and then, to start on centos postgresql service use:

service postgresql start

to make centos postgresql load on boot use the chkconfig command as follows:

chkconfig postgresql on

 

and That’s it!

 

What next?

• read the PostgreSQL: Documentation: Getting Started to configure your environment.

 

Managing from Command line

login to postgres

As I mentioned, default setup has ident authentication means the only user that can get in initially is user “postgres”, so if you haven’t changed permissions scheme you should su to postgres before.

to start ‘psql’ as postgres:

# change user to postgres
su - postgres
# start psql manager
psql
# CTRL + D twice to exit both psql and su.

# You can also short the two commands into:
# su postgres -c psql

Add (or create) a user with permission to specific database?

Read this great tutorial.

 

PhpPgAdmin

PostgreSQL visual interface similar to phpMyAdmin? – in short, if you know phpMyAdmin and want phpPgAdmin, you need to add the EPEL repositories, Apache (yum install httpd) and then install using:

If your permissions scheme is currently ‘Ident’ you might need to change that to ‘md5’ as PhpPgAdmin requires it.

yum install phpPgAdmin

Then visit in your browser: http://localhost/phpPgAdmin

Remote connection

Edit /etc/httpd/conf.d/phpPgAdmin.conf if you want to allow access remotly and restart httpd (service httpd restart).

do you use pgsql, postgres, root, administrator as login or even user without password?
if you do, set the $conf[‘extra_login_security’] entry to false in your et/phpPgAdmin/config.inc.php.

 

Change default Postgres user password

If you really want to use the “postgres” role, make sure you set it up a password and $conf[‘extra_login_security’] is false.

use the command:

passwd postgres

to change the system user password and

ALTER USER Postgres WITH PASSWORD 'password';

That alters the password for within the database. To change the password inside Postgresql. there is also short code (inside psql):

\password

Which will ask from you a new password to set.

 

 

Cheers!