Table of Contents
Table of Contents
The first step is to create a user
Step 2: Create space extension
Step 3 Import the database
result:
Ending question:
1 database is occupied
2Commonly used statements
3 Change password
4External network access
The first step is to create a user
When creating a PG database, you need to pay attention to user permissions and the use of space expansion statements. Here is how to start creating it.
1Create the database and user under the pg (postgresql default user) user
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;
The second step is to create a space extension
Under the PG user, select the database and execute the spatial expansion statement
Under the navicate tool, still log in as the default user pg, but select our newly created database and execute the following spatial expansion statement. Otherwise, the imported spatial data will be imported under the new user and the import will not come in
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;
The screenshot is as follows: Log in as pG user and select your newly created library
The third step is to import the database
Import data under the user ak_globe_hkygyxfx and use the Pgadmin tool to import the bat file. The bat file also contains spatial database index and other information.
First create the connection
Configure connection name
Write IP address, database, username, password
After logging in as your own user, right-click and select restore to import the database.
Be careful not to have Chinese paths or underlined paths.
OtherwiseNo permission
Conduct data query test after import
select st_astext(t.geometry) from geo_line t;
Result:
1 Permissions can be queried under the new user
2 The PG function can be used, indicating that the spatial database can be used
Ending question:
1 database is occupied
select pg_terminate_backend(pid) from (select pid from pg_stat_activity where datname = 'database name' ) a;
Related
Idle connections can be closed because the connection is full
1) Query the status of all current connections
select datname,pid,application_name,state from pg_stat_activity;
Check the number of remaining connections to the database:
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;
View the number of connections reserved for superusers:
show superuser_reserved_connections;
psql: FATAL: 53300: sorry, too many clients already
The database connection is full and a new connection cannot be established.
1. Close idle connections
select datname,pid,application_name,state from pg_stat_activity;
–View the process ID, application name, and status of all current connections.
select pg_terminate_backend(pid) from pg_stat_activity;
–Terminate idle connections by pid
Total number of connections currently in use:
select count(1) from pg_stat_activity;
Displays the maximum number of connections allowed by the system
show max_connections;
Display the number of users retained by the system
show superuser_reserved_connections;
– View grouped by users
select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;
2Commonly Used Statements
SELECT name, ST_AsText(geomline) as geom FROM “AA”
nearest facilities
where + =” ORDER BY geom <-> GeomFromEWKT(‘SRID=4326;” + postdata.getString(“geoTxt2”) + “‘) limit 5”;
INSERT INTO “AA” (name,geom) VALUES (‘Base Station 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 (‘Base Station 1002’,GeomFromEWKT(‘SRID=4326;MULTIPOLYGON(((100.297174 27.739359,100.301248 27.739855,100.30324 27.738706,10 0.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.723 522,100.327387 27.723576,100.329325 27.724604,100.331431 27.726352,100.333506 27.727376,100.33539 27.728588,100.337359 27.730 339,100.33951 27.733174,100.340807 27.73507,10
3 Change Password
1 Modify the connection user password
ALTER USER postgres WITH PASSWORD ‘123456’;
4External network access
Postgresql allows remote access to configuration modifications
Two modifications:
1.postgresql.conf file
The value of the listen_addresses item is set to “*”. In the 9.0 Windows version, this configuration is already “*” and does not need to be modified.
2.pg_hba.conf file
Add the following configuration under the host all all 127.0.0.1/32 md5 line
host all all 0.0.0.0/0 md5
The overall replacement of the pg_hba.conf file is as follows
Replace IP 47.95.217.111
Note that MD5 is used
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
If you do not want to allow remote access from all IPs, you can set 0.0.0.0 in the above configuration items to a specific IP value.
Under Linux, you can find the file location through find . -name “pg_hba.conf”
Under window, directly install the directory data/. For example: C:\Program Files\PostgreSQL\9.3\data