How to install and use PostgreSQL and PostGIS in Linux

This article mainly introduces the installation and use of PostgreSQL and PostGIS in Linux, and analyzes and explains the points that need to be paid attention to. Friends who need it can learn from it. I hope it can help everyone.

Installing PostgreSQL and PostGIS

PostgreSQL and PostGIS are already popular open source projects and have been included in the yum or apt packages of major Linux distributions. For Ubuntu, for example, just install the following packages:

$ sudo apt-get install postgresql-client postgresql postgis -y

For RedHat series, please install:

$ sudo yum install postgresql-server postgresql postgis

First time installation Afterwards, a database named postgres and a database user named postgres are generated by default. It should be noted here that a Linux system user named postgres is also generated. When we operate PostgreSQL in the future, we should do it in this newly created postgres user.

PostgreSQL configuration

If you are installing from the source code

It is not recommended to install from the source code. I have tried to install from the source code. It is too troublesome, and various Make install is error-prone. Finally I installed it using rpm. But since I spent some time researching and I successfully installed it, I'll record it - however, there may be errors, so if readers want to install from source code, please be prepared to roll back.

If you are using source compilation and make install installation, then this section requires additional configuration.

It seems that the installation of the CentOS series also requires...

After the default make install, the PostgreSQL installation directory is: /usr/local/pgsql/

First, according to For the reference of this link, you need to configure the environment variable

$ set $PGDATA = "/usr/local/pgsql/database"

but after executing pg_ctl start, an error will appear:

pg_ctl: directory "/usr/local/pgsql/database" is not a database cluster directory

In this case, you need to follow the steps in the official PostGreSQL documentation to create a real database:

PostgreSQL: Documentation: 9.1: Creating a Database Cluster

First of all Create a user account named postgres

$ usradd postgres
$ sudo chown postgres /usr/local/pgsql/database

Then enter this account and create database

$ sudo su postgres
$ initdb -D /usr/local/pgsql/database/

At this time shell It will output:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/database ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/database/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /usr/local/pgsql/database/ -l logfile start

Congratulations, you can start PostgreSQL next:

pg_ctl -D /usr/local/pgsql/database/ -l /usr/local/pgsql/database/psql.log start

After PostgreSQL is installed

Enter the postgres account and enter the PostgreSQL console:

$ sudo su postgres
$ psql

This is equivalent to the system user postgres logging in to the database as a database user with the same name, otherwise Every time we execute psql, we must specify the user in the parameters, which is easy to forget.

Set a password in psql - it should be noted that the password set here is not the password of the postgres system account, but the user password in the database:

postgres=# \password postgres

Then just enter the password as prompted.

Install PostGIS from source code

If you choose to install PostgreSQL from source code, you first need to determine what version of PostgreSQL you have installed

Then, go to PostGIS Go to the web page to check which version of PostGIS it corresponds to.

Finally, download the corresponding source according to the version of PostGIS

The final import is very troublesome. The author is stuck at this step, so I finally gave up installing from the source code...

Import PostGIS extension

According to different versions of postgresql and postgis, the path will be somewhat different, mainly because the path contains version information:

$ sudo su postgres
$ createdb template_postgis
$ createlang plpgsql template_postgis
$ psql -d template_postgis -f /usr/share/postgresql/9.5/contrib/postgis-2.2/postgis.sql
$ psql -d template_postgis -f /usr/share/postgresql/9.5/contrib/postgis-2.2/spatial_ref_sys.sql

In the above operation, an empty database called "template_postgis" was created. This database is empty and belongs to the postgres user. Be careful not to add data to this database. The reason why this database is called a "template" means that it is used for derivation.

The corresponding PostGIS path may be different. If it fails, try more near the above path and find a few .sql files to try.

Convert .shp file to PostGIS database

Convert .shp to .sql file

First find the file that needs to be converted, assuming that it needs to be converted The .shp file is: /tmp/demo.shp, then do the following:

$ sudo su postgres
$ cd /tmp
$ shp2pgsql -W GBK -s 3857 ./demo.shp entry > demo.sql

Here we need to explain the meaning of each part of the last sentence:

• -W GBK: If your .shp file contains Chinese characters, please add this option

• -s 3857: Specify the reference coordinates of the file system. My .shp file uses EPSG:3857

• ./demo.shp: The path of the .shp file

• entry: indicates that you want Imported database table name - assuming that this .shp file represents each entry, so I named it "entry"

• demo.sql

After getting the .sql file, you can directly import it into the PostgreSQL database.

Create a PostGIS database

You need to use the previous template here.

sudo su postgres
psql
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

• newdb: new database name

• originaldb: which is the previous template_postgis

• dbuser：你的账户名，我一般使用 postgres

导入 .sql 文件

sudo su postgres
psql
\c newdb
\i demo.sql
\d

可以看到，.sql 文件已经被导入了。

设置数据库权限

OK，现在我们在本机（服务器 IP 假设是 192.168.1.111）用以下命令登录 psql，会发现一段输出：

$ psql -h 192.168.1.111 -p 5432
psql: could not connect to server: Connection refused
    Is the server running on host "100.94.110.105" and accepting
    TCP/IP connections on port 5432?

这是因为 PostgreSQL 默认不对外开放权限，只对监听环回地址。要修改的话，需要找到 postgresql.conf 文件，修改值 listen_addresses：

listen_addresses = '*'

相关推荐：

Python连接PostgreSQL数据库的方法

PHP连接不上PostgreSQL的问题

PHP 读取Postgresql中的数组_PHP教程

The above is the detailed content of How to install and use PostgreSQL and PostGIS in Linux. For more information, please follow other related articles on the PHP Chinese website!