PostgreSQL Little Play

Because CDH need it to build hive Genku ...

Reference:

Http://www.cnblogs.com/mchina/archive/2012/06/06/2539003.html

First, Introduction

PostgreSQL is a very complex object-relational database management system (ORDBMS), and is currently the most powerful, feature richest and most complex free software database system. Some features don't even have a commercial database. The Berkeley-based database research program has now become an international development project and has a very wide range of users.

Second, the system environment

System Platform: CentOS Release 6.3 (Final)

PostgreSQL Version: PostgreSQL 9.2.4

The firewall is turned off/iptables:firewall is not running.

Selinux=disabled

Three, installation method

A. RPM Package Installation

B. Yum Installation

C. Source Package Installation

Iv. installation Process

A. RPM Package Installation

1. Check if PostgreSQL is installed

# Rpm-qa|grep Postgres

If it is already installed, uninstall it using the RPM-E command.

2. Download RPM Package

#wget http://yum.postgresql.org/9.2/redhat/rhel-6-i386/postgresql92-server-9.2.4-1PGDG.rhel6.i686.rpm

#wget http://yum.postgresql.org/9.2/redhat/rhel-6-i386/postgresql92-contrib-9.2.4-1PGDG.rhel6.i686.rpm

#wget http://yum.postgresql.org/9.2/redhat/rhel-6-i386/postgresql92-libs-9.2.4-1PGDG.rhel6.i686.rpm

#wget http://yum.postgresql.org/9.2/redhat/rhel-6-i386/postgresql92-9.2.4-1PGDG.rhel6.i686.rpm

3. Install PostgreSQL, pay attention to the installation sequence

# RPM-IVH postgresql92-libs-9.2.4-1pgdg.rhel6.i686.rpm
# RPM-IVH postgresql92-9.2.4-1pgdg.rhel6.i686.rpm
# RPM-IVH postgresql92-server-9.2.4-1pgdg.rhel6.i686.rpm
# RPM-IVH postgresql92-contrib-9.2.4-1pgdg.rhel6.i686.rpm

4. Initializing the PostgreSQL database

The PostgreSQL service will prompt for initialization when it first starts.

Initializing the database

# service postgresql-9.2 Initdb

5. Start the service

# service postgresql-9.2 Start

6. Add the PostgreSQL service to the startup list

# Chkconfig postgresql-9.2 on

# chkconfig--list|grep Postgres

7. Modify the password of the PostgreSQL database user Postgres (note that it is not a Linux system account)

The PostgreSQL database is created by default to create a Postgres database user as the administrator of the database, the default password is empty, we need to change to the specified password, which is set to ' Postgres '.

# Su-postgres

$ psql

# ALTER USER postgres with PASSWORD ' Postgres '; # select * from Pg_shadow;

8. Testing the Database

8.1 Creating a test database

# CREATE DATABASE David;

8.2 Switch to the David database

# \c David

8.3 Creating a Test table

david=# CREATE TABLE Test (ID integer, name text);

8.4 Inserting test data

8.5 Select data

david=# select * from test; ID | Name  ----+-------  

Test completed, RPM package installed successfully.

9. Modify the password of the Linux system user Postgres

The PostgreSQL database creates a Linux system user Postgres by default and sets the system user's password to post123 with the passwd command.

# passwd Postgres

10. Modify the Postgressql database configuration for remote access

10.1 Modify postgresql.conf File

# vi/var/lib/pgsql/9.2/data/postgresql.conf

If you want PostgreSQL to listen to the entire network, remove the # before listen_addresses and change listen_addresses = ' localhost ' to listen_addresses = ' * '

10.2 Modifying the Client Authentication profile pg_hba.conf

Add the IP address or address segment of the remote Access database to the file.

# vi/var/lib/pgsql/9.2/data/pg_hba.conf

11. Restart the service for the settings to take effect

# Service postgresql-9.2 Restart

12. Remote Test Connection

The connection was successful.

B. Yum Installation

1. Uninstall the PostgreSQL that you just installed

Stop the PostgreSQL service

#/etc/init.d/postgresql-9.2 Stop

To view installed packages

# Rpm-qa|grep Postgres

Unloading

# RPM-E postgresql92-server-9.2.4-1pgdg.rhel6.i686

# RPM-E postgresql92-contrib-9.2.4-1pgdg.rhel6.i686

# RPM-E postgresql92-9.2.4-1pgdg.rhel6.i686

# RPM-E postgresql92-libs-9.2.4-1pgdg.rhel6.i686

2. Yum Installation

If the default Yum installation is installed, the lower version of PostgreSQL 8.4 will be installed, which does not meet our requirements.

We use the PostgreSQL Yum Repository to install the latest version of PostgreSQL.

2.1 Installing PostgreSQL Yum Repository

# rpm-i http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm

2.2 Installing a new version of PostgreSQL

# yum Install Postgresql92-server Postgresql92-contrib

2.3 Viewing the installation

3. Initialize and start the database

4. Testing

Other steps such as a way.

C. Source Package Installation

1. Download the PostgreSQL source package

# wget HTTP://FTP.POSTGRESQL.ORG/PUB/SOURCE/V9.2.4/POSTGRESQL-9.2.4.TAR.BZ2

2. Unzip the source package

# tar XJF postgresql-9.2.4.tar.bz2

3. Enter the extracted directory

# CD postgresql-9.2.4

4. View the Install File

The short Version section of the install file explains how to install PostgreSQL commands, Requirements section describes the lib that is dependent on the installation of PostgreSQL, long, first configure try, if error occurs, Then you need to check whether the requirements requirements are met.

5. Start compiling and installing the PostgreSQL database.

[Email protected] postgresql-9.2.4]#./configure

Configure success, no errors.

6. Executive Gmake

[Email protected] postgresql-9.2.4]# Gmake

Gmake successful, ready to install.

7. Execute Gmake Install

[[email protected] postgresql-9.2.4]# gmake Install

Gmake install successful, to this step, PostgreSQL source code compiled installation completed, the following start to configure PostgreSQL.

8. Setting Environment variables

# VI. bash_profile

Put path= $PATH: $HOME/bin

Change to path= $PATH: $HOME/bin:/usr/local/pgsql/bin

Save exit.

Let the environment variable take effect:

# source. bash_profile

9. Add User Postgres

# AddUser Postgres

* Change user directory (optional action)

# VI/ETC/PASSWD

Put Postgres:x:528:528::/home/postgres:/bin/bash

Change into Postgres:x:528:528::/usr/local/pgsql:/bin/bash

Move the. Bash_profile to the new user directory and modify the permissions

# cp/home/postgres/.bash_profile/usr/local/pgsql/

# chown Postgres.postgres. Bash_profile

To delete a user directory:

[Email protected] home]# RM-RF postgres/

10. Initializing the database

10.1 New Data Catalog

# Mkdir/usr/local/pgsql/data

10.2 Changing permissions

# chown Postgres/usr/local/pgsql/data

10.3 Switch to Postgres user

# Su-postgres

10.4 Init db

$/usr/local/pgsql/bin/initdb-d/usr/local/pgsql/data/

The initialization of the data here is complete.

11. System Services

11.1 Back to root user

$ exit

11.2 Copy the Linux files from the installation directory to/etc/init.d/

Enter the installation directory for PostgreSQL (that is, the directory you just unzipped with the tar command)

# CD postgresql-9.2.4

# CP Contrib/start-scripts/linux/etc/init.d/postgresql

11.3 Adding Execute Permissions

# chmod +x/etc/init.d/postgresql

11.4 Starting the database

11.5 Getting the database up and starting

# chkconfig--add PostgreSQL

# chkconfig PostgreSQL on

11.6 Create a history file for database operations

12. Test use

# Su-postgres

$ createdb Test

$ psql Test

test=# CREATE TABLE test (id int);

Source code compiled and installed successfully.

PostgreSQL Little Play