主页 / user-2135

francs's questions

Martin Hope
francs
Asked: 2016-05-20 05:20:54 +0800 CST
  • 1

PostgreSQL9.6 为配置参数 synchronous_commit 添加了新的设置 remote_apply ，看了下面的文档我还是不明白设置on和remote_apply的区别。谁能帮我？多谢 。

同步提交（枚举）

指定在命令向客户端返回“成功”指示之前事务提交是否将等待 WAL 记录写入磁盘。有效值为 on、remote_apply、remote_write、local 和 off。谁能帮我？多谢。

当设置为 on 时，提交将等待，直到来自当前同步备用数据库的回复表明它们已收到事务的提交记录并将其刷新到磁盘。这确保事务不会丢失，除非主数据库和所有同步备用数据库都遭受数据库存储损坏。当设置为 remote_apply时，提交将等待，直到来自当前同步备用数据库的回复表明他们已经收到事务的提交记录并应用它，以便它对备用数据库上的查询可见

同步提交

http://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT

postgresql
Martin Hope
francs
Asked: 2015-09-08 05:04:40 +0800 CST
  • 1

我对 pg_stat_replication view 的write_location和flush_location感到困惑。文档说， write_location 代表最后一个事务日志位置被这个备用服务器写入磁盘， flush_location 代表最后一个事务日志位置被这个备用服务器刷新到磁盘，有什么区别吗？

在主数据库上查询 pg_stat_replication 视图

sent_location :      Last transaction log position sent on this connection
write_location:      Last transaction log position written to disk by this standby server
flush_location:      Last transaction log position flushed to disk by this standby server
replay_location:     Last transaction log position replayed into the database on this standby server

postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 23453
usesysid         | 24619
usename          | repuser
application_name | node1
client_addr      | 192.168.2.37
client_hostname  | 
client_port      | 58412
backend_start    | 2015-09-07 20:56:13.141438+08
backend_xmin     | 
state            | streaming
sent_location    | 2/3D397B1C
write_location   | 2/3D397B1C
flush_location   | 2/3D397B1C
replay_location  | 2/3D390954
sync_priority    | 1
sync_state       | sync
postgresql
Martin Hope
francs
Asked: 2014-07-25 00:23:00 +0800 CST
  • 0

我想通过编写 .psqlrc 文件来自定义我的个人监视器 sql，但是我遇到了一些问题。

--表空间

skypcsuit=> \db
                          List of tablespaces
       Name        |  Owner   |                Location                 
-------------------+----------+-----------------------------------------
 pg_default        | postgres | 
 pg_global         | postgres | 
 tbs_db_francs     | postgres | /database/pg92/pg_tbs/tbs_db_francs
 tbs_skypcsuit     | postgres | /database/pg92/pg_tbs/tbs_skypcsuit
 tbs_skypcsuit_idx | postgres | /database/pg92/pg_tbs/tbs_skypcsuit_idx

--.psqlrc

\set top10_ts_table '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=\':v_spcname\' order by a.relpages desc limit 10;'

--调用sql但返回空值

skypcsuit=> \set v_spcname tbs_skypcsuit_idx

skypcsuit=> \echo :v_spcname 
tbs_skypcsuit_idx

skypcsuit=> :top10_ts_table 
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
(0 rows)

我将变量v_spcname的值设置为'tbs_skypcsuit_idx'。

--csvlog

2014-07-24 01:10:00.091 PDT,"skypcsuit","skypcsuit",2123,"[local]",53d0bf4c.84b,3,"idle",2014-07-24 01:09:48 PDT,2/94,0,LOG,00000,"statement: 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=':v_spcname' order by a.relpages desc limit 10;",,,,,,,,,"psql"

从日志中我们可以看到变量v_spcname的值没有传递给 sql。有人知道吗？

postgresql
Martin Hope
francs
Asked: 2014-05-28 00:19:46 +0800 CST
  • 5

我们知道 WAL 文件位于$PGDATA/pg_xlog目录中，但我不知道 WAL 文件的含义，例如名为“0000000A00005283000000E0”的 WAL 文件，

我看到一些博客说 WAL 文件名尊重格式名称，该格式名称细分为 3 个 8 个六进制数字序列，定义：

时间线 ID

块 ID

细分 ID

但我还是不明白这一点。谁能解释一下？

- 询问

francs=> select pg_current_xlog_location();
 pg_current_xlog_location 
--------------------------
 5283/D9C2A320
(1 row)

francs=> select pg_xlogfile_name(pg_current_xlog_insert_location());
     pg_xlogfile_name     
--------------------------
 0000000A00005283000000E0
postgresql
Martin Hope
francs
Asked: 2013-04-19 22:49:59 +0800 CST
  • 2

我有 2 个类似的功能，不同的方式导致性能差异很大。

PostgreSQL 版本：9.2.1。

功能 1

create or replace function func_1()  returns text  as
$$
declare
   v_i integer;  
   v_md5 varchar; 
begin
   for v_i in 1..1000000 loop
       v_md5:='a'|| v_i::character varying;
   end loop;  
   return 'ok';
end;
$$
language plpgsql;

功能二

create or replace function func_select()  returns text  as
$$
declare
   v_i integer;  
   v_md5 varchar; 
begin
   for v_i in 1..1000000 loop
      select 'a'|| v_i::character varying into  v_md5;
   end loop;
   return 'ok';
end;
$$
language plpgsql;

功能时序

francs=> \timing
Timing is on.
francs=> select func_1();
 func_1 
--------
 ok
(1 row)

Time: 1467.231 ms
francs=> select func_1();
 func_1 
--------
 ok
(1 row)

Time: 1383.424 ms
francs=> select func_select();
 func_select 
-------------
 ok
(1 row)

Time: 22176.600 ms
francs=> select func_select();
 func_select 
-------------
 ok
(1 row)

Time: 23265.755 ms

从上面看，functionfunc_1()只需要大约1383 ms，但是 functionfunc_select()大约需要 23265 ms，有人可以解释一下吗？

postgresql performance
Martin Hope
francs
Asked: 2012-06-26 21:56:56 +0800 CST
  • 2

现在我们想用 pgsql_fdw 来选择一个远程 postgresql 数据库的表，当我们在会话中选择表时是好的，但是当我们在一个函数中使用外部表时结果是 "ERROR: cache lookup failed for type 0" ，有谁知道，谢谢！

--1 base informaiton

skytf=> \d ft_test;
       Foreign table "skytf.ft_test"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(32) | 
Server: pgsql_srv


skytf=> \des+ pgsql_srv
                                                List of foreign servers
   Name    | Owner | Foreign-data wrapper | Access privileges | Type | Version |                Options                 
-----------+-------+----------------------+-------------------+------+---------+----------------------------------------
 pgsql_srv | skytf | pgsql_fdw            |                   |      |         | {host=127.0.0.1,port=1923,dbname=mydb}
(1 row)

--2 destination table 
mydb=> \d test
             Table "mydb.test"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(32) | 
Indexes:
    "idx_test_1" btree (id)

--3 function
 CREATE or replace FUNCTION  func_sync_bill() RETURNS INTEGER  AS $$
    BEGIN

     begin
      insert into test_tf (id,name) select id,name from ft_test;
      return 1;
     end; 
    END;
  $$ LANGUAGE 'plpgsql';




--4 it works in a session
skytf=> create table test_tf(id integer,name varchar(32));
CREATE TABLE

skytf=> insert into test_tf select * from ft_test;
INSERT 0 1990000


--5 function call error
skytf=> truncate table test_tf;
TRUNCATE TABLE

skytf=> select func_sync_bill();
ERROR:  cache lookup failed for type 0
CONTEXT:  SQL statement "insert into test_tf (id,name) select id,name from ft_test"
PL/pgSQL function "func_sync_bill" line 5 at SQL statement

当我调用将选择外表的函数 func_sync_bill() 时，结果是错误。这是 pgsql_fdw 的错误吗？

--详细的信息

    skytf=> \set VERBOSITY verbose
    skytf=> select func_sync_bill();
    ERROR:  XX000: cache lookup failed for type 0
    CONTEXT:  SQL statement "insert into test_tf (id,name) select id,name from ft_test"
    PL/pgSQL function "func_sync_bill" line 5 at SQL statement

LOCATION:  getTypeOutputInfo, lsyscache.c:2441
postgresql
Martin Hope
francs
Asked: 2012-05-13 23:45:02 +0800 CST
  • 1

从 postgresql 文档中，我知道 vacuum 可以跳过可见性映射中已经标记的页面，因此 vacuum 进程只会对需要 vacuum 的页面进行 vacuum，我们称之为部分 vacuum；有时一个表的所有页恰好需要清理死行版本，我们称这个为整表清理（不是清理完全清理），我的问题是：如何判断一个清理是局部清理还是整表清理，如下是vacuum verbose commond的输出，我们可以从中判断吗？

--vacuum 冗长
mydb=> 真空详细 test_vacuum2;
信息：吸尘“mydb.test_vacuum2”
信息：“test_vacuum2”：在 5 页中的 5 页中找到 0 个可移动、774 个不可移动的行版本
详细信息：尚无法删除 0 个死行版本。
有 226 个未使用的项目指针。
0 页完全是空的。
CPU 0.00s/0.00u sec elapsed 0.00 sec.
真空
postgresql vacuum
Martin Hope
francs
Asked: 2012-03-04 00:17:00 +0800 CST
  • 4

我已经成功安装了 oracle_fdw。创建扩展名oracle_fdw和外部表后，它显示以下错误。这是服务器配置问题吗？

--env
Oracle :    10.2.0.1
PostgreSQL:  9.1.3


--create foreign server
skytf=#   CREATE SERVER oracle_srv 
skytf-#   FOREIGN DATA WRAPPER oracle_fdw 
skytf-#   OPTIONS (dbserver '//192.168.1.30:1521/MANUA');
CREATE SERVER

skytf=# grant usage on foreign server oracle_srv to skytf;
GRANT

--create mapping user
skytf=# CREATE USER MAPPING FOR skytf
skytf-# SERVER oracle_srv 
skytf-# OPTIONS (user 'read_only', password 'read_only');
CREATE USER MAPPING

--create foreign table
skytf=# CREATE FOREIGN TABLE ft_test_1 (
skytf(#  id       integer,
skytf(#  name     character varying(20) 
skytf(#  ) SERVER oracle_srv
skytf-# OPTIONS (schema 'ocp', table 'test_1');
CREATE FOREIGN TABLE


skytf=# \c skytf skytf
skytf=> select * from ft_test_1;
ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:
oracle postgresql
Martin Hope
francs
Asked: 2011-12-08 17:13:39 +0800 CST
  • 6

最近开始学习 pgbouncer，但是对参数有一些不明白的地方server_reset_query。

问题一：主要目的是server_reset_query什么？

问题2：pgbouncer的pool_mode有3种模式：session、transaction和statement模式。当 pool_mode 为 session 时，我们通常将
server_reset_query 的值设置为 'DISCARD ALL'。当 pool_mode 为 transaction 时我们通常将 server_reset_query 的值设置为 ''，

我不知道这方面的细节，所以有人可以解释一下吗？

postgresql
Martin Hope
francs
Asked: 2011-11-28 22:08:16 +0800 CST
  • 3

我想创建一个以cp一些定义的权限命名的角色，然后我们将创建一些其他角色，这些角色将被授予 cp 角色。我知道 Oracle 可以胜任这项工作。例如grant resources to user_name;，这意味着向用户授予资源角色。我在 PostgreSQL 中做了以下测试，但它不起作用。有人知道吗？

--create role cp and grant privilege
postgres=# create role cp login nosuperuser nocreatedb nocreaterole
           noinherit encrypted password 'cp';
CREATE ROLE

postgres=# grant connect on database skytf to cp;
GRANT

postgres=# \c skytf skytf;
You are now connected to database "skytf" as user "skytf".

skytf=> grant usage on schema skytf to cp;
GRANT

skytf=> grant  select on skytf.test_1 to cp;
GRANT


--create role cp_1, and grant cp role privilege to cp_1
skytf=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

postgres=# create role cp_1 login nosuperuser nocreatedb nocreaterole
           noinherit encrypted password 'cp_1';
CREATE ROLE

skytf=# grant cp to cp_1;
GRANT ROLE


--test cp_1
skytf=# \c skytf cp_1;
You are now connected to database "skytf" as user "cp_1".

skytf=> select * from skytf.test_1 limit 1;
ERROR:  permission denied for schema skytf
LINE 1: select * from skytf.test_1 limit 1;
postgresql
Martin Hope
francs
Asked: 2011-10-01 17:45:11 +0800 CST
  • 10

现在，我阅读了有关“Transaction ID Wraparound”的文档，但有一些我真的不明白，文档是以下网址 http://www.postgresql.org/docs/9.0/static/routine-vacuuming .html#VACUUM-FOR-WRAPAROUND

23.1.4. 防止事务 ID 环绕失败

PostgreSQL 的 MVCC 事务语义依赖于能够比较事务 ID (XID) 编号：插入 XID 大于当前事务 XID 的行版本是“在未来”并且不应该对当前事务可见。但是由于事务 ID 的大小有限（32 位），长时间运行（超过 40 亿个事务）的集群会遭受事务 ID 回绕：XID 计数器回绕为零，并且突然之间，在过去似乎在未来——这意味着它们的输出变得不可见。简而言之，灾难性的数据丢失。（实际上数据仍然存在，但如果您无法获取它，那将是一种冷酷的安慰。）为了避免这种情况，有必要每 20 亿次事务至少对每个数据库中的每个表进行一次清理。

我不明白“将遭受交易 ID 环绕：XID 计数器环绕为零，突然之间过去的交易似乎在未来——这意味着它们的输出变得不可见”

有人可以解释一下吗？为什么在数据库遭受事务 ID 环绕之后，过去的事务会出现在未来？简而言之，我想知道 PostgreSQL 在事务 ID 被 autovacuum wraparound 后是否会出现“数据丢失”的情况。

就我个人而言，我们可以使用 txid_current() 函数获取当前事务 ID，其输出为 64 位，不会循环。所以我认为知道为 xmin 的元组的插入事务 ID 会比得到的 xid 神经更大通过 txid_current() 函数。除非您在关闭 PostgreSQL 服务器后使用 pg_resetxlog 重置重置事务 ID。我对吗 ？谢谢

postgresql
Martin Hope
francs
Asked: 2011-09-20 18:48:29 +0800 CST
  • 1

今天测试一下PostgreSQL9.1的“复制特性”的新特性，首先我已经成功安装了PostgreSQL9.1的Hot-standby。不是问题。我要测试的是我想使用命令“pg_ctl start Promotion”到主备节点，而不是创建触发器文件。是的，我执行命令后，备用节点然后成为主节点并允许写/读操作。但初始主节点不能自动切换到备用角色。我不确定PostgreSQL9.1是否能够使用“pg_ctl促进”在主节点和备用节点之间进行切换。任何人都可以解释一下吗？

postgresql