「rpm 安装」linux_postgresql安装rpm

rpm 安装

pgdg-centos10-10-2.noarch.rpm下载

百度云下载地址 https://pan.baidu.com/s/1t5A5F7EspecqpdXOGr1Usw

官网下载地址 https://yum.postgresql.org/

卸载postgresql

rpm -qa | grep postgres 检查PostgreSQL 是否已经安装如果已经安装了可以通过rpm -ev xxx 删除

rpm -qal | grep postgres 检查PostgreSQL 安装位置手动删除文件夹目录

创建postgres的linux的用户组

以后操作都使用该账户操作

useradd postgres

passwd postgres

#两次输入密码并确认这里密码都为postgres

安装postgresql

yum localinstall pgdg-centos10-10-2.noarch.rpm

yum list | grep postgresql #能够列表所有关于postgresql列表这里选中postgresql10-server.x86_64

yum install -y postgresql10-server.x86_64

初始化数据库

/usr/pgsql-10/bin/postgresql-10-setup initdb

postsql安装完成的物理目录的位置

/usr/pgsql-10/bin/ #postgresql

/var/lib/pgsql/10/data #postgresql存放数据的地方

设置开机启动数据库

sudo systemctl enable postgresql-10.service #设置数据库的开机启动

启动/重启数据库

sudo systemctl start postgresql-10 #启动数据库

/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile start #启动数据库手工的方式

systemctl restart postgresql-10 #重启数据库

/usr/pgsql-10/bin/pg_ctl restart -D /var/lib/pgsql/10/data #手工重启数据库

修改postgres用户的密码

su - postgres

psql

ALTER USER postgres WITH PASSword 'postgres';

修改远程配制文件

vi /var/lib/pgsql/10/data/pg_hba.conf

host all all 0.0.0.0/0 md5 #修改成这样

METHOD这列要修改成md5

vi /var/lib/pgsql/10/data/postgresql.conf

修改listen_addresses 及port 这两项

放开防火墙

默认的端口为5432

vi /etc/sysconfig/iptables

-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

service iptables restart #重启

postgis的安装

yum list | grep postgis 查询一下

yum install postgis24_10.x86_64 #安装postgis

扩展test数据库的gis

假如已经创建了test数据库

\c test

CREATE EXTENSION postgis; #开户了gis

CREATE TABLE mylocation ( 
  id SERIAL PRIMARY KEY,
  geom GEOMETRY(Point, 4326),
  name VARCHAR(128),
  x double precision,
  y double precision
); 
 
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.0001 0)', 4326),'zhangsan',0.0001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.001 0)', 4326),'zhangsan',0.001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.001 0)', 4326),'zhangsan',0.001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.1 0)', 4326),'zhangsan',0.1,0
);
 
 
 
SELECT id, name,geom,x,y,   ST_distanceSphere(
                      geom,
                      ST_GeometryFromText('POINT(0 0)')) distance
FROM mylocation
WHERE ST_Dwithin(
  geom, 
  ST_GeomFromText('POINT(0 0)', 4326),
  0.001
)order BY distance asc;;

查询单位为米

SELECT id, name,geom,x,y,   ST_DistanceSphere(
                      geom,
                      ST_GeometryFromText('POINT(0 0)')) distance
FROM mylocation
WHERE ST_DWithin(
  geom::geography, 
  ST_GeomFromText('POINT(0 0)', 4326)::geography,
  1000
) ORDER BY distance asc;

linux_postgresql安装rpm