How to install PostgreSQL on a CentOS 6 VPS

Leave reply

postgresqlToday we will show you how to install PostgreSQL on a CentOS 6 VPS. PostgreSQL is a cross-platform object-relational database management system and its primary function is to store data and retrieve it later. It is open source and it is designed for high volume environments.

Current stable release of PostgreSQL is 9.3 and it is community supported until 2018-09-09.

The installation of PostgreSQL is very simple and requires only few minutes to be completed. Let’s start.

First thing you need to do is to exclude the PostgreSQL in your /etc/yum.repos.d/CentOS-Base.repo. You need to add the following line

exclude=postgresql*

in the [base] and [updates] sections and save the changes.

Next, you need to install the PGDG RPM file by using the commands:

For 32bit CentOS systems:

## yum localinstall http://yum.postgresql.org/9.3/redhat/rhel-6-i386/pgdg-centos93-9.3-1.noarch.rpm

For 64bit CentOS systems:

## yum localinstall http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm

If you like to list the available packages you can use the command:

## yum list postgres*

To install a basic PostgreSQL 9.3 server use the following command:

## yum install postgresql93-server

Once the postgresql93-server package is installed, you need to initialize a database:

## service postgresql-9.3 initdb

If you like to enable PostgreSQL on system startup you can type:

## chkconfig postgresql-9.3 on

And finally, use the following command to start the database service:

## service postgresql-9.3 start

Once the installation is completed, you can start using the PostgreSQL 9.3 on your CentOS 6 VPS by typing the commands:

## su - postgres
## psql

If you don’t know what to do next, you can always use the help command which will tell you exactly how to use the PostgreSQL command line interface.

The easiest way to start using PostgreSQL is to create a schema in the database called postgres, which is default.

## CREATE SCHEMA newschema;

Next, create new user and set a password for that user

## CREATE USER newuser PASSWORD 'yourpassword';

Grant privileges with

## GRANT ALL ON SCHEMA newschema TO newuser;
## GRANT ALL ON ALL TABLES IN SCHEMA newschema TO newuser;

And finally exit PostgreSQL using the command

## \q

Now, you can log in using the newuser with

## su - newuser

Please note, the default authentication mode is set to ‘ident’, so if you like to log in as newuser, you should register the newuser as a Linux system user on your CentOS 6 VPS.

Of course you don’t have to do any of this if you use one of our Linux VPS hosting services, in which case you can simply ask our expert Linux admins to do this for you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

Leave a Reply