Centos 6/RHEL install and access Postgresql

PostgreSQL is an Open Source Object-Relational Database Management System and is available from the standard Centos repo.

It's architecture and large array of features make it an attractive solution for many companies who are concerned with data integrity.  

Postgres is an easy-to-install database system and uses a
template system which supports a large part of the SQL standard.
To install it in Centos 6, log in as root and type.

# yum install postgresql postgresql-server

Now enable the database server at boot by typing

# chkconfig postgresql on

Then initialize the database

# service postgresql initdb

Start the database by typing

# service postgresql start

Now assign your current CentOS user account as a database user

Connect to the database using the following command

$ sudo su ­ postgres

A new database will be created by cloning the standard system database so launch the psql command-line utility.

-bash-4.1$ psql template1

Now issue a command to create a database, so by substituting the relevant values with those associated with your system user account

CREATE USER <username> WITH PASSWORD '<password>'

Create your first database, replacing the <database-name> value with something more appropriate

CREATE DATABASE <database-name>

Now complete the user setup by assigning the correct privileges, substituting the relevant values with those used previously

GRANT ALL PRIVILEGES ON DATABASE <database-name> to <username>

When finished, quit by typing


-bash-4.1$ exit

Changing logging parameters

You can alter the logging parameters to customise the recorded values.

$ sudo vi /var/lib/pgsql/data/postgresql.conf

Scroll down and find the following line


Now uncomment and change this line to read

log_line_prefix = '%d %u %t'

This will use the database name, username, and timestamp format when writing the log files,

Save the file and restart the database server

$ sudo service postgresql restart

Connecting to Postgresql

Connecting to Postgresql is different to connecting to MySql,
to access to access the database, first issue the folowing

$ sudo so postgres

Now access the interactive screen by typing

psql template1

-bash-4.1$ psql template1


psql (8.4.13)

Type "help" for help.


From here you can use SQL to complete any template related task, you can quit the terminal by typing


This command will return you to the postgres user prompt, which you can close at any time by issuing the following command


Accessing a specific database as a specific user

If you wish to access a specific database as a specific user you would begin by accessing the main terminal as postgresql user

$ sudo su ­ postgres

Having done this you would access the relevant database by using the appropriate user in the following way

psql -d <database-name> -U <username> ­W

Complete this process by submitting your password when requested 

The entire process may look similar to below

$ sudo su - postgres

-bash-4.1$ psql -d <database-name> -U <username> -W

Password for user <username>

psql (8.4.13)

Type "help" for help


Creating a copy of a database in PostgreSQL
You can use any existing database on the server as a template when creating a new database.

To do this, simply access the psql console as postgres user, and issue the following command.

CREATE DATABASE <new-database-name> WITH TEMPLATE <original-database-name> OWNER <username>;

The original database needs to be idle in order for this command to work properly.

See the PostgreSql site for more

Labels: , ,