Install PostgreSQL9.1.9 Database Server on Fedora 17

"PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness."  

The following are the steps to install PostgreSQL9.1.9 in Fedora17.

Step-1: Change to root user

[ranga@ranga ~]$ su
Password:
[root@ranga ranga]#

Step-2: Install PostgreSQL Server
[root@ranga ~]# yum install postgresql-server

Step-3: Configure PostgreSQL Server

[root@ranga ~]# postgresql-setup initdb
Initializing database ... OK 
 

Step-8: Enable and Start PostgreSQL Server

Start server:
[root@ranga ~]# systemctl start postgresql.service

Enable postgresql:
[root@ranga ~]# systemctl enable postgresql.service


Creating a Database

Connect as user mypguser to new database

[root@ranga ~]# sudo su - postgres

Check that you can login.
-bash-4.2$ psql
psql (9.1.6)
Type "help" for help.

postgres=#

bash-4.2$ createdb RangaDB

bash-4.2$ psql RangaDB
psql (9.1.9)
Type "help" for help.

 

Create Database User

RangaDB=# CREATE ROLE ranga WITH SUPERUSER LOGIN PASSWORD 'ranga';
CREATE ROLE

or

RangaDB=# CREATE USER ranga WITH PASSWORD 'ranga';
CREATE ROLE

RangaDB=# psql -h localhost -U ranga ranga
RangaDB-#

If you get errors like:
psql: FATAL:  Peer authentication failed for user "ranga"
edit pg_hba.conf in  /var/lib/pgsql/data/pg_hba.conf

change peer and ident to md5.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

to

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Quit from the database
RangaDB=# \q

bash-4.2$ psql -d RangaDB -U ranga
could not change directory to "/root"
Password for user ranga:
psql (9.1.9)
Type "help" for help.

RangaDB=# create table test(id int);
CREATE TABLE
RangaDB=# select * from test;
 id
----
(0 rows)

RangaDB=# insert into test values(1);
INSERT 0 1
RangaDB=# select * from test;
 id
----
  1
(1 row)

RangaDB=# drop table test;
DROP TABLE
 
Changing Password:

[root@ranga trunk]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

Install PostgreSQL GUI Client

If you want to manage PostgreSQL using a GUI client, you need to install pgadmin3.
[root@ranga ranga]# sudo yum install pgadmin3

To list available packages: 
[root@ranga ranga]# yum list postgres*

Removing (or) Uninstalling PostgreSQL:
 
[ranga@ranga trunk]$ yum erase postgresql*
yum remove postgresql postgresql-devel postgresql-server
 
[root@ranga ~]# rpm -e --nodeps postgresql-libs

PostgreSQL Upgrade:
http://docs.1h.com/PostgreSQL_Upgrade

Installing pgAdmin:

The command line administration will be good for the people who has full experience on PostgreSQL, but for the beginner pgAdmin will be best option to manage the databases. By default pgAdmin packages available on fedora repository, so just issue the following command to install it.

Install pgAdmin3:
[root@ranga ranga]# yum install pgadmin3

Start pgAdmin3:
[root@ranga ranga]# pgadmin3

Now Connect to the database server using pgAdmin.

------------------------------------------------------------------------------------------------

Drop all tables:

DROP SCHEMA public CASCADE;  
CREATE SCHEMA public;