编译安装postgresql 12.6及常用指令

目录

• 一、前期准备
  • 1.1 创建postgres用户
  • 1.2 安装依赖包
  • 1.3 系统参数优化
  • 1.4 创建postgres相关目录
• 二、源码安装postgresql
  • 2.1 下载软件包并编译安装
  • 2.2 切换用户设置环境变量
  • 2.3 初始化数据
• 三、启动关闭
  • 3.1 手动方式
  • 3.2 配置为系统服务
• 四、基础管理
  • 4.1 连接管理
    • 4.1.1 修改配置文件，便于远程登陆
  • 4.2 用户
    • 4.2.1 用户作用
    • 4.2.2 用户的定义方式
      • 例子：
  • 4.3 权限管理
    • 4.3.1 权限级别
    • 4.3.2 权限定义
  • 5、常用命令

postgresql官网地址：https://www.postgresql.org/

postgresql中文社区：http://www.postgres.cn/

本篇博文主要记录如何源码安装postgresql 12.6 ！

一、前期准备

1.1 创建postgres用户

$ useradd postgres
$ echo postgres | passwd --stdin postgres

1.2 安装依赖包

$ yum groupinstall -y "Development Tools" "Legacy UNIX Compatibility"
$ yum install -y bison flex readline* zlib-devel gcc* gmake

1.3 系统参数优化

# 内核优化
$ cat >> /etc/sysctl.conf << EOF
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
EOF
$ sysctl -p
$ cat >> /etc/security/limits.conf << EOF
*               soft    nofile          131072
*               soft    nproc           131072
*               hard    nproc           131072
*               soft    core            unlimited
*               hard    core            unlimited
*               soft    memlock         50000000
*               hard    memlock         50000000
EOF

1.4 创建postgres相关目录

$ mkdir -p /usr/local/pg12
$ mkdir -p /pgdata/12/data
$ chown -R postgres. /pgdata
$ chown -R postgres. /usr/local/pg12
$ chmod -R 700 /pgdata/12/data/

二、源码安装postgresql

2.1 下载软件包并编译安装

$ cd /opt
$ wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v12.6/postgresql-12.6.tar.gz
$ tar xf postgresql-12.6.tar.gz 
$ cd postgresql-12.6/
$ ./configure --prefix=/usr/local/pg12 --with-pgport=1921
$ gmake world
$ gmake install-world

2.2 切换用户设置环境变量

$ su - postgres
[postgres@pg1 ~]$ vim .bash_profile
export PGDATA=/pgdata/12/data
export LANG=en_US.utf8
export PGHOME=/usr/local/pg12
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +%Y%m%d%H%M`
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
 
[postgres@pg1 ~]$ source .bash_profile
[postgres@pg1 ~]$ psql --version
psql (PostgreSQL) 12.6

2.3 初始化数据

[postgres@pg1 ~]$ initdb -D /pgdata/12/data -W
# 简易初始化
[postgres@pg1 ~]$ initdb -A md5 -D $PGDATA -E utf8 --locale=C -W
# 生产建议
 
[postgres@pg1 ~]$ pg_ctl start   # 启动postgresql
 
[postgres@pg1 ~]$ psql           # 使用本地socket连接到postgresql
Password for user postgres: 
psql (12.6)
 
postgres=# create database lvzhenjiang;
# 创建lvzhenjiang数据库
postgres=# \c lvzhenjiang;
# 切换到lvzhenjiang数据库
lvzhenjiang=# create table t1 (id int);
# 创建t1表
lvzhenjiang=# insert into t1 values(1);
# 向t1表中插入数据
lvzhenjiang=# select * from t1;
# 查看t1表中的数据
 id 
----
  1
(1 row)
lvzhenjiang=# \l    # 查看所有的数据库
                              List of databases
    Name     |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-------------+----------+----------+---------+-------+-----------------------
 lvzhenjiang | postgres | UTF8     | C       | C     | 
 postgres    | postgres | UTF8     | C       | C     | 
 template0   | postgres | UTF8     | C       | C     | =c/postgres          +
             |          |          |         |       | postgres=CTc/postgres
 template1   | postgres | UTF8     | C       | C     | =c/postgres          +
             |          |          |         |       | postgres=CTc/postgres
(4 rows)
 
lvzhenjiang=# \d    # 查看lvzhenjiang库中的表
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)
 
lvzhenjiang=# \dt   # 查看lvzhenjiang库中的表
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

三、启动关闭

3.1 手动方式

[postgres@pg1 ~]$ pg_ctl -D /pgdata/12/data -l logfile start
[postgres@pg1 ~]$ pg_ctl -D /pgdata/12/data stop -ms
[postgres@pg1 ~]$ pg_ctl -D /pgdata/12/data stop -mf   # 常用关闭方式
[postgres@pg1 ~]$ pg_ctl -D /pgdata/12/data stop -mi
[postgres@pg1 ~]$ pg_ctl restart -mf

3.2 配置为系统服务

注: 若想添加为系统服务，使用systemctl指令来控制服务的启停，可以参考官方文档。

四、基础管理

4.1 连接管理

默认安装完成后 只能允许本地socket连接！

[postgres@pg1 ~]$ psql  
# 本地socket连接方式

4.1.1 修改配置文件，便于远程登陆

[postgres@pg1 ~]$ vim $PGDATA/pg_hba.conf
host    all             all             192.168.99.0/24         md5
[postgres@pg1 ~]$ cp $PGDATA/postgresql.conf{,.bak}
[postgres@pg1 ~]$ vim $PGDATA/postgresql.conf
listen_addresses = '*'   # *表示监听所有地址
port = 5432      # 监听端口
max_connections = 1000    # 指定最大连接数
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 1GB   # 设置共享内存缓冲区的内存量，建议值为系统总内存的25％
temp_buffers = 80MB    # 设置用于每个数据库会话中的临时缓冲区的最大内存量
max_files_per_process = 65535   # 设置允许每个服务器子进程同时打开的文件的最大数量。不能大于ulimt -n的数值
bgwriter_delay = 100ms 
bgwriter_lru_maxpages = 1000
bgwriter_flush_after = 0
synchronous_commit = off
wal_level = replica
archive_mode = on
# 以下路径替换为实际备份路径
archive_command = 'test ! -f /pgdata/12/data/backup/incre/%f && cp %p /pgdata/12/data/backup/incre/%f'
full_page_writes = on
wal_buffers = -1
wal_writer_delay = 100ms
wal_writer_flush_after = 256kB
checkpoint_timeout = 30min
max_wal_size = 5GB
min_wal_size = 1GB
log_destination = 'stderr'
logging_collector = on
log_directory = 'logs'
log_filename = 'postgresql-%Y-%m-%d_%H.log'
log_file_mode = 0640
log_rotation_age = 1d 
log_rotation_size = 100MB
log_truncate_on_rotation = off
log_min_messages = notice
log_min_error_statement = notice
log_min_duration_statement = 3s
log_checkpoints = on
log_connections = on
log_error_verbosity = verbose
log_line_prefix = '%m '
log_timezone = 'PRC'
track_activities = on
log_autovacuum_min_duration = 3s 
autovacuum_max_workers = 4
autovacuum_naptime = 45s
autovacuum_vacuum_scale_factor = 0.1 
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 1600000000 
autovacuum_multixact_freeze_max_age = 1600000000
vacuum_freeze_table_age = 1500000000
vacuum_multixact_freeze_table_age = 1500000000
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
default_text_search_config = 'pg_catalog.english'

关于配置文件的优化，可以参考官方文档。

[postgres@pg1 ~]$ pg_ctl restart -mf    # 重启生效
[postgres@pg1 ~]$ psql -d postgres -h 192.168.99.4 -p 1921 -U postgres
Password for user postgres: 
postgres=#    
# 测试命令行远程登陆
# 自行下载测试pgadmin连接pg
https://www.pgadmin.org/
# 也可以通过navicat工具进行连接测试

4.2 用户

4.2.1 用户作用

用来登陆数据库实例、管理数据库对象！

4.2.2 用户的定义方式

create user  # 默认自带连接功能(常用)
create role

例子：

CREATE USER test1 WITH PASSWORD 'test1';
CREATE ROLE test2 WITH LOGIN PASSWORD 'test2' VALID UNTIL '2021-06-30';
CREATE USER admin WITH SUPERUSER PASSWORD 'admin';
CREATE USER repl RELICATION LOGIN ENCRYPTED PASSWORD 'repl';
DROP USER test1;
# 删除用户
ALTER USER admin with PASSWORD 'admin123';
# 更改用户密码
ALTER USER admin WITH nologin PASSWORD 'admin123';
# 更改用户权限
\help CREATE USER;   # 查看帮助
\du   # 查看所有的用户

4.3 权限管理

4.3.1 权限级别

• cluster权限：实例权限通过pg_hba.conf配置；
• database权限：数据库权限通过grant和revoke操作schema配置；
• TBS权限：表空间权限通过grant和revoke操作表、物化视图、索引、临时表配置；
• schema权限：模式权限通过grant和revoke操作模式下的对象配置；
• object权限：对象权限通过grant和revoke配置；

4.3.2 权限定义

• database权限设置

GRANT create ON DATABASE lvzhenjiang TO lvzhenjiang;

• schema权限

ALTER SCHEMA abc OWNER to abc;
GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA abc to abc;

• object权限

GRANT select,insert,update,delete ON a.b TO u;

• 案例：创建业务用户

postgres=# create database taobao;
postgres=# \c taobao;
taobao=# create SCHEMA miaosha;
taobao=# create user miaosha with password '123';
taobao=# ALTER SCHEMA miaosha OWNER to miaosha;
taobao=# GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA miaosha to miaosha;

5、常用命令

\?
\l
\d
\du
\d t1
\c lvzhenjiang
\help
\help create user
\du
\x