Setting up PostgreSQL

Install PostgreSQL 7.4.2 on Linux from rpm

1. Download all rpm files into a directory

   postgresql-7.4.2-1PGDG.i386.rpm
   postgresql-contrib-7.4.2-1PGDG.i386.rpm
   postgresql-debuginfo-7.4.2-1PGDG.i386.rpm
   postgresql-devel-7.4.2-1PGDG.i386.rpm
   postgresql-docs-7.4.2-1PGDG.i386.rpm
   postgresql-jdbc-7.4.2-1PGDG.i386.rpm
   postgresql-libs-7.4.2-1PGDG.i386.rpm
   postgresql-pl-7.4.2-1PGDG.i386.rpm
   postgresql-python-7.4.2-1PGDG.i386.rpm
   postgresql-server-7.4.2-1PGDG.i386.rpm
   postgresql-tcl-7.4.2-1PGDG.i386.rpm
   postgresql-test-7.4.2-1PGDG.i386.rpm

2. su - root
3. Try one of the following to install depending on your situation:
   • rpm -i *.rpm
   • rpm -U *.rpm
   • rpm -F *.rpm
4. Add linux user: postgres (maybe already there if previously installed)
5. mkdir /usr/local/pgsql/data
6. chown postgres /usr/local/pgsql/data
7. su - postgres
8. initdb -D /usr/local/pgsql/data
9. postmaster -i -D /usr/local/pgsql/data >logfile 2>&1 &
10. createdb -E UNICODE newfire
11. To check if the database is online:
    • psql newfire

newfire is the database name here.

Start up Database

Log in as postgres.

postmaster -i -D /usr/local/pgsql/data >logfile 2>&1 &

Depending on the linux platform, to start postgresql on boot can be different. For Redhat Linux, add to /etc/rc.local file:
su - postgres -c "postmaster -i -D /usr/local/pgsql/data > /usr/local/pgsql/logfile 2>&1 &"

Shut down Database

Log in as postgres.

pg_ctl stop -D /usr/local/pgsql/data
or
pg_ctl stop -D /usr/local/pgsql/data -m fast
or
pg_ctl stop -D /usr/local/pgsql/data -m immediate

Install PostgreSQL 8.2.3 on Linux from source

1. download postgresql-8.2.3.tar.gz
2. follow instructions in INSTALL file
   • ...
   • /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
     /usr/local/pgsql/bin/createdb -E UNICODE newfire
     /usr/local/pgsql/bin/psql newfire
3. Set up service:
   • su -c "cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql"
   • su -c "chmod a+x /etc/rc.d/init.d/postgresql"
4. Starting and stopping database:
   • /sbin/service postgresql start
   • /sbin/service postgresql stop

Client Authentication

Log in as postgres.

append the following to /usr/local/pgsql/data/pg_hba.conf to allow all users from localhost to connect without authentication.

local all all 0.0.0.0 0.0.0.0 trust

Back up

Log in as postgres.

pg_dump dbname | gzip > filename.gz

or shut down database first and back up from file system
zip -r data.zip /usr/local/pgsql/data

Restore

Log in as postgres.

gunzip -c filename.gz | psql dbname

or shut down database first and restore from file system
replace /usr/local/pgsql/data with data.zip

Note: the dbname must be an empty database. Run "createdb -E UNICODE dbname" to create a database.

Performance Tuning For Linux

Log in as root.

Set shared memory to 256M:
echo 256000000 > /proc/sys/kernel/shmmax

Put the following line in /etc/sysctl.conf :
kernel.shmmax = 256000000

Modify the following lines in /usr/local/pgsql/data/postgresql.conf:

shared_buffers = 30400
sort_mem = 32168
max_connections=64
fsync=false