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.
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.
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.
3. Add or modify the PGPORT and PGDATA lines like this:
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.
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)
Control the PostgreSQL service:
sudo service postgresql-9.3 start sudo service postgresql-9.3 stop sudo service postgresql-9.3 restart
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.