PostgreSQL Linux 安装及使用,简易手册.doc

PostgreSQL Linux 安装及使用，简易手册 一，配置方法： 1 ，首先下载任意版本的PostgreSQL For Linux X86_64 wget /postgresql/postgresql-8.4.1-1-linux-x64.bin2 ，文本模式安装 # ./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: y3 ，以下是配置用户，环境变量，乱七八糟后就会成功 # 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 # chown -R postgres.postgres /home/PostgreSQL/* # ls -ltr /home/PostgreSQL/8.4/.bash* -rw-rr 1 postgres postgres 174 Nov 10 19:18 /home/PostgreSQL/8.4/.bash_profile -rw-rr 1 postgres postgres 176 Nov 10 19:18 /home/PostgreSQL/8.4/.bashrc # su postgres4, 初始化数据库完毕，类似于MySQL的installdb 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 data$5, 启动PostgreSQL Server 后台服务 data$ pg_ctl -D /home/PostgreSQL/8.4/data -l logfile start server starting data$二，使用方法 data$ psql psql (8.4.1) Type help for help. postgres=# bin$ ./createdb imdba 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_1_cnc bin$ pg_dump pg_dump pg_dumpall bin$ pg_dump imdba imdba.pgsql 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; Own