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，如果需要可以转换为intergerSELECT 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('#标记内容');
- 打标记的作用配合还原使用，启动时候可以指定:
# 命名的还原点
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'
查询表名称及表结构
-
- 查询表名称
# 在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;
-
- 查询表结构
# 在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]
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)
---------------------
