安装postgresql 9.1.1

安装postgresql 9.1.1 ---版本自选，步骤相同 

1、安装必要的依赖包 
yum install -y gcc readline readline-devel zlib zlib-devel iostat python python-devel 

2、解压 
[postgres@mysqltest tmp]$ tar -zxvf postgresql-9.1.1.tar.gz  
[postgres@mysqltest tmp]$ cd postgresql-9.1.1 
[root@localhost postgresql-9.1.1]# ll 
total 2144 
-rw-r--r--  1 258 258     385 Sep 22  2011 aclocal.m4 
drwxrwxrwx  2 258 258    4096 Jul 16 00:14 config 
-rwxr-xr-x  1 258 258  862411 Sep 22  2011 configure 
-rw-r--r--  1 258 258   63014 Sep 22  2011 configure.in 
drwxrwxrwx 51 258 258    4096 Jul 16 00:14 contrib 
-rw-r--r--  1 258 258    1192 Sep 22  2011 COPYRIGHT 
drwxrwxrwx  3 258 258    4096 Jul 16 00:14 doc 
-rw-r--r--  1 258 258    3741 Sep 22  2011 GNUmakefile.in 
-rw-r--r--  1 258 258 1077847 Sep 22  2011 HISTORY 
-rw-r--r--  1 258 258   76550 Sep 22  2011 INSTALL 
-rw-r--r--  1 258 258    1489 Sep 22  2011 Makefile 
-rw-r--r--  1 258 258    1284 Sep 22  2011 README 
drwxrwxrwx 14 258 258    4096 Jul 16 00:14 src 
[root@localhost postgresql-9.1.1]# 

3、编译安装 
使用默认，那么会安装到/usr/local/pgsql/bin目录下 
./configure --prefix=/home/pgsql/9.1/   指定目录 

安装： 
gmake命令   ---直接执行，大概3-5分钟，最后几行如下： 
gmake[2]: Leaving directory `/postgresql-9.1.1/src/test/regress' 
gmake[1]: Leaving directory `/postgresql-9.1.1/src' 
gmake -C config all 
gmake[1]: Entering directory `/postgresql-9.1.1/config' 
gmake[1]: Nothing to be done for `all'. 
gmake[1]: Leaving directory `/postgresql-9.1.1/config' 
All of PostgreSQL successfully made. Ready to install. 

gmake install 
最后两行： 
gmake[1]: Leaving directory `/postgresql-9.1.1/config' 
PostgreSQL installation complete. 

也可以直接使用make && make install 

5、新建数据库用户postgres 
cd /home/pgsql/9.1 
mkdir data 
useradd postgres 
passwd postgres 
cd /home 
chown -R postgres:postgres  pgsql/   --修改data目录的属主为postgres，以便它可以进行读写 


6、环境变量设置，这一步千万不要忘记 
vi ~/.bash_profile 
export PGDATA=/home/pgsql/9.1/data 
export LD_LIBRARY_PATH=/home/pgsql/9.1/lib 
export PATH=/home/pgsql/9.1/bin:$PATH 
编辑完之后，记得source .bash_profile 

7、数据库初始化 
initdb 禁止除 PostgreSQL 用户帐户以外的任何用户访问这个目录。 
[postgres@localhost pg]$ /home/pgsql/9.1/bin/initdb  -D /home/pgsql/9.1/data 


8、启动和停止命令： 
启动: 
安装路径/bin/pg_ctl -D /usr/local/pgsql/data start 
安装路径/bin/postmaster -D /home/pgsql/9.1/data > logfile 2>&1 & 
  
停止: 
安装路径/bin/pg_ctl -D /usr/local/pgsql/data stop 

关闭：-m 是模式 smart 是等连接全部停掉。 fast 事务提交与回滚当前事务后关机 immediate 相当于断电 
/usr/pgsql/bin/pg_ctl stop -D /usr/pgsql/data -m f 

启动： 
/usr/pgsql/bin/postgres -D /usr/pgsql/data 
/usr/pgsql/bin/pg_ctl restart -D /usr/pgsql/data -m f 
/usr/pgsql/bin/pg_ctl start -D /usr/pgsql/data -m f 

9、修改远程连接设置 
修改文件 /opt/sda/pg922/data/postgresql.conf 
#listen_addresses = 'local' 改为 ： listen_addresses = '*' 

修改文件 /opt/sda/pg922/data/pg_hba.conf 安装下面的形式改 
# IPv4 local connections: 
host    all             all             0.0.0.0/0            md5 

10、参数设置 
可参考12.20上的设置：(注意12.20的内存是32GB) 
[root@pg data]# grep ^[a-z] postgresql.conf 
listen_addresses = '*' # what IP address(es) to listen on; 
port = 5432 # (change requires restart) 
max_connections = 500 # (change requires restart) 
superuser_reserved_connections = 3 # (change requires restart) 
shared_buffers = 8GB # min 128kB 
work_mem = 400MB # min 64kB 
maintenance_work_mem = 512MB # min 1MB 
wal_level = hot_standby # minimal, archive, or hot_standby 
fsync = on # turns forced synchronization on or off 
synchronous_commit = on # synchronization level; on, off, or local 
checkpoint_segments = 128 # in logfile segments, min 1, 16MB each 
checkpoint_timeout = 20min # range 30s-1h 
archive_mode = on # allows archiving to be done 
archive_command = 'cp %p /home/pgsql/backup_new/archived_log/%f' # command to use to archive a logfile segment 
max_wal_senders = 5 # max number of walsender processes 
wal_keep_segments = 1024 # in logfile segments, 16MB each; 0 disables 
vacuum_defer_cleanup_age = 4 # number of xacts by which cleanup is delayed 
replication_timeout = 120s # in milliseconds; 0 disables 
max_standby_archive_delay = 60min # max delay before canceling queries 
max_standby_streaming_delay = 60min # max delay before canceling queries 
effective_cache_size = 30GB 
logging_collector = on # Enable capturing of stderr and csvlog 
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern, 
log_truncate_on_rotation = off # If on, an existing log file with the 
log_rotation_age = 1d # Automatic rotation of logfiles will 
log_rotation_size = 10MB # Automatic rotation of logfiles will 
log_min_duration_statement = 500ms # -1 is disabled, 0 logs all statements 
log_line_prefix = '%t-%d-%h-%a :' # special values: 
log_statement = 'ddl' # none, ddl, mod, all 
log_temp_files = 10MB # log temporary files equal or larger 
datestyle = 'iso, ymd' 
lc_messages = 'en_US.UTF-8' # locale for system error message 
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting 
lc_numeric = 'en_US.UTF-8' # locale for number formatting 
lc_time = 'en_US.UTF-8' # locale for time formatting 
default_text_search_config = 'pg_catalog.english' 

11、新建归档存放目录 
su - postgres 
cd /home/pgsql/ 
mkdir -p backup_new/archived_log 


12、添加postgresql到自动启动，以便可以service postgresql start启动 
添加自动启动 
[root@mysqltest data]# vi /etc/init.d/postgresql   ---将自动启动的代码贴进去 
#! /bin/sh 
# chkconfig: 2345 98 02 
# description: PostgreSQL RDBMS 

# This is an example of a start/stop script for SysV-style init, such 
# as is used on Linux systems.  You should edit some of the variables 
# and maybe the 'echo' commands. 
# 
# Place this file at /etc/init.d/postgresql (or 
# /etc/rc.d/init.d/postgresql) and make symlinks to 
#   /etc/rc.d/rc0.d/K02postgresql 
#   /etc/rc.d/rc1.d/K02postgresql 
#   /etc/rc.d/rc2.d/K02postgresql 
#   /etc/rc.d/rc3.d/S98postgresql 
#   /etc/rc.d/rc4.d/S98postgresql 
#   /etc/rc.d/rc5.d/S98postgresql 
# Or, if you have chkconfig, simply: 
# chkconfig --add postgresql 

# Installation prefix 
prefix=/home/pgsql/9.1 

# Data directory 
PGDATA="/home/pgsql/9.1/data" 

# Who to run the postmaster as, usually "postgres".  (NOT "root") 
PGUSER=postgres 

# Where to keep a log file 
PGLOG="$PGDATA/serverlog" 

# The path that is to be used for the script 
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin 

# What to use to start up the postmaster.  (If you want the script to wait 
# until the server has started, you could use "pg_ctl start -w" here. 
# But without -w, pg_ctl adds no value.) 
DAEMON="$prefix/bin/postmaster" 

# What to use to shut down the postmaster 
PGCTL="$prefix/bin/pg_ctl" 

set -e 

# Only start if we can find the postmaster. 
test -x $DAEMON || 
{ 
echo "$DAEMON not found" 
if [ "$1" = "stop" ] 
then exit 0 
else exit 5 
fi 
} 


# Parse command line parameters. 
case $1 in 
  start) 
echo -n "Starting PostgreSQL: " 
test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj 
su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1 
echo "ok" 
;; 
  stop) 
echo -n "Stopping PostgreSQL: " 
su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast" 
echo "ok" 
;; 
  restart) 
echo -n "Restarting PostgreSQL: " 
su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w" 
test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj 
su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1 
echo "ok" 
;; 
  reload) 
        echo -n "Reload PostgreSQL: " 
        su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s" 
        echo "ok" 
        ;; 
  status) 
su - $PGUSER -c "$PGCTL status -D '$PGDATA'" 
;; 
  *) 
# Print help 
echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2 
exit 1 
;; 
esac 

exit 0 

[root@mysqltest init.d]# chmod +x postgresql 
[root@mysqltest init.d]# chkconfig --add postgresql 

以服务的形式启动pg 
[root@mysqltest init.d]# service postgresql start 
Starting PostgreSQL: ok 

13、创建用户，修改postgres密码: 
postgres=# CREATE ROLE engine LOGIN ENCRYPTED PASSWORD 'enginepassword'  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; 

CREATE ROLE fenxi LOGIN 
  ENCRYPTED PASSWORD 'fenxi' 
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; 

CREATE ROLE sqluser LOGIN 
  ENCRYPTED PASSWORD 'sqlpassword' 
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; 
  
CREATE ROLE repluser LOGIN 
  ENCRYPTED PASSWORD 'repluser' 
  SUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION; 

postgres=# alter user postgres password with 'tina'; 


14、建库： 
CREATE DATABASE rename_check 
WITH OWNER = postgres 
   ENCODING = 'UTF8' 
   TABLESPACE = pg_default 
   LC_COLLATE = 'zh_CN.UTF-8' 
   LC_CTYPE = 'zh_CN.UTF-8' 
   CONNECTION LIMIT = -1 
   TEMPLATE template0;    --必须是这个模板，不然会报下面的错 

GRANT CONNECT, TEMPORARY ON DATABASE rename_check TO public; 
GRANT ALL ON DATABASE rename_check TO postgres; 
COMMENT ON DATABASE rename_check  IS '改名系统'; 

报错： 
ERROR:  new collation (zh_CN.UTF-8) is incompatible with the collation of the template database (en_US.UTF-8) 

15、备份表(12.20): 
pg_dump --host localhost --port 5432 --username postgres -Fc --encoding=UTF8 --verbose 
--file /opt/db_backup/tmp_bk/five_tables.bk  -t t_rename_sample_state_list  -t t_rename_sample_state_sts 
-t  t_rename_task_type -t t_rename_task_type_list -t  t_rename_task_type_sts tm_samples 

16、新库来恢复表(12.108): 
pg_restore -U postgres -d rename_check  /tmp_bk/t_analyst_list.bk  2>&1 
pg_restore -U postgres -d rename_check  /tmp_bk/t_rename_analyst_list.bk 2>&1