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