PostgreSQL Linux安装及使用，简易手册
一，配置方法：
1，首先下载任意版本的PostgreSQL For Linux X86_64
wget http://downloads.enterprisedb.com/postgresql/postgresql-8.4.1-1-linux-x64.bin
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]: —————————————————————————- Pleaseprovide a password for the database superuser (postgres). A locked Unixuser 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? StackBuilder may be used to download and install additional tools, driversand 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
【以上文档都是字节，字符少，也可能会有错别字哦！嘎嘎！写于2009-11-10，$time
做事的能力往往只能给你一种机会，
