centos7安装postgres并开启远程访问

安装教程：

https://www.postgresql.org/download/linux/redhat/

开启远程访问：

修改配置文件

配置文件目录 /var/lib/pgsql/12/data/ 如果是安装的postgres 12  则 目录是 12

1.修改配置文件postgresql.conf

listen_addresses = ‘localhost‘取消注释，更改为：listen_addresses = ‘*‘ 

2.pg_hba.conf

在该配置文件的host all all 127.0.0.1/32 trust行下添加以下配置，或者直接将这一行修改为以下配置

host    all    all    0.0.0.0/0    trust

用工具连接上数据库后，修改数据库的密码

ALTER USER postgres WITH PASSWORD 'postgres';

然后再将上面的trust改成md5重启数据

 

 

最后重启postgres

关闭postgres服务的命令 sudo systemctl stop postgresql-12
启动postgres服务的命令 sudo systemctl start postgresql-12

postgres的安装目录/usr/pgsql-12 里面有lib和bin目录

 创建账号并指定只能访问某个数据库（建议使用这个方式）。这个方式创建的账号，可以访问其他数据库的public模式，所有建议不要将表放到public模式下，自己新建一个模式

 

 

 

创建账号并指定只能访问某个数据库（使用命令方式）

创建账号，限制访问指定的数据库
创建一个账号为pgde 密码为Esad@2023的账号
CREATE ROLE pgde WITH LOGIN PASSWORD 'Esad@2023';
指定该角色（账号pgde）只能访问 de库：
GRANT CONNECT ON DATABASE de TO pgde;
并将其赋予对 public 模式的使用权限。（这个可以实际的时候不怎么用）
GRANT USAGE ON SCHEMA public TO pgde;
授予新账号对 public 模式中所有表的所有权限：（包括 SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER 等权限。）
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO pgde;

　

指定该角色（账号regularuser）只能访问 jh_geodb库：//支持在mis模式下面创建表

创建账号

CREATE USER regularuser WITH PASSWORD 'Esri@1243';

GRANT USAGE ON SCHEMA mis, public TO regularuser;

//给账号赋予可以在mis模式下创建表的权限

GRANT CREATE ON SCHEMA mis TO regularuser;

//可以在模式下面操作表的权限

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA mis, public TO regularuser;

限制账号只能通过某个ip登录

限制创建的账号只能在指定的ip登录数据库
创建一个名为 example_group 的新登录组，并将账号 pgde 添加到该组中：
CREATE ROLE example_group WITH LOGIN;
GRANT example_group TO pgde;

编辑 PostgreSQL 的主配置文件 postgresql.conf，并确保以下配置行没有被注释掉：
listen_addresses = '*'

编辑 PostgreSQL 的 pg_hba.conf 文件，添加一行用于限制 example_user 只能从指定 IP 地址登录：
host    de      pgde    192.0.2.1/32    md5
以上代码将限制 pgde 只能从 IP 地址为 192.0.2.1 的主机登录到 de 数据库，并使用 md5 加密的密码验证方式。

注意：192.0.2.1/32 表示仅限制单个 IP 地址，如果要限制一段 IP 地址范围，可以使用类似 192.0.2.0/24 的 CIDR 表示法。

重启 PostgreSQL 服务以使配置生效：
sudo service postgresql restart

　　

 

 

下载已经安装的postgres

yum remove postgresql*

 

如果是docker安装的postgres数据，lib目录在

 

安装命令

docker run --name postgres -p 12432:5432 -e POSTGRES_PASSWORD=J123456 -v /root/pgsql:/var/lib/postgresql/data -d 367676e04d30

 --privileged 会让创建的容器账号有root账号的权限。这样会带来很多的安全问题，建议不要使用这个命令

docker run --privileged --name postgres -p 12432:5432 -e POSTGRES_PASSWORD=J123456 -v /root/pgsql:/var/lib/postgresql/data -d 367676e04d30

加入/root的目录空间占满了，postgres自动挂了，无法启动： 

容器启动错误No space left on device

首先关闭docker容器 systemctl stop docker

可以修改容器的映射路径，将/root/pgsql 目录替换成磁盘空间更大的目录，比如 /mnt/pgsql

比如现在的数据库的容器id 是123456

进入目录/var/lib/docker/contaniers/123456

修改两个文件的内容 

hostconfig.json
config.v2.json

修改hostconfig.json

修改config.v2.json

MountPoints属性中的值，修改Source和Spec里面的Source的值

启动docker: systemctl start docker

然后启动数据库容器

 

常用命令

进入数据库 su - postgres
登录数据库命令 psql -U postgres -h localhost
查询所有数据库 psql -l
进入某个数据库 psql testdb
\l 列出所有数据库
\d 列出数据库中所有表
\dt 列出数据库中所有表
\d [table_name] 显示指定表的结构
\di 列出数据库中所有 index
\dv 列出数据库中所有 view
\h sql命令帮助
\q 退出连接
\c [database_name] 切换到指定的数据库
\c 显示当前数据库名称和用户
\conninfo 显示客户端的连接信息
\du 显示所有用户
\dn 显示数据库中的schema
\encoding 显示字符集
select version(); 显示版本信息

如果是docker启动的数据库，先进入容器里面，在用上面的命令 docker exec -it 容器id bash，退出容器 exit

在查询数据库里面的表数据的时候，要确数据库的owner是否属于认当前连接的这个账号，如果部署则无法查询出表数据

查看数据库下的所有（schema）：
select * from information_schema.schemata;
查询schema中所有表：
select table_name from information_schema.tables where table_schema = 'myschema';
查看指定的schema下的所有表信息
SELECT * FROM pg_tables WHERE schemaname = 'your_schema_name';
修改schema的owner
alter schema system owner sde;
修改表的owner
ALTER TABLE public.table1 OWNER TO new_owner;

　　

设置账号可以操作序列的权限

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO target_user;

创建账号，修改scheme的所有者‘

CREATE USER ynqxuser WITH PASSWORD 'Esri@1249';

授予该账号对指定数据库的所有权限。执行以下 SQL 命令来赋予该账号对特定数据库'
GRANT ALL PRIVILEGES ON DATABASE ynqx TO ynqxuser;

授予该账号对所有表的权限（必须切换到该数据库下面执行）
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ynqxuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA mis TO ynqxuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sde TO ynqxuser;

修改schema的所有者 
ALTER SCHEMA schema_name OWNER TO new_owner;
修改schema下面的所有表的所有者 
DO $$
DECLARE
    v_table_name text;
    cursor_tables CURSOR FOR SELECT table_name AS table_name_alias FROM information_schema.tables WHERE table_schema = 'mis';
BEGIN
    OPEN cursor_tables;
    LOOP
        FETCH NEXT FROM cursor_tables INTO v_table_name;
        EXIT WHEN NOT FOUND;
        EXECUTE 'ALTER TABLE ' || quote_ident(v_table_name) || ' OWNER TO ynqxuser';
    END LOOP;
    CLOSE cursor_tables;
END $$ LANGUAGE plpgsql;

　　

使用超级用户或具有足够权限的用户登录到 PostgreSQL 数据库。
执行以下命令来授予当前用户对 "sde" 模式的所有权限：
GRANT ALL PRIVILEGES ON SCHEMA sde TO your_user;

如果你只想授予用户对 "sde" 模式下的特定表的权限，而不是整个模式，你可以使用类似的 GRANT 命令，但是将表名添加到命令中，如下所示：
GRANT ALL PRIVILEGES ON TABLE sde.aaa TO your_user;

　　

实现对某个表的数据备份和恢复（下面的命令只能在服务器上面执行，不能在数据库或navicat上面使用）

通过在数据库服务器上面执行命令实现数据备份和恢复（不能通过发送sql语句的方式来实现，所以不能通过java代码来实现）
备份数据命令（allproject4490 是要备份的表名）
pg_dump -U postgres -d geodb_jh -t allproject4490 -f /home/backup_file.bak
数据恢复命令
psql -U postgres -d geodb_jh -f /home/backup_file.bak

通过发送sql语句的方式来实现（可以通过java代码来执行sql）（/var/lib/pgsql/11/backups 是数据库服务器上的一个路径）
如果不是超级管理员执行如下命令，需要给账号（这里账号是sde）设置权限

    grant pg_write_server_files to sde;
    grant pg_read_server_files to sde;

copy (SELECT * FROM allproject4490) TO '/var/lib/pgsql/11/backups/allproject4490.csv' WITH (FORMAT 'csv', HEADER true, DELIMITER ',');
数据恢复命令
COPY allproject4490 FROM '/var/lib/pgsql/11/backups/allproject4490.csv' WITH (FORMAT 'csv', HEADER true, DELIMITER ',');

　　如果创建的角色名称有大写，比如 Test，在给账号赋权限的时候要用双引号

GRANT ALL PRIVILEGES ON SCHEMA sde TO "Test";