Something about PostGres

1. How to Setup

(1)  Download postgresql-8.2.4.tar.gz

Copy postgresql-8.2.4.tar.gz to system temporary  directory, and then unzip this zip file.

# tar -xvf postgresql- 8.2 . 4 . tar . gz

(2)  Compile and install

#   cd   / tmp /  postgresql- 8.2 . 4

#./ configure            #( default location:  / usr / local / pgsql /)

#./  gmake

# gmake install

(3)  Add a user as to manage the database

#  pw user add pgsql      #( add a user named `pgsql` )

#   echo  `qweasdzxc` | pw usermod pgsql -h  0

#( set  pgsql’s password to ‘qweasdzxc’ )

(4)  Initialize the database

#   mkdir   / usr / local / pgsql / data              # make a new directory to store data

#  chown pgsql  / usr / local / pgsql / data        # change the directory’s ownership

#  su pgsql

$   cd   / usr / local / pgsql / bin

$  initdb -D  / usr / local / pgsql / data          # initialize the database

2.    How to configure

(1) Add a new environment variable and add pgsql’s bin directory to system variable path

#  vi  / root /. cshrc

setenv  PATH    $ JAVA_HOME / bin: $ JAVA_HOME / jre / bin: / usr / local / pgsql / bin: $ PATH

setenv PGDATA  / usr / local / pgsql / data

# source  / root /. cshrc                             # bring it into effect

(2) Modify some configuration for remote access

(a) Modify postgresql.conf file

Change “listen_addresses = ‘localhost’ ” to “ listen_addresses = ‘*’ ”

(b) Modify pg_hba.conf file

Add a single line after “host  all  all  127.0.0.1  255.255.255.255  trust”

“host  all  all  192.168.0.1  255.255.0.0  trust”

(c) Reload this new configure

$pg_ctl -D  / usr / local / pgsql / data reload

3.    Useful command
(1) Start
(a)   

$ postmaster –D  / usr / local / pgsql / data &

(b)   

$ pg_ctl –D  / usr / local / pgsql / data &

(2) Shutdown

$ pg_ctl –m fast -D  / usr / local / pgsql / data stop

(3) Connect

$ psql  < database name >

4.    Database backup

(1)    SQL Dump

(a)    Backup

1. Export database

$ pg_dump -Ft testdb  >  testdb . tar

2. Export schema

$ pg_dump -Ft -n public testdb  >  public . tar

(b)    Restore

1. Import database

$ pg_restore -d testdb1 testdb . tar

2. Import schema

$ pg_restore -c -d testdb1 public . tar

(2)    File system level backup

(a)    Backup

$ pg_ctl –m fast stop
$ tar –czvf testdb . tar  / usr / local / pgsql / data
$ pg_ctl  start

(b)    Restore

#  pg_ctl –m fast stop
#  tar –xzvf testdb . tar –C  /
#  pg_ctl  start

(3)    PgAdmin

(a)    bacup
(b)    restore

(4)    Automation backup script

Call hierarchy:
Cron --> backupscript.sh --> fullbackup.sh --> optimize.sql

(a) cron script

# crontab -e
36   3  * * *  / usr / local / pgsql / backupscript . sh  >>  backupInfo . txt

(b) backupscript.sh

# su pgsql -c  / usr / local / pgsql / fullbackup . sh

(c) fullbackup.sh

weekday =$( date   + " %w " )
time =$( date   + " %c " )

pgsql_bin =/ usr / local / pgsql / bin

backupname = fullbackup- $ weekday . tar . gz
backupfullname =/ backup / database /$ backupname
optimizeScript =/ usr / local / pgsql / optimize . sql

# BEGIN TO OPTIMIZE
echo   " $(date +"%c")   Begin to optimize... "
$ pgsql_bin / psql -d bh_portal -f  " $optimizeScript "
echo   " $(date +"%c")   Optimization Finished "

# BEGIN TO  BACKUP
echo   " $(date +"%c") Begin to backup... "
echo   " $(date +"%c") Begin to stop database... "

$ pgsql_bin / pg_ctl -m fast stop

while [ps | grep 'postmaster']
do
  sleep  3 ;
   echo   " $(date +"%c") waiting for the server... "
done

echo   " $(date +"%c") database stopped "
echo   " $(date +"%c") Begin to delete old file... "

rm -f  $ backupfullname

echo   " $(date +"%c") Delete old file over "
echo   " $(date +"%c") Begin to tar file... "

tar zcf  $ backupfullname  / usr / local / pgsql / data

echo   " $(date +"%c") tar file over "

file  $ backupfullname

echo   " $(date +"%c") Begin to start server... "

$ pgsql_bin / postmaster &

echo   " $(date +"%c") server started. "
echo   " $(date +"%c") Backup Finished "

(d)optimize.sql

VACCUM  FULL ;

5.    update and vaccum 

EXPLAIN  < sql >  #describes the sql execution status.

Postgres 执行update操作的时候，在内部会对所涉及到的数据做复制，保留更新前备份，这一点对数据库用户来讲是透明的。通过du –h data命令可以看出所设计到的数据库的磁盘占用量变大。特别是在update涉及到的数据量较大，磁盘占用量会很明显的发生变化，这样则会影响数据库的性能，例如使得SQL的执行返回时间变长等。因此不要特别频繁的进行update操作。对于数据量大的数据库，应该保证每天晚上去执行一次vacuum full操作，以释放这部分空间。