postgresql（V-9.1） debian7.x（wheezy）安装过程+配置详解+数据库的基本使用方法

本文链接：https://blog.csdn.net/wangyezi19930928/article/details/18314075

安装：

http://blog.csdn.net/beiigang/article/details/8314216

安装PostgreSQL数据库不一定需要root用户权限，普通用户也可以。安装方式有多种，一种是编译安装，一种是二进制安装包安装，还有各种linux系统的包安装。

一. 源码编译安装

总体流程：

./configure
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

2.下载PostgreSQL源码包

到www.postgresql.org网站上下载源码包，然后解压到一个目录下： http://www.postgresql.org/ftp/source/v9.3.0/

gunzip postgresql-8.4.3.tar.gz tar xvf postgresql-8.4.3.tar

如果在Linux下，可以一步解压：

tar zxvf postgresql-8.4.3.tar.gz

如果是.tar.bz2包：

tar jxvf postgresql-8.4.3.tar.bz2

3.安装过程

⑴ ./configure

后面可以跟我们前面讲过的一些选项，如--with-libedit-preferred ：

./configure --with-libedit-preferred

后面可以跟很多的选项，具体可以参见PostgreSQL提供的官方手册，这里介绍几个常用的选项：

--prefix=PREFIX：指定安装PostgreSQL的安装目前，如果没有指定，则安装到/usr/local/pgsql目录下。

--with-pgport=NUMBER:指定PostgreSQL的监听端口，默认为5432

--with-wal-segsize=SEGSIZE：指定WAL日志文件的大小。

--with-wal-blocksize=BLOCKSIZE:指定WAL日志的块大小。

--enable-thread-safety：允许客户端的库函数是线程安全的。

执行的时候可能报：

1. configure: error: readline library not found

这是因为readline没有安装，只要安装这个库就可以了，:

aptitude install libreadline6-dev 2. configure: error: zlib library not found 解决办法： sudo apt-get install libghc-zlib-dev

⑵ 编译源代码

gmake 有可能你的机器的没有gmake命令，而make就是gnu make，这时运行make编译：

make

⑶ 安装编译生成的文件

gmake install 机器没有gmake命令时使用： sudo make install

⑷ 卸载

gmake uninstall 机器没有gmake命令时使用： make uninstall

二：命令安装：
sudo apt-get install postgresql-client-9.1

配置：

第一步：修改PostgreSQL数据库的默认用户postgres的密码(注意不是linux系统帐号)

1.PostgreSQL登录(使用psql客户端登录)

# sudo -u postgres psql ###debian下 su postgres -c psql

//其中，sudo -u postgres 是使用postgres 用户登录的意思

         //PostgreSQL数据默认会创建一个postgres的数据库用户作为数据库的管理员，密码是随机的，所以这里设定为 'postgres'
          2.修改PostgreSQL登录密码：
          postgres=# ALTER USER postgres WITH PASSWORD 'postgres';          //postgres=#为PostgreSQL下的命令提示符
          3.退出PostgreSQL psql客户端
         postgres=# \q
        [代码说明]

‘#’和’#'之前的字符是系统提示符，’postgres=#’是psql客户端的提示符，红色字符为输入命令（本文其它部分亦如此）；
[功能说明]

PostgreSQL数据默认会创建一个postgres的数据库用户作为数据库的管理员，密码是随机的，需要修改为指定的密码，这里设定为’postgres’

第二步：修改linux系统的postgres用户的密码（密码与数据库用户postgres的密码相同）
        1.删除PostgreSQL用户密码
         # sudo passwd -d postgres
         passwd: password expiry information changed.
          //passwd -d 是清空指定用户密码的意思
         2.设置PostgreSQL用户密码
         PostgreSQL数据默认会创建一个linux用户postgres，通过上面的代码修改密码为'postgres’（这取决于第一步中的密码，只要与其相同即可）。现在，我们就可以在数据库服务器上用 postgres帐号通过psql或者pgAdmin等等客户端操作数据库了。
        # sudo -u postgres passwd
        输入新的 UNIX 密码：
        重新输入新的 UNIX 密码：
        passwd：已成功更新密码

第三步：修改PostgresSQL数据库配置实现远程访问
        # vi /etc/postgresql/8.4/main/postgresql.conf

1.监听任何地址访问，修改连接权限

#listen_addresses = ‘localhost’改为 listen_addresses = ‘*’ //如果想让PostgreSQL 监听整个网络的话，将 listen_addresses 前的#去掉，并将 listen_addresses = 'localhost' 改成 listen_addresses = '*'

          2.启用密码验证
           #password_encryption = on 改为 password_encryption = on//这里记得要把#去掉
           3.可访问的用户ip段
           # vi /etc/postgresql/8.4/main/pg_hba.conf
并在文档末尾加上以下内容
         # to allow your client visiting postgresql server
           host all all 0.0.0.0 0.0.0.0 md5
          4.重启 PostgreSQL数据库
          # /etc/init.d/postgresql restart


第四步：管理PostgreSQL用户和数据库
             1.登录postgre SQL数据库
           # psql -U postgres -h 127.0.0.1
             2.创建新用户wye，但不给建数据库的权限
            postgres=# create user “wangye” with password ‘123456’ nocreatedb;
          / / 注意用户名要用双引号，以区分大小写，密码不用
            3.建立数据库，并指定所有者
            postgres=# create database “testdb” with owner=”wangye”;
            4.在外部命令行的管理命令
            # -u postgres createuser -D -P test1
            // -D该用户没有创建数据库的权利，-P提示输入密码,选择管理类型y/n
            # -u postgres createdb -O test1 db1

//-O设定所有者为test1

第五步：PostgreSQL的交互终端基本使用

psql 是 PostgreSQL的交互终端。

psql 是一个以终端为基础的 PostgreSQL 前端。它允许你交互地键入查询，把它们发出给 PostgreSQL，然后看看查询的结果。另外，输入可以来自一个文件。还有，它提供了一些元命令和多种类 shell 地特性来实现书写脚本以及对大量任务的自动化。

wangye@debian:~$ su - postgres 
Password: 
postgres@debian:~$ /etc/init.d/postgresql start  //<span style="font-size:14px;"><span style="font-size:18px;"><strong><span style="font-family:verdana,geneva;font-size:14px;color:#000000;"><span style="font-size:12px;">初始化PostgreSQL 数据库</span>，</span></strong></span></span><span style="font-size:14px;"><strong><span style="font-family:verdana,geneva;color:#000000;">PostgreSQL 服务初次启动的时候会提示初始化</span></strong></span>
Starting PostgreSQL 8.4 database server: main.
postgres@debian:~$ createdb testdb1
postgres@debian:~$ psql testdb1
psql (8.4.17)
Type "help" for help.

testdb1=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
testdb1=# create table test(id varchar(5),name varchar(10));
CREATE TABLE
testdb1=# insert into test values('id_1','name_1');
INSERT 0 1
testdb1=# select * from test;                                                                                                              id  |  name  
------+--------
 id_1 | name_1
(1 row)

testdb1=# \q

第六步：对于PostgreSQL的创建用户、创建表、增删改查等操作示例

PostgreSQL 前端psql在debian下的启动方式有两种：

1、

<strong>wangye@debian:~$ su - postgres   //切换到postgresql的默认用户postgres下
Password: 
postgres@debian:~$ psql      //进入postgresql的交互终端psql下
psql (8.4.17)
Type "help" for help.

postgres=# 
</strong>

2、‘

<strong>wangye@debian:~$ su postgres -c psql 
Password: 
psql (8.4.17)
Type "help" for help.
</strong>

创建用户：

为数据库创建一个名为wangye的用户：

<strong>wangye@debian:~$ su - postgres
Password: 
postgres@debian:~$ createuser wangye  //创建用户的工作要在postgres下完成</strong>

查看用户是否存在：

<strong>postgres=# select rolname from pg_roles;                                      
rolname  
----------
 postgres
 wangye
(2 rows)

postgres=# 
</strong>

另一种查看用户信息的方法：

<strong>postgres=# \du     //查看工作要在postgres的交互终端下完成
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 postgres  | Superuser   | {}
           : Create role   
           : Create DB     
 wangye    |             | {}

postgres=# 
</strong>

删除创建的用户：

<strong>postgres@debian:~$ dropuser wangye;  //删除工作要在postgresql下完成
postgres=# \du    //查看工作要在psql的交互终端下完成
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 postgres  | Superuser   | {}
           : Create role   
           : Create DB    

</strong>

创建用户和删除用户的工作要在postgresql下完成，查看工作要在psql交互终端下完成！！！！！

创建测试数据库：

<strong>postgres=# create database david;
CREATE DATABASE
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 david     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 testdb    | wangye   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 testdb1   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

(6 rows)
</strong>

删除已创建的数据库：

postgres=# drop database testdb1;   <span style="color:#000066;">//现在的ower为postgres故不能对ower为wangye的数据库进行操作；</span>
DROP DATABASE
postgres=#

切换到david 数据库：

<strong><strong><strong>
postgres=# \c david
psql (8.4.17)
You are now connected to database "david".
david=# create table test(david)
david-# \c david
psql (8.4.17)
You are now connected to database "david".
</strong></strong></strong>

创建测试表：

<strong><strong><strong><strong>david=# create table test (
david(# id integer ,
david(#  name text );
CREATE TABLE
</strong></strong></strong></strong>

插入测试数据：

<strong><strong><strong><strong><strong>david=# insert into test values (1,'davis');   
INSERT 0 1
</strong></strong></strong></strong></strong>

选择数据：

<strong><strong><strong><strong><strong><strong>david=# select * from test ;
 id | name  
----+-------
  1 | davis
(1 row)

david=#</strong></strong></strong></strong></strong></strong>

删除表操作：

<strong>david=# drop table test ;
DROP TABLE
david=# select * from test ;   //用来检验test表是否被删除
ERROR:  relation "test" does not exist
LINE 1: select * from test ;
                      ^
david=# 
</strong>

第七步：postgresql修改表（主要是指修改表的定义、结构，而非修改表中数据）

如果你创建了一个表后发现自己犯了一个错误，或者是应用的需求发生了变化，那么你可以删除这个表然后重新创建它。但是如果这个表已经填充了好多数据了，或者是该表已经被其它数据库对象引用，（比如一个外键约束）那这可不是一个方便的选项。因此 PostgreSQL提供了一族命令用于修改现有表。请注意它在概念上和修改一个表中包含的数据是不一样的：这里我们感兴趣的是修改一个表的定义，或者说结构。

增加字段，删除字段，增加约束，删除约束，修改缺省值，修改字段数据类型，重命名字段，重命名表。所有这些动作都是用 ALTER TABLE 命令执行的。

7.1、表的定义：

   对于任何一种关系型数据库而言，表都是数据存储的最核心、最基础的对象单元。
   1. 创建表：
   CREATE TABLE products (
       product_no integer,
       name text,
       price numeric
   );

   2. 删除表：
   DROP TABLE products;

   3. 创建带有缺省值的表：
   CREATE TABLE products (
        product_no integer,
        name text,
        price numeric DEFAULT 9.99 --DEFAULT是关键字，其后的数值9.99是字段price的默认值。
   );

   CREATE TABLE products (
        product_no SERIAL,           --SERIAL类型的字段表示该字段为自增字段，完全等同于Oracle中的Sequence。
        name text,
        price numeric DEFAULT 9.99
   );
   输出为：
   NOTICE: CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no"

   4. 约束：
   检查约束是表中最为常见的约束类型，它允许你声明在某个字段里的数值必须满足一个布尔表达式。不仅如此，我们也可以声明表级别的检查约束。
    CREATE TABLE products (
        product_no integer,
        name text,
        --price字段的值必须大于0，否则在插入或修改该字段值是，将引发违规错误。还需要说明的是，该检查约束
        --是匿名约束，即在表定义时没有显示命名该约束，这样PostgreSQL将会根据当前的表名、字段名和约束类型，
        --为该约束自动命名，如：products_price_check。
        price numeric CHECK (price > 0)
   );

   CREATE TABLE products (
        product_no integer,
        name text,
        --该字段的检查约束被显示命名为positive_price。这样做好处在于今后维护该约束时，可以根据该名进行直接操作。
        price numeric CONSTRAINT positive_priceCHECK (price > 0)
   );
    下面的约束是非空约束，即约束的字段不能插入空值，或者是将已有数据更新为空值。
   CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric
   );
   如果一个字段中存在多个约束，在定义时可以不用考虑约束的声明顺序。
   CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric NOT NULL CHECK (price > 0)
   );
   唯一性约束，即指定的字段不能插入重复值，或者是将某一记录的值更新为当前表中的已有值。
   CREATE TABLE products (
        product_no integer UNIQUE,
        name text,
        price numeric
   );

   CREATE TABLE products (
        product_no integer,
        name text,
        price numeric,
        UNIQUE (product_no)
   );
   为表中的多个字段定义联合唯一性。
   CREATE TABLE example (
        a integer,
        b integer,
        c integer,
        UNIQUE (a, c)
   );
   为唯一性约束命名。
   CREATE TABLE products (
        product_no integer CONSTRAINT must_be_differentUNIQUE,
        name text,
        price numeric
   );
   在插入数据时，空值(NULL)之间被视为不相等的数据，因此对于某一唯一性字段，可以多次插入空值。然而需要注意的是，这一规则并不是被所有数据库都遵守，因此在进行数据库移植时可能会造成一定的麻烦。

    5. 主键和外键：
   从技术上来讲，主键约束只是唯一约束和非空约束的组合。
   CREATE TABLE products (
        product_no integer PRIMARY KEY, --字段product_no被定义为该表的唯一主键。
        name text,
        price numeric
   );
   和唯一性约束一样，主键可以同时作用于多个字段，形成联合主键：
   CREATE TABLE example (
        a integer,
        b integer,
        c integer,
        PRIMARY KEY (b, c)
   );
   外键约束声明一个字段（或者一组字段）的数值必须匹配另外一个表中某些行出现的数值。我们把这个行为称做两个相关表之间的参考完整性。
    CREATE TABLE orders (
        order_id integer PRIMARY KEY,--该表也可以有自己的主键。
        --该表的product_no字段为上面products表主键(product_no)的外键。
        product_no integer REFERENCES products(product_no),
        quantity integer
   );

   CREATE TABLE t1 (
       a integer PRIMARY KEY,
       b integer,
       c integer,
       --该外键的字段数量和被引用表中主键的数量必须保持一致。
       FOREIGN KEY (b, c) REFERENCES example (b, c)
   );
   当多个表之间存在了主外键的参考性约束关系时，如果想删除被应用表(主键表)中的某行记录，由于该行记录的主键字段值可能正在被其引用表(外键表)中某条记录所关联，所以删除操作将会失败。如果想完成此操作，一个显而易见的方法是先删除引用表中和该记录关联的行，之后再删除被引用表中的该行记录。然而需要说明的是，PostgreSQL为我们提供了更为方便的方式完成此类操作。
   CREATE TABLE products (
        product_no integer PRIMARY KEY,
        name text,
        price numeric
   );

   CREATE TABLE orders (
        order_id integer PRIMARY KEY,
        shipping_address text
    );

   CREATE TABLE order_items (
        product_no integer REFERENCES productsON DELETE RESTRICT,--限制选项
        order_id integer REFERENCES ordersON DELETE CASCADE,--级联删除选项
        quantity integer,
        PRIMARY KEY (product_no, order_id)
   );
   限制和级联删除是两种最常见的选项。RESTRICT 禁止删除被引用的行。 NO ACTION的意思是如果在检查约束的时候，如果还存在任何引用行，则抛出错误；如果你不声明任何东西，那么它就是缺省的行为。(这两个选择的实际区别是，NOACTION 允许约束检查推迟到事务的晚些时候，而 RESTRICT 不行。)CASCADE声明在删除一个被引用的行的时候，引用它的行也会被自动删除掉。在外键字段上的动作还有两个选项： SET NULL 和 SETDEFAULT。这样会导致在被引用行删除的时候，引用它们的字段分别设置为空或者缺省值。请注意这些选项并不能让你逃脱被观察和约束的境地。比如，如果一个动作声明 SETDEFAULT，但是缺省值并不能满足外键，那么动作就会失败。类似ON DELETE，还有ON UPDATE选项，它是在被引用字段修改(更新)的时候调用的。可用的动作是一样的。

二、系统字段：

   PostgreSQL的每个数据表中都包含几个隐含定义的系统字段。因此，这些名字不能用于用户定义的字段名。这些系统字段的功能有些类似于Oracle中的rownum和rowid等。
   oid: 行的对象标识符(对象ID)。这个字段只有在创建表的时候使用了WITH OIDS，或者是设置了配置参数default_with_oids时出现。这个字段的类型是oid(和字段同名)。
   tableoid: 包含本行的表的OID。这个字段对那些从继承层次中选取的查询特别有用，因为如果没有它的话，我们就很难说明一行来自哪个独立的表。tableoid可以和pg_class的oid字段连接起来获取表名字。
   xmin: 插入该行版本的事务的标识(事务ID)。
   cmin: 在插入事务内部的命令标识(从零开始)。
   xmax: 删除事务的标识(事务ID)，如果不是被删除的行版本，那么是零。
   cmax: 在删除事务内部的命令标识符，或者是零。
   ctid: 一个行版本在它所处的表内的物理位置。请注意，尽管ctid可以用于非常快速地定位行版本，但每次VACUUM FULL之后，一个行的ctid都会被更新或者移动。因此ctid是不能作为长期的行标识符的。
   OID是32位的量，是在同一个集群内通用的计数器上赋值的。对于一个大型或者长时间使用的数据库，这个计数器是有可能重叠的。因此，假设OID是唯一的是非常错误的，除非你自己采取了措施来保证它们是唯一的。如果你需要标识表中的行，我们强烈建议使用序列号生成器。

三、表的修改：

   1. 增加字段：
   ALTER TABLE products ADD COLUMN description text;
   新增的字段对于表中已经存在的行而言最初将先填充所给出的缺省值(如果你没有声明DEFAULT子句，那么缺省是空值)。
   在新增字段时，可以同时给该字段指定约束。
   ALTER TABLE products ADD COLUMN description textCHECK(description <> '');

   2. 删除字段：
   ALTER TABLE products DROP COLUMN description;
   如果该表为被引用表，该字段为被引用字段，那么上面的删除操作将会失败。如果要想在删除被引用字段的同时级联的删除其所有引用字段，可以采用下面的语法形式。
   ALTER TABLE products DROP COLUMN descriptionCASCADE;

   3. 增加约束：
   ALTER TABLE products ADD CHECK(name <> ''); --增加一个表级约束
   ALTER TABLE products ADD CONSTRAINT some_nameUNIQUE(product_no);--增加命名的唯一性约束。
   ALTER TABLE products ADD FOREIGN KEY(pdt_grp_id)REFERENCES pdt_grps; --增加外键约束。
   ALTER TABLE products ALTER COLUMN product_noSET NOT NULL;--增加一个非空约束。

   4. 删除约束：
   ALTER TABLE products DROP CONSTRAINT some_name;
   对于显示命名的约束，可以根据其名称直接删除，对于隐式自动命名的约束，可以通过psql的\dtablename来获取该约束的名字。和删除字段一样，如果你想删除有着被依赖关系地约束，你需要用CASCADE。一个例子是某个外键约束依赖被引用字段上的唯一约束或者主键约束。如：
   MyTest=# \d products
         Table "public.products"
       Column     | Type   | Modifiers
   ------------+---------+-----------
   product_no | integer |
   name      | text    |
   price       | numeric |
   Check constraints:
        "positive_price" CHECK (price > 0::numeric)
   和其他约束不同的是，非空约束没有名字，因此只能通过下面的方式删除：
   ALTER TABLE products ALTER COLUMN product_noDROP NOT NULL;

   5. 改变字段的缺省值:
   在为已有字段添加缺省值时，不会影响任何表中现有的数据行，它只是为将来INSERT命令改变缺省值。
   ALTER TABLE products ALTER COLUMN priceSET DEFAULT 7.77;
   下面为删除缺省值：
   ALTER TABLE products ALTER COLUMN priceDROP DEFAULT

   6. 修改字段的数据类型：
   只有在字段里现有的每个项都可以用一个隐含的类型转换转换成新的类型时才可能成功。比如当前的数据都是整型，而转换的目标类型为numeric或varchar，这样的转换一般都可以成功。与此同时，PostgreSQL还将试图把字段的缺省值（如果存在）转换成新的类型，还有涉及该字段的任何约束。但是这些转换可能失败，或者可能生成奇怪的结果。在修改某字段类型之前，你最好删除那些约束，然后再把自己手工修改过的添加上去。
   ALTER TABLE products ALTER COLUMN priceTYPE numeric(10,2);

   7. 修改字段名：
   ALTER TABLE products RENAME COLUMN product_noTO product_number;

   8. 修改表名：
   ALTER TABLE products RENAME TO items;

参考：

http://www.highgo.com.cn/docs/docs90cn/index.html

http://www.2cto.com/database/201306/217601.html