Some problems and workarounds encountered with PostgreSQL

Recently help QA colleagues to create a PostgreSQL test server process encountered a few problems, listed as a backup, but also for future use as a reference.

1. PostgreSQL default system Yum source only version is 8.4.18-1.el6_4, and I need to install version 9.2.

Go to the Pgsql website to view it, which provides a 9.2 version of the Yum Source address: http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-sl92-9.2-8.noarch.rpm

Install the source using the following command

Yum Install http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-sl92-9.2-8.noarch.rpm

The command to install pgsql-9.2 is

Yum Install Postgresql92-server

To install the package file postgresql92-server-9.2.9-1pgdg.rhel6.x86_64 to the system

Start Service postgresql-9.2 Start

2. Error "Error:type" Hstore "does not exist" when importing a database

Follow the online prompt to execute an SQL statement "create extension hstore;", but report another error message

"Error:could not open Extension control file"/usr/pgsql-9.2/share/extension/hstore.control ": No such file or directory"

Go to file path/usr/pgsql-9.2/share/extension/, found that the following really did not hstore.control this file, and therefore doubt whether some of the relevant package files are not fully installed, then take a violent approach, all the installation package of the POSTGRESQL92 all loaded up,

Yum Install postgresql92*

Postgresql92-libs-9.2.9-1pgdg.rhel6.x86_64postgresql92-pltcl-9.2.9-1pgdg.rhel6.x86_ 64postgresql92-debuginfo-9.2.9-1pgdg.rhel6.x86_64postgresql92-plpython-9.2.9-1pgdg.rhel6.x86_ 64postgresql92-9.2.9-1pgdg.rhel6.x86_64postgresql92-devel-9.2.9-1pgdg.rhel6.x86_ 64postgresql92-contrib-9.2.9-1pgdg.rhel6.x86_64postgresql92-odbc-09.02.0100-1pgdg.rhel6.x86_ 64postgresql92-tcl-2.0.0-1.rhel6.x86_64postgresql92-test-9.2.9-1pgdg.rhel6.x86_ 64postgresql92-odbc-debuginfo-09.02.0100-1pgdg.rhel6.x86_ 64postgresql92-jdbc-debuginfo-9.2.1002-1pgdg.rhel6.x86_64postgresql92-tcl-debuginfo-2.0.0-1.rhel6.x86_ 64postgresql92-server-9.2.9-1pgdg.rhel6.x86_64postgresql92-jdbc-9.2.1002-1pgdg.rhel6.x86_ 64postgresql92-plperl-9.2.9-1pgdg.rhel6.x86_64postgresql92-docs-9.2.9-1pgdg.rhel6.x86_64

Then go to the path/usr/pgsql-9.2/share/extension/, all the files have, and then try to import the database, did not appear just the error

3. The QA colleague cannot connect to the database server when it starts the program on the test server

View Pgsql configuration file/var/lib/pgsql/9.2/data/postgresql.conf with relevant options

-Connection settings-listen_addresses = ' localhost ' # What IP address (es) to listen on; # defaults to ' localhost '; Use ' * ' for all

Originally this way can only play a stand-alone version, the other local area network server can not connect to the database server, change to

listen_addresses = ' * '

There is also a configuration file/var/lib/pgsql/9.2/data/pg_hba.conf also need to modify, add the following parameters, allow all network segments to access the database, you can also specify some network segments such as 192.168.1.0/24 and so on

# IPV4 Local connections:host All 0.0.0.0/0 Trust

Other servers can connect to the database after restarting Pgsql

This is the current use of PostgreSQL some of the problems encountered, but also need to strengthen their knowledge of the learning to continue to improve.

This article is from the "Strive for" blog, please be sure to keep this source http://carllai.blog.51cto.com/1664997/1531284