PostgreSQL on CentOS by using yum

The easy choice for installing PostgreSQL on CentOS is using yum:

$ yum list | grep postgresql
$ yum grouplist | grep PostgreSQL
$ yum groupinfo 'PostgreSQL Database' 

CentOS prepares a group for PostgreSQL: PostgreSQL Database.  We could check the details about this group by using groupinfo command.  This group only installs the binary of PostgreSQL database.  It doesn't install those header files for development usage.  We need install manually for the dev package: postgresql-devel.

CentOS yum will install PostgreSQL as a server: /etc/init.d/postgresql.  The home folder will be in folder /var/lib/pgsql.  The data, log files, and configuration files are in folder /var/lib/pgsql/data.

After install the devel package for PostgreSQL, the pg_config will tell all details about the PostgreSQL database installation.

Basic Resources

Here is a details document about how to set up client authentication for PostgreSQL database, file pg_hda.conf: http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html.

PostgreSQL client applications: http://developer.postgresql.org/pgdocs/postgres/reference-client.html.

Basic psql Commands

get into psql by using the following command

$ su - postgres
$ psql dbname username 

\? psql command help

\dt list tables

some shell programs:

createdb for creating a database

dropdb for droping a database

Basic Performance Tunning

The first thing we can do is Database Maintenance Tasks: Routine Vacuuming.  We could easily use the shell command vacuumdb.

http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html

PostgreSQL Server Configuration: http://developer.postgresql.org/pgdocs/postgres/runtime-config.html

Here is article talking about some tunning tips for PostgreSQL: http://www.varlena.com/GeneralBits/Tidbits/perf.html