postgresql备注的简单介绍

PostgreSQL：如何查询基于用户(role)设置的参数

下面演示下：一 方法一：通过 pg_user 视图查询--1.1 设置用户的 log_statement 参数 postgres=# alter role francs set log_statement="all";ALTER ROLE --1.2 验证 postgres=# select * From pg_user where usename='francs';-[ RECORD 1 ]--------------------usename | francsusesysid | 24920usecreatedb | fusesuper | fusecatupd | fuserepl | fpasswd | ********valuntil | useconfig | {log_statement=all}--1.3 设置用户的 maintenance_work_mem 参数 postgres=# alter role francs set maintenance_work_mem="1GB";ALTER ROLE--1.4 再次验证 postgres=# select * From pg_user where usename='francs';-[ RECORD 1 ]---------------------------------------------usename | francsusesysid | 24920usecreatedb | fusesuper | fusecatupd | fuserepl | fpasswd | ********valuntil | useconfig | {log_statement=all,maintenance_work_mem=1GB} 备注：上面是通过 pg_user.useconfig 查询。二 方法二： 通过 pg_db_role_setting catalog 基表查询--2.1 pg_db_role_setting 表结构 Table "pg_catalog.pg_db_role_setting" Column | Type | Modifiers -------------+--------+----------- setdatabase | oid | not null setrole | oid | not null setconfig | text[] | Indexes: "pg_db_role_setting_databaseid_rol_index" UNIQUE, btree (setdatabase, setrole), tablespace "pg_global"备注：可见 pg_db_role_setting 会针对数据库，用户级别进行记录。--2.2 验证 postgres=# select oid,rolname from pg_authid where rolname='francs'; oid | rolname -------+--------- 24920 | francs(1 row)postgres=# select * From pg_db_role_setting where setrole=24920; setdatabase | setrole | setconfig -------------+---------+---------------------------------------------- 0 | 24920 | {log_statement=all,maintenance_work_mem=1GB}备注：因为之前的设置只针对用户 francs 设置，而没指定相应的数据库，所以 setdatabase 值为 0。 接着往下看。

相关问答

Q1: postgresql如何执行sql文件

在pgAdmin的窗口中，直接打开SQL文件然后执行就可以了。

你所说的Oracle的执行方式，应该是在sql

plus命令窗口中执行sql文件。这个功能在PostgreSQL的命令窗口psql中也有此功能：开启psql命令窗口之后，执行命令:

\i sqlfile_path就可以执行指定的sql文件了,

其中sqlfile_path是要执行的sql文件，

如下图,

要注意的是:

在Windows系统中，路径中用的是斜杠，不是反斜杠。

Q2: postgresql 查看表建立哪些索引

在数据库运维工作中，经常会有数据目录使用率较高需要调整的情况，通常会给数据库建立多个表空间，

并分别位于不同的盘上，这时需要做的工作就是调整库中现有表和索引的表空间，下面简单总结下这块维护

工作的内容，以下都是基于 PostgreSQL 9.0.1 做的测试。

一 查询某个表所在表空间的简单方法

PostgreSQL 提供类似" \ "命令很方便得到相关信息，命令如下：

skytf= \d test_2

Table "skytf.test_2"

Column | Type | Modifiers

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

id | integer |

obj_id | integer | not null

name | character varying(64) |

Indexes:

"idx_hash_name" hash (name)

"idx_test_2" btree (id, obj_id)

Tablespace: "tbs_skytf_idx"

备注：如果这个表的表空间为当前数据库的默认表空间，那么上面则不会显示 Tablespace 信息，

相反，则会显示这张有的表空间，例如上面的表 test_2 的表空间为 tbs_skytf_idx，而

表空间 "tbs_skytf_idx" 不是数据库 skytf 的默认表空间， 那么如何查询数据库的默认

表空间呢，可以通过以下命令查询。

--1.1 查询数据库的默认表空间

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

datname | dattablespace

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

skytf | 14203070

(1 row)

skytf= select oid,spcname from pg_tablespace where oid=14203070;

oid | spcname

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

14203070 | tbs_skytf

(1 row)

备注：通过以上查出数据库 skytf 的默认表空间为 tbs_skytf。

二 批量查询数据库表和索引的表空间

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

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;

备注：上面只取了部分结果，这个查询能够查询表和索引所处的表空间，但是有一点需要注意，这个查询

仅显示表空间不是数据库默认表空间的数据库对像，而我们通常需要查出位于数据库默认表空间的

对像，显然上面的查询不是我们想要的，接下来看另一个查询。

--2.2 查询位于默认数据库表空间的对像

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

from pg_class a

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

and reltablespace='0'

order by a.relpages desc;

备注：这个查询加入限制条件 reltablespace='0'，即可查找出位于当前数据库默认表空间的

数据库表和索引。 通常这才是我们想要的结果，接下来可以把部分表转移到其它表空间上去，转移

的方法可以用 "ALTER TABLE move tablespace "或者重建索引移表空间等方法，这里不详细介绍。

--2.3 查询在某个表空间上的对像

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='tablespace_name'

order by a.relpages desc;

--2.4 手册上对于 pgclass 视图的 reltablespace 字段解释

The tablespace in which this relation is stored. If zero, the database is default tablespace is

implied. (Not meaningful if the relation has no on-disk file.)

Q3: postgresql 怎么同时更改字段和默认值

修改字段的 attnum

francs= c francs postgres

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

francs=# update pg_attribute set attnum=4 where attrelid='francs.test_6'::regclass and attname='col3';

UPDATE 1

francs=# update pg_attribute set attnum=3 where attrelid='francs.test_6'::regclass and attname='col2';

UPDATE 1

francs=# update pg_attribute set attnum=2 where attrelid='francs.test_6'::regclass and attname='col3';

UPDATE 1

francs=# select * from francs.test_6;

col1 | col3 | col2

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

1 | 2 | 3

4 | 5 | 6

(2 rows)

备注：修改系统表 pg_attribute ，更改表 francs.test_6 字段的 attnum 值，结果发现只是字段名称

换了下顺序，而字段中的值却没有更改，显然通过修改系统表 pg_attribute.attnum 值是行不通

的，更进一步，假设这步成功了，如果表字段上有索引，或者约束，那么还得修改相应的系统表，

显然修改数据库系统表的做法是危险的，容易给数据库带来灾难，万不得已，不建议这么做。

既然直接修改系统表字段顺序的方法行不通，那么可以通过其它间接的方法，这里想到了两种，

第一种是重建表，即新建表结构再把老表数据导进去。第二种是新建一个符合规则的视图，以

后应用程序不直接查原表，而是查视图。

Q4: 如何退出postgresq数据库

启动数据库服务器(posgres用户)

[postgres@localhost bin]$ postgres D /opt/postgresql/data/ /opt/postgresql/log/pg_serverlog

[]

当然如果设置了环境变量

PGDATA=/opt/postgresql/data

export PGDATA

后可使用pg_ctl工具进行启动:

[postgres@localhost log]$ pg_ctl start l /opt/postgresql/log/pg_serverlog

pg_ctl: another server might be running; trying to start server anyway

pg_ctl: could not start server

Examine the log output

[postgres@localhost log]$

因为之前已经启动所以打印another server might be running此时查看日志有如下信息:

[postgres@localhost log]$ cat pg_serverlog

FATAL: lock file postmasterpid already exists

HINT: Is another postmaster (PID ) running in data directory /opt/postgresql/data?

[postgres@localhost log]$

当然最简的启动方式是

[postgres@localhost ~]$ pg_ctl start

server starting

[postgres@localhost ~]$ LOG: database system was shut down at :: CST

LOG: autovacuum launcher started

LOG: database system is ready to accept connections

如果要在操作系统启动时就启动PG可以在/etc/rcd/rclocal 文件中加以下语句

/opt/postgresql/bin/pg_ctl start l /opt/postgresql/log/pg_serverlog D /opt/postgresql/data

关闭服务器

最简单方法

[postgres@localhost ~]$ pg_ctl stop

waiting for server to shut down done

server stopped

与Oracle相同在关闭时也可采用不同的模式简介如下

SIGTERM

不再允许新的连接但是允许所有活跃的会话正常完成他们的工作只有在所有会话都结束任务后才关闭这是智能关闭

SIGINT

不再允许新的连接向所有活跃服务器发送 SIGTERM(让它们立刻退出)然后等待所有子进程退出并关闭数据库这是快速关闭

SIGQUIT

令 postgres 向所有子进程发送 SIGQUIT 并且立即退出(所有子进程也会立即退出)而不会妥善地关闭数据库系统这是立即关闭这样做会导致下次启动时的恢复(通过重放 WAL 日志)我们推荐只在紧急的时候使用这个方法

SIGKILL

此选项尽量不要使用这样会阻止服务器清理共享内存和信号灯资源那样的话你只能在启动服务器之前自己手工做这件事另外SIGKILL 直接把 postgres 杀掉而不会等它把信号中继给它的子进程因此我们还需要手工杀掉每个独立子进程

使用方法举例

[postgres@localhost ~]$ pg_ctl stop o SIGTERM

LOG: received smart shutdown request

LOG: autovacuum launcher shutting down

waiting for server to shut downLOG: shutting down

LOG: database system is shut down

done

server stopped

[postgres@localhost ~]$

最快速关闭方法kill postgres 进程

[postgres@localhost ~]$ kill INT `head /opt/postgresql/data/postmasterpid`

[postgres@localhost ~]$ LOG: received fast shutdown request

LOG: aborting any active transactions

LOG: autovacuum launcher shutting down

LOG: shutting down

LOG: database system is shut down

附postgre启动后的进程如下:

[postgres@localhost ~]$ ps ef|grep post

root : pts/ :: su postgres

postgres : pts/ :: bash

postgres : pts/ :: /opt/postgresql/bin/postgres

postgres : ? :: postgres: writer process

postgres : ? :: postgres: wal writer process

postgres : ? :: postgres: autovacuum launcher process

postgres : ? :: postgres: stats collector process

postgres : pts/ :: ps ef

postgres : pts/ :: grep post

[postgres@localhost ~]$

Q5: postgresql怎么查看表的创建时间

方法一：通过查找表数据文件方式

这种方法通过查找表的数据文件的方式从而确定表的创建时间，但是这种方法并不能准备查询表的创建

时间，而且有时候，这种方法得到的信息还有可能是错误的，下面大致演示下。

--1.1 创建表并插入数据

francs= create table test_ctime (id int4 primary key ,name varchar(32));

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_ctime_pkey" for table "test_ctime"

CREATE TABLE

francs= insert into test_ctime select generate_series(1,10000),'create_time test';

INSERT 0 10000

francs= \d test_ctime;

Table "francs.test_ctime"

Column | Type | Modifiers

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

id | integer | not null

name | character varying(32) |

Indexes:

"test_ctime_pkey" PRIMARY KEY, btree (id)

francs= \dt+ test_ctime;

List of relations

Schema | Name | Type | Owner | Size | Description

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

francs | test_ctime | table | francs | 536 kB |

(1 row)

备注：表创建好了，接下来演示如何定位表的物理文件。

--1.2 定位表所在的表空间

francs= select relname,relfilenode,reltablespace from pg_class where relname='test_ctime';

relname | relfilenode | reltablespace

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

test_ctime | 24650 | 0

(1 row)

备注：在 PostgreSQL 的逻辑结构体系中，表位于数据库中，同时表位于表空间上，面表空间对应系统上一个

文件目录，每个表由一个或者多个文件组成； 根据上面的结果，表 test_ctime 的 reltablespace

值为 0,表示位于所属数据库的默认表空间，注意 relfilenode 值为 24650。

--1.3 查询数据库 francs 的默认表空间

francs= select oid,datname,dattablespace from pg_database where datname='francs';

oid | datname | dattablespace

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

16386 | francs | 16385

备注：上面查出数据库 francs 的默认表空间的 oid 为 16385。

--1.4 查找 oid 为 16385 的表空间

francs= select oid,* from pg_tablespace where oid=16385;

oid | spcname | spcowner | spcacl | spcoptions

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

16385 | tbs_francs | 10 | {postgres=C/postgres,francs=C/postgres} |

(1 row)

备注：查了半天才查到表 test_ctime 的默认表空间为 tbs_francs，这里之所以饶这么大圈，是为

了展示 postgresql 中的一些逻辑结构关系，如果自己对环境比较熟悉，可以直接定位到

哪个表空间。

--1.5 查询表空间 tbs_francs 对应的物理目录

francs= \db

List of tablespaces

Name | Owner | Location

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

pg_default | postgres |

pg_global | postgres |

tbs_francs | postgres | /database/1922/pgdata1/pg_tbs/tbs_francs

(3 rows)

备注：表空间 tbs_francs 的数据目录为 /database/1922/pgdata1/pg_tbs/tbs_francs。

--1.6 进入数据目录

[postgres@redhat6 16386]$ cd /database/1922/pgdata1/pg_tbs/tbs_francs

[postgres@redhat6 tbs_francs]$ ll

total 4.0K

drwx------. 4 postgres postgres 4.0K May 22 10:35 PG_9.2_201204301

[postgres@redhat6 tbs_francs]$ cd PG_9.2_201204301/

[postgres@redhat6 PG_9.2_201204301]$ ll

total 16K

drwx------. 2 postgres postgres 12K Jun 26 19:03 16386

drwx------. 2 postgres postgres 4.0K May 22 10:37 pgsql_tmp

备注：根据前面的步骤 1.3 查询的信息知道 16386 为数据库 francs 的 oid。 再根据步骤 1.2 的信息知道

表 test_ctime 的 relfilenode 值为 24650

--1.7 查找表 test_ctime 的数据文件

[postgres@redhat6 16386]$ ll 24650

-rw-------. 1 postgres postgres 512K Jun 26 18:57 24650

备注：根据数据文件 24650 知道表的创建时间为 2012-06-26 18:57。但这种方法并不准确，因为

表上的操作可能导致表重新生成文件，接着演示。

--1.8 cluster 表

francs= cluster verbose test_ctime using test_ctime_pkey;

INFO: clustering "francs.test_ctime" using index scan on "test_ctime_pkey"

INFO: "test_ctime": found 0 removable, 10000 nonremovable row versions in 64 pages

DETAIL: 0 dead row versions cannot be removed yet.

CPU 0.00s/0.03u sec elapsed 0.08 sec.

CLUSTER

francs= select relname,relfilenode,reltablespace from pg_class where relname='test_ctime';

relname | relfilenode | reltablespace

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

test_ctime | 24655 | 0

(1 row)

备注：表 test_ctime 经过 cluster 操作后，重新生成了数据文件，文件号由原来的 24650 变成了 24655

--1.9 系统上再次查询表数据文件

[postgres@redhat6 16386]$ ll 24650

-rw-------. 1 postgres postgres 0 Jun 26 19:19 24650

[postgres@redhat6 16386]$ ll 24655

-rw-------. 1 postgres postgres 512K Jun 26 19:19 24655

备注：显然新文件的时间 24655 并不是表 test_ctime 的初始创建时间。

--1.10 vacuum full 表

francs= vacuum full test_ctime;

VACUUM

francs= select relname,relfilenode,reltablespace from pg_class where relname='test_ctime';

relname | relfilenode | reltablespace

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

test_ctime | 24659 | 0

(1 row)

备注： vacuum full 操作后，同样产生了新文件，新文件号为 24659

--1.11 系统上再次查询表数据文件

[postgres@redhat6 16386]$ ll 24659

-rw-------. 1 postgres postgres 512K Jun 26 19:22 24659