postgresql 基础及常见操作命令

PG基础

模式 (schema)

一个数据库包含一个或多个命名模式，模式中包含着表。模式还包含其他类型的命名对象， 包括数据类型、函数和操作符。相同的对象名称可以被用于不同的模式中而不会出现冲突， 例如schema1和myschema都可以包含名为mytable的表。和数据库不同，模式并不是被严格地 隔离：一个用户可以访问他们所连接的数据库中的所有模式内的对象，只要他们有足够的权 限。
下面是一些使用模式的原因：
• 允许多个用户使用一个数据库并且不会互相干扰。
• 将数据库对象组织成逻辑组以便更容易管理。
• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
模式类似于操作系统层的目录，但是模式不能嵌套。

默认情况下，用户不能访问不属于他们的模式中的任何对象。要允许这种行为，模式的拥有 者必须在该模式上授予USAGE权限。为了允许用户使用模式中的对象，可能还需要根据对 象授予额外的权限。
一个用户也可以被允许在其他某人的模式中创建对象。要允许这种行为，模式上 的CREATE权限必须被授予。

2. PG安装

2.1 见安装文档

启动
su postgres
pgstart     /   pg_ctl start
停止
su postgres
pgstop      /   pg_ctl stop

2.2 docker 安装 pg

1. 搜索postgres数据库

docker search postgres

查看到排名第一的是一个官方的镜像，有6000多个star

2. 下载

docker pull postgres

3. 查看，下载成功之后可以查看到postgres镜像

docker images

4. 运行，启动一个实例

docker run --name postgresql -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres

这里使用的实例名称为postgresql，
数据库密码是postgres，用户名是默认的postgres,
-p 5432:5432表示映射的端口号为5432，如果不设置端口号，下面通过工具登陆数据库将会出现问题，
-d postgres表示创建了一个默认的数据postgres
查看实例是否运行

docker container ls

如果已经成功运行，能够通过该命令查看到运行的实例

3.基本操作命令

1. 创建用户

   postgres=# create user testdb with password '123456';
   create user dgjcw with password 'dgjcw'
   ​	CREATE ROLE	
   #修改用户密码
   alter user username with password ‘password’
   
   切换用户
   postgres=# \c - username;
   

2. 创建schema

   create schema jcw authorization jcw;
   
   #设置schema权限
   grant all privileges on schema native to dgjcw;
   \#将schema下的所有表的所有权限给用户
   grant all on all tables in schema schemaname to username;
   grant select on all tables in schema schemaname to username;
   \# 更改schema所属用户
   alter schema schemaname owner to username
   \# 更改表所属schema
   alter table table_name set schema schema_name;
   

3. 创建数据库

   postgres=# create database testdb owner testdb;
   ​	CREATE DATABASE
   ​	postgres=# grant all privileges on database testdb to testdb;
   

4. 切换数据库

   postgres=# \c database;
   

5. 查看数据库下所有的表

   postgres=# \dt;
   

4.字段类型介绍

1. 数字类型

   整数类型中，常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在 磁盘空间紧张的时候才使用 smallint类型。而只有在integer的范围不够的时候才使用bigint。

   类型numeric可以存储非常多位的数字。我们特别建议将它用于货币金额和其它要求计算准确 的数量。numeric值的计算在可能的情况下会得到准确的结果，例如加法、减法、乘法。不 过，numeric类型上的算术运算比整数类型或者下一节描述的浮点数类型要慢很多。

2. 时间类型

date、time、timestamp、interval

3. 字符类型
   

   这些类型的存储需求是 4 字节加上实际的字串，如果是 character 的话再加上填充的字节。 长的字串将会自动被系统压缩， 因此在磁盘上的物理需求可能会更少些。长的数值也会存储 在后台表里面，这样它们就不会干扰对短字段值的快速访问。 不管怎样，允许存储的最长字 串大概是 1 GB。

5.sql实例

5.1 PGlimit命令

LIMIT子句由两个独立的子句构成：
LIMIT { count | ALL } OFFSET start
count指定要返回 的最大行数，而start 指定在返回行之前要跳过的行数。在两者都被指定 时，在开始计算要返回的 count行之前会跳过 start行。
如果count表达式计算 为 NULL，它会被当成LIMIT ALL，即没有限制。如果 start计算为 NULL，它会被当作OFFSET 0。

5.2 sequence序列

-----pg
CREATE SEQUENCE MY_TEST_SEQ
    INCREMENT 1
    START 10
    MINVALUE 1
    MAXVALUE 99999999
    CACHE 2;
   
alter table test_local alter column id set default nextval('MY_TEST_SEQ');

SELECT nextval('MY_TEST_SEQ');


CREATE TABLE products (    product_no integer DEFAULT nextval('products_product_no_seq'),    ... );
或者
CREATE TABLE products (    product_no SERIAL,    ... );

5.4 pg数据库迁移

a.备份命令：
pg_dump -h 127.0.0.1 -Upostgres databasename > /home/files/databasename.bak
 
指令解释：
pg_name 是备份数据库指令
127.0.0.1是数据库的ip地址（必须保证数据库允许外部访问权限）
postgres 是数据库的用户名
databasename 是数据库名
\>  意思是导出到/home/files/databasename.bak 文件里，如果没有写路径，只写文件名则保存在PostgreSQl\10\bin 文件夹中
注：直接使用pg_dump命令可能会出现版本不一致的问题
可使用当前安装pg的绝对路径执行：
/usr/pgsql-10/bin/pg_dump -h 127.0.0.1 -Upostgres databasename > /home/files/databasename.bak

-n 指定schema
-d 指定数据源
-s 只导入表结构
\#用root用户执行#：
pg_dump -h 127.0.0.1 -Upostgres dgjcw -n native > /home/iknowFiles/native.bak

===============================实例===========================
#用root用户执行#：
/opt/pgsql/bin/pg_dump -h 127.0.0.1 -Upostgres postgres -n publiic > /home/pg_test_data/databasename.bak


b.恢复命令（）
需要先创建pg对应的用户
psql -h localhost -Upostgres -d databasename < databasename.bak
===============================实例===========================
#用postgres用户执行#：
psql -h 127.0.0.1 -Upostgres -d postgres < /home/pg_test_data/databasename.bak

5.5 copy 命令

表到文件(文件中原来的内容会被覆盖)
    [e-mail][local]:5432=#\copy test_user to '/home/pg_test_data/user.txt'
指定分隔符
[e-mail][local]:5432=#\copy test_user to '/home/pg_test_data/user.txt' delimiter ',';
文件到表
[e-mail][local]:5432=#\copy test_user from '/home/pg_test_data/user_in.txt';
指定分隔符
\copy test_user from '/home/pg_test_data/user_in.txt' delimiter ',';

文件读写权限问题
chmod -R 777 /dirname

5.6 dblink

###启动两个pg容器
安装插件
create extension dblink;

select * from 
dblink('dbname=postgres host=127.0.0.1 port=5432 user=postgres password=postgres'::text,'select id from test_link'::text) as table_a (id integer); 

SELECT dblink_connect('mycoon','hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=postgres');  
SELECT dblink_exec('mycoon', 'BEGIN');
SELECT dblink_exec('mycoon', 'delete from test_link where id=1');
SELECT dblink_exec('mycoon', 'COMMIT');
SELECT dblink_disconnect('mycoon');


如果不只是查询数据，而是需要修改postgres的数据的情况下怎么弄呢？
1. 先执行dblink_connect保持连接
SELECT dblink_connect('mycoon','hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=postgres');  
2. 执行BEGIN命令
SELECT dblink_exec('mycoon', 'BEGIN');
3. 执行数据操作（update，insert，create等命令）
SELECT dblink_exec('mycoon', 'delete from test_link where id=1');
4. 执行事务提交
SELECT dblink_exec('mycoon', 'COMMIT');
5. 解除连接
SELECT dblink_disconnect('mycoon');

DDL事务

postgres=# begin;
BEGIN
postgres=# drop table t ;
DROP TABLE
postgres=# rollback ;
ROLLBACK