PostgreSQL基本操作

目录

1.源码安装PostgreSQL

1.1.前置条件（root下操作）

1.1.1.卸载yum安装的postgresql

1.1.2.创建postgres用户

1.1.3.安装部分依赖

1.1.4.源码安装uuid

1.2.安装PostgreSQL

1.2.1.使用postgres用户管理PostgreSQL

1.2.2.下载解压postgres12源码包

1.2.3.编译及安装

1.2.4.安装contrib工具

1.2.5.配置环境变量

1.2.6.初始化postgreSQL 

1.3.PostgreSQL服务基本管理

1.3.1启动数据库

1.3.2停止数据库

1.3.3 重启数据库

1.3.4 查看数据库状态

2.psql基本使用

2.1.登录PostgreSQL数据库

2.2.断开psql客户端

2.3."?"查询PostgreSQL支持的所有命令

2.4."\l"查看所有数据库

2.5."\c"切换数据库

2.6."\d"看当前数据库中所有表、视图、序列

2.7."\d tablename"查看该表的表定义

2.8."\db"查看表空间的信息

2.9."\dn"查看所有的模式信息

2.10."\du"或”\dg"查看数据库中的所有角色或用户

2.11."\dt+ tablename"查看表大小

2.12."\di+"查看索引大小

2.13."\h 命令"查看SQL命令语法

2.14."\timing"显示SQL语句执行时间

2.15.“\x”可以把按行展示的数据变成按列展示

2.16.自动提交功能

2.17.查看数据库版本

---

1.源码安装PostgreSQL

1.1.前置条件（root下操作）

1.1.1.卸载yum安装的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

1.1.4.源码安装uuid

 wget ftp://ftp.ossp.org/pkg/lib/uuid/uuid-1.6.2.tar.gz
 tar -zxvf uuid-1.6.2.tar.gz
 cd uuid-1.6.2
 ./configure  
 make && make install

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.配置环境变量

 # 切换到用户根目录下(/home/postgres)
cd ~
# 编辑 .bash_profile
vi .bash_profile
# 修改为
# 新增 
PGHOME=/data/opt/postgresql
PGDATA=/data/pgdata
PATH后追加 $PGHOME/bin
例如：PATH=$PATH:$PGHOME/bin
# 保存，退出（命令模式下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

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）是创建的数据库名称

2.2.断开psql客户端

按Ctrl+D组合键 或输入命令：\q

2.3."?"查询PostgreSQL支持的所有命令

2.4."\l"查看所有数据库

2.5."\c"切换数据库

2.6."\d"看当前数据库中所有表、视图、序列

2.7."\d tablename"查看该表的表定义

2.8."\db"查看表空间的信息

2.9."\dn"查看所有的模式信息

2.10."\du"或”\dg"查看数据库中的所有角色或用户

2.11."\dt+ tablename"查看表大小

2.12."\di+"查看索引大小

2.13."\h 命令"查看SQL命令语法

2.14."\timing"显示SQL语句执行时间

再执行一次关闭

2.15.“\x”可以把按行展示的数据变成按列展示

2.16.自动提交功能

方法1：运行”begin”命令，然后执行DML语句，最后再执行commint或rollback预计
方法2：执行”\set AUTOCOMMIT off”关闭自动提交功能。AUTOCOMMINT必须是大小，小写无效也不报错。

2.17.查看数据库版本

方法1：psql –version
方法2：select version();