Postgres基础操作

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

显示数据库 \l \l+

dw=# \l

                                        List of databases

    Name     |        Owner        | Encoding |   Collate   |    Ctype    |   Access privileges

-------------+---------------------+----------+-------------+-------------+-----------------------

 crawl       | bm_repo             | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/bm_repo          +

             |                     |          |             |             | bm_repo=CTc/bm_repo  +

             |                     |          |             |             | monitoring=c/bm_repo

 dw          | bluemoon            | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 kettlejobs  | mgnt_manager        | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 mgntetl     | mgnt_manager        | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 postgres    | postgres            | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 recommender | bd_tool_recommender | 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

 test        | hadoop              | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/hadoop           +

             |                     |          |             |             | hadoop=CTc/hadoop

 test2       | postgres            | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

(10 rows)

dw=# SELECT datname FROM pg_database;

   datname

-------------

 template1

 template0

 postgres

 test

 test2

 mgntetl

 dw

 recommender

 kettlejobs

 crawl

(10 rows)

SELECT   tablename   FROM   pg_tables WHERE   tablename   NOT   LIKE   'pg%' AND tablename NOT LIKE 'sql_%'  ORDER   BY   tablename;

select tablename from pg_tables where schemaname='bluemoon';

postgres=# \d+ pg_stat_activity

                        View "pg_catalog.pg_stat_activity"

      Column      |           Type           | Modifiers | Storage  | Description

------------------+--------------------------+-----------+----------+-------------

 datid            | oid                      |           | plain    |

 datname          | name                     |           | plain    |

 pid              | integer                  |           | plain    |

 usesysid         | oid                      |           | plain    |

 usename          | name                     |           | plain    |

 application_name | text                     |           | extended |

 client_addr      | inet                     |           | main     |

 client_hostname  | text                     |           | extended |

 client_port      | integer                  |           | plain    |

 backend_start    | timestamp with time zone |           | plain    |

 xact_start       | timestamp with time zone |           | plain    |

 query_start      | timestamp with time zone |           | plain    |

 state_change     | timestamp with time zone |           | plain    |

 waiting          | boolean                  |           | plain    |

 state            | text                     |           | extended |

 backend_xid      | xid                      |           | plain    |

 backend_xmin     | xid                      |           | plain    |

 query            | text                     |           | extended |

View definition:

 SELECT s.datid,

    d.datname,

    s.pid,

    s.usesysid,

    u.rolname AS usename,

    s.application_name,

    s.client_addr,

    s.client_hostname,

    s.client_port,

    s.backend_start,

    s.xact_start,

    s.query_start,

    s.state_change,

    s.waiting,

    s.state,

    s.backend_xid,

    s.backend_xmin,

    s.query

   FROM pg_database d,

    pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin),

    pg_authid u

  WHERE s.datid = d.oid AND s.usesysid = u.oid;

https://www.yiibai.com/postgresql/postgresql-create-database.html

创建一个数据库

CREATE DATABASE db1;

授权

db1=# create role yzw;

CREATE ROLE

db1=# grant all privileges on database db1 to yzw;

GRANT

db1=# revoke all on database db1 from yzw;

REVOKE

db1=# drop user yzw;

DROP ROLE

postgres=# alter user yzw superuser;

ALTER ROLE

postgres=# alter user yzw login;

ALTER ROLE

postgres=# \l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 db1       | 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

(4 rows)

postgres=# \du

                             List of roles

 Role name |                   Attributes                   | Member of

-----------+------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication | {}

 yzw       | Superuser, Create DB                           | {}

postgres=# \c - yzw

You are now connected to database "postgres" as user "yzw".

postgres=# select current_user;

 current_user

--------------

 yzw

(1 row)

查询表名和表注释

select relname as tabname,

cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c

where   relname ='table_name' ;

查询字段名、字段类型及字段长度和字段注释

select a.attnum,a.attname,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type,d.description from pg_class c, pg_attribute a , pg_type t, pg_description d

where  c.relname = 'table_name' and a.attnum>0 and a.attrelid = c.oid and a.atttypid = t.oid and  d.objoid=a.attrelid and d.objsubid=a.attnum

查看参数

db1=# show config_file;

           config_file

----------------------------------

 /data/pgsql_data/postgresql.conf

(1 row)

db1=# show hba_file;

           hba_file

------------------------------

 /data/pgsql_data/pg_hba.conf

(1 row)

db1=# show ident_file;

           ident_file

--------------------------------

 /data/pgsql_data/pg_ident.conf

(1 row)

# 查看所有参数

show all;

# 参看某个参数

db1=# show enable_seqscan;

 enable_seqscan

----------------

 on

(1 row)

备份命令

pg_dump -h 127.0.0.1 -U postgres gitlabhq_production > 20181114092738.bak

pg_dump -h 127.0.0.1 -U postgres postgres > postgres.bak

pg_dump -h 127.0.0.1 -U postgres template0 > template0.bak

pg_dump -h 127.0.0.1 -U postgres template1 > template1.bak

https://blog.csdn.net/ctypyb2002/article/details/79881745

切换搜索路径，可以查询所有的表

set search_path to mysql;

set search_path to public;

SET search_path TO myschema,public;

\dt

详细显示数据库 \l+
查看版本
- 查看详细版本 select version();
- 查看基本版本 show server_version;SHOW server_version_num;SELECT current_setting('server_version_num');
- SELECT current_setting(‘server_version_num’); 返回类型为text，如果需要可以转换为interger
```
SELECT current_setting('server_version_num')::integer;
```
显示schema信息

set search_path to mysql;

\dt

切换用户或者链接远程数据库

\c postgres     # 切换数据库切换用户

\c - postgres   # 切换用户不切换数据库

\c postgres role1 # 切换数据库和用户

postgres=> \c postgres role1 172.16.10.143 5432 # 连接远程数据库 ？

You are now connected to database "postgres" as user "role1".

\d [名字] 描述表, 索引, 序列, 或者视图列出表/索引/序列/视图/系统表
\d{t|i|s|v|S} [模式] (加 "+" 获取更多信息) 列出表/索引/序列/视图/系统表
查看表结构

pdb1=> \dt t1;

        List of relations

 Schema | Name | Type  |  Owner

--------+------+-------+----------

 mysql  | t1   | table | postgres

(1 row)

查看用户 \du
查看表访问权限

pdb1=> \dp

                              Access privileges

 Schema | Name | Type  |     Access privileges     | Column access privileges

--------+------+-------+---------------------------+--------------------------

 mysql  | t1   | table | postgres=arwdDxt/postgres+|

        |      |       | role1=r/postgres         +|

        |      |       | mydb_select=r/postgres   +|

        |      |       | u1=r/postgres             |

(1 row)

\timing 查询计时开关切换 (目前是关闭)
\t 只显示行 (当前是关闭)
\x 在扩展输出之间切换 (目前是关闭)
查看当前是否正在备份

select pg_is_in_backup();

开始结束备份打标记

select pg_start_backup(now()::text);

[root@mycat02 pgsql_data]# cat backup_label

START WAL LOCATION: 0/1B000028 (file 00000002000000000000001B)

CHECKPOINT LOCATION: 0/1B000060

BACKUP METHOD: pg_start_backup

BACKUP FROM: master

START TIME: 2018-11-28 14:09:06 CST

LABEL: 2018-11-28 14:09:06.446921+08

select pg_stop_backup();

创建还原点

checkpint;

# 或者打个标记

select pg_create_restore_point('#标记内容');

打标记的作用配合还原使用，启动时候可以指定:

https://blog.csdn.net/pg_hgdb/article/details/79168044

# 命名的还原点

recovery_target_name = '' # e.g.‘daily backup 2018-01-14‘

# 目标时间还原点

recovery_target_time = '' # e.g.‘2018-01-14 22:39:00 EST‘

# XID事务还原点

recovery_target_xid = '' # 慎用事务点不一定准确

切换归档日志

select pg_switch_xlog();

查看当前txid

select txid_current();

-[ RECORD 1 ]+-----

txid_current | 1892

查看tablespace

pdb1=> \db

                List of tablespaces

    Name    |  Owner   |         Location

------------+----------+---------------------------

 pg_default | postgres |

 pg_global  | postgres |

 tabspace01 | role1    | /data/pgsql_data/pgdata01

(3 rows)

create user 和 role 的区别

# CREATE USER is the same as CREATE ROLE except that it implies LOGIN."----CREATE USER除了默认具有LOGIN权限之外，其他与CREATE ROLE是完全相同的

CREATE ROLE kanon PASSWORD 'kanon' LOGIN;

CREATE USER kanon PASSWORD 'kanon'.

官方建议是这样的：在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后，将search_path设置为"$user"，

这样，任何当某个用户连接上来后，会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

create user bluemoon password '123456';

create database bluemoon owner bluemoon tablespace tabspace01;

create schema bluemoon authorization bluemoon;

postgres=# \c bluemoon

You are now connected to database "bluemoon" as user "postgres".

bluemoon=# show search_path;

  search_path

----------------

 "$user",public

(1 row)

bluemoon=> set search_path=bluemoon;

SET

bluemoon=> show search_path;

 search_path

-------------

 bluemoon

(1 row)

bluemoon=> create table t1(c int);

CREATE TABLE

bluemoon=> create table bluemoon.t1(c int);

ERROR:  relation "t1" already exists

ALTER database "bluemoon" SET search_path TO bluemoon;

ALTER DATABASE name SET TABLESPACE new_tablespace

GRANT CREATE ON TABLESPACE tabspace01 TO user;

select d.datname,p.spcname from pg_database d, pg_tablespace p where d.datname='lottu01' and p.oid = d.dattablespace;

ALTER TABLE name SET TABLESPACE new_tablespace

查询数据库所在默认表空间

select datname,dattablespace from pg_database where datname='bluemoon';

 datname  | dattablespace

----------+---------------

 bluemoon |         16428

(1 row)

select oid,spcname from pg_tablespace where oid=16428;

  oid  |  spcname

-------+------------

 16428 | tabspace01

(1 row)

查询表和索引所在的表空间

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname

  from pg_class a, pg_tablespace tb

 where a.reltablespace = tb.oid

   and a.relkind in ('r', 'i')

 order by a.relpages desc;

查询某个表空间上的数据库

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner

from pg_class a, pg_tablespace tb

where a.relkind in ('r', 'i')

and a.reltablespace=tb.oid

--and tb.spcname='edw_data'

order by a.relpages desc;

查询数据库所在表空间

select d.datname,p.spcname from pg_database d, pg_tablespace p;

  datname  |  spcname

-----------+------------

 template1 | pg_default

 template1 | pg_global

 template1 | tabspace01

 template0 | pg_default

 template0 | pg_global

 template0 | tabspace01

 postgres  | pg_default

 postgres  | pg_global

 postgres  | tabspace01

 pdb1      | pg_default

 pdb1      | pg_global

 pdb1      | tabspace01

 bluemoon  | pg_default

 bluemoon  | pg_global

 bluemoon  | tabspace01

(15 rows)

http://francs3.blog.163.com/blog/static/4057672720120133544960/

http://www.cnblogs.com/lottu/p/9239535.html

查看某个schema的所有表

select * from information_schema.tables where table_schema='public'

查询表名称及表结构

1. 查询表名称

# 在psql状态下查询表名称

bluemoon=# \c bluemoon bluemoon

bluemoon=> \dt t1;

         List of relations

  Schema  | Name | Type  |  Owner

----------+------+-------+----------

 bluemoon | t1   | table | bluemoon

(1 row)

bluemoon=> \dt

         List of relations

  Schema  | Name | Type  |  Owner

----------+------+-------+----------

 bluemoon | t1   | table | bluemoon

 bluemoon | t2   | table | bluemoon

 bluemoon | t3   | table | bluemoon

(3 rows)

# SQL方式查看表名称

SELECT tablename FROM pg_tables;

1. 查询表结构

# 在psql状态下查询表结构

\d t1

     Table "bluemoon.t1"

 Column |  Type   | Modifiers

--------+---------+-----------

 c      | integer |

# SQL方式查看表结构

SELECT a.attnum,

c.relname,

a.attname AS field,

t.typname AS type,

a.attlen AS length,

a.atttypmod AS lengthvar,

a.attnotnull AS notnull,

b.description AS comment

FROM pg_class c,

pg_attribute a

LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,

pg_type t

WHERE  1=1

-- and c.relname = 'udoc_saldiscount' /*relname是表名*/

and a.attnum > 0

and a.attrelid = c.oid

and a.atttypid = t.oid

ORDER BY a.attnum;

创建索引

# 单字段索引：

CREATE INDEX index_name ON table_name (field1);

# 联合索引：

CREATE INDEX index_name ON table_name (field1,field2);

导出指定数据库指定schema的表

pg_dump -h [db ip] -U [db user name] -s [db name] -n [schema name] > [file path]

输出结果到文件 https://blog.csdn.net/pg_hgdb/article/details/78552656

postgres=# select * from abcd; \g /tmp/a.txt

 a | b | c | d

---+---+---+---

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

(7 rows)

[root@db1 ~]# tail -f /tmp/a.txt

---+---+---+---

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

 1 | 2 | 3 | 4

(7 rows)

---------------------