francs's questions

PostgreSQL9.6 adiciona nova configuração remote_apply para o parâmetro de configuração synchronous_commit. Lendo o documento a seguir, ainda não entendo a diferença entre a configuração on e remote_apply . Alguém pode me ajudar? Muito obrigado .

synchronous_commit (enum)

Especifica se a confirmação da transação aguardará que os registros do WAL sejam gravados no disco antes que o comando retorne uma indicação de "sucesso" ao cliente. Os valores válidos são on, remote_apply, remote_write, local e off. Alguém pode me ajudar? Muito obrigado.

Quando definido como on , os commits aguardarão até que as respostas do(s) standby(s) síncrono(s) atual(is) indiquem que receberam o registro de commit da transação e o liberaram no disco. Isso garante que a transação não será perdida, a menos que o primário e todos os standbys síncronos sofram corrupção de seu armazenamento de banco de dados. Quando definido como remote_apply , os commits aguardarão até que as respostas do(s) standby(s) síncrono(s) atual(is) indiquem que receberam o registro de commit da transação e o aplicaram, de modo que se torne visível para consultas no(s) standby(s)

síncrono_commit

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

Estou confuso sobre write_location e flush_location de pg_stat_replication view . O documento diz, write_location significa a última posição do log de transações gravada no disco por este servidor em espera, flush_location significa a última posição do log de transações liberada no disco por este servidor em espera, isso faz alguma diferença?

Consulte a exibição pg_stat_replication no banco de dados primário

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

Eu quero customizar meu monitor SQL pessoal escrevendo o arquivo .psqlrc, mas encontro alguns problemas.

--tablespace

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;'

--chamar o sql mas retornar valor nulo

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)

Eu defino o valor da variável v_spcname como '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"

Pelo log podemos ver que os valores da variável v_spcname não passaram para o sql. alguem sabe disso?

Sabemos que o arquivo WAL está localizado no diretório $PGDATA/pg_xlog , mas não sei o significado do arquivo WAL, como um arquivo WAL chamado "0000000A00005283000000E0",

Vejo o blog de alguns dizer que o nome do arquivo WAL respeita um nome de formato subdividido em 3 sequências de 8 dígitos hexadecimais definindo:

Código da linha do tempo

ID do bloco

ID do segmento

Mas eu ainda não entendo isso. alguém pode explicar isso?

--consulta

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

Eu tenho 2 funções semelhantes, maneiras diferentes resultam em grande diferença no desempenho.

A versão do PostgreSQL: 9.2.1.

Função 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;

Função 2

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;

Tempos de Função

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

Do exposto, a função func_1()leva apenas cerca de 1383 ms , mas a função func_select()leva cerca de 23265 ms , alguém pode explicar isso?

Agora queremos usar pgsql_fdw para selecionar uma tabela de banco de dados postgresql remoto，Quando selecionamos a tabela em uma sessão, está tudo bem, mas quando usamos a tabela externa em uma função, ocorre "ERRO: falha na pesquisa de cache para o tipo 0" ， alguém sabe, obrigado!

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

quando chamo a função func_sync_bill() que vai selecionar uma tabela estrangeira, dá o erro. Isso é um bug do pgsql_fdw?

--verbose meeage

    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

Do documento postgresql, eu sei que o vácuo pode pular páginas que já estão marcadas no mapa de visibilidade, então o vácuo progride apenas páginas de vácuo que requerem vácuo, e chamamos isso de vácuo parcial; Às vezes, todas as páginas de uma tabela requerem aspiração para remover a versão de linha morta, chamamos essa mesa inteira de vácuo (não vácuo completo). Minha pergunta é: como podemos ver se um vácuo é um vácuo parcial ou um vácuo de tabela inteira, o seguinte é a saída do vácuo verboso comum, podemos julgar a partir disso?

--vacuum detalhado
mydb=> vacuo verbose test_vacuum2;
INFO: aspirando "mydb.test_vacuum2"
INFO: "test_vacuum2": encontrou 0 versões de linha removíveis e 774 não removíveis em 5 de 5 páginas
DETALHE: 0 versões de linhas mortas ainda não podem ser removidas.
Havia 226 ponteiros de itens não utilizados.
0 páginas estão totalmente vazias.
CPU 0,00s/0,00u seg decorrido 0,00 seg.
VÁCUO

Eu instalei o oracle_fdw com sucesso. Depois de criar a extensão oracle_fdwe a tabela estrangeira, ele mostra o seguinte erro. Isso é um problema de configuração do servidor?

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

Recentemente, comecei a aprender sobre o pgbouncer, mas há algumas coisas que não entendo sobre o parâmetro server_reset_query.

Pergunta 1: Qual é o objetivo principal do server_reset_query?

Questão 2: Existem três modos do pool_mode do pgbouncer: sessão, transação e modo de declaração. Quando o pool_mode é sessão, geralmente definimos os valores de
server_reset_query como 'DISCARD ALL'. Quando o pool_mode é uma transação, geralmente definimos os valores de server_reset_query como '',

Eu não sei os detalhes sobre isso, então alguém pode explicar isso?

Eu quero criar uma função nomeada cpcom alguns privilégios definidos, então criaremos algumas outras funções que serão concedidas com a função cp. Eu sei que a Oracle pode fazer esse trabalho. Por exemplo , o grant resources to user_name;que significa conceder a função de recursos a um usuário. Eu faço o seguinte teste no PostgreSQL, mas não funciona. Alguém sabe disso?

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

Agora, eu li o documento sobre "Transaction ID Wraparound", mas há algo que eu realmente não entendo, o documento é o seguinte url http://www.postgresql.org/docs/9.0/static/routine-vacuuming .html#VACUUM-FOR-WRAPAROUND

23.1.4. Prevenção de falhas de contorno de ID de transação

A semântica de transação MVCC do PostgreSQL depende da capacidade de comparar números de ID de transação (XID): uma versão de linha com um XID de inserção maior que o XID da transação atual é "no futuro" e não deve ser visível para a transação atual. Mas, como os IDs de transação têm tamanho limitado (32 bits), um cluster que executa por um longo período (mais de 4 bilhões de transações) sofreria uma quebra de ID de transação: o contador XID volta a zero e, de repente, as transações que estavam no passado parecem estar no futuro - o que significa que sua produção se torna invisível. Resumindo, perda de dados catastrófica. (Na verdade, os dados ainda estão lá, mas isso é um consolo se você não conseguir obtê-los.) Para evitar isso, é necessário limpar todas as tabelas em todos os bancos de dados pelo menos uma vez a cada dois bilhões de transações.

Eu não entendo as declarações "sofreria a volta do ID da transação: o contador XID chega a zero e, de repente, as transações que estavam no passado parecem estar no futuro - o que significa que sua saída se torna invisível"

Alguém pode explicar isso? Por que, depois que o banco de dados sofre um wraparound de ID de transação, as transações que estavam no passado parecem estar no futuro? Resumindo, quero saber se o PostgreSQL entrará na situação de "perda de dados" após o wraparound do ID da transação pelo autovacuum。

Para minhas visões pessoais, podemos obter o ID da transação atual usando a função txid_current () cuja saída é de 64 bits e não será alternada. pela função txid_current(). Exceto que você usará pg_resetxlog reset reset ID da transação após desligar o PostgreSQL Server. Estou certo ? Obrigado

Hoje testei o novo recurso sobre "recursos de replicação" do PostgreSQL9.1. Primeiro, instalei o Hot-standby do PostgreSQL9.1 com sucesso. Não é um problema。 O que eu quero testar é que eu quero usar o comando "pg_ctl start promote" para ativar o nó de espera em vez de criar um arquivo de gatilho 。 Sim, depois de executar o comando, o nó de espera torna-se primário e permite operações de gravação/leitura。 Mas o nó primário inicial não pode alternar para a função de espera automaticamente. Não tenho certeza se o PostgreSQL9.1 é capaz de usar "pg_ctl promote" alterne entre o nó principal e o de espera 。Alguém pode explicar isso?