Linux-postgres Linux-postgresql安装

Linux下postgresql的安装

一、 数据库的安装

我们根据官网给出的下载引导，就可以拿到对应操作系统版本的安装命令，本文是在CentOS7 root用户下安装的postgres12，官网给出的是rpm包安装：

(1) 更新源并安装软件包：

# 更新软件源
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 下载并安装postgres12
yum install -y postgresql12-server

(2) 初始化数据库：

# 初始化数据库
/usr/pgsql-12/bin/postgresql-12-setup initdb
# 添加数据库服务
 systemctl enable postgresql-12

(3) 数据库服务管理：

# 启动数据库服务
systemctl start postgresql-12
# 停止数据库服务
systemctl stop postgresql-12
# pg_ctl start/stop/reload -D DATA_DIR

rpm格式包为postgres的服务编写了系统服务/usr/lib/systemd/system/postgresql-12.service，systemctl会读取/usr/lib/systemd/system下服务名.service文件，然后调用相关的命令。postgres的服务管理命令是pg_ctl，在使用该命令时，需要通过-D选项指定pg数据目录，或者提前设置好PGDATA环境变量。读service文件可以大致了解pg相关的目录结构：

目录	说明
安装目录	/usr//pgsql-12 (bin为二进制目录；lib为相关依赖共享库文件，包含了client端)
数据目录	/var/lib/pgsql/12/data

​ 了解了postgres的目录结构后方便我们后续方便卸载干净postgres的相关文件，以免下次安装时出错，比如initdb命令需要确保所使用的数据目录是一个空文件夹，否则命令会执行失败，下面看看如何卸载rpm安装的postgres：

(4) 卸载postgresql数据库：

# 1 停止服务， 一般情况下卸载先停止服务
systemctl stop postgresql-12
# 2 查询已安装的rpm包
rpm -qa | grep postgres
# 3 卸载rpm包, 粘贴2中列出的包名(会删除rpm解压的相关目录)
rpm -e ...
# 4 卸载残留 数据目录
rm -rf /var/lib/pgsql/12/data
# 5 可选项 删除用户
# userdel -r postgres

二、数据库的使用：

​ 我们初始化数据库时，initdb程序默认创建了postgres用户和组，数据库操作使用psql命令，如果单纯键入psql命令，是默认使用当前命令行同名的登录用户和数据库，而现在数据库只创建了一个相关的角色postgres，而我们并不知道postgres用户的密码，shell环境的登录密码，默认新建后的postgres用户数据库登录密码为空。

(1) 第一次登入数据库：

错误提示（root用户下）： psql: error: FATAL: Peer authentication failed for user “postgres”

# 1 切换到postgres用户, -选项同时切换环境到用户目录
[root@localhost ~]# sudo su - postgres
# 2 psql登录
bash-4.2$           psql
# 3 改变角色postgres密码
postgres=#          \password postgres
# 4 退出postgres管理命令行
postgres=# 		    \q

(2) psql命令选项说明：

psql
-h # 指明远程主机地址，默认本地127.0.0.1
-p # 指明远程主机端口，默认5432
-U # 指明登录用户
-d # 指明所选数据库
# 默认是所登录命令行用户同名的角色以及数据库

(3) 数据库命令行使用：除了我们熟悉的SQL语句外，还有pg独有的命令

postgres=# \l				# 列出所有数据库
postgres=# \c DB_NAME    	 # 连接使用数据库
postgres=# \d				# 列出所有数据库表名
postgres=# \d TABLE_NMAE	 # 列出表结构
create user hr with password 'Abcd1234';
postgres=# \q				# 退出数据库命令行
postgres=# create user USER_NAME with password 'xxx';	# 创建角色
postgres=# create database DB_NMAE owner USER_NAME;		# 创建数据库

三、postgres 配置文件：

​ postgresql主要涉及两个配置文件pg_hba.conf、postgresql.conf， postgresql.auto.conf用于覆盖postgresql.conf配置的内容，因为后者中的选项是在太多了，你可以专门配置postgresql.auto.conf文件，使配置生效。

配置文件的详细解释可以专项搜索，这里只是快速使用上手。

(1) postgresql开启远程连接：

[postgresql.conf]
# 默认使用本地连接，允许所有主机连接请用*
listen_address = 'localhost'
-> listen_address = '*'
# 配置最大连接数，和数据库连接池相关
-> max_connections = 100

[pg_hba.conf] ban-allow(禁止-允许) 客户端认证文件
# 默认本机无密码可连接, 投入使用需开启远程任何主机可密码连接
# host	all		all		127.0.0.1/32	trust 
-> host		all		all 	0.0.0.0/0		md5

配置了远程连接后，若服务正在运行，需要重启服务（pg_ctl --reload），如果配置了远程连接，还是连接不上，还需要把postgres服务端口进行防火墙放行：

# 查看防火墙服务
servcie firewalld status
# 添加端口
firewall-cmd --zone=public --add-port=5432/tcp --permanent
# 重启防火墙
firewall-cmd --reload
# 查看当前已开放端口
firewall-cmd --list-ports

(2) postgresql日志配置： 见postgres日志配置

四、postgresql备份、postgresql恢复：

(1) 全库备份

可以使用-F指定输出文件格式，默认使明文SQL文本§，(c表示postgres适用于pg_restore的客户化归档，文件重排压缩)

pg_dump -h XXX -U XXX -d XXX -f 'XXX.sql'
pg_dump -h XXX -U XXX -d XXX -Fc -f 'XXX.dump'

(2) 全库恢复

# 需要提前创建数据库、用户等，使用psql命令登录，见二(2)
pg_restore -h XXX -U XXX -d XXX -f 'XXX'

单表或者多表查询的导出，一般导出到csv文件方便观察数据，这个命令属于数据库命令行使用的，需要psql先登录。

(3) 数据导出

\COPY (SQL) TO 'DDD/XXX.csv' WITH CSV HEADER;							
\COPY TABLE_NMAE TO 'DDD/XXX.csv' WITH CSV HEADER;
\COPY TABLE_NAME(COLUMN_NAME, ...) TO 'DDD/XXX.csv' WITH CSV HEADER;

(4) 数据导入

\COPY TABLE_NAME FROM 'DDD/XXX.csv' WITH (FORMAT csv, DELIMITER ',', HEADER true);

​ 一般就是把一个表的全部列(带标题) 导出即可(执行一个没有符合条件的SQL语句导出WITH CSV HEADER)，只填充必填列再进行导入。也可以psql命令直接执行COPY命令：

# psql -c选项 可以连接数据库并执行命令后退出
psql -h XXX -U XXX -d XXX -c "COPY (SELECT * FROM test WHERE 1=2) TO '/home/apple/out.csv' WITH CSV HEADER;" 

这就是关于postgres的初步安装以及命令简介，关于python的数据库操作在后续将会介绍。