1、安装存储库 RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2、安装指定版本
sudo yum install -y postgresql12-server
3、初始化
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
4、激活并开启
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12
5、查看服务情况
ps -ef|grep postgres
yum install lsof
lsof -i:5432
查看端口
netstat -an | grep 5432
6、修改配置文件:
这里将localhost修改成机器IP
修改:listen_addresses = ‘192.168.202.200’
vim /var/lib/pgsql/12/data/postgresql.conf
vim /var/lib/pgsql/12/data/pg_hba.conf
修改认证文件pg_hba.conf,登陆使用密码。把这个配置文件中的认证 METHOD的ident修改为trust,可以实现用账户和密码来访问数据库
解决psql: 致命错误: 用户 “postgres” Ident 认证失败 这个问题
由于这里需要设置可以远程链接,将127.0.0.1的都改成自己ip地址
7、重启postgresql服务器使设置生效
sudo systemctl restart postgresql-12
8、访问数据库
psql -h 192.168.202.200 -d postgres -U postgres -p 5432
执行 \q 退出交互式界面
9、修改默认密码:
sudo -u postgres psql
注意: 字符长度以及密码复杂度会有验证
ALTER USER postgres WITH PASSWORD 'L*****2*****x#';
\q
修改linux对应用户密码,与数据库密码保持一致
先清空默认密码:
sudo passwd -d postgres
开始输入新密码
sudo -u postgres passwd
10、简单命令:
查看当前数据库命令:
\l
查看表的详细信息
\d student;
查看当前库空间信息
select * from information_schema.schemata;
查看当前库sehcma大小,并按schema大小排序
SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint) as "disk space",
round((sum(table_size) / pg_database_size(current_database())) * 100,2)
as "percent(%)"
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY "percent(%)" desc;
查看指定空间表信息
SELECT table_name FROM information_schema.tables WHERE table_schema = 'ods';
psql切换schema,
切换以后的操作都是在此空间下
set search_path to ods;
查看当前库中所有表大小,并按降序排列
SELECT
table_catalog AS database_name,
table_schema AS schema_name,
table_name,
pg_size_pretty(relation_size) AS table_size
FROM (
SELECT
table_catalog,
table_schema,
table_name,
pg_total_relation_size(('"' || table_schema || '"."' || table_name || '"')) AS relation_size
FROM information_schema.tables
WHERE table_schema not in ('pg_catalog', 'public', 'public_rb', 'topology', 'tiger', 'tiger_data', 'information_schema')
ORDER BY relation_size DESC
)
AS all_tables
WHERE relation_size >= 1073741824;