Postgres database installation and basic operation (reproduced)

7 database

Version: 2004-10-28-01

7.1 Preparing

If you don't need a function like a view, you can choose MySQL, which is quite fast. You can also choose Oracle, this kind of fate-level Dongdong installation troubles and hardware requirements, and its powerful features are also surprised.

Of course, if you want to find a fold, or you are a fanatic free software supporter, PostgreSQL should be the choice of not bilateral, it is the only database management system that is comparable to commercial software. You can go to http://www.pgsqldb.org to see the characteristics and development history of this database management system, there are many information.

7.2 installation

To http://www.pgsql.org Download the source code. The version I downloaded was posgreSQL7.3.4, saved as "/opt/prog/tmp/postgreSQL-7.3.4.gz". After download, open the terminal (RXVT), enter this directory:

[root @ TFW-RFL40F root] # CD / OPT / PROG / TMP

Unzip the installation file:

[root @ TFW-RFL40F TMP] # tar zxvfp ./postgreSQL-7.3.4.TAR.GZ

......

......

......

[root @ TFW-RFL40F TMP] #

Get a directory "PostgreSQL-7.3.4".

Enter this directory:

[root @ TFW-RFL40F TMP] # CD PostgreSQL-7.3.4

[root @ TFW-RFL40F PostgreSQL-7.3.4] #

Precompiled:

[root @ TFW-RFL40F PostgreSQL-7.3.4] # ./configure --prefix = / opt / prog / c / postgreSQL --Nable-locale --Nable-multibyte --with-perl --with-odbc - WITH-TCL

......

......

......

[root @ TFW-RFL40F PostgreSQL-7.3.4] #

For this version of PostgreSQL, "./ Configure" does not add any options, the system will be installed by default settings, see the configre-related options You can use the command "./configure -help".

Some of the most common options are as follows:

--Prefix = Basedir

The reference document is "selecting a different base path to install PostgreSQL", in fact, it is to choose to install. Install by default to "/ usr / local / pgsql". I don't know if this software will move everywhere, and the installation path is selected as "/ opt / prog / c / postgreSQL".

--enable-local

If you want to use localization support.

--enable-multibyte

Allow multi-byte character encoding. This option is primarily used in Japanese, Korean or Chinese.

--with-perl

Add a Perl module interface. Note that the Perl interface will be installed into the common position of the Perl module (typically in / usr / lib / perl), so you must have ROOT permissions.

--with-odbc

Make an ODBC driver package.

--with-TCL

The interface libraries and procedures required for TCL / TK include LibPGTCL, PGTCLSH, and PGTKSH.

These parameters entered during precompilation will tell the compiler how to compile PostgreSQL. Start compilation:

[root @ TFW-RFL40F PostgreSQL-7.3.4] # Make

......

......

......

[root @ TFW-RFL40F PostgreSQL-7.3.4] # This is a relatively long process.

Install compilation Dongdong to the precompiled location:

[root @ TFW-RFL40F PostgreSQL-7.3.4] # Make Install

......

......

......

[root @ TFW-RFL40F PostgreSQL-7.3.4] #

If you don't report an error in the middle, the installation is complete. If there is an error in the middle, the solution can be referred to the software to install that chapter, and details will not be described here.

7.2 Initialization

Create a directory where the database file is stored:

[root @ TFW-RFL40F PostgreSQL-7.3.4] # MKDIR / OPT / PROG / C / PostgreSQL-Database

Set the environment variable to modify the appropriate environment variable file. For security reasons, PostgreSQL is a privilegeous user such as "root". For convenience, I will manage PostgreSQL as the "Typhoon" of their daily work, but it is recommended that everyone creates a user for managing PostgreSQL. "Bash" user wants to modify the ".bash_profile" in the personal primary directory.

The relevant part of the relevant part of my environment variable file is as follows:

############################################################################

#V PostgreSQL 7.3.4

# Compiled

# Moveable

# PosgreSQL

LD_Library_Path = "/ OPT / PROG / M / PostgreSQL / LIB"

Export ld_library_path

PGLIB = "/ opt / prog / m / postgreSQL / lib"

PATH = $ PATH: "/ OPT / PROG / M / PostgreSQL-7.3.4 / BIN"

MANPATH = $ MANPATH: "/ opt / prog / m / postgreSQL / MAN"

Export Pglib Path ManPath

############################################################################

#v PostgreSQL-Database 7.3.4

# Moveable

PgData = "/ opt / prog / m / postgreSQL-database"

Export PGData

############################################################################

The "#" "#" is just a comment symbol for "Bash", followed by it behind it, which only plays a visual segmentation. I found that "postgreSQL" is the software that said to Mon, and remove it, "/ opt / prog / m" is its current location.

"Ld_library_path", POSTGRESQL server library file;

"Pglib", the library file of the Linux client;

"Path", no longer explained;

"MANPATH", Linux / UNIX software manual, help documentation;

"Pgdata", postgreSQL generally finds its database;

"Export", force it to take effect behind it, not all software needs, but it is best to add, to prevent in case.

The directory mentioned just now is created under "root", let them use "Typhoon", but also to change rights:

[root @ TFW-RFL40F PostgreSQL-7.3.4] # chown -r typhoon.typhoon / opt / prog / c / postgreSQL [root @ TFW-RFL40F PostgreSQL-7.3.4] # chown -r typhoon.typhoon / opt / prog / c / postgreSQL-DATABASE

[root @ TFW-RFL40F PostgreSQL-7.3.4] #

which is:

Chown -r .

Chown -r .

Then I closed X and log out, log in again to enable environment variables to take effect.

This command is used to initialize the PostgreSQL database, make sure that you put the database file before you perform it.

[Typhoon @ TFW-RFL40F Typhoon] $ INITDB

......

......

......

[Typhoon @ TFW-RFL40F Typhoon] $

If you don't want to use "pgdata" or not specify "PGDATA", you can initialize it with "INitdb you want to store the database file". PostgreSQL allows multiple directories that store database files, but I have not tried to use these directories simultaneously.

Initialization After the super user of the database, the user name is automatically created, the username is installed, and the initialized operating system user is the same name. You may also automatically create a database that is the same as the username. I am here is the user "typhoon" and database "typhoon". Execute "<- Prefix" to the place> / bin / psql "can log in. "/ Q" can be returned to the shell of the operating system. If this database does not automatically generate, execute "<- PREFIX"> / BIN / CREATEDB [User Name ", you create a database with your username. This database is equivalent to your "home", no database, it is very inconvenient to do something. However, you must start the PostgreSQL database service before creating a database.

7.3 Start and stop method

[Typhoon @ TFW-RFL40F Typhoon] $ POSTMASTER -I -D $ PGDATA &

......

......

......

[Typhoon @ TFW-RFL40F Typhoon] $

This is the service that starts the database system. The next "&" is best to bring, otherwise, ...: -d

The startup method said above is a formal method, and PostgreSQL also provides a package control script "<- PREFIX refers to the place> / bin / pg_ctl":

[Typhoon @ TFW-RFL40 Typhoon] $ PG_CTL -L 123 / Pg.log Start

Postmaster SuccessFully Started

[Typhoon @ TFW-RFL40 TYPHOON] $

If there is a "-l file name" parameter, all errors will be recorded in this file.

Out of service:

[Typhoon @ TFW-RFL40 Typhoon] $ PG_CTL STOP

Waiting for postmaster to shut down ... DONE

Postmaster SuccessFully Shut Down

[Typhoon @ TFW-RFL40 TYPHOON] $

Since it is stopped, don't take any record files.

Restart:

[Typhoon @ TFW-RFL40 TYPHOON] $ PG_CTL -L 123 / PG.log RestartWaiting for Postmaster to Shut Down ... DONE

Postmaster SuccessFully Shut Down

Postmaster SuccessFully Started

[Typhoon @ TFW-RFL40 TYPHOON] $

It is equivalent to "STOP" and "start".

If you make a modification for the configuration file, you will take effect immediately, just like this:

[Typhoon @ TFW-RFL40 Typhoon] $ PG_CTL RELOAD

Postmaster SuccessFully Signaled

[Typhoon @ TFW-RFL40 TYPHOON] $

But this is not valid for all changes.

Note: PostgreSQL administrators are best logged in from "Login:", XDM or KDM because if "su" past operations will not be performed normally.

7.4 Safety Management

After initialization, there will be two files in the database directory: "postgreSQL.conf" and "pg_hba.conf".

Modify "PostgreSQL.CONF" on the 30th line of the top "TCPIP_SOCKET = TRUE" to allow network access.

"Pg_hba.conf", based on host access control (Host Based Access). The contents of "#" on the left are all commented, and the instructions or references can be used.

I told my current document as an example (inside Chinese for me):

# PostgreSQL Client Authentication Configuration File

# ===================================================== ==

#

# Refer to the postgreSQL Administrator's Guide, Chapter "Client

# Authentication "for a completion. A Short Synopsis

# Follows.

#

# This file controls: Which Hosts Are ALOWED to Connect, How Clients

# are automated, Which PostgreSQL User Names They Can Use, Which

# Databases They can access. Records Take ONE OF Three Forms:

#

# Local Database User Method [Option]

# Host Database User IP-Address IP-Mask Method [Option]

# Hostssl Database User IP-Address IP-Mask Method [Option]

#

# (The Uppercase Quantities SHOULD BE Replaced by Actual Values.)

# Database CAN be "All", "Sameuser", "SameGroup", A Database Name (or # a comma-separated thereof), or a file name prefixed with "@".

# User can be "all", an actual user name or a group name prefixed with

# " " OR a List Containing Either. ip-address and ip-mask specify the

# set of hosts the record matches. Method Can Be "trust", "reject",

# "MD5", "Crypt", "Password", "KRB4", "KRB5", "Ident", or "PAM". Note

# That "Password" Uses Clear-Text Passwords; "MD5" is preferred for

# Encrypted passwords. Option is the Ident map or the name of the pam

# service.

#

# This file is read on Server Startup and when The Postmaster Receives

# a sighup signal. if Edit The File on a Running System, You Have

# t SIGHUP The Postmaster for the change to take Effect, or Use

# "pg_ctl reload".

# Pt c t t f c

# ----------------------------------

#

# Caution: The Default Configuration Allows Any Local User To Connect

# Using any postgresql user name, incruding the superuser, over either

# UNIX-DOMAIN Sockets or TCP / IP. If you are all a multiple-user

# Machine, The Default Configuration IS Probably Too LibERAL for you.

# Change it to use something Other Than "Trust" Authentication.

#

# If you want to allow non-local connects, you need to add more

# "Host" Records. Also, Remember TCP / IP Connections Are ONLY ENABED

# if you enable "TCPIP_SOCKET" in PostgreSql.conf.

# Type Database User ip-address ip-mask method

# 访 来 数据 数据 用户 i i--方式 方式 方式 方式 方式 方式 认

LOCAL TYPHOON TYPHOON MD5 # For access from local, the database "Typhoon" is "MD5" encryption password authentication for users "Typhoon"

LOCAL TYPHOON ALL REJECT

# For access from the local, database "Typhoon" is unconditional to all users not mentioned above

Host Typhoon Typhoon 255.255.255.255 127.0.0.1 md5

# For access to the network, the database "Typhoon" is "typhoon" for the user, if the access is the machine, use "MD5" encryption password certification

Host Typhoon All 0.0.0.0 0.0.0.0 Reject

# For access to the network, the database "Typhoon" is all users mentioned above, regardless of which machine proposed, no matter which subnet it comes from, unconditional refusal certification

Local All All MD5

# For all the databases from the local access, all databases not mentioned above adopt "MD5" encryption password authentication

Host All ALL 0.0.0.0 0.0.0.0 MD5

# For all users who come to the network, all the databases not mentioned above have all users mentioned above, no matter which machine proposed, no matter which subnet it comes from, "MD5" encryption password certification

############################################################################

# Tahers area all Denied Other Access One No conditional Reject

Local all all reject

Host all all 0.0.0.0 0.0.0.0 TEJECT

############################################################################

############################################################################

# All Denied Permissed, Not Safe

# Trust all access, too unsafe, was banned by me, only as a reference

#local all all trust

#host all all 0.0.0.0 0.0.0.0 Trust

############################################################################

These authentication rules are loaded from top to the next one. It should be noted that "all" inside is not true "all", but the part not mentioned in the previous provision. Personally feel more appropriate to use "other".

In this way, the final result is:

All users can access databases other than "Typhoon" through encryption passwords, but only users "Typhoon" can access the database "Typhoon" with an encrypted command.

7.5 Basic operation

[Client Basic Connection Operation] ===================== * View PostgreSQL System Table:

(Explore / look up)

* Terminal login:

PSQL -L [hostname / ip] -d [database name] -u [username]

* Connect to another database:

/ C [Database Name]

* Disconnect the terminal:

/ Q

=========================================

[Basic User Management] ===========================

*Add user:

Create User [User Name];

* Modify the user password:

ALTER USER [User Name] with password [password];

*delete users:

DROP User [User Name];

* View User Information:

(Explore / look up)

=========================================

[Basic Database Operation] =========================

* Create a database:

CREATE DATABASE [Database Name];

* View Database list:

/ L

* Delete the database:

DROP DATABASE [Database Name];

* Modify the owner of the database:

(Explore / look up)

=========================================

[Basic operation within the database] =======================

* Give an authorization to create a table in a database:

(Explore / look up)

* Remove the authorization of a user creation table in a database:

(Explore / look up)

Create a table:

Create Table ([Field Name 1] [Type 1] , [Field Name 2] [Type 2], ...,

* View the table name list:

/ d

* View a status of a table:

/ d [Name]

* Rename a table:

ALTER TABLE [Name A] Rename To [Name B];

* Modify the owner of the table:

(Explore / look up)

* Delete a table:

DROP TABLE [Name];

=========================================

[Basic operation in the table] =================================================================================================================================

* Add fields in existing tables:

ALTER TABLE [Table] Add Column [Field Name] [Type];

* Delete the fields in the table:

ALTER TABLE [Name] DROP Column [Field Name];

* Rename a field:

Alter Table [Table] Rename Column [Field Name A] To [Field Name B];

* Set the default value to a field:

ALTER TABLE [Name] ALTER Column [Field Name] set default [new default];

* Remove the default value:

ALTER TABLE [Table] ALTER Column [Field Name] Drop Default;

* Set a column in the existing table to the master code:

(Explore / look up)

* Set a few columns in the existing table to the master code:

(Explore / look up)

* Cancel the qualification qualification of a column:

(Explore / look up)

Insert data in the table:

INSERT INTO Name ([Field Name M], [Field Name N], ...) VALUES (value of [column M], [column N value], ...);

Modify the data of a column in a list in the table:

Update [Table Name] set [Target Field Name] = [Target Value] Where [Line Features];

Delete a line of data in the table:

Delete from [Table] WHERE [Line Characteristics];

Delete from [Table]; - Delete the entire table

=========================================

[Backup] ==================================

Back up a database:

pg_dump Database Name> Backup File

Guide the backup file to the original database or import the new database:

CAT backup file | PSQL database name

It is best to change the authentication of this database to "trust" first.

Back up a table:

PG_Dump -t table1 dbname> bk1.sql

==============================================3

As mentioned earlier, this version of PostgreSQL can be placed anywhere in the system, as long as the database manager has sufficient permissions to the installation directory and database file directory, and what it makes it works just to change the environment variable file. The line in the line.

After moving the database file directory, if the database is not found, "initdb" and "pg_ctl" can use the specified database directory file with the parameter "-d directory name".

7.7 Remarks

Come together with you. Here is the post I have been in installation:

http://www.freeeelamp.com/new/publish/1022414614/index_html

Http://chinaunix.net/jh/18/546.html

http://chinaunix.net/jh/18/548.html

I used to find the "EGCS-C " mentioned in the link, and I didn't download it. Later I learned that "EGCS-C " has been integrated into the "GCC" in the high version. My "GCC" is 3.2. If you have a "GCC", if you are more than 2.8, you don't want to find "EGCS-C " everywhere.