安装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