# linux系统操作postgresql总结
# 1、登录数据库：
psql -U postgres -d postgres -h 127.0.0.1 -W
# 2、创建用户并且授权
CREATE USER jcjwwms WITH PASSWORD 'Galax@123';
CREATE DATABASE wmsdb OWNER jcjwwms;
GRANT ALL PRIVILEGES ON DATABASE wmsdb TO jcjwwms;
grant select on all tables in schema public to jcjwwms;---超级用户登录哪个数据库就授予哪个数据库权限
grant ALL PRIVILEGES on all tables in schema public to jcjwwms;---超级用户登录哪个数据库就授予哪个数据库权限
1
2
3
4
5
2
3
4
5
# 3、修改用户密码
ALTER USER postgres with encrypted password 'abc123';
# 4、启动，停止，重启服务
systemctl start postgresql-9.6.service
systemctl stop postgresql-9.6.service
systemctl restart postgresql-9.6.service
#查看一个服务的状态
systemctl status postgresql-9.6.service
#开机时启用一个服务
systemctl enable postgresql-9.6.service
#开机时关闭一个服务
systemctl disable postgresql-9.6.service
#查看服务是否开机启动
systemctl is-enabled postgresql-9.6.service
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 5、postgresql 字符串转整数 int、integer
--把'1234'转成整数
select cast('1234' as integer ) ;
--用substring截取字符串，从第8个字符开始截取2个字符：结果是12
select cast(substring('1234abc12',8,2) as integer)
---使用to_number函数来转换成整数
---to_number(text, text)? 返回的类型 numeric???? 把字串转换成numeric?? to_number('12,454.8-', '99G999D9S')
select to_number('12121','999999999')
1
2
3
4
5
6
7
2
3
4
5
6
7
# 6、备份恢复
- 备份数据库： /usr/pgsql-9.6/bin/pg_dump -h 127.0.0.1 -U postgres postgres > /usr/local/postgresqlbak/wms0612.bak
- 恢复数据库： /usr/pgsql-9.6/bin/psql -h 127.0.0.1 -U postgres -d postgres < /usr/local/postgresqlbak/wms0612.bak