PostgreSQL学习（一）—— 单机安装篇

PostgreSQL 9.6.9(Source Code)


OS: CentOS 7.2(Physical Machine)


1. 下载PostgreSQL 9.6.9安装包,下载地址: https://postgresql/org/download

2. root解压安装包: tar zxvf pg.tar.gz

3. 安装依赖包,否则无法编译: yum -y install readline readline-devel zlib-devel

4. cd ~/postgresql-9.6.9/
   ./configure --prefix=/opt/postgre-9.6.9
   mkdir -p /opt/postgre-9.6.9
   --with-python 让PG可以使用PL/Python写存储过程.
   --with-perl   让PG可以使用PL/Perl写存储过程.

5. make && make install(可以分开执行)
   说明: make是不编译组件的,contrib组件包含pgbench用于压力测试,如果需要可以用make world代替make
   如果只安装客户端和库文件:
   make -C src/bin install
   make -C src/include install
   make -C src/interfaces install
   make -C doc install
   ...

   PostgreSQL installation complete.

6. export LD_LIBRARY_PATH=/opt/postgre-9.6.9/lib (~./bash_profile)
   source ~/.bash_profile
   /sbin/ldconfig /opt/postgre-9.6.9/lib/

7. 设置Path环境变量:
   export PATH=/opt/postgre-9.6.9/bin:$PATH (~/.bash_profile)
   source ~/.bash_profile

8. 添加Postgres账号:
   useradd postgres;  echo 'niujinlin' | passwd --stdin postgres

9. 创建数据目录,并赋权给postgres管理:
   mkdir -p /opt/postgre-9.6.9/data
   chown postgres /opt/postgre-9.6.9/data

10.为Postgres用户添加环境变量:
   export LD_LIBRARY_PATH=/opt/postgre-9.6.9/lib
   export PATH=/opt/postgre-9.6.9/bin:$PATH
   export PGDATA=/opt/postgre-9.6.9/data
   source ~/.bash_profile

11.初始化数据库:
   initdb /opt/postgre-9.6.9/data/
   The files belonging to this database system will be owned by user "postgres".
   This user must also own the server process.
   The database cluster will be initialized with locale "en_US.UTF-8".
   The default database encoding has accordingly been set to "UTF8".

   The default text search configuration will be set to "english".

   Data page checksums are disabled.

   fixing permissions on existing directory /opt/postgre-9.6.9/data ... ok
   creating subdirectories ... ok
   selecting default max_connections ... 100
   selecting default shared_buffers ... 128MB
   selecting dynamic shared memory implementation ... posix
   creating configuration files ... ok
   running bootstrap script ... ok
   performing post-bootstrap initialization ... ok
   syncing data to disk ... ok

   WARNING: enabling "trust" authentication for local connections
   You can change this by editing pg_hba.conf or using the option -A, or
   --auth-local and --auth-host, the next time you run initdb.

   Success. You can now start the database server using:

    pg_ctl -D /opt/postgre-9.6.9/data/ -l logfile start

   此时,可在$PGDATA目录下看到很多关于postgresql db的文件. 

12.启动DB Server:
   mkdir -p ~/pg_start_log
   postgres -D /opt/postgre-9.6.9/data > ~/pg_start_log/start_`date +%Y-%m-%d-%H:%M:%S`.log 2>&1 &
   如果不使用-D参数,那么postgresql将尝试使用$PGDATA目录来读取启动需要的数据文件. 

   tail -n 100 ~/pg_start_log/start_2018-06-15-16:26:45.log
   LOG:  database system was shut down at 2018-06-15 16:17:52 CST
   LOG:  MultiXact member wraparound protections are now enabled
   LOG:  database system is ready to accept connections
   LOG:  autovacuum launcher started

   此时,查看进程可以看到如下所示:
   ps -ef | grep postgre
   ...
   postgres  6793 23240  0 16:26 pts/1    00:00:00 postgres -D /opt/postgre-9.6.9/data
   postgres  6796  6793  0 16:26 ?        00:00:00 postgres: checkpointer process   
   postgres  6797  6793  0 16:26 ?        00:00:00 postgres: writer process   
   postgres  6798  6793  0 16:26 ?        00:00:00 postgres: wal writer process   
   postgres  6799  6793  0 16:26 ?        00:00:00 postgres: autovacuum launcher process  
   postgres  6800  6793  0 16:26 ?        00:00:00 postgres: stats collector process  
   ...
   ...
   DB已经启动完毕.
   也可以使用该方式启动: pg_ctl start -l ~/pg_start_log/start_`date +%Y-%m-%d-%H:%M:%S`.log

13.设置PostgreSQL自动启动(不推荐):
   RHEL Family 6.x系列可以使用rc.local rc.d/rc.local,添加如下内容:
   su postgres -c "pg_ctl start -D /opt/postgre-9.6.9/data -l ~/pg_start_log/start_`date +%Y-%m-%d-%H:%M:%S`.log"

   如果是RHEL Family 7.x系列,由于使用Systemd,应该做如下改变:
   vi /etc/systemd/system/postgresql.service,添加如下内容:
   [Unit]
   Description=PostgreSQL database server
   Documentation=man:postgres(1)

   [Service]
   Type=notify    --要求编译时跟上--with-systemd
   User=postgres
   ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
   ExecReload=/bin/kill -HUP $MAINPID
   KillMode=mixed
   KillSignal=SIGINT
   TimeoutSec=0

   [Install]
   WantedBy=multi-user.target

14.PostgreSQL对内核参数的要求:
   SHMMAX Maximum size of shared memory segment (bytes) at least 1kB (more if running many copies of the server)
   SHMMIN Minimum size of shared memory segment (bytes) 1
   SHMALL Total amount of shared memory available (bytes or pages) if bytes, same as SHMMAX; if pages, ceil(SHMMAX/PAGE_SIZE)
   SHMSEG Maximum number of shared memory segments per process only 1 segment is needed, but the default is much higher
   SHMMNI Maximum number of shared memory segments system-wide like SHMSEG plus room for other applications
   SEMMNI Maximum number of semaphore identifiers (i.e., sets) at least ceil((max_connections + autovacuum_max_workers + max_worker_processes + 5) / 16)
   SEMMNS Maximum number of semaphores system-wide ceil((max_connections + autovacuum_max_workers + max_worker_processes + 5) / 16) * 17 plus room for other applications
   SEMMSL Maximum number of semaphores per set at least 17
   SEMMAP Number of entries in semaphore map see text
   SEMVMX Maximum value of semaphore at least 1000 (The default is often 32767; do not change unless necessary)

   由于我的PostgreSQL测试环境是安装在Oracle Server上的,肯定满足需求;
   如不满足需求,使用sysctl -w命令进行修改.

15.Remove IPC设置:
   由于在Red Hat Family 7.x系统上,有一个新的特性叫Remove IPC,在最后一个postgres用户退出系统后,将会清除所有的IPC对象,会导致数据库宕机,请进行如下设置:
   vi /etc/systemd/logind.conf
   添加如下内容:
   RemoveIPC=no
   该特性也影响Oracle Database.

16.Linux Memory Overcommit设置: 
   报错信息:
   Out of Memory: Killed process 12345(postgres) .
   Linux 2.4之后默认的虚拟内存表现对PostgreSQL并不理想,因为内核支持内存的溢出,如果虚拟内存需求压力过大,操作系统可能会杀死PostgreSQL postmaster进程(Master进程,类似Oracle SMON进程),这也证明服务器目前内存压力比较大.
   避免方式: 
   1) sysctl -w vm.overcommit_memory=2 (Not Overcommit Memory)
   2) echo -1000 > /proc/self/oom_score_adj 可以与1)配合使用,将OOM评分设置为-1000, 使得OOM Killer不会杀死postmaster进程.

17.Huge Pages管理:
   $ head -1 $PGDATA/postmaster.pid
   4170
   $ grep ^VmPeak /proc/4170/status
   VmPeak:  6490428 kB
   $ grep ^Hugepagesize /proc/meminfo
   Hugepagesize:       2048 kB

   估算PostgreSQL使用的内存,Round(Memory in PostgreSQL/Huge Page Size + 1)=vm.nr_hugepages

18.关闭PostgreSQL Server
   pg_ctl stop -D $PGDATA [fast|immediate]

19.创建并连接数据库:
   createdb mydb; dropdb mydb(删除数据库)

   psql mydb;
   查看版本: mydb=> select version();
  version 
------------------------------------------------------------------------------------------
PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

20.基本操作:
   select current_date;
   select 2 + 2;

   \h  语法帮助(这个是真的方便)
   \q  退出psql命令行.
   \d  查看当前数据库的表.
   \d t 查看表t的表结构.
   \c  切换数据库 用户 Host Port
   \?  查看psql帮助

21.依赖包解决:
Error 1:
checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
BEGIN failed--compilation aborted.
no
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.

解决方式: yum -y install perl-ExtUtils-Embed


Error 2:
configure: error: header file <Python.h> is required for Python

解决方式: yum -y install python python-devel

22.遇到的问题:
因为编译选项的问题,我重新安装了PostgreSQL-9.6.9,并且将目录变更为/opt/postgresql-9.6.9,这次我没有执行initdb选项.仅仅改了环境变量,将目录创建好,移动数据文件、配置文件,启动报错:

FATAL:  data directory "/opt/postgresql-9.6.9/data" has group or world access
DETAIL:  Permissions should be u=rwx (0700).

将$PGDATA目录变更为700权限即可, 也就是initdb也就是做了这样一件事.