PostgreSQL基本安装和操作

Edited on 2022-07-04 In DB , PostgreSql

安装

我用的 Manjaro ，安装特别简单pacman -S postgresql就好了。

pgAdmin安装也是一条命令pacman -S pgadmin4。

也可以查看一下版本pacman -Ss |grep pgadmin。

下面是 Centos7 安装

postgres有些人估计都没有听说过，没关系，可以百度自己了解一下，这里说一下Centos7.6_1810版本的安装（目前的最新版）

查询有没有安装

1	rpm -qa\|grep postgre

查询postgre的yum源

1	yum list \|grep postgre

下载地址：https://www.postgresql.org/download/linux/redhat/

一、yum安装

点开下载网址，安装姿势有了。直接看（三、通用操作）

yum安装已经在/usr/bin中添加了链接，可以执行postgre的命令。

安装之后的路径是：/var/lib/pgsql/10/data/

二、解压安装

1 下载源代码并解压

wget https://ftp.postgresql.org/pub/source/v11.0/postgresql-11.0.tar.gz
 tar -xvzf postgresql-11.0.tar.gz  #解压pg包，并到安装目录
./configure
 sudo make
sudo make install

如果编译过程中遇到问题，比如缺少GCC、readline、zlib等，安装方式都是：

1
2
3

yum list |grep xxx
yum install -y xxxx
yum install -y xxxx-dev*

2 创建用户组和用户

1
2
3

groupadd postgres    #新增用户组
useradd -g postgres postgres    #新增用户
 passwd postgres    #为用户设置密码

3 创建数据目录

1
2
3

 mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data    #设置data文件加属性
chmod 700 /usr/local/pgsql/data    #设置data文件夹权限

4 数据库操作

su postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data    #初始化数据库
cd /usr/local/pgsql/bin/  & ./pg_ctl [start | stop | restart] -D /usr/local/pgsql/data/   #启动\停止\重启数据库
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &  #设置日志输出位置
/usr/local/pgsql/bin/createdb test    #创建测试数据库
5. /usr/local/pgsql/bin/psql test    #启动测试数据库

三、通用操作

修改密码

yum安装postgresql，默认会建一个名为”postgres”的系统账号，用于执行PostgreSQL；

1	su - postgres

切换用户后，提示符变更为“-bash-4.2$”; #同时数据库中也会生成一个名为”postgres”的数据库用户，且密码已自动生成; PostgreSQL在数据库用户同名的系统账号下登录免密；进入数据库后修改密码；

1
2
3

 -bash-4.2$  psql -U postgres
postgres=#  alter user postgres with password 'maxzhao'
postgres=#\password

配置文件中，添加访问（/usr/local/pgsql/data/postgresql.conf）

修改listen_addresses = ‘localhost’为listen_addresses = ‘*’，允许所有远程访问；

修改配置文件需要重启服务。

手动修改的时候，可能配置文件会有点长，在命令行模式下输入/listen_addresses 回车，就可以找到了。

1	[root@p~]# sed -i "s\|#listen_addresses = 'localhost'\|listen_addresses = '*'\|g" /var/lib/pgsql/10/data/postgre

修改data/pg_hba.conf

trust的意思是用账户密码登录。

# "local" is for Unix domain socket connections only
local   all             all                                  trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

四、防火墙操作

1. 查看防火墙是否关闭：firewall-cmd --state
2. 启动服务：systemctl start firewalld.service
3. 关闭服务：systemctl stop firewalld.service
4. 重启服务：systemctl restart firewalld.service
5. 显示服务的状态：systemctl status firewalld.service
6. 在开机时启用服务：systemctl enable firewalld.service
7. 在开机时禁用服务：systemctl disable firewalld.service
8. 查看服务是否开机启动：systemctl is-enabled firewalld.service;echo $?
9. 查看已启动的服务列表：systemctl list-unit-files|grep enabled
10. 添加开发端口：firewall-cmd --zone=public --add-port=5432/tcp --permanent
11. 重新加载防火墙：firewall-cmd --reload

https://www.jianshu.com/p/ee4ee15d3658

五、安装pgdmin4 web客户端

（我刚开始装的时候找个资料真的难）

打开下载链接：https://www.postgresql.org/ftp/

或者https://www.pgadmin.org/download/pgadmin-4-rpm/

建议直接选择rpm安装，简单省事。

1	yum list \|grep pgadmin4

因为我用过web，也不知道安装哪一个，干脆装个web（然后就装了好大一堆东西，一大堆pgadmini4的python依赖）。

1	yum install -y pgadmin4-web.noarch

安装之后的路径在：/usr/lib/python2.7/site-packages/pgadmin4-web/

打开pgadmin4

1 2	cd /usr/lib/python2.7/site-packages/pgadmin4-web/ python pgAdmin4.py

初次配置pgadmin4:设置邮箱/密码(登录用)
访问pgadmin4:http://localhost:5050

启动很麻烦？写个脚本就好了

（脚本能用！想关闭的话，ps -ef |grep pgadmin4 kill -9 +上面列出的第一个数字）

cd /usr/lib/python2.7/site-packages/pgadmin4-web/
vim pgadmin4.sh
#添加如下内容
#执行 i
#!/bin/bash
nohup  python /usr/lib/python2.7/site-packages/pgadmin4-web/pgAdmin4.py &
#执行esc  
#执行：wq!
chmod 777 pgadmin4.sh
ln -s   /usr/lib/python2.7/site-packages/pgadmin4-web/pgadmin4.sh /usr/local/bin/pgadmin4
#执行pgadmin4 就会启动web客户端

命令行登录

1	$ psql -U dbuser -d exampledb

登录后的权限操作参考
https://blog.csdn.net/u010166404/article/details/53188882
其实很多初学者也不一定看的懂。。

-- 连接命令（最后加上<filepath  就是还原命令，最后说）
-- 用户U大写
psql -h 127.0.0.1 -p 5432  -U user_name -d db_name
-- 创建用户的示例
CREATE USER userName WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT REPLICATION CONNECTION LIMIT -1 password 'pwd';
-- 创建超级用户
CREATE USER userName WITH SUPERMAN password 'pwd';
-- 创建模式,并分配给userName用户
CREATE SCHEMA schemaName authorization userName;
-- 更改权限
ALTER ROLE name SET enable_indexscan TO off;

常用的控制台命令

\password           设置密码
\q                  退出
\h                  查看SQL命令的解释，比如\h select
\?                  查看psql命令列表
\l                  列出所有数据库
\c [database_name]  连接其他数据库
\d                  列出当前数据库的所有表格
\d [table_name]     列出某一张表格的结构
\x                  对数据做展开操作
\du                 列出所有用户

常用的SQL语句

# 创建新表
CREATE TABLE table_name(name VARCHAR(20), birth DATE);
# 插入数据
INSERT INTO table_name(name, birth) VALUES('欧文', '1994-08-23');
# 查询记录
SELECT * FROM table_name;
# 更新数据
UPDATE table_name set name = '勒夫' WHERE name = '欧文';
# 删除记录
DELETE FROM table_name WHERE name = '欧文' ;
# 添加字段
ALTER TABLE table_name ADD email VARCHAR(40);
# 更改字段类型
ALTER TABLE table_name ALTER COLUMN birth SET NOT NULL;
# 设置字段默认值（注意字符串使用单引号）
ALTER TABLE table_name ALTER COLUMN email SET DEFAULT 'example@example.com';
# 去除字段默认值
ALTER TABLE table_name ALTER email DROP DEFAULT;
# 重命名字段
ALTER TABLE table_name RENAME COLUMN birth TO birthday;
# 删除字段
ALTER TABLE table_name DROP COLUMN email;
# 表重命名
ALTER TABLE table_name RENAME TO backup_table;
# 删除表
DROP TABLE IF EXISTS backup_table;
# 删除库
\c postgres;
DROP DATABASE IF EXISTS hello;

备份和恢复

1 2	$ pg_dump --format=t -d db_name -U user_name -h 127.0.0.1 -O -W > dump.sql $ psql -h 127.0.0.1 -U user_name -d db_name < dump.sql

分布式事物

XA是open group提出的分布式事务处理规范，JTA支持XA规范，JTA只规定了接口，有些应用容器提供实现，也有一些三方的开源实现可用，比如Atomikos。

如果PostgreSQL参与分布式事务(XA)处理，则需要在配置文件postgres.conf中设置参数，此参数用于指定分布式事务中两步提交准备事务的最大数量。默认值为0，此时不支持分布式事务。

max_prepared_transactions参数值不应该小于max_connections参数值，这样每一个session都可以至少有一个可用的准备事务。

1 2	max_connections = 100 max_prepared_transactions = 100

本文地址：https://www.jianshu.com/p/80b690597541

本文地址： https://github.com/maxzhao-it/blog/post/32518/