PostgreSQL is the world's most advanced open source Relational Database Management System (RDBMS). It is ANSI SQL:2008 standards compliant and has most of the data types defined in its specification, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.
It has been in active development for more than 15 years and has gained more buzz lately due to its addition of JSON and JSONB native data types, causing it to be looked at as a viable solution to problems NoSQL databases are traditionally used to solve.
Update yum repository:
sudo yum install update
Get 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.rpm
sudo yum -y install postgresql93-server postgresql93-contrib
Configure and Start Database
Initialize the database:
sudo service postgresql-9.3 initdb
If the previous command failed, call the setup binary directly:
sudo /usr/pgsql-9.3/bin/postgresql93-setup initdb
Issue the command below to edit
/etc/sysconfig/pgsql/postgresql-9.3 file. If the file does not exist it will be blank, which is fine.
Add or Modify PGPORT and PGDATA options so they match the sample below. Press CTRL + X, then Y after editing.
Start the database and configure it to run when server boots:
sudo service postgresql-9.3 start sudo chkconfig postgresql-9.3 on
PostgreSQL creates a default user on the system named
postgres with no password. Switch to the
postgres user and access PostgreSQL prompt.
su postgres psql
When in the PosgreSQL prompt, you can type
help to see a list of commands to help with accessing the database. Note that you might need to type
: followed by
q to exit the help screen.
List all of the databases on the system and connect to default 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 be none):
postgres=# \d No relations found.
Create 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 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 from PosgreSQL prompt:
Control the PostgreSQL service:
sudo service postgresql-9.3 start sudo service postgresql-9.3 stop sudo service postgresql-9.3 restart
PosgreSQL 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.