PostgreSQL source code installation _ correct

Source: Internet
Author: User
Tags psql postgis postgresql client
The Linux release version is Ubuntu server 6.06, And the Ubuntu version is only 8.1. To be an early adopter, 8.2.0 has improved the compatibility based on the Windows platform to facilitate the conversion of the platform. At the same time, you can customize some parameters and settings for source code installation. Although Ubuntu has good usability and compatibility, and some features are newer, it is somewhat similar to Windows, the reduction also closes a lot of details, so developers do not have the opportunity to try to understand the installation process and parameters of some programs. It is not necessarily a good thing for program development.

1. Install postgresql8.2.0

1. Download the source code.
Download the latest source code from

2. decompress the package.
Tar-vxf PostgreSQL * .tar.gz
3. After Entering the extract directory, you can use./configure-help to view available parameters. I am using:

#./Configure -- prefix =/home/shaken/PostgreSQL -- without-Readline -- without-zlib

The previous parameter is to install your software in the/OPT directory according to the usual practice. The next two parameters are because Readline and zlib are not installed in my system. In fact, you can install them.

In ubuntu, we can remove the following two -- without parameters:
# Apt-Get install libreadline5-dev zlib1g-dev
Solve the Problem of configure when installing PostgreSQL In ubuntu.

4. Compile and install:

# Make

# Make install

5. Add Postgres, the highest user of the PostgreSQL database, and set the password:

# Adduser Postgres
# Mkdir/home/shaken/PostgreSQL/Data
# Chown Postgres/home/shaken/PostgreSQL/Data

It is better to do this again:
# Chown-r Postgres/home/shaken/PostgreSQL

6. log in as Postgres:

# Su-Postgres

7. initialize the database:

8. Run the database server:

$/Home/shaken/PostgreSQL/bin/postmaster-D/home/shaken/PostgreSQL/data> logfile 2> & 1 &

9. Create a database:

$/Home/shaken/PostgreSQL/bin/createdb Test

10. Run the client on the local machine for management:

$/Home/shaken/PostgreSQL/bin/Psql Test

This requires you to be familiar with the basic SQL syntax.

By now, PostgreSQL is installed. However, in order to remotely use the database, for example, to use pgadmin on another Windows computer on the network to manage and use the PostgreSQL server, you still need to make some modifications, these modifications mainly involve two aspects: one is to modify two configuration files of PostgreSQL and the other is to modify some user permissions of the data base:

11. Modify the PostgreSQL configuration file PostgreSQL. conf in the/home/shaken/PostgreSQL/data directory:

Listen_address = 'localhost'

Remove the preceding comment and set 'localhost' '*'.

12. Modify the PostgreSQL configuration file pg_mirror.conf, which is located in the/home/shaken/PostgreSQL/data directory. Add the following sentence to the file:

Host All all Password

It means that machines with 192.168.0. * In the same network can use all databases in the form of passwords. For more specific parameter meanings, see the annotations in the configuration file.

Make sure that the configuration is correct. Otherwise, you cannot connect to the PostgreSQL database remotely.

13 restart the PostgreSQL Server:

$/Home/shaken/PostgreSQL/bin/pg_ctl stop-D/home/shaken/PostgreSQL/Data

Stop the original server

$/Home/shaken/PostgreSQL/bin/postmaster-I-d/home/shaken/PostgreSQL/data> logfile 2> & 1 &

Start again and add a-I Parameter

14 some simple configuration of Database User Permissions, which are used in Psql below

# Create userwen1 password '123 ';

Create a user and set the password

# Createdatabase data1 owner wen1;

Create a database

# Create tablewen11 (name varcha (10 ))

Create a data table

# Alter table wen11 owner to wen1;

Modify the owner of a data table.

Now you can use other PostgreSQL client tools on other computers in the LAN to open the data1 database with the username "wen1.

Some problems that need to be improved:

1. Question about Chinese support: Can I use encoding = 'utf-8' to create a database?

2. PostgreSQL is automatically started when the system starts. For details, refer to the corresponding STARTUP script of Debian.

Install and configure phpPgAdmin (optional)

To manage and configure my database server, a convenient management program is required for PostgreSQL users like me. There are many such management tools. I chose phpPgAdmin as a convenient phpMyAdmin when I used MySQL. PhpPgAdmin is an Internet-based PostgreSQL management utility. It processes all basic functions and an advanced function, such as triggers and views, is a management software based on B/S mode and must be installed on the server.

The installation process is as follows:
1. Download the source code. The latest version is 4.0.1.
# Cd
# Wget http: // ....../phpPgAdmin-4.0.1.tar.gz
2 decompress:
# Tar zxvf phpPgAdmin-4.0.1.tar.gz
3. Check whether Apache + PHP and PHP support PostgreSQL has been installed in the system. If not, install the following:
# Apt-Get install Apache PhP4 php4-pgsql
Note: Modify the Apache configuration file to make Apache support PHP.
4. Make phpPgAdmin available on the client. I usually create a symbolic connection under the apapche directory:
# Ln-S/root/phpPgAdmin-4.0.1/var/www/pgadmin
5. modify the configuration file config/config. Inc. php of phpPgAdmin. The following parameters must be modified:
$ Conf ['servers'] [0] ['host'] = 'localhost ';
Indicates that the database server is on the local machine
$ Conf ['extra _ security'] = false;
Allow Logon using the Postgres account.
6. Use the browser on the client and enter the address:
Http: //.../pgadmin
You can manage the PostgreSQL database.

3. Install postgis

1 It is best to install proj4 and OGR first. I am lazy and have not installed it. It should be because the performance is compromised and it does not affect the installation and use.
In Debian, you can install postgis first:
# Apt-Get install proj libgeos-Dev

2. Download the source code.

3. decompress the package to the directory.

4 Configuration:

#./Configure -- prefix =/opt/postgis -- With-pgsql =/home/shaken/PostgreSQL/bin/pg_config


5. Compilation and installation:

# Make

# Make install

Some work after 6:

$/Home/shaken/PostgreSQL/bin/createdb data1

$/Home/shaken/PostgreSQL/bin/createlang plpgsql data1

$/Home/shaken/PostgreSQL/bin/Psql-D data1-F lwpostgis. SQL

$/Home/shaken/PostgreSQL/bin/Psql-D data1-F spatial_ref_sys. SQL

Note: Find and enter the directory containing the following two SQL files and then execute them.

Now we can use postgis. First, try the two small programs that convert the shape file and postgis data in postgis.

Iv. initial use of postgis

There are not many articles on the use of postgis on the Internet, and they are not detailed enough. Today, after some exploration, we have gained some basic application experience, hoping to reduce the twists and turns of some beginners.

Assume that I want to set a test. the SHP file (which should actually contain three files) is imported to the test data table in the PostgreSQL database data1. For the sake of security, the owner of the database data1 and related data tables is set to wen1, and then the postgis data is used on the GIS client, such as qgis udig. The detailed process is;

1. Assume that both the PostgreSQL database server and postgis are installed. (See "Install postgresql8.1.3 + postgis1.1.2" in the source code ")
2. Access Psql with database superuser Postgres:
#/Home/shaken/PostgreSQL/bin/Psql (note the path)
The following operations are performed on the Psql console.
3 Basic settings:
# Create user wen1 password '123'; create a user and set the password
# Create database data1 owner wen1; Create a database
# Alter table spatial_ref_sys owner to wen1;
# Alter table geometry_columns owner to wen1; it is very important to modify the owner of the two postgis-related tables as wen1, which is not mentioned in general articles.
4. Run the following command to exit Psql and perform data conversion on the shell interface.
First put three test. copy the SHP file to the/opt/postgis/bin directory and go to the directory. Pay attention to the path and the permissions of the Directory and file. It may be easier to log out of ipvs to the root user.
#/Opt/postgis/bin/shp2pgsql test data1> test. SQL
#/Home/shaken/PostgreSQL/bin/Psql-D data1-F test. SQL
Note that all parameters must be correct. If you do not understand them, you must use the -- help parameter to view them.
5. Open qgis on the client machine, select "layer"> "add postgis layer"> "new", and set the correct Server IP address and database name data1, user wen1 and password, and then click "Connect". The name of the test layer is displayed below, and you can select it.
The usage of udig is similar.

1. First, you must understand the use of postgresql, especially the concepts and relationships of various permissions. This is generally lacking for those transferred from windows.
2. You 'd better select a good PostgreSQL client tool to help debugging. pgadmin is recommended.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.