【PgSQL日常管理】PostgreSQL12.3部署

• 一、服务器环境信息

软件版本：postgresql12.3

二进制安装：postgresql-12.3.tar.gz.tar.gz

postgresql-12.3.tar.gz.md5：4aeff45d4180d8d8cdb907a0e4690da2 postgresql-12.3.tar.gz

postgresql-12.3.tar.gz.sha256：708fd5b32a97577679d3c13824c633936f886a733fc55ab5a9240b615a105f50 postgresql-12.3.tar.gz

操作系统信息：

# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)

# free -m
total used free shared buff/cache available
Mem: 7983 295 7013 16 674 7403
Swap: 8191 0 8191

# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 4
On-line CPU(s) list: 0-3
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 4
NUMA node(s): 1 

• 二、安装依赖包

yum -y install readline readline-devel zlib-devel

• 三、创建相应的文件目录、用户及修改环境变量

• 添加postgresql用户及用户组

#groupadd -g 5432 postgres
#useradd -u 5432 -g 5432 -m -d /home/postgres -s /bin/bash  postgres
#passwd postgres

• 系统数据目录、日志目录、表空间目录、wal日志目录

mkdir -p /pgsql/{pg_home,pg_base/{pgdata,pg_log,tbsdata,pg_arch}}

#pg_home：postgresql软件目录、编译安装目录

#pg_base：postgresql数据目录，包括数据目录、日志目录、表空间目录、归档日志目录

chown -R postgres.postgres /pgsql

chmod 700 /pgsql/pg_base/pgdata

#这里业务库为小库，或未挂在/pgsql盘的情况下，可选/home/postgres进行安装部署，即配置/home/postgres/{pg_home,pg_base/{pgdata,pg_log,tbsdata,pg_arch}}

• 配置postgresql环境变量

su - postgres

export PGHOME=/pgsql/pg_home
export PGDATA=/pgsql/pg_base/pgdata
export LD_LIBRARY_PATH=/pgsql/pg_home/lib

若配置物理备份pg_rman，需增加pg_rman的环境变量配置，需新增备份盘/pgshare
#for pg_rman
export BACKUP_PATH=/pgshare/rman
export ARCLOG_PATH=/pgsql/pg_base/pg_arch
export SRVLOG_PATH=/pgsql/pg_base/pg_log

export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin 

• 四、root用户编译安装PostgreSQL

解压软件安装包：tar -zxvf postgresql-12.3.tar.gz -D /pgdata/

mv postgresql-12.3 pg_home

cd  /pgsql/pg_home
./configure
make && make install

自定义安装目录编译方式，如：

方式一：

#./configure --prefix=/pgsql/pg_home  --with-libedit-preferred --with-perl --with-python --with-openssl --with-libxml --with-libxslt --enable-thread-safety --enable-nls=en_US.UTF-8

方式二：提前定义wal segment方式

#./configure --prefix=/pgsql/pg_home --with-segsize=16 --with-blocksize=32 --with-wal-segsize=64 --with-wal-blocksize=64 --with-libedit-preferred --with-perl --with-python --with-openssl --with-libxml --with-libxslt --enable-thread-safety --enable-nls=en_US.UTF-8
#PostgreSQL默认安装目录为 /usr/local/pgsql/

#默认端口：5432
修改属性权限

chown -R postgres.postgres /pgsql/pg_home

• 五、初始化启动数据库

su - postgres
/pgsql/pg_home/bin/initdb -D /pgsql/pg_base/pgdata
$ /pgsql/pg_home/bin/initdb -D /pgsql/pg_base/pgdata/

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 /data/pgsql_5432/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

/usr/local/pgsql/bin/pg_ctl -D /data/pgsql_5432/pgdata/ -l logfile start

• 启动数据库

/pgsql/pg_home/bin/pg_ctl -D /pgsql/pg_base/pgdata/ -l /pgsql/pg_base/pg_log/postgres.log start

ln -s /pgsql/pg_home/bin/psql /usr/bin/psql
ln -s /pgsql/pg_home/bin/pg_ctl /usr/bin/pg_ctl

#数据库启停命令

pg_ctl -D /pgsql/pg_base/pgdata status
pg_ctl -D /pgsql/pg_base/pgdata stop -m fast
pg_ctl -D /pgsql/pg_base/pgdata start

#修改postgres用户登录密码
ALTER user postgres with password 'xxxxxx';

#编辑postgresql.conf

listen_addresses = '*'
port = 5432
max_connections = 2000

logging_collector = on
log_directory = '/data/pgsql_5432/logs/'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S'

shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 32MB
effective_cache_size = 10GB
maintenance_work_mem = 128MB
#max_stack_depth = 2MB
dynamic_shared_memory_type = posix

编辑pg_hba.conf

local   all             all                                      md5
host    pgdb            srv_pgdb        0.0.0.0/0                md5
host    all             all             ::1/128                  ident

 

#创建业务用户srv_pgtest
create user srv_pgtest with ENCRYPTED password 'srv_pgtest';
#创建schema，并赋予所有者为用户srv_pgtest
CREATE SCHEMA pgtest;
ALTER SCHEMA pgtest OWNER to srv_pgtest;
#创建所有者是srv_qhms的数据库,owner是role，不是schema
CREATE DATABASE pgtest WITH OWNER srv_pgtest ENCODING UTF8 TEMPLATE template0;

 

• 六、多实例部署-部署1921端口实例

一、创建系统数据目录、日志目录、表空间目录、wal日志目录
mkdir -p /pgsql/pg_1921/{pgdata,pg_log,tbsdata,pg_arch}
chown -R postgres.postgres /pgsql/pg_1921/

chmod 700 /pgsql/pg_1921/pgdata

初始化数据库
su - postgres
/bin/initdb -D /pgsql/pg_1921/pgdata/

$ /bin/initdb -D /pgsql/pg_1921/pgdata/
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 /data/pgsql_1921/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

    /pgsql/pg_home/bin/pg_ctl -D /pgsql/pg_1921/pgdata/ -l logfile start

配置postgresql.conf

listen_addresses = '*'
port = 1921
max_connections = 2000
superuser_reserved_connections = 10
full_page_writes = on
wal_level = replica
wal_log_hints = off
max_wal_senders = 50
max_wal_size = 1GB
min_wal_size = 80MB

hot_standby = on
log_destination = 'csvlog'
logging_collector = on
log_directory = '/pgsql/pg_1921/pg_log/'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_filename = 'postgresql-%w.log'
log_file_mode = 0640
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB

log_statement = 'mod'
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'

unix_socket_directories = '/pgsql/pg_1921'
temp_tablespaces = 'ts_temp_customer'
shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 128MB
effective_cache_size = 1GB
maintenance_work_mem = 512MB
max_stack_depth = 2MB
dynamic_shared_memory_type = posix

checkpoint_timeout = 30min
checkpoint_completion_target = 0.9

wal_buffers = 16MB
wal_writer_delay = 200ms
synchronous_commit = remote_write
commit_delay = 0
commit_siblings = 5

archive_mode = on
archive_command = 'cp %p /pgsql/pg_1921/pg_arch/%f && echo %f >> /pgsql/pg_1921/pg_arch/archive.list'
wal_keep_segments=1024


log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default
archive_timeout = 60s
deadlock_timeout = 3s
log_duration = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d'

 

启动数据库

$/bin/pg_ctl -D /pgsql/pg_1921/pgdata/ -l /pgsql/pg_1921/pg_log/postgres.log start

 连接1921端口PG实例：

$ psql -h localhost -p 1921
psql (12.3)
Type "help" for help.

postgres=#

 另外，针对业务用户的链接测试：

$ psql -U prod_user -W -h 10.12.12.151 -p 5432 -d postgres
Password:
psql (17.6)
Type "help" for help.

postgres=#