Q. How do I install and configure Postgresql database server under Red hat Enterprise Linux 5 / CentOS 5 / Fedora Linux?
A. The postgresql-server package includes the programs needed to create and run a PostgreSQL server, which will in turn allow you to create
and maintain PostgreSQL databases. PostgreSQL is an advanced Object-Relational database management system (DBMS) that supports almost all SQL constructs (including transactions, subselects and user-defined types and functions). You should install postgresql-server if you want to create and maintain your own PostgreSQL databases and/or your own PostgreSQL server. You also need to install the postgresql package.
You also need to install client package called postgrsql. This package contains the docs in HTML for the whole package, as well as command-line utilities for managing PostgreSQL databases on a PostgreSQL server.
Install Postgrsql Server
Login as the root user and enter the command:
# yum install postgresql postgresql-server
Output:
Loading "installonlyn" plugin Setting up Install Process Setting up repositories Reading repository metadata in from local files Parsing package install arguments Resolving Dependencies --> Populating transaction set with selected packages. Please wait. ---> Downloading header for postgresql-server to pack into transaction set. postgresql-server-8.1.9-1 100% |=========================| 87 kB 00:00 ---> Package postgresql-server.i386 0:8.1.9-1.el5 set to be updated --> Running transaction check --> Processing Dependency: postgresql = 8.1.9-1.el5 for package: postgresql-server --> Restarting Dependency Resolution with new changes. --> Populating transaction set with selected packages. Please wait. ---> Downloading header for postgresql to pack into transaction set. postgresql-8.1.9-1.el5.i3 100% |=========================| 119 kB 00:00 ---> Package postgresql.i386 0:8.1.9-1.el5 set to be updated --> Running transaction check Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: postgresql-server i386 8.1.9-1.el5 updates 4.0 M Installing for dependencies: postgresql i386 8.1.9-1.el5 updates 2.8 M Transaction Summary ============================================================================= Install 2 Package(s) Update 0 Package(s) Remove 0 Package(s) Total download size: 6.8 M Is this ok [y/N]: y Downloading Packages: (1/2): postgresql-server- 100% |=========================| 4.0 MB 00:05 (2/2): postgresql-8.1.9-1 100% |=========================| 2.8 MB 00:04 Running Transaction Test Finished Transaction Test Transaction Test Succeeded Running Transaction Installing: postgresql ######################### [1/2] Installing: postgresql-server ######################### [2/2] Installed: postgresql-server.i386 0:8.1.9-1.el5 Dependency Installed: postgresql.i386 0:8.1.9-1.el5 Complete!
Start Postgresql
Type the following two command:
# chkconfig postgresql on
# service postgresql start
Output:
Initializing database: [ OK ] Starting postgresql service: [ OK ]
Connect to Postgresql Server
Connect to server using
# su - postgres
Connect using psql command line tool:
$ psql -d template1 -U postgres
Output:
Welcome to psql 8.1.9, the PostgreSQL interactive terminal.
Type: copyright for distribution terms
h for help with SQL commands
? for help with psql commands
g or terminate with semicolon to execute query
q to quit
Open TCP port 5432
Finally make sure iptables allows remote access to Postgresql database server:
Open /etc/sysconfig/iptables file:
# vi /etc/sysconfig/iptables
Append following line before COMMIT line to open port 5432:
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
Save and close the file. Restart the firewall:
# service iptables restart
See also: PostgreSQL add or create a user account and grant permission for database
