下载源码包：
[root@hmcf-02 opt]# wget https://ftp.postgresql.org/pub/source/v10.6/postgresql-10.6.tar.gz
傻瓜式安装依赖、解压编译
[root@hmcf-02 opt]# yum -y install perl-ExtUtils-Embed openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel openldap openldap-devel python-devel readline-devel
[root@hmcf-02 opt]# tar -zxvf postgresql-10.6.tar.gz
[root@hmcf-02 opt]# cd postgresql-10.6
[root@hmcf-02 postgresql-10.6]# ./configure --prefix=/usr/local/pgsql-10
# 在make的过程中可能会存在gcc 不存在等问题，yum安装一下即可。
[root@hmcf-02 postgresql-10.6]# make -j4
[root@hmcf-02 postgresql-10.6]# make install
创建数据库系统用户
[root@hmcf-02 ~]# userdel postgres
[root@hmcf-02 ~]# groupdel postgres
[root@hmcf-02 ~]# groupadd -g 701 postgres
[root@hmcf-02 ~]# useradd -m -g postgres -u 701 postgres
至此，/home下面就有了postgres的用户家目录
修改操作系统配置参数
修改/etc/sysctl.conf，主要更新两个参数
kernel.shmmax = 16724692992 #实际内存大小，单位为字节B
kernel.shmall = 4083177 #机器物理内存的页面数，物理内存除以4K
其中kernel.sem对应四个参数：
* SEMMSL(信号集的最大信号量数）：需要大于17，我们取250
* SEMMNS（整个系统范围内的最大信息量数）：SEMMSL * SEMMNI = 650*250
* SEMOPM（semop函数在一次调用中所能操作一个信号量集中最大的信号量数）: 与SEMMSL相同，为250
* SEMMNI（信号量集的最大数目）：ceil(max_conntions + autovacuum_max_workers + 4)/16，如果对于10000个连接，而autovacuum_max_workers=3，所以SEMMNI最少要大于626，我们取650
修改/etc/security/limits.conf，一般添加如下内容
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
禁止ssh的DNS反解
修改/etc/ssh/sshd_config，设置UseDNS no
查看是否开启了透明大页：
cat /sys/kernel/mm/transparent_hugepage/enabled
CentOS7.X下禁止透明大页：
grubby --update-kernel=ALL --args="transparent_hugepage=never"
添加环境变量path
vi .bash_profile
#一般添加以下几行
export PATH=/usr/local/pgsql-10/bin:$PATH #pg程序的安装目录
export LD_LIBRARY_PATH=/usr/local/pgsql-10/lib:$LD_LIBRARY_PATH #pg静态库路径
export PGDATA=/data/pgdata/ #pg数据库实例路径
export PGHOST=/tmp #pg数据库的socket文件路径，/
#配置文件中的unix_socket_directory默认指定 /
#在/var/run/postgresql和/tmp下， /
#有时会因为前者postgres用户没有权限造成启动报错。
最后 source .bash_profile 让配置生效。
为了在后续的添加一些插件的过程中，root用户能找到对应的配置，建议将上述的配置文件内容在root根目录下的.bash_profile中也添加一下并启用。
创建数据库目录，并赋权
[root@hmcf-02 ~]# mkdir -p /data/pgdata
[root@hmcf-02 ~]# chown -R postgres.postgres /data/
初始化数据库
initdb 一般需要-D 添加数据库目录路径，但前面已经配置了PGDATA，所以就不需要了。
[postgres@hmcf-02 ~]$ initdb
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 "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
fixing permissions on existing directory /data/pgdata ... 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 /data/pgdata/ -l logfile start
[postgres@hmcf-02 ~]$
启动和关闭数据库
通常，我们只需要在postgresq用户下，执行命令
pg_ctl start ，pg_ctl stop 或 pg_ctl restart 来启动，停止和重启数据库。
按照initdb中提示的 ，通常需要-D 指定实例的路径，因为本次只使用了一个实例，并且在path中加入了PGDATA，所以可以省略-D参数。
如
[postgres@hmcf-02 ~]$ pg_ctl start
waiting for server to start....2019-11-23 16:24:17.848 CST [16971] LOG: listening on IPv6 address "::1", port 5432
2019-11-23 16:24:17.848 CST [16971] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-11-23 16:24:17.855 CST [16971] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-11-23 16:24:17.887 CST [16972] LOG: database system was shut down at 2019-11-23 16:19:55 CST
2019-11-23 16:24:17.889 CST [16971] LOG: database system is ready to accept connections
done
server started
[postgres@hmcf-02 ~]$ psql
psql (10.6)
Type "help" for help.
postgres=#
[postgres@hmcf-02 ~]$ pg_ctl stop
waiting for server to shut down....2019-11-23 16:24:55.163 CST [16971] LOG: received fast shutdown request
2019-11-23 16:24:55.164 CST [16971] LOG: aborting any active transactions
2019-11-23 16:24:55.165 CST [16971] LOG: worker process: logical replication launcher (PID 16978) exited with exit code 1
2019-11-23 16:24:55.165 CST [16973] LOG: shutting down
2019-11-23 16:24:55.170 CST [16971] LOG: database system is shut down
done
server stopped
也可以通过psql 命令远程连接其它服务器上的pg数据库，只需要加入-h参数指定远程数据库地址及对应的端口和认证信息即可。
[postgres@hmcf-02 ~]$ psql -Upostgres -h 192.168.0.50 -p 5433 -d postgres
当然想要远程连接别的pg数据库，或者想别应用远程访问，还需要关闭防火墙或者开启对应的端口和对允许访问的IP做黑白名单处理。下面看一下postgresql的网络和配置文件。
首先，设置网络, /data/pgdata/pg_hba.conf (根据各自实际路径情况) 文件
原文件关键内容如下，默认METHOD都是trust--可信任，免密访问，实际生产中对外开放的网络要设置成md5--通过用户密码形式登陆。单机模式下，我们只需要配置IPv4下的内容，添加需要允许连接的具体IP或者IP段即可。最下面的replication部分是主从模式下的流复制相关配置信息，这个在以后的文章中会继续讲解。
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
修改成允许所有地址通过用户密码形式访问。
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host testdb hmc 0.0.0.0/0 md5
# 允许所有地址，使用hmc用户及密码形式访问testdb数据库
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
修改完后需要重新加载配置文件
pg_ctl reload (推荐)
或者
pg_ctl restart
关于postgresql.conf, 常见的选项
### 连接相关
listen_addresses = 'localhost' # 一般改为'*'
port = 5432 **端口号，默认为5432**
max_connections = 100
**最大可以同时连接数据库的数量，生产库一般为几千**
unix_socket_directories = '/var/run/postgresql, /tmp' **socket文件路径，默认为/tmp**
unix_socket_permissions = 0777 **socket文件权限，一般不用修改**
### 内存相关
shared_buffers = 128MB **共享缓冲区，一般改为物理内存的50%**
work_mem = 4MB **工作内存，默认为4MB**
maintenance_work_mem = 64MB **维护工作内存，默认为64MB**
### 归档相关
archive_mode = off **默认关闭，on则开启归档模式**
archive_command = ''
**归档命令选项，如’test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f’**
### wal日志相关
max_wal_size = 1GB **wal日志允许的最大值，默认每个wal日志大小为16MB**
min_wal_size = 80MB
wal_keep_segments = 0 **建议改为80，控制wal日志大小的参数之一**
### 搭建备库相关
wal_level = replica **默认为replica，搭建备库改为logical**
max_wal_senders = 10 **要想搭建备库，这个参数至少为1**
hot_standby = on **搭建备库必须需开启**
synchronous_standby_names = ‘’
**如stb232，stb234等，在备库recovery.conf中添加application_name=stb234后，该备库与主库之间备份就位同步。**
hot_standby_feedback = off **改为on，表示如果有错误的数据复制，是否向主进行反馈。**
### 其它
shared_preload_libraries = ‘’ **一般添加为$libdir/plugin_debugger’，增加pg调试功能**
lock_timeout = 0
track_io_timing = on
track_functions = all
log_min_duration_statement = 10000
bgwriter_delay = 200ms
同理，修改完参数后同样需要重新加载配置文件。命令同上。
典型的数据库配置：
listen_addresses = '*'
max_connections = 10000
tcp_keepalives_idle = 5
tcp_keepalives_interval = 5
tcp_keepalives_count =3
shared_buffers = 64GB
maintenance_work_mem = 256M
bgwriter_delay = 50ms
wal_level = logical
max_wal_size = 4GB
min_wal_size = 160MB
max_wal_senders = 10
wal_keep_segments = 80
hot_standby = on
hot_standby_feedback = on
logging_collector = on
log_min_duration_statement = 10000
track_io_timing = on
track_functions = all
autovacuum_vacuum_cost_delay = 1ms
statement_timeout = 0
lock_timeout = 60000
网友评论