PostgreSQL入门

2022-04-14 22:46:15

PostgreSQL

一种超越MYSQL的开源关系型数据库
告知：作者在初学PostgreSQL的过程中将学习思路和操作流程记录下来，希望对初学者有一定帮助
环境告知：
1 操作系统：Centos7.7
PostgreSQL入门
2 必须的依赖工具库
① make版本3.80或以上
PostgreSQL入门
②GCC编译器
③GNU Readline库：它允许psql记住你输入的每个命令，这样就可以通过上下方向键快速输入之前的命令，默认开启，也可以通过编译参数–without-readline来禁止它，建议保留默认。
④Zlib库

yum install -y readline \
readline-devel \
openssl \
openssl-devel \
zlib \
zlib-devel

3 环境变量

#默认安装路径/usr/local/pgsql/
export PG_HOME=/usr/local/pgsql/
export PATH="$PG_HOME/bin:$PATH"

##########################################################
1 安装
作者提供三种安装方式，并亲测有效
1.1 yum安装
流程：
①下载官方yum源
https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#路径可能根据官方的更新而失效，进入连接，选择你想要的版本
https://yum.postgresql.org/repopackages.php#pg10
②yum安装

    yum clean all \
 && yum repolist \
 && yum -y install postgresql10-server

③初始化
yum安装默认路径

/usr/pgsql-10/bin/postgresql-10-setup initdb

④启动

   systemctl restart postgresql-10.service \
&& systemctl enable postgresql-10.service

⑤验证

#使用postgres用户进入
[root@db1 ~]# su postgres
#进入PostpreSQL数据库
bash-4.2$ psql
#退出
postgres=# \q
#返回终端
bash-4.2$ exit
#已退出
[root@db1 ~]#

PostgreSQL入门
⑥安装成功，到此，yum安装PostgreSQL完成！！！
###########################################################
1.2 源码安装
流程：
①获取官方源码包
我准备装11.7版本（12版本还不稳定），选择其他版本进入下面的第一个地址后退选择目的版本即可
安装官方教学：
https://www.postgresql.org/docs/11/install-getsource.html
源码包：
https://www.postgresql.org/ftp/source/
11.7:连接可能随着官方的更新而失效
https://ftp.postgresql.org/pub/source/v11.7/postgresql-11.7.tar.gz
②源码安装
指定安装路径，便于管理:/usr/local/postgresql

--prefix=/usr/local/postgresql

依赖gcc等，自行安装（根据报错提示安装）

mkdir -p /usr/local/postgresql
wget https://ftp.postgresql.org/pub/source/v11.7/postgresql-11.7.tar.gz \
-O /usr/local/postgresql/postgresql-11.7.tar.gz
cd /usr/local/postgresql/
tar -xvf postgresql-11.7.tar.gz
cd postgresql-11.7
#按照默认安装
./configure  --prefix=/？
#如果出现报错：configure: error: readline library not found
yum -y install readline readline-devel
#再运行
./configure  --prefix=/？
make && make install
#成功
#生成/usr/local/pgsql目录，默认路径/usr/local/pgsql

详细的configure配置：
https://blog.csdn.net/pg_hgdb/article/details/81946121
③初始化数据库

cd /usr/local/pgsql/
mkdir /usr/local/pgsql/data/
chown postgres: /usr/local/pgsql/data/
sudo su postgres
#-D定义数据的存放路径
/usr/local/pgsql/bin/initdb  -D /usr/local/pgsql/data

④启动与开机自启
在解压路径下有一个
/usr/local/postgresql/postgresql-11.7/contrib/start-scripts/linux

cp -a /usr/local/postgresql/postgresql-11.7/contrib/start-scripts/linux \
/etc/init.d/postgresql
vi /etc/init.d/postgresql
#根据实际情况修改下列两行内容
prefix=/usr/local/pgsql          #安装路径
PGDATA="/usr/local/pgsql/data"   #数据库路径
#centos6版本的启动方式，centos7版本的启动方式还未研究
service  postgresql start
service  postgresql status
#关闭
service  postgresql stpo
#启动成功，能看见PID
#使用postgres用户连接
sudo su postgres
/usr/local/pgsql/bin/psql
#进入
#安装成功
#查看版本
postgres=# select version();
                             version                                                 
----------------------------------------------------------------
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled 
by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

#制作软连接
rm -rf  /usr/bin/psql
ln -s  /usr/local/pgsql/bin/psql  /usr/bin/psql
sudo su postgres
psql
#成功进入

⑤！！！缺少源码启动方式，后面补充
补充：

#源码启动
#初始化数据库时，提示了启动方式，注意有一个logfile
#这里在安装目录增加一个文件夹log，用来记录日志
mkdir -p /usr/local/pgsql/logs/server.log
#启动，并指定数据库与日志
pg_ctl -D /usr/local/pgsql/data/ \
-l /usr/local/pgsql/logs/server.log \
(start/stop)

⑥安装成功，到此，yum安装PostgreSQL完成！！！
postgresql详细配置，端口，开放网段
https://blog.csdn.net/Linjingke32/article/details/80393576

###########################################################
1.3 二进制安装
①下载二进制包
pgsql有很多类型的包，对于不同linux发行版都有对应的编译好的包，安装很方便，另外如果对于通用的linux平台可以编译源码安装或者安装官方编译好的二进制包，源码包的安装仅仅比二进制安装多出一个编译步骤，其余的都一样，所以这里使用安装方式是安装编译好的二进制包
pgsql官网地址：https://www.postgresql.org/，进入后点击download就来到下载页，这里点击Linux下面的Other Linux选项，然后点击下方的tar.gz archive下载二进制归档
　　PostgreSQL入门
然后就来到最终的pgsql下载页了，地址为：https://www.enterprisedb.com/download-postgresql-binaries，如下图下载最新版本的10.1https://sbp.enterprisedb.com/getfile.jsp?fileid=12574&_ga=2.120855624.1731532218.1589505053-724926244.1589505053
PostgreSQL入门

如上图根据机器的位数下载对应的包，我这里下载的是x86-64的包，为：postgresql-10.1-1-linux-x64-binaries.tar.gz，下载之后准备安装
②安装二进制包
将下载好的二进制包上传到你的目标路径，我放到/root/postgresql###验证结果表面，不能放在root下，后来我转移到/pgsql

useradd postgres
mkdir /pgsql
cd /pgsql
tar -xvf postgresql-10.12-1-linux-x64-binaries.tar.gz
#得到pgsql文件夹
cd pgsql
#创建数据库文件和日志文件data logs
mkdir data logs
chown postgres:  /root/postgresql/pgsql -R
#初始化数据库data
sudo su postgres
./bin/initdb  -D data
#初始化成功，提示启动命令
#启动，定义启动日志名称server.log
./bin/pg_ctl  -D data -l logs/server.log  start
#查看帮助
./bin/pg_ctl  --help
#启动成功
#连接postgresql
./bin/psql
#建议设置软连接或者别名
#软连接设置了很多次都没有成功，几个bin sbin都试了
alias psql='/pgsql/pgsql/bin/psql'
echo 'alias psql="/pgsql/pgsql/bin/psql"' >> /etc/bashrc

*③安装成功，到此，yum安装PostgreSQL完成！！！
安装部分结束。
###########################################################

2 PostgreSQL基本操作
2.1 添加用户和新数据库
初次安装后，默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是，同时还生成了一个名为postgres的Linux系统用户。
下面，我们使用postgres用户，来生成其他用户和新数据库。好几种方法可以达到这个目的，这里介绍两种。

2.1.1 使用PostgreSQL控制台

#首先，新建一个Linux新用户，可以取你想要的名字，这里为dbuser。

sudo adduser dbuser

#然后，切换到postgres用户。

sudo su - postgres

#下一步，使用psql命令登录PostgreSQL控制台。

psql

#这时相当于系统用户postgres以同名数据库用户的身份，登录数据库，这是不用输入密码的。
#如果一切正常，系统提示符会变为"postgres=#"，表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。

#第一件事是使用\password命令，为postgres用户设置一个密码。

\password postgres

#第二件事是创建数据库用户dbuser（刚才创建的是Linux系统用户），并设置密码。

CREATE USER dbuser WITH PASSWORD 'password';

#第三件事是创建用户数据库，这里为exampledb，并指定所有者为dbuser。

CREATE DATABASE exampledb OWNER dbuser;

#第四件事是将exampledb数据库的所有权限都赋予dbuser，否则dbuser只能登录控制台，没有任何数据库操作权限。

GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;

#最后，使用\q命令退出控制台（也可以直接按ctrl+D）。

\q

2.1.2 使用shell命令行

#添加新用户和新数据库，除了在PostgreSQL控制台内，还可以在shell命令行下完成。
#这是因为PostgreSQL提供了命令行程序createuser和createdb。还是以新建用户dbuser和数据库exampledb为例。

#源码和二进制安装方式则需要指定./bin/createuser ./bin/createdb 命令
#也可以制作软连接和设置别名Alias实现

#首先，创建数据库用户dbuser，并指定其为超级用户。

sudo -u postgres createuser --superuser dbuser

sudo -u postgres createuser -s dbuser -P #交互式设置新用户密码

#接着，在shell命令行下，创建数据库exampledb，并指定所有者为dbuser。

sudo -u postgres createdb -O dbuser exampledb

##########################################################
2.2 登录数据库

#修改配置文件，开放所有IP
vi postgresql.conf
vi pg_hba.conf 
#添加新用户和新数据库以后，就要以新用户的名义登录数据库，这时使用的是psql命令。

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432 -W

#上面命令的参数含义如下：
#-U指定用户，-d指定数据库，-h指定服务器，-p指定端口，-W指定密码。

#输入上面命令以后，系统会提示输入dbuser用户的密码。
#输入正确，就可以登录控制台了。

#psql命令存在简写形式。
#如果当前Linux系统用户，同时也是PostgreSQL用户，则可以省略用户名（-U参数的部分）。
#举例来说，我的Linux系统用户名为ruanyf，且PostgreSQL数据库存在同名用户
#则我以ruanyf身份登录Linux系统后，可以直接使用下面的命令登录数据库，且不需要密码。

psql exampledb

#此时，如果PostgreSQL内部还存在与当前系统用户同名的数据库，则连数据库名都可以省略。
#比如，假定存在一个叫做ruanyf的数据库，则直接键入psql就可以登录该数据库。

psql

#另外，如果要恢复外部数据，可以使用下面的命令。

psql exampledb < exampledb.sql

###########################################################
2.3 控制台命令集

#除了前面已经用到的\password命令（设置密码）和\q命令（退出）以外，控制台还提供一系列其他命令。

\h：查看SQL命令的解释，比如\h select。 
\?：查看psql命令列表。 
\l：列出所有数据库。 
\c [database_name]：连接其他数据库。 
\d：列出当前数据库的所有表格。 
\d [table_name]：列出某一张表格的结构。 
\du：列出所有用户。 
\e：打开文本编辑器。 
\conninfo：列出当前数据库和连接的信息。

##########################################################
2.4 数据库SQL

#基本的数据库操作，就是使用一般的SQL语言。

# 创建新表 

CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE); 

# 插入数据 

INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22'); 

 # 选择记录 

SELECT * FROM user_tbl; 

# 更新数据 

UPDATE user_tbl set name = '李四' WHERE name = '张三'; 

# 删除记录 

DELETE FROM user_tbl WHERE name = '李四' ; 

# 添加栏位 

ALTER TABLE user_tbl ADD email VARCHAR(40); 

# 更新结构 

ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL; 

# 更名栏位 

ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup; 

# 删除栏位 

ALTER TABLE user_tbl DROP COLUMN email; 

# 表格更名 

ALTER TABLE user_tbl RENAME TO backup_tbl; 

# 删除表格 

DROP TABLE IF EXISTS backup_tbl;

到此，PostgreSQL的安装和基本操作部分已完成，下面对架构进行优化。
##########################################################

3 PostgreSQL主从结构
数据库版本必须一致
https://www.jianshu.com/p/2d07339774c0
主服务器：

#先创建一个新目录用来存放归档文件
mkdir /opt/pgsql/pg_archive
#创建一个数据库用户进行主从同步。创建用户replica，并赋予登录和复制的权限
postgres# CREATE ROLE replica login replication encrypted password 'replica'
#修改pg_hba.conf，允许replica用户来同步
#允许94连接到主服务器
host all          all       192.168.200.90/32     trust  
#允许94使用replica用户来复制 
host replication  replica   192.168.200.90/32      md5   
#这样，就设置了replica这个用户可以从主服务器进行流复制请求
#注：
#第二个字段必须要填replication
#修改postgresql.conf
listen_addresses = '*'   # 监听所有IP
archive_mode = on  # 允许归档
archive_command = 'cp %p /opt/pgsql/pg_archive/%f'  # 用该命令来归档logfile segment
wal_level = hot_standby 
max_wal_senders = 32 #这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s #设置流复制主机发送数据的超时时间
max_connections = 100 #这个设置要注意下，从库的max_connections必须要大于主库的
#配置完两个文件后重启服务器
#测试从服务器能否连接主服务器数据库
#在从服务器上运行如下命令：
psql -h 192.168.200.113 -U postgres
#看看是否能进入数据库。若可以，则正常

从服务器：

#从主节点拷贝数据到从节点
su - postgres
rm -rf /opt/pgsql/data/*   #先将data目录下的数据都清空
pg_basebackup -h 192.168.200.113 -U replica -D /opt/pgsql/data -X stream -P  # 从93拷贝数据到94（基础备份）
#./bin/pg_basebackup -h 192.168.200.91 -U replication -D data/  -F p  -P  -W
mkdir /opt/pgsql/pg_archive
#配置recovery.conf
#复制/usr/pgsql-9.4/share/recovery.conf.sample 到 /opt/pgsql/data/recovery.conf
#recovery.conf
standby_mode = on    # 说明该节点是从服务器
primary_conninfo = 'host=192.168.20.93 port=5432 user=replica password=replica'  # 主服务器的信息以及连接的用户
recovery_target_timeline = 'latest'
#配置postgresql.conf
wal_level = hot_standby
max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈

#配置完后重启从服务器

验证部署：

#在主节点上执行：
select client_addr,sync_state from pg_stat_replication;
#结果如下:
postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr 	 | sync_state
-----------------+------------
 192.168.200.90  | async
(1 行记录)
#说明90是从服务器，在接收流，而且是异步流复制。
#进程查看
ps aux | grep postgres

亲测有效：
主服务器
PostgreSQL入门
从服务器
PostgreSQL入门
注意：这里有坑，官方二进制包的动态库不够，会失败
建议使用yum安装，源码安装未测试
拓展：

#备注：归档标识文件archive_active 需要手工创建。
archive_command = '/bin/date'  # (change requires restart)
#archive_command='cp %p /archive/pg93/%f'

archive_command = 'test ! -f /pgsql/pgsql/archive/10/archive_active  ||  cp %p /pgsql/pgsql/archive/10/%f'

archive_command = 'if [ -f "/archive/pg93/archive_active" ]; then cp %p /archive/pg93/%f; fi'

archive_command =  'DIR=/pgarch/arch/`date +%F`; sudo test ! -d $DIR && sudo mkdir $DIR; sudo test ! -f $DIR/%f && sudo cp %p $DIR/%f'
上一篇：pgsql --批量修改


下一篇：pgsql基本操作