PostgreSQL基本操作

#检查是否已经通过rpm安装postgresql数据库
rpm -qa|grep postgresql
# 卸载包含postgresql名的程序包
yum remove postgresql*
# 删除postgres用户及其对应的用户目录（/home/postgres）
userdel -r postgres
# 检查是否存在 /usr/psqlXXX文件夹，有的话删除，例如
rm -rf /usr/pgsql-12

1.1.2.创建postgres用户

# 创建postgres用户
useradd postgres
# 为postgres用户设置密码
passwd postgres

1.1.3.安装部分依赖

yum install -y perl-ExtUtils-Embed python-devel bison flex readline-devel zlib-devel gcc gcc-c++ wget libicu-devel

1.2.安装PostgreSQL

1.2.1.使用postgres用户管理PostgreSQL

# 切换用户
su postgres
# 回到用户目录
cd ~

1.2.2.下载解压postgres12源码包

tar -zxvf postgresql-12.13.tar.gz

1.2.3.编译及安装

# 新建PostgreSQL安装路径下的文件夹(/data/opt/postgresql)
mkdir postgresql
# 进入解压后的源码文件夹(/home/postgres/postgresql-12.13)
cd postgresql-12.3

# 检查、并配置(--prefix=/data/opt/postgresql 为配置postgreSQL的安装路径)
./configure --prefix=/data/opt/postgresql --with-blocksize=32 --with-wal-blocksize=32 --with-wal-segsize=64
-–prefix 指定安装目录。
--with-perl
--with-python
--enable-thread-safety
--with-uuid=ossp
-–with-blocksize 以KB为单位设置block size。此为表中存储及I/O的单位。默认为8kb。值必须为2的1到32次方（kb），可选。
-–with-wal-blocksize 以kb为单位，设置WAL block size。此为WAL日志中存储及I/O的单位。默认为8kb。值必须为2的1到64次方（kb），可选。
-–with-wal-segsize 设置WAL段大小，以兆字节为单位。这是WAL日志中每个文件的大小。默认大小为16兆字节。该值必须是1到64（兆字节）之间的2的幂，可选。
注意：数据块、WAL日志块的大小需要在编译的时候配置才生效，对应此时编译出来的PostgreSQL程序创建的PostgreSQL数据库，不能使用其他块大小的PostgreSQL程序启动!!!

# 编译及安装
make && make install

1.2.4.安装contrib工具

cd postgresql-12.3/contrib
make && make install

1.2.5.配置环境变量

# 编辑 .bash_profile
# vi ~/.bash_profile

PGHOME=/data/opt/postgresql
PGDATA=/data/pgdata
PATH=$PATH:$PGHOME/bin

export PATH PGHOME PGDATA

# 保存，退出（命令模式下Esc）
# 输入： wq! Enter(回车)
# 重载.bash_profile
source ~/.bash_profile

1.2.6.初始化postgreSQL

# /data/pgdata为postgres数据文件的存放路径,如果环境变量已经配置PGDATA=/data/pgdata，则 -D /data/pgdata 参数可不加
initdb -D /data/pgdata

1.3.PostgreSQL服务基本管理

1.3.1启动数据库

# /data/pgdata为postgres数据文件的存放路径，里面也有一些配置文件,如果环境变量已经配置PGDATA=/data/pgdata，则 -D /data/pgdata 参数可不加
启动：pg_ctl -D /data/pgdata -l logfile start

1.3.2停止数据库

停止：pg_ctl -D /data/pgdata -l logfile stop [-m smart]
-m 用于指定数据库停止方式：
smart: smart模式等待所有客户端断开连接以及任何在线备份结束。如果该服务器是热备，一旦所有的客户端已经断开连接，恢复和流复制将被终止。
fast: fast模式（默认）不会等待客户端断开连接并且将终止进行中的在线备份。所有活动事务都被回滚并且客户端被强制断开连接，然后服务器被关闭。
immediate: immediate模式将立刻中止所有服务器进程，而不是做一次干净的关闭。这将导致下一次重启时进行一次崩溃恢复。

1.3.3 重启数据库

重启：pg_ctl -D /data/pgdata -l logfile restart

1.3.4 查看数据库状态

查看状态：pg_ctl -D /data/pgdata -l logfile status

1.3.5 设置允许远程访问配置

修改pg_hba.conf文件，在最后一行添加添加:

#vi /data/pgdata/pg_hba.conf

host all all 0.0.0.0/0 md5

修改postgresql.conf文件，设置监听所有端口：

#vi /data/pgdata/postgresql.conf

listen_addresses = '*'

重启数据库：

pg_ctl -l logfile restart

2.psql基本使用

psql是PostgreSQL的一个命令行交互式客户端工具，它具有非常丰富的功能，类似于Oracle的命令行工具sqlplus。

2.1.登录PostgreSQL数据库

使用psql命令登录数据库，格式：psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
-h后面的IP地址（192.168.1.210）是数据库所在的IP地址
-p后面的端口（5432）是数据库的监听端口
-U后面的参数（postgres）是创建的用户名
-d后面的参数（postgres）是创建的数据库名称