1第一节postgresql初始化创建

目录

目录

第一步 创建用户

第二步 创建空间扩展

第三步 导入数据库

结果：

结局问题：

1 数据库被占用

2常用语句

3 修改密码

4外网访问

---

---

第一步 创建用户

创建PG数据库，需要注意用户权限，空间扩展语句的使用，下面介绍如何开始创建

1在pg（postgresql 默认用户下）用户下创建数据库 和用户

CREATE USER ak_globe_hkygyxfx  CREATEDB LOGIN PASSWORD 'ak_globe_hkygyxfx';
 
CREATE DATABASE ak_globe_hkygyxfx;
 
GRANT ALL ON DATABASE   ak_globe_hkygyxfx    TO ak_globe_hkygyxfx;

第二步 创建空间扩展

在PG用户下 选择该数据库 执行空间扩展语句

 在navicate工具下，依然用 pg默认用户登陆，但是选择 我们新创建的数据库，执行如下空间扩展语句，否则的话导入的空间数据会导入新用户下 导入不进来

CREATE EXTENSION postgis;
 
CREATE EXTENSION postgis_topology;
 
CREATE EXTENSION postgis_sfcgal;
 
CREATE EXTENSION fuzzystrmatch;
 
CREATE EXTENSION address_standardizer;
 
CREATE EXTENSION address_standardizer_data_us;
 
CREATE EXTENSION postgis_tiger_geocoder;
 
CREATE EXTENSION pgrouting;

 截图如下： 用pG用户登陆，选择自己 新创建的库

第三步 导入数据库

在ak_globe_hkygyxfx   这个用户下导入数据，使用Pgadmin 工具导入 bat文件，bat文件内还有空间数据库索引等信息

首先创建连接

配置连接名字

编写IP 地址， 数据库，用户名，密码

用自己的用户身份登录后，右键，选择 restore导入数据库

注意不要有中文路径，不要有下滑线的路径

否则 没有权限

导入后进行数据查询测试

select   st_astext(t.geometry)  from  geo_line t ;

结果：

1  权限可查询 在新用户下

2  PG函数可使用，说明空间数据库已经可以使用

结局问题：

1 数据库被占用

select pg_terminate_backend(pid) from (select pid from pg_stat_activity where datname = '数据库名称' ) a;

相关

由于连接已满，可以关闭空闲的连接

1）查询当前所有连接的状态

select datname,pid,application_name,state from pg_stat_activity;

查看数据库剩余连接数：

select max_conn-now_conn as resi_conn from (select setting::int8 as max_conn,(select count(*) from pg_stat_activity) as now_conn from pg_settings where name = 'max_connections') t;

查看为超级用户保留的连接数: 

show superuser_reserved_connections;

psql: FATAL:  53300: sorry, too many clients already

数据库连接已满，无法建立新的连接。

1、关闭空闲连接
select datname,pid,application_name,state from pg_stat_activity; 
--查看目前所有的连接的进程id、应用名称、状态。
select pg_terminate_backend(pid) from pg_stat_activity; 
--通过pid终止空闲连接

当前总共正在使用的连接数：

select count(1) from pg_stat_activity;

显示系统允许的最大连接数

show max_connections;

显示系统保留的用户数

show superuser_reserved_connections ;

–按照用户分组查看

select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;

2常用语句

 SELECT    name,  ST_AsText(geomline) as geom  FROM   "AA"
    最近设施
            where+="  ORDER BY  geom <-> GeomFromEWKT('SRID=4326;"+postdata.getString("geoTxt2")+"') limit 5";

INSERT INTO "AA"  (name,geom) VALUES ('基站1001',GeomFromEWKT('SRID=4326;POINT(121.50 31.22)'));

 SELECT    name,  ST_AsText(geom) as geom  FROM   "geo_sfz_point"  
     where  1=1  ORDER BY  geom <-> GeomFromEWKT('SRID=4326;POINT(121.50 31.22)') limit 5;

 SELECT    ST_AsText(geom) as geom  FROM    geo_zyzx_polygon  LIMIT 10 OFFSET 0

INSERT INTO "AA"  (name,geogon) VALUES ('基站1002',GeomFromEWKT('SRID=4326;MULTIPOLYGON(((100.297174 27.739359,100.301248 27.739855,100.30324 27.738706,100.304529 27.736854,100.306184 27.733901,100.308992 27.729341,100.311838 27.725506,100.313838 27.724476,100.316142 27.724286,100.319399 27.724304,100.322512 27.723967,100.325213 27.723522,100.327387 27.723576,100.329325 27.724604,100.331431 27.726352,100.333506 27.727376,100.33539 27.728588,100.337359 27.730339,100.33951 27.733174,100.340807 27.73507,10

3 修改密码

1  修改连接用户密码
ALTER USER postgres WITH PASSWORD '123456';

4外网访问

Postgresql允许远程访问配置修改
两处修改：

1.postgresql.conf 文件

listen_addresses项值设定为“*”，在9.0 Windows版中，该项配置已经是“*”无需修改。

2.pg_hba.conf 文件

host all all 127.0.0.1/32 md5行下添加以下配置

host    all    all    0.0.0.0/0    md5

 pg_hba.conf 文件  总体替换如下 
  将IP   47.95.217.111替换
  注意都使用MD5
host    all             all             127.0.0.1/32            md5
host    all             all             47.95.11.11/32            md5
host    all    all    0.0.0.0/0    md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            md5
host    replication     all             47.95.211.11/32            md5
host    replication     all             ::1/128                 md5

如果不希望允许所有IP远程访问，则可以将上述配置项中的0.0.0.0设定为特定的IP值。

Linux下可通过find . -name "pg_hba.conf" 查找文件位置

window下 直接安装目录下 data/ 例如：C:\Program Files\PostgreSQL\9.3\data