在Linux环境下安装PostgreSQL

由于实际的生产环境，PostgreSQL数据库都是部署在linux服务器上的，因此记录一下通过源码安装与命令行操作PG数据库的过程

下载

官方下载网址：https://www.postgresql.org/ftp/source/

我这里下载的是PostgresSQL源码版本为：postgresql-13.3.tar.gz

安装

1.创建postgres用户与用户组

--创建postgres用户组
groupadd postgres
--创建postgres用户，其用户组为postgres
useradd postgres -g postgres

2.安装必需依赖

yum install -y gcc gcc-c++
yum install -y readline-devel
yum install -y zlib-devel

3.解压源码包并进行编译

--解压源码包
tar -zxvf postgresql-13.3.tar.gz
--解压出 postgresql-13.3文件夹，移动到指定路径下
mv postgresql-13.3 /opt/
--创建一个路径用于存放编译后的postgresql
mkdir /opt/postgresql
--进入postgresql-13.3路径下，编译源码并将编译后的postgresql存放在/opt/postgresql路径下
cd /opt/postgresql
./configure --prefix=/opt/postgresql
--构建
make
--安装文件
make install

4.创建存放postgresql数据的目录，初始化数据库并启动

--在/opt/postgresql路径下创建数据目录pg_data
mkdir /opt/postgresql/pg_data
--修改数据目录的所属用户与用户组均为postgres
chown -R postgres:postgres /opt/postgresql/pg_data
--切换到postgres用户，并初始化数据库
su postgres
/opt/postgresql/bin/initdb -D /opt/postgresql/pg_data
--启动数据库，并将日志打印到pg.log文件上
/opt/postgresql/bin/pg_ctl -D /opt/postgresql/pg_data -l /opt/postgresql/pg_data/pg.log start

5.创建测试数据库，修改超级管理员postgres的密码

--创建test数据库
/opt/postgresql/bin/createdb test
--访问test数据库
/opt/postgresql/bin/psql test
--修改管理员postgres的密码为postgres
ALTER USER postgres WITH PASSWORD 'postgres';

修改配置

远程连接授权

--修改pg_hba.conf文件
vi /opt/postgresql/pg_data/pg_hba.conf
修改IPV4的连接
# IPv4 local connections:
host    all             all             0.0.0.0/0            trust
--修改postgresql.conf文件
vi /opt/postgresql/pg_data/postgresql.conf
修改listen_address为任意ip
listen_addresses = '*' 
--完成修改后重启数据库
/opt/postgresql/bin/pg_ctl -D /opt/postgresql/pg_data -l /opt/postgresql/pg_data/pg.log restart

psql常用命令

PostgreSQL的交互式终端

命令行中使用的psql命令

参数	参数含义
-h	数据库服务器的IP
-p	数据库服务器的端口号
-U	连接数据库服务器时使用数据库用户
-w	连接服务器时不需要输入用户密码(默认不输入用户密码)
-W	连接服务器时需要输入用户密码
-d	指定要连接的数据库(默认连接postgres数据库)
-c	执行单一的SQL命令
-f	执行sql脚本文件
-V	输出版本信息

--连接本地数据库，端口号为5432，使用postgres用户，连接test数据库
./psql -h 127.0.0.1 -p 5432 -U postgres -d test
--执行sql脚本文件/root/pg.sql
./psql -h 127.0.0.1 -p 5432 -U postgres -d test -f /root/pg.sql
--执行SQL语句
./psql -h 127.0.0.1 -p 5432 -U postgres -d test -c "sql_one;sql_two;"
--查看数据库版本
./psql -V

pg命令行中使用的psql命令

命令	含义
\h	查看sql语法
\l	列出所有数据库
\dn	列出所有schema(模式)
\db	显示所有表空间(相当于一个目录，表放在表空间中)
\d	查看当前schema中所有的表、视图、序列
\dt	只显示匹配的表
\di	只显示匹配的索引
\ds	只显示匹配的序列
\dv	只显示匹配的视图
\df	只显示匹配的函数
\sf	查看指定的函数定义
\du	列出数据库中所有用户
\dp	显示表的分配情况
\c	切换数据库
\q	退出pg命令行

--查看所有数据库
postgres=# \l
--显示pg内部的函数acos
postgres=# \df acos
--查询acos函数的函数定义
postgres=# \sf acos
--退出pg命令行
postgres=# \q

pg_dump常用命令

把PostgreSQL数据库抽取为一个脚本文件或其他归档文件

常用参数
-f xx
--file=xx
将输出发送到指定文件

-d xx
--dbname=xx
指定要被转储的数据库名

-a
--data-only
只转储数据(表数据、大对象、序列值)，而不转储模式(数据定义)

-s
--schema-only
只转储对象定义(模式),而不转储数据

-O
--no-owner
不输出设置对象拥有关系来匹配原始数据库的命令。默认情况下，pg_dump会发送ALTER OWNER或SET SESSION AUTHORIZATION语句来设置被创建的数据库对象的拥有关系，除非该脚本被一个超级用户启动，否则这些语句将会失败。想要一个脚本能够被任意用户恢复，可指定此选项

-x
--no-privileges
--no-acl
防止转储访问特权

--column-inserts
--attribute-inserts
将数据转储为带有显式列名的INSERT命令

--inserts
将数据转储为INSERT命令，此选项如果列序重新安排，则恢复有可能会一起失败，--column-inserts选项对于列序改变是安全的

--disable-dollar-quoting
禁止在函数体中使用美元符合引用，并且强制它们使用SQL标准字符串语法被引用

--quote-all-identifiers
强制引用所有标识符。列名、表名也会加上引号，避免歧义

-n xx
--schema=xx
只转储匹配xx的模式，选择模式本身以及它所包含的所有对象。当没有指定这个选项时，目标数据库中所有非系统模式都将被转储

-N xx
--exclude-schema=xx
不转储匹配xx模式的任何模式，-N可以指定多次来排除多个模式

-t xx
--table=xx
只转储名字匹配xx的表，可以写多个-t来选择多个表。当-t被使用时，-n和-N选项不会有效果，非表对象将不会被转储

-T xx
-exclude-table=xx
不转储匹配xx模式的任何表，可以写多个-T来排除多个表

-b
--blobs
在转储中包括大对象，这是当--schema、--table、--schema-only被指定时的默认行为

-B
--no-blobs
在转储中排出大对象

-E xx
--encoding=xx
以指定的字符集编码创建转储，默认情况下，转储会以该数据库的编码创建

-F xx
--format=xx
选择输出的格式：
p | plain :输出一个纯文本形式的SQL脚本文件(默认值)
c | custom :输出一个适合作为pg_restore输入的自定义格式归档
d | directory :输出一个适合作为pg_restore输入的目录格式归档
t | tar :输出一个适合输入到pg_restore中的tar格式归档

-?
--help
显示有关pg_dump命令行参数的帮助

eg: 把一个数据库school转储到一个SQL脚本文件，只转储public模式的数据，不转储test1表和test2表，转储数据为INSERT命令

pg_dump -U postgres -h 127.0.0.1 -d school -f /root/school_data.sql --schema=public -T test1 -T test2 --column-inserts --data-only --disable-dollar-quoting --quote-all-identifiers --no-owner --no-privileges