Setting Up PostgreSQL on Your Linux System

PostgreSQL, also known as "Postgres," is a highly extensible and standards-compliant object-relational database management system (ORDBMS). It's renowned for its robustness, ACID-compliance, advanced features like multiversion concurrency control (MVCC), and a wide array of indexing methods, functions, and more. This guide will walk you through configuring your YUM repository, installing PostgreSQL, and setting up a basic database schema.

Step 1: Configure Your YUM Repository

Locate and Edit Your Distributions .repo File:

• Fedora: Edit /etc/yum.repos.d/fedora.repo and /etc/yum.repos.d/fedora-updates.repo, specifically the [fedora] sections.
• CentOS: Edit /etc/yum.repos.d/CentOS-Base.repo, focusing on the [base] and [updates] sections.
• Red Hat: Edit /etc/yum/pluginconf.d/rhnplugin.conf and look for the [main] section.

Append the Exclude Line: To each of the sections identified above, append the following line to prevent the default PostgreSQL package from being installed, as it may be outdated:

exclude=postgresql*

Step 2: Download and Install the PGDG RPM File

PGDG File: PostgreSQL Global Development Group (PGDG) provides an optimized and more up-to-date version of PostgreSQL.

1. Find the Correct RPM:

   • Browse PostgreSQL's YUM repository to find the RPM suitable for your distribution, architecture, and desired PostgreSQL version.

2. Download the RPM:

   • For example, to install PostgreSQL 9.3 on CentOS 6 64-bit:

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

3. Install the RPM Distribution:

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

Step 3: Install PostgreSQL

List Available Packages:

yum list postgres*

Install PostgreSQL Server:

• For a basic PostgreSQL 9.3 server installation:

  yum install postgresql93-server
  

Step 4: Accessing PostgreSQL

Switch to the PostgreSQL User:

su - postgres

Start Using PostgreSQL:

psql

You're now in the PostgreSQL command line. Here you can manage databases, execute SQL queries, and more.

Step 5: Setting Up a Basic Database Schema

Create a Schema Called test:

CREATE SCHEMA test;

Create a Role (User) with Password:

CREATE USER xxx PASSWORD 'yyy';

Grant Privileges on New Schema to New Role:

GRANT ALL ON SCHEMA test TO xxx;

Grant Privileges on Tables in the New Schema to the New Role:

GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;

Step 6: Disconnecting

Exit psql:

\q

Conclusion

Congratulations! You've successfully set up PostgreSQL on your Linux system. You've also created your first schema and user with privileges. PostgreSQL is a powerful tool with many more capabilities and features to explore. As you become more familiar with its workings, you'll be able to leverage its full potential in managing and analyzing your data effectively. Don't forget to regularly check for updates and maintain your PostgreSQL installation to ensure security and performance.