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
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#./
configure
#(
default location:
/
usr
/
local
/
pgsql
/)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#./
gmake
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#
gmake install
(3) Add a user as to manage the database
#
pw user add pgsql
#(
add a user named `pgsql`
)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#
echo
`qweasdzxc` | pw usermod pgsql -h
0
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#(
set
pgsql’s password to ‘qweasdzxc’
)
(4) Initialize the database
#
mkdir
/
usr
/
local
/
pgsql
/
data
#
make a new directory to store data
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#
chown pgsql
/
usr
/
local
/
pgsql
/
data
#
change the directory’s ownership
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#
su pgsql
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
$
cd
/
usr
/
local
/
pgsql
/
bin
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
$
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
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
setenv
PATH
$
JAVA_HOME
/
bin:
$
JAVA_HOME
/
jre
/
bin:
/
usr
/
local
/
pgsql
/
bin:
$
PATH
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
setenv PGDATA
/
usr
/
local
/
pgsql
/
data
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#
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
"
)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
pgsql_bin
=/
usr
/
local
/
pgsql
/
bin
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
backupname
=
fullbackup-
$
weekday
.
tar
.
gz
backupfullname
=/
backup
/
database
/$
backupname
optimizeScript
=/
usr
/
local
/
pgsql
/
optimize
.
sql
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#
BEGIN TO OPTIMIZE
echo
"
$(date +"%c") Begin to optimize...
"
$
pgsql_bin
/
psql -d bh_portal -f
"
$optimizeScript
"
echo
"
$(date +"%c") Optimization Finished
"
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
#
BEGIN TO
BACKUP
echo
"
$(date +"%c") Begin to backup...
"
echo
"
$(date +"%c") Begin to stop database...
"
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
$
pgsql_bin
/
pg_ctl -m fast stop
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
while [ps | grep 'postmaster']
do
sleep
3
;
echo
"
$(date +"%c") waiting for the server...
"
done
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
echo
"
$(date +"%c") database stopped
"
echo
"
$(date +"%c") Begin to delete old file...
"
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
rm -f
$
backupfullname
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
echo
"
$(date +"%c") Delete old file over
"
echo
"
$(date +"%c") Begin to tar file...
"
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
tar zcf
$
backupfullname
/
usr
/
local
/
pgsql
/
data
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
echo
"
$(date +"%c") tar file over
"
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
file
$
backupfullname
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
echo
"
$(date +"%c") Begin to start server...
"
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
$
pgsql_bin
/
postmaster &
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
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操作,以释放这部分空间。
(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.
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(2) Compile and install
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(3) Add a user as to manage the database
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(4) Initialize the database
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
2. How to configure
(1) Add a new environment variable and add pgsql’s bin directory to system variable path
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(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
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
3. Useful command
(1) Start
(a)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(b)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(2) Shutdown
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(3) Connect
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
4. Database backup
(1) SQL Dump
(a) Backup
1. Export database
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
2. Export schema
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(b) Restore
1. Import database
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
2. Import schema
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(2) File system level backup
(a) Backup
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(b) Restore
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(3) PgAdmin
(a) bacup
(b) restore
(4) Automation backup script
Call hierarchy:
Cron --> backupscript.sh --> fullbackup.sh --> optimize.sql
(a) cron script
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(b) backupscript.sh
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(c) fullbackup.sh
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(d)optimize.sql
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
5. update and vaccum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
Postgres 执行update操作的时候,在内部会对所涉及到的数据做复制,保留更新前备份,这一点对数据库用户来讲是透明的。通过du –h data命令可以看出所设计到的数据库的磁盘占用量变大。特别是在update涉及到的数据量较大,磁盘占用量会很明显的发生变化,这样则会影响数据库的性能,例如使得SQL的执行返回时间变长等。因此不要特别频繁的进行update操作。对于数据量大的数据库,应该保证每天晚上去执行一次vacuum full操作,以释放这部分空间。