postgresql基本操作

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

系统管理

系统内核参数

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

1. 配置共享内存值

   $ sysctl -w kernel.shmmax=134217728
   $ sysctl -w kernel.shmall=2097152
   

   可以把这些设置放到 /etc/sysctl.conf 里，在重启后保持有效

影响PostgreSQL的内存使用参数

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

服务器配置

设置参数

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);
   

查询字符编码

   $ psql -l
            List of databases
   Database    |  Owner  |   Encoding
---------------+---------+---------------
 euc_cn        | t-ishii | EUC_CN
 euc_jp        | t-ishii | EUC_JP
 euc_kr        | t-ishii | EUC_KR
 euc_tw        | t-ishii | EUC_TW
 mule_internal | t-ishii | MULE_INTERNAL
 postgres      | t-ishii | EUC_JP
 regression    | t-ishii | SQL_ASCII
 template1     | t-ishii | EUC_JP
 test          | t-ishii | EUC_JP
 utf8          | t-ishii | UTF8
(9 rows)

备份和恢复数据

备份

要声明 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 -- 所有数据库