PostgreSQL的学习与安装
PG的定义：
与mysql的区别：
Linux下载PG的路径：
https://www.postgresql.org/download/
# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Install PostgreSQL:
sudo yum install -y postgresql16-server
yum install yum install -y postgresql14-server
-- 环境变量
echo "export PATH=/usr/pgsql-14/bin:$PATH" >> /etc/profile
# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14
##检查PG14的状态
[root@gitlab yum.repos.d]# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
Active: active (running) since 日 2023-11-19 22:46:44 CST; 45s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 6282 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 6287 (postmaster)
Tasks: 8
Memory: 5.4M
CGroup: /system.slice/postgresql-14.service
├─6287 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─6289 postgres: logger
├─6291 postgres: checkpointer
├─6292 postgres: background writer
├─6293 postgres: walwriter
├─6294 postgres: autovacuum launcher
├─6295 postgres: stats collector
└─6296 postgres: logical replication launcher
11月 19 22:46:44 gitlab systemd[1]: Starting PostgreSQL 14 database server...
11月 19 22:46:44 gitlab postmaster[6287]: 2023-11-19 22:46:44.669 CST [6287] 日志: 日志输出重定向到日志收集进程
11月 19 22:46:44 gitlab postmaster[6287]: 2023-11-19 22:46:44.669 CST [6287] 提示: 后续的日志输出将出现在目录 "log"中.
11月 19 22:46:44 gitlab systemd[1]: Started PostgreSQL 14 database server.
Hint: Some lines were ellipsized, use -l to show in full.
##检查PG的端口：5432
[root@gitlab yum.repos.d]# netstat -ntulp |grep -i 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 6287/postmaster
tcp6 0 0 ::1:5432 :::* LISTEN 6287/postmaster
配置远程访问：
2个配置文件：
/var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '*'
/var/lib/pgsql/14/data/pg_hba.conf
host all all 0.0.0.0/0 md5
#重启令配置生效
systemctl restart postgresql-14
修改postgres用户密码
alter user postgres with password 'postgres'
远程连接测试成功：
PG的基本使用：
#创建数据库：
postgres=# create database mydb;
CREATE DATABASE
postgres=#
#查看数据库\l
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
#进入到某个数据库
\c mydb
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=#
#删除数据库：drop
postgres=# drop database mydb;
DROP DATABASE
直接命令行创建数据库：
-bash-4.2$ createdb xdb
-bash-4.2$ createdb mydb;
#直接进到某个数据库：psql -d mydb
-bash-4.2$ psql -d mydb
psql (14.10)
输入 "help" 来获取帮助信息.
mydb=# pwd
mydb-# ls
mydb-# ls
mydb-# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
mydb | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
xdb | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(5 行记录)
