postgresql基本操作

﹏ヽ暗。殇╰゛Y 2022-08-01 03:15 290阅读 0赞

## Table of Contents ##

*  1. 切换到postgres用户
 *  2. 启动数据库
 *  3. 已创建数据库
 *  4. 查看版本号
 *  5. 创建数据库
 *  6. SQL相关
    
     *  6.1. 从文件中读取操作
     *  6.2. 建表
     *  6.3. 插入数据
     *  6.4. 从文件导入数据
     *  6.5. 查询数据
     *  6.6. 更新
     *  6.7. 删除数据
     *  6.8. 删除表
     *  6.9. 创建视图
     *  6.10. 事物
     *  6.11. 继承
 *  7. 删除数据库
 *  8. 系统管理
    
     *  8.1. 系统内核参数
     *  8.2. 影响PostgreSQL的内存使用参数
 *  9. 服务器配置
    
     *  9.1. 设置参数
 *  10. 管理数据库
    
     *  10.1. 创建数据库
     *  10.2. 指定模板库
     *  10.3. 修改配置参数
     *  10.4. 删除数据库
     *  10.5. 表空间管理
     *  10.6. 查询字符编码
 *  11. 备份和恢复数据
    
     *  11.1. 备份
     *  11.2. 恢复

# 切换到postgres用户 #

su - postgres

# 启动数据库 #

postgres -D /usr/local/pgsql/data或者pg_ctl -D /usr/local/pgsql/data -l logfile start

# 已创建数据库 #

psql -Upostgres

# 查看版本号 #

psql
    select version();

# 创建数据库 #

createdb mydb

# SQL相关 #

## 从文件中读取操作 ##

\i basics.sql

\-s会进入单步模式

## 建表 ##

CREATE TABLE weather (
     city            varchar(80),
     temp_lo         int,           -- 最低气温
     temp_hi         int,           -- 最高气温
     prcp            real,          -- 降水量
     date            date
    );

支持标准的 SQL 类型 int，smallint， real，double precision， char(N)， varchar(N)，date， time，timestamp 和 interval  
特有数据类型point

CREATE TABLE cities (
     name            varchar(80),
     location        point
    );

## 插入数据 ##

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
    INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
    INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
     VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
    INSERT INTO weather (date, city, temp_hi, temp_lo)
     VALUES ('1994-11-29', 'Hayward', 54, 37);

## 从文件导入数据 ##

COPY weather FROM '/home/user/weather.txt';

## 查询数据 ##

SELECT * FROM weather;
    SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

## 更新 ##

UPDATE weather
     SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
     WHERE date > '1994-11-28';

## 删除数据 ##

DELETE FROM weather WHERE city = 'Hayward';

## 删除表 ##

drop table weather;

## 创建视图 ##

CREATE VIEW myview AS
     SELECT city, temp_lo, temp_hi, prcp, date, location
         FROM weather, cities
         WHERE city = name;

## 事物 ##

BEGIN;
     UPDATE accounts SET balance = balance - 100.00
       WHERE name = 'Alice';
     -- 等等
    COMMIT;

## 继承 ##

CREATE TABLE capitals (
        name            text,
        population      real,
        altitude        int,    -- (单位是英尺)
        state           char(2)
    );
    
    CREATE TABLE non_capitals (
        name            text,
        population      real,
        altitude        int     -- (单位是英尺)
    );
    
    CREATE VIEW cities AS
        SELECT name, population, altitude FROM capitals
            UNION
        SELECT name, population, altitude FROM non_capitals;
    
      ;; 上面语句可以实现为:
      CREATE TABLE cities (
        name            text,
        population      real,
        altitude        int     -- (单位是英尺)
    );
    
    CREATE TABLE capitals (
        state           char(2)
    ) INHERITS (cities);
    
            比如，下面的查询找出所有海拔超过 500 英尺的城市的名字， 包括州首府：
    
    SELECT name, altitude
        FROM cities
        WHERE altitude > 500;
    
    它返回：
    
       name    | altitude
    -----------+----------
     Las Vegas |     2174
     Mariposa  |     1953
     Madison   |      845
    (3 rows)

另外一方面，下面的查询找出所有不是州首府并且位于海拔大于或等于 500 英尺的城市：

SELECT name, altitude
        FROM ONLY cities
        WHERE altitude > 500;
    
       name    | altitude
    -----------+----------
     Las Vegas |     2174
     Mariposa  |     1953
    (2 rows)

这里的 cities 前面的 ONLY 指示系统只对 cities 表运行查询，而不包括继承级别中低于 cities 的表。 许多我们已经讨论过的命令 — SELECT, UPDATE 和 DELETE — 支持这个 ONLY 表示法。

# 删除数据库 #

dropdb XXX

# 系统管理 #

## 系统内核参数 ##

<table>      
 <tbody> 
  <tr> 
   <td>名称</td> 
   <td>描述</td> 
   <td>合理取值</td> 
  </tr> 
  <tr> 
   <td>SHMMAX</td> 
   <td>最大共享内存段尺寸</td> 
   <td>最少若干兆</td> 
  </tr> 
  <tr> 
   <td>SHMMIN</td> 
   <td>最小共享内存段尺寸</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>SHMALL</td> 
   <td>可用共享内存的总数量</td> 
   <td>如果是字节，就和 SHMMAX 一样；如果是页面，ceil(SHMMAX/PAGE_SIZE)</td> 
  </tr> 
  <tr> 
   <td>SHMSEG</td> 
   <td>每个进程最大共享内存段数量</td> 
   <td>只需要一个段，不过缺省比这个高的多。</td> 
  </tr> 
  <tr> 
   <td>SHMMNI</td> 
   <td>系统范围最大共享内存段数量</td> 
   <td>类似SHMSEG加上用于其他应用的空间</td> 
  </tr> 
 </tbody> 
</table>

1.  配置共享内存值
    
        $ sysctl -w kernel.shmmax=134217728
        $ sysctl -w kernel.shmall=2097152
    
    可以把这些设置放到 /etc/sysctl.conf 里，在重启后保持有效

## 影响PostgreSQL的内存使用参数 ##

<table>     
 <tbody> 
  <tr> 
   <td>名称</td> 
   <td>近似倍率(每次增加字节数)</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>max_connections</td> 
   <td>400 + 220 \* max_locks_per_transaction</td> 
  </tr> 
  <tr> 
   <td>max_prepared_transactions</td> 
   <td>600 + 220 \* max_locks_per_transaction</td> 
  </tr> 
  <tr> 
   <td>shared_buffers</td> 
   <td>8300 （假设 8K 的BLCKSZ）</td> 
  </tr> 
  <tr> 
   <td>wal_buffers</td> 
   <td>8200 （假设 8K 的BLCKSZ）</td> 
  </tr> 
  <tr> 
   <td>max_fsm_relations</td> 
   <td>70</td> 
  </tr> 
  <tr> 
   <td>max_fsm_pages</td> 
   <td>6</td> 
  </tr> 
 </tbody> 
</table>

# 服务器配置 #

## 设置参数 ##

postgresql.conf

# 管理数据库 #

postgres是系统库，不能删除。template1是模板库，创建数据库都会使用这个模板，不要轻易修改。  
template0是不能修改的。

## 创建数据库 ##

1.  通过sql创建
    
        create database name owner rolename;
2.  通过shell命令创建
    
        createdb -O rolename dbname

## 指定模板库 ##

1.  通过sql创建
    
        create database name template template0;
2.  通过shell命令创建
    
        createdb -T template0 dbname

## 修改配置参数 ##

一种是在配置文件中修改  
也可以通过SQL修改，如:

ALTER DATABASE mydb SET geqo TO off;
    ALTER DATABASE dbname RESET varname;

## 删除数据库 ##

1.  SQL方式
    
        drop database name;
2.  shell命令方式
    
        dropdb dbname

## 表空间管理 ##

initdb 自动创建两个表空间。pg\_global 表空间用于共享的系统表。 pg\_default 是  
template1 和 template0 数据库的缺省表空间 （因此，这个表空间也将是任何其它数据库  
的缺省表空间，除非在 CREATE DATABASE 的时候使用了明确的 TABLESPACE 子句。）

1.  创建
    
        CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
2.  创建表指定表空间
    
        CREATE TABLE foo(i int) TABLESPACE space1;
3.  指定默认表空间
    
        SET default_tablespace = space1;
        CREATE TABLE foo(i int);

## 查询字符编码 ##

# 备份和恢复数据 #

## 备份 ##

要声明 pg\_dump 应该以哪个用户身份进行联接，使用命令行选项 -h host 和 -p port。  
缺省主机是本地主机或你的环境变量PGHOST声明的值

pg_dump dbname > outfile
    pg_dump dbname | gzip > filename.gz -- 压缩方式
    pg_dumpall > outfile -- 所有数据库

## 恢复 ##

createdb -T template0 dbname
    psql dbname < infile
    gunzip -c filename.gz |psql dbname -- 压缩方式
    psql -f infile postgres -- 所有数据库