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
cd /usr/local

mv postgresql-12.3 pgsql

二、编译安装PostgreSQL

cd /usr/local/pgsql
./configure
make && make install
添加用户组
groupadd -g 5432 postgres
useradd -u 5432 -g postgres postgres
passwd postgres
#
PostgreSQL默认安装目录为 /usr/local/pgsql/

默认端口：5432
三、创建用户及相关目录

chown -R postgres.postgres /usr/local/pgsql/
系统数据目录、日志目录、表空间目录、wal日志目录
mkdir -p /data/pgsql_5432/{pgdata,logs,tbsdata,archive}
chown -R postgres.postgres /data/pgsql_5432/

chmod 700 /data/pgsql_5432/pgdata

四、初始化数据库
su - postgres
/usr/local/pgsql/bin/initdb -D /data/pgsql_5432/pgdata/
$ /usr/local/pgsql/bin/initdb -D /data/pgsql_5432/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

五、启动数据库

/usr/local/pgsql/bin/pg_ctl -D /data/pgsql_5432/pgdata/ -l /data/pgsql_5432/logs/postgres.log start

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

数据库启停命令
pg_ctl -D /data/pgsql_5432/pgdata status
pg_ctl -D /data/pgsql_5432/pgdata stop -m fast
pg_ctl -D /data/pgsql_5432/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;