大数据——PostgreSQL快速安装使用及简单命令_Caron_xcb_psql命令安装

未知 02-07 2663

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、简单命令：查看当前数据库命令：

查看表的详细信息

\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;