PostgreSQL is the world’s most advanced open source Relational Database Management System (RDBMS). It is ANSI SQL:2008 standard-compliant and defines most data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.
It has been in active development for more than 15 years. It has gained more traction lately due to the addition of native JSON and JSONB data types, making it a viable solution for problems traditionally addressed by NoSQL databases.
Update yum repository:
sudo yum install updateGet the latest package of PostgreSQL for CentOS 7:
Visit the official repository and locate the latest stable version of PostgreSQL for CentOS 7 (As of writing, the latest version is 9.3).
Right-click on the download link and copy its address.
Download on your VPS by running: curl -O http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-1.noarch.rpm Replace the URL with the repo package accordingly.
Install the downloaded PostgreSQL package:
sudo rpm -ivh pgdg-centos93-9.3-1.noarch.rpmInstall PostgreSQL:
sudo yum -y install postgresql93-server postgresql93-contribInitialize the database:
sudo service postgresql-9.3 initdbIf the previous command failed, call the setup binary directly:
sudo /usr/pgsql-9.3/bin/postgresql93-setup initdbIssue the command below to edit the /etc/sysconfig/pgsql/postgresql-9.3 file. If the file does not exist, it will be blank, which is fine.
nano /etc/sysconfig/pgsql/postgresql-9.3Add or modify PGPORT and PGDATA options so they match the sample below. Press CTRL + X, then Y after editing.
PGPORT=5438
PGDATA=/pgdata93Start the database and configure it to run when the server boots:
sudo service postgresql-9.3 start
sudo chkconfig postgresql-9.3 onPostgreSQL creates a default user on the system named postgres with no password. Switch to the postgres user and access the PostgreSQL prompt.
su postgres
psql When in the PostgreSQL prompt, you can type help See a list of commands to help with accessing the database. Note that you might need to type q or : followed by q to exit the help screen.
List all of the databases on the system and connect to the default database, postgres:
postgres=# \list
postgres=# \c postgres
You are now connected to the database "postgres" as user "postgres"List the tables present in the Postgres database (there should be none):
postgres=# \d
No relations foundCreate a simple table and check to make sure that it was created properly:
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 into the table:
postgres=# INSERT INTO tweets VALUES ('Lami','mrLami','Best cloud hosting in the world, AKLWEB Host');
INSERT 0 1
postgres=# INSERT INTO tweets VALUES ('AKLWEB Host','@The AKLWEB Host','50% off coupon for new instances');
INSERT 0 1Query the table to see the results:
postgres=# select * from tweets;
name | twitterhandle | message
--------------+---------------+--------------------------------------------------
Lami | mrLami | Best cloud hosting in the world, AKLWEB Host
AKLWEB Host | @The AKLWEB Host | 50% off coupon for new instances
(2 rows)Exit from PostgreSQL prompt:
postgres=# \qControl the PostgreSQL service:
sudo service postgresql-9.3 start
sudo service postgresql-9.3 stop
sudo service postgresql-9.3 restartPostgreSQL is a highly advanced database that can do more than just what was outlined in this article. Visit the PostgreSQL documentation for further reading on more specific features such as: Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, query planner/optimizer, and write-ahead logging for fault tolerance.