How To Install PostgreSQL on CentOS 7

PostgreSQL is the most advanced open source relational database management system (RDBMS) in the world.

It has been in active development for more than 15 years, and lately, it has gained more recognition due to the incorporation of native JSON and JSONB data types, a reason why it is considered a viable solution to the problems that NoSQL databases solve traditionally.

Install PostgreSQL

1. Update the yum repository:

sudo yum install update

2. Get the latest PostgreSQL package for CentOS 7:

Visit the official repository and look for the most recent stable version of PostgreSQL for CentOS 7.

https://yum.postgresql.org/repopackages.php

3. Right click on the download link and copy the address.

4. Download to your VPS or local server by running:

curl -O http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-1.noarch.rpm (Replaces the URL)

5. Install the downloaded PostgreSQL package:

sudo rpm -ivh pgdg-centos93-9.3-1.noarch.rpm

6. Install PostgreSQL:

sudo yum -y install postgresql93-server postgresql93-contrib

Configure and Start the Database

1. Initialize the database:

sudo service postgresql-9.3 initdb

If the previous command failed, try this instead:

sudo /usr/pgsql-9.3/bin/postgresql93-setup initdb

2. Execute the following command to edit the file /etc/sysconfig/pgsql/postgresql-9.3. If the file doesn’t exist, it will be blank so that you will create it.

nano /etc/sysconfig/pgsql/postgresql-9.3

3. Add or modify the PGPORT and PGDATA lines like this:

PGPORT=5438
PGDATA=/pgdata93

4. Start the database and configure it to run when the server starts:

sudo service postgresql-9.3 start
sudo chkconfig postgresql-9.3 on

Access To The Database

PostgreSQL creates a default user on the system named postgres without a password. Change the postgres user and access the PostgreSQL indicator.

su postgres
psql

When you are at the PostgreSQL prompt, you can type help to see a list of commands to help you access the database.

Useful Examples

List all the databases in the system and connect to the default postgres database:

postgres=# \list
postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".

List the tables present in the Postgres database (there should not be any):

postgres=# \d
No relations found.

Create a Simple Table and Verify If was Created Correctly:

postgres=# create table tweets (name varchar(25), twitterHandle varchar(25), message varchar(250));
CREATE TABLE
postgres=# \d
     List of relations
Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
public | tweets | table | postgres
(1 row)

Insert some Records to the table:

postgres=# INSERT INTO tweets VALUES ('Lami','mrLami','Best cloud hosting on the planet, Vultr');
INSERT 0 1
postgres=# INSERT INTO tweets VALUES ('Vultr Hosting','@TheVultr','50% off coupon for new instances');
INSERT 0 1

Query table to see results:

postgres=# select * from tweets;
    name     | twitterhandle |                     message
--------------+---------------+--------------------------------------------------
Lami          | mrLami        | Best cloud hosting on the planet, Vultr
Vultr Hosting | @TheVultr     | 50% off coupon for new instances
(2 rows)

Exit PostgreSQL:

postgres=# \q

Control the PostgreSQL service:

sudo service postgresql-9.3 start
sudo service postgresql-9.3 stop
sudo service postgresql-9.3 restart

Conclusion

PostgreSQL is a very advanced database engine that can do more than what is described in this article. Visit the PostgreSQL documentation to read more about its features such as Concurrency control of several versions (MVCC), point recovery, table-spaces, asynchronous replication, nested transactions (save points), online/hot backups, planner/query optimizer and advanced writing for fault tolerance.