weixin_39939108的博客

1.创建新的表空间:CREATE TABLESPACE temptablespace LOCATION '/path/../';2.检查当前表空间:SELECT tablespace FROM pg_tables WHERE tablename = 'mybigtable';（注:如果为空就是缺省表空间）3.移动表到新的表空间:ALTER TABLE mybigtable SET ...

SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activityWHERE datname = 'datbase_name' AND pid <> pg_backend_pid();SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_st...

1.版本信息SELECT version();2.创建数据库CREATE DATABASE Database_Name WITH OWNER User_Name;3.修改数据库属主ALTER DATABASE Database_Name OWNER TO User_Name;4.基于模板创建数据库CREATE DATABASE NewDB WITH TEMPLATE OldDB;5...

SELECT pid ,datname ,usename ,application_name ,client_hostname ,client_port ,backend_start ,query_start ,query FROM pg_stat_activityWHERE state <> 'idle'AND pid<>pg_backend_p...

1. 表大小SELECT table_schema || '.' || table_name AS TableName, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSizeFROM information_schema.tab...

SELECT pg_terminate_backend(pid)FROM pg_stat_activityWHERE datname = 'Database_Name' AND pid <> pg_backend_pid() AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)',...

SELECT table_catalog ,table_schema ,table_name ,column_name ,data_type ,column_defaultFROM information_schema.columnsWHERE table_schema = 'public' AND column_default IS NOT NULLORDER BY col...

1. 方法一testdb=# \password test1;Enter new password:Enter it again:testdb=#2. 方法二testdb=# ALTER USER test1 WITH PASSWORD '4321';ALTER ROLEtestdb=#

1.在线库备份:#pg_dump -h host1 dbname | psql -h host2 dbname2.离线备份#pg_dump -U username database_name > filepath\backup.sql3.恢复#pg_dump -U username database_name < filepath\backup.sql...

testdb=# SELECT current_setting('server_version_num'); current_setting----------------- 100010(1 row)testdb=# select version();

1. 表定义testdb=# \d+ person Table "public.person" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description--------+...

1.创建序列testdb=# CREATE SEQUENCE test_serial START 101;2. 查找孤立序列SELECT ns.nspname AS SchemaName , c.relname AS SequenceNameFROM pg_class AS c JOIN ...

1. 查看数据库对象SELECT n.nspname as schema_name , CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' ...

1.创建临时表create temporary table "tmp_tbl_001" as select * from person;2. 查找临时表SELECT n.nspname as SchemaName , c.relname as RelationName , CASE c.relkind ...

1. SQL语句SELECT schemaname ,relname ,n_live_tup ,n_dead_tup ,last_autovacuumFROM pg_stat_all_tablesWHERE schemaname='public'ORDER BY n_dead_tup2.结果testdb=# schemaname | re...

1. 表说明2. SQLregression=# SELECT c.relname, count(*) AS buffers FROM pg_buffercache b INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND ...

1.查询某个用户的拥有的对象这里是postgres，可以换成对应用户select nsp.nspname as SchemaName ,cls.relname as ObjectName ,rol.rolname as ObjectOwner ,case cls.relkind when 'r' then 'TABLE' wh...

1. 列出表信息testdb-# \dt; List of relations Schema | Name | Type | Owner--------+--------+-------+---------- public | person | table | postgres(1 row)2.通配查询

1. 连接数据库[postgres@ab50dcf74584 /]$ psql -d postgrespsql (10.10)Type "help" for help.2. 查看数据库实例testdb-# \l List of databases Name | Owner | Encoding | Collate...

1. VI编辑first.sh在这里插入代码片

1.创建用户（用户或角色）testdb=# CREATE USER test WITH PASSWORD '123';;CREATE ROLE2. 赋予超级用户权限testdb=# ALTER USER test WITH SUPERUSER;ALTER ROLE3. 取消超级用户权限testdb=# ALTER USER test WITH NOSUPERUSER;ALTER ...

1.连接数据库[postgres@ab50dcf74584 ~]$ psql -h 127.0.0.1psql (10.10)Type "help" for help.postgres=#2.列出数据库实例postgres-# \l List of databases Name | Owner | Encodi...