linux装数据库手册,PostgreSQL Linux 安装及使用，简易手册

PostgreSQL Linux 安装及使用，简易手册

一，配置方法：

1 ，首先下载任意版本的PostgreSQL For Linux X86_64

2 ，文本模式安装

[root@imdba.cn ~]# ./postgresql-8.4.1-1-linux-x64.bin

–mode text

—————————————————————————-

Welcome to the PostgreSQL Setup Wizard.

—————————————————————————-

Please specify the directory where PostgreSQL will be

installed.

Installation Directory [/opt/PostgreSQL/8.4]:

/home/PostgreSQL/8.4

—————————————————————————-

Please select a directory under which to store your data.

Data Directory [/home/PostgreSQL/8.4/data]:

—————————————————————————-

Please provide a password for the database superuser (postgres). A

locked Unix user account (postgres) will be created if not

present.

Password :[ 输入密码]

Retype password :[ 重复输入哦]

—————————————————————————-

Please select the port number the server should listen on.

Port [5432]:[ 默认端口，那就回车]

[616] yi_US

[617] yi_US.utf8

[618] zh_CN

[619] zh_CN.utf8

[620] zh_HK

[621] zh_HK.utf8

[622] zh_SG

[623] zh_SG.utf8

[624] zh_TW

[625] zh_TW.euctw

[626] zh_TW.utf8

[627] zu_ZA

[628] zu_ZA.iso88591

[629] zu_ZA.utf8

Please choose an option [1] :619[ 默认字符集]

Install pl/pgsql in template1 database? [Y/n]: n[是否安装模板库]

—————————————————————————-

Setup is now ready to begin installing PostgreSQL on your

computer.

Do you want to continue? [Y/n]: y[ 是否继续安装]

—————————————————————————-

Please wait while Setup installs PostgreSQL on your computer.

Installing

0% ______________ 50% ______________ 100%

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

—————————————————————————-

Setup has finished installing PostgreSQL on your computer.

Launch Stack Builder at exit?

Stack Builder may be used to download and install additional tools,

drivers and applications to complement your PostgreSQL

installation. [Y/n]: y

3 ，以下是配置用户，环境变量，乱七八糟后就会成功

[root@imdba.cn ~]# cp .bash_profile .bashrc

/home/PostgreSQL/8.4/

cp: overwrite `/home/PostgreSQL/8.4/.bash_profile’? y

cp: overwrite `/home/PostgreSQL/8.4/.bashrc’? y

[root@imdba.cn ~]# chown -R postgres.postgres

/home/PostgreSQL/*

[root@imdba.cn ~]# ls -ltr /home/PostgreSQL/8.4/.bash*

-rw-r–r– 1 postgres postgres 174 Nov 10 19:18

/home/PostgreSQL/8.4/.bash_profile

-rw-r–r– 1 postgres postgres 176 Nov 10 19:18

/home/PostgreSQL/8.4/.bashrc

[root@imdba.cn ~]# su – postgres

4, 初始化数据库完毕，类似于MySQL的installdb

[root@imdba.cn data]$ initdb -D

/home/PostgreSQL/8.4/data/

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 en_US.

The default database encoding has accordingly been set to

LATIN1.

The default text search configuration will be set to

"english".

fixing permissions on existing directory /home/PostgreSQL/8.4/data

… ok

creating subdirectories … ok

selecting default max_connections … 100

selecting default shared_buffers … 32MB

creating configuration files … ok

creating template1 database in /home/PostgreSQL/8.4/data/base/1 …

o

initializing pg_authid … ok

initializing dependencies … ok

creating system views … ok

loading system objects’ descriptions … ok

creating conversions … ok

creating dictionaries … ok

setting privileges on built-in objects … ok

creating information schema … ok

vacuuming database template1 … ok

copying template1 to template0 … ok

copying template1 to postgres … ok

WARNING: enabling "trust" authentication for local

connections

You can change this by editing pg_hba.conf or using the -A option

the

next time you run initdb.

Success. You can now start the database server using:

postgres -D /home/PostgreSQL/8.4/data

or

pg_ctl -D /home/PostgreSQL/8.4/data -l logfile start

[root@imdba.cn data]$

5, 启动PostgreSQL Server 后台服务

[root@imdba.cn data]$ pg_ctl -D

/home/PostgreSQL/8.4/data -l logfile start

server starting

[root@imdba.cn data]$

二，使用方法

[root@imdba.cn data]$ psql

psql (8.4.1)

Type "help" for help.

postgres=#

[root@imdba.cn bin]$ ./createdb imdba

[root@imdba.cn bin]$ psql imdba

psql (8.4.1)

Type "help" for help.

imdba=# \l

List of databases

Name | Owner |

Encoding | Collation | Ctype | Access privileges

———–+———-+———-+———–+——-+———————–

imdba | postgres | LATIN1 |

en_US | en_US |

postgres | postgres |

LATIN1 |

en_US | en_US |

template0 | postgres | LATIN1 |

en_US | en_US | =c/postgres :

postgres=CTc/postgres

template1 | postgres | LATIN1 |

en_US | en_US | =c/postgres :

postgres=CTc/postgres

(4 rows)

imdba=#

imdba=# \c postgres

psql (8.4.1)

You are now connected to database "postgres".

postgres=# \c imdba

psql (8.4.1)

You are now connected to database "imdba".

imdba=# create table imdba(id int4,name char(16));

CREATE TABLE

imdba=# drop table imdba;

DROP TABLE

imdba=# create table t_imdba(id int4,name char(16));

CREATE TABLE

imdba=# \d t_imdba

Table "public.t_imdba"

Column

| Type | Modifiers

——–+—————+———–

id |

integer |

name | character(16)

|

imdba=# select * from t_imdba;

id

| name

—-+——————

1 | imdba

2 | imdba

3 | imdba

(3 rows)

imdba=# create index idx_t_imdba on t_imdba(id);

CREATE INDEX

imdba=# \di

List of relations

Schema | Name | Type | Owner | Table

——–+————-+——-+———-+———

public | idx_t_imdba | index | postgres | t_imdba

(1 row)

imdba=# insert into t_imdba values(2,’imdba’);

INSERT 0 1

imdba=# insert into t_imdba values(2,’imdba’);

INSERT 0 1

imdba=# insert into t_imdba values(2,’imdba’);

INSERT 0 1

imdba=# insert into t_imdba values(2,’imdba’);

INSERT 0 1

imdba=# insert into t_imdba values(2,’imdba’);

INSERT 0 1

imdba=# insert into t_imdba values(2,’imdba’);

INSERT 0 1

imdba=# select * from t_imdba;

id

| name

—-+——————

1 | imdba

2 | imdba

3 | imdba

2 | imdba

2 | imdba

2 | imdba

2 | imdba

2 | imdba

2 | imdba

(9 rows)

imdba=# \q

1 ，备份数据库

[@tc_10.11.54.21_cnc bin]$ pg_dump

pg_dump pg_dumpall

[root@imdba.cn bin]$ pg_dump imdba

>imdba.pgsql

[root@imdba.cn bin]$ more imdba.pgsql

–

– PostgreSQL database dump

–

SET statement_timeout = 0;

SET client_encoding = ‘LATIN1′;

SET standard_conforming_strings = off;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = ”;

SET default_with_oids = false;

–

– Name: t_imdba; Type: TABLE; Schema: public; Owner: postgres;

Tablespace:

–

CREATE TABLE t_imdba (

id

integer,

name

character(16)

);

ALTER TABLE public.t_imdba OWNER TO postgres;

–

– Data for Name: t_imdba; Type: TABLE DATA; Schema: public; Owner:

postgres

–

COPY t_imdba (id, name) FROM stdin;

1 imdba

2 imdba

3 imdba

2 imdba

2 imdba

2 imdba

2 imdba

2 imdba

2 imdba

\.

–

– Name: idx_t_imdba; Type: INDEX; Schema: public; Owner: postgres;

Tablespace:

–

CREATE INDEX idx_t_imdba ON t_imdba USING btree (id);

–

– Name: public; Type: ACL; Schema: -; Owner: postgres

–

REVOKE ALL ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON SCHEMA public FROM postgres;

GRANT ALL ON SCHEMA public TO postgres;

GRANT ALL ON SCHEMA public TO PUBLIC;

–

– PostgreSQL database dump complete

–

2 ，恢复数据库

[root@imdba.cn bin]$ dropdb imdba

[root@imdba.cn bin]$ psql imdba < imdba.pgsql

[root@imdba.cn bin]$ ./createdb imdba

[root@imdba.cn bin]$ psql imdba < imdba.pgsql

SET

SET

SET

SET

SET

SET

SET

SET

SET

CREATE TABLE

ALTER TABLE

CREATE INDEX

REVOKE

REVOKE

GRANT

GRANT