Postgresql之基础
Postgresql: https://www.postgresql.org/
[安装]
删除已经存在的pg:
yum remove -y postgresql* && rm -rf /var/lib/pgsql && rm -rf /usr/pgsql* && userdel -r postgres && groupdel postgres
yum:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum install -y postgresql14-server sudo /usr/pgsql-14/bin/postgresql-14-setup initdb # 初始化数据库 sudo systemctl enable postgresql-14 sudo systemctl start postgresql-14
Docker:
这里使用docker-compose.yml
version: '3' services: pgsql: image: postgres:14.2 container_name: pg14 restart: unless-stopped environment: POSTGRES_PASSWORD: 123456 TZ: Asia/Shanghai ports: - 5432:5432 volumes: - ./data:/var/lib/postgresql/data privileged: true
启动服务
docker exec -it pg14 psql -U postgres -d postgres
编译安装(生产环境推荐)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 | -- 下载源码包 wget https: //ftp.postgresql.org/pub/source/v14.2/postgresql-14.2.tar.gz --no-check-certificate wget https: //ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz wget https: //ftp.postgresql.org/pub/source/v12.7/postgresql-12.7.tar.gz wget https: //ftp.postgresql.org/pub/source/v11.12/postgresql-11.12.tar.gz wget https: //ftp.postgresql.org/pub/source/v10.17/postgresql-10.17.tar.gz wget https: //ftp.postgresql.org/pub/source/v9.6.22/postgresql-9.6.22.tar.gz wget https: //ftp.postgresql.org/pub/source/v9.4.26/postgresql-9.4.26.tar.gz -- 一些依赖包 yum install -y cmake make gcc zlib zlib-devel gcc-c++ perl readline readline-devel \ python36 tcl openssl ncurses-devel openldap pam flex -- 创建用户 useradd pgsql echo "lhr" | passwd --stdin pgsql -- 创建目录 mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg14,soft} chown -R pgsql:pgsql /postgresql chmod -R 775 /postgresql -- 编译 su - pgsql cd /postgresql/soft tar zxvf postgresql-14.2.tar.gz cd postgresql-14.2 ./configure --prefix=/postgresql/pg14 make -j 16 && make install # 编译完成,最后一行显示:All of PostgreSQL successfully made. Ready to install. -- 如果你希望编译所有能编译的东西,包括文档(HTML和手册页)以及附加模块(contrib),这样键入: make world -j 16 && make install-world #最后一行显示:PostgreSQL, contrib, and documentation successfully made. Ready to install. -- 源码安装postgresql时,而make时又没有make world,就会导致的pg最终没有类似pg_stat_statements的扩展功能 -- 配置环境变量 cat >> ~/.bash_profile << "EOF" export PGPORT=5432 export PGDATA=/postgresql/pgdata export PGHOME=/postgresql/pg14 export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export PATH=$PGHOME/bin:$PATH:. export PGHOST=$PGDATA export PGUSER=postgres export PGDATABASE=postgres EOF source ~/.bash_profile -- 初始化 su - pgsql /postgresql/pg14/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres -- 修改参数 cat >> /postgresql/pgdata/postgresql.conf << "EOF" listen_addresses = '*' port=5432 unix_socket_directories= '/postgresql/pgdata' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on EOF cat > /postgresql/pgdata/pg_hba.conf << EOF # TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5 EOF -- 启动 su - pgsql pg_ctl start pg_ctl status pg_ctl stop -- 配置系统服务 cat > /etc/systemd/system/PG14.service << "EOF" [Unit] Description=PostgreSQL database server Documentation=man:postgres(1) After=network.target [Service] Type=forking User=pgsql Group=pgsql Environment=PGPORT=5433 Environment=PGDATA=/postgresql/pgdata OOMScoreAdjust=-1000 ExecStart=/postgresql/pg14/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300 ExecStop=/postgresql/pg14/bin/pg_ctl stop -D ${PGDATA} -s -m fast ExecReload=/postgresql/pg14/bin/pg_ctl reload -D ${PGDATA} -s KillMode=mixed KillSignal=SIGINT TimeoutSec=0 [Install] WantedBy=multi-user.target EOF systemctl daemon-reload systemctl enable PG14 systemctl start PG14 systemctl status PG14 su - pgsql psql \password postgres or: alter user postgres with password 'lhr' ; -- 安装插件 create extension pageinspect; create extension pg_stat_statements; select * from pg_extension ; select * from pg_available_extensions order by name; |
编译参数介绍:
--with-pgport=NUMBER
把NUMBER设置为服务器和客户端的默认端口。默认是 5432。这个端口可以在以后修改,不过如果你在这里声明,那么服务器和客户端将有相同的编译好了的默认值。这样会非常方便些。通常选取一个非默认值的理由是你企图在同一台机器上运行多个PostgreSQL服务器。--with-openssl
编译SSL(加密)连接支持。这个选项需要安装OpenSSL包。configure将会检查所需的头文件和库以确保你的 OpenSSL安装足以让配置继续下去。--with-perl
制作PL/Perl服务器端编程语言。--with-python
制作PL/Python服务器端编程语言。--with-blocksize=BLOCKSIZE
设置块尺寸,以 K 字节计。这是表内存储和I/O的单位。默认值(8K字节)适合于大多数情况,但是在特殊情况下可能其他值更有用。这个值必须是2的幂并且在 1 和 32 (K字节)之间。注意修改这个值需要一次 initdb。--with-systemd
系统服务方式管理
更多编译选项请参考:http://postgres.cn/docs/13/install-procedure.html
[使用]
在默认配置下, 之允许本机访问Postgresql
# 切换到postgres用户 su - postgres Last login: Wed Mar 1 13:16:48 CST 2017 on pts/1 -bash-4.2$ psql psql (9.2.18) Type "help" for help. postgres=# 此时就在数据库postgres中了。
创建新用户:
create user dbuser with password '密码'; 注意点: 密码要使用单引号
修改postgres密码
alter user postgres with encrypted password '密码';
创建用户数据库:
create database demo owner dbuser;
赋权给用户
postgres=# GRANT ALL PRIVILEGES ON DATABASE demo TO dbuser;
注意:这里跟MySQL不同 是需要使用关键字DATABASE
开启远程访问:
配置文件:/var/lib/pgsql/data/postgresql.conf
listen_addresses = '0.0.0.0'
更多:
cat >> /var/lib/pgsql/14/data/postgresql.conf <<EOF
listen_addresses = '*'
port=5432
unix_socket_directories='/var/lib/pgsql/14/data'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF
/var/lib/pgsql/data/pg_hba.conf
添加/修改:允许任意用户从任意机器上以密码方式访问数据库,把下行添加为第一条规则: host all all 0.0.0.0/0 md5
更多:
cat > /var/lib/pgsql/14/data/pg_hba.conf << EOF
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 trust/md5
EOF
trust: 相当于免密登录
设置md5即可
重启服务:
sudo systemctl restart postgresql
数据库备份:
/usr/pgsql-9.6/bin/pg_dump -h 127.0.0.1 -U rw -d test > pgsql-20220819.sql 将整个库进行备份导出
开启防火墙端口:
firewall-cmd --add-port=5432/tcp --permanent firewall-cmd --reload firewall-cmd --list-port
常用命令:
1 2 | # 查看所有用户及权限 \du<br><br># 退出<br>\q<br><br><br># 进入数据库<br>\d dbname<br><br>cli链接:<br>psql -U lanlang -h 120.xx.xx.xxx -d postgres<br>-U: 指定用户名<br>-h: 链接地址<br>-d: 指定认证数据库<br>之后 输入密码即可登录<br><br><br>-- 从Postgresql 9.2开始,还可以使用URI格式进行连接:psql postgresql: //myuser:mypasswd@myhost:5432/mydb<br> psql postgresql://postgres:lhr@192.168.66.35:25432/postgres<br><br><br><br># 创建数据库<br>CREATE DATABASE dbname WITH OWNER=postgres ENCODINT='UTF-8';<br><br><br># 创建表<br>create table student (<br> id integer not null,<br> name character(32),<br> number char(5),<br> constraint student_pkey primary key (id)<br>);<br><br><br> |
常见问题:
程序链接PostgreSQL 时报错"no pg_hba.conf entry"
这条错误的原因是因为客户端远程访问postgresql受限所致,因为postgresql默认情况下除本机外的机器是不能连接的。
默认情况下,postgresql本机的连接是信任连接,只要有用户名就可以连接,不用密码. 要改变这种默认设置需要更改postgresql安装目录下的两个配置文件(pg_hba.conf与postgresql.conf)
##Added by liu.chunming
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
联接使用的认证方法:
trust
无条件地允许联接。这个方法允许任何可以与PostgreSQL数据库服务器联接的用户以他们期望的任意PostgreSQL 数据库用户身份进行联接,而不需要口令。
md5
要求客户端提供一个 MD5 加密的口令进行认证。
如果我想让10.86.12.0~10.86.12.154的IP段能访问PostgreSQL 数据库,需要增加下面一行:
host all all 10.86.12.0/24 trust
文档参考:
【推荐】2025 HarmonyOS 鸿蒙创新赛正式启动,百万大奖等你挑战
【推荐】博客园的心动:当一群程序员决定开源共建一个真诚相亲平台
【推荐】开源 Linux 服务器运维管理面板 1Panel V2 版本正式发布
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步