postgresql数据库常见操作

数据库迁移备份

1.启动本地虚拟机，查看ip，使用xshell连接
2.进入docker，postgresql容器，使用pg_dump远程连接数据库，执行：
pg_dump -h 127.0.0.1 -U postgres -p 11005 -d nacos > nacos.dump
执行：docker cp postgres:/nacos.dump ./将数据拷出来
3.将数据拷到目标服务器中，执行：
psql -h 127.0.0.1 -U postgres -p 5432 -d nacos_db -f “C:/Users/Administrator/Desktop/nacos.dump”
导入数据

psql连接操作

psql -h localhost -p 5432 -U postgres

\l 列出所有数据库

\c dbname 切换到其他数据库

\dt 列出表名称

create database name 创建数据库

角色添加与授权

create user b with password ‘1234’

CREATE DATABASE test2;

GRANT CONNECT ON DATABASE test2 TO b;

revoke CREATE ON SCHEMA tuisong FROM a

— 取消对所有表的权限
revoke ALL PRIVILEGES on all tables in schema public from b;

REVOKE ALL PRIVILEGES ON DATABASE test2 FROM a;
REVOKE ALL on pg_class FROM a;

REVOKE CONNECT ON DATABASE test2 FROM a;

revoke connect on database test2 from a

grant all PRIVILEGES on tuisong.a to b

POSTGIS安装过程

1.安装docker
2.使用docker安装postgresq镜像
3.创建postgresql容器命令：docker run –name postgres1 –restart=always -e POSTGRES_PASSWORD=123456 -p 15432:5432 -v /home/data/postgresql:/var/lib/postgresql/data -d 0f
4.给postgresql安装postgis扩展参考：https://blog.csdn.net/weixin_39168541/article/details/127518146
5.导入数据库

postgis 修改表的空间参考：

select UpdateGeometrySRID(‘pg_xswd’, ‘geom’, 4490);

字符串转日期

select to_timestamp(“FINISH_TIME”, ‘dd/mm/yyyy hh24:mi:ss’) AS TIME FROM “PM_PROJECT”

序列

创建序列
CREATE SEQUENCE test_tables_id_seq START 1;
查询序列
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema = ‘public’;

日期加上一段时间

update table set time= ( time + ‘7 min’)