All perguntas(dba)

Tenho um arquivo de despejo do PostgreSQL 13 feito com este comando:

$ sudo -u postgres pg_dump --no-privileges -Fc -n my_schema --file="/path/to/my_schema.sql" my_database

Mas quando tento restaurá-lo em um novo banco de dados (PG15), enfrento um problema inesperado por causa da --clean --if-existsopção:

$ sudo -u postgres pg_restore  --single-transaction --clean --if-exists --schema=my_schema --no-owner --role="app-user" --dbname=my_database /path/to/my_schema.sql
pg_restore: error: could not execute query: ERROR:  schema "my_schema" does not exist
LINE 1: CREATE TABLE my_schema.my_table (
                     ^
Command was: CREATE TABLE my_schema.my_table (
    id integer NOT NULL,
    name character varying(128),
    description text
);

O esquema não deveria ser recriado antes que as tabelas sejam realmente criadas?

Se isso puder ajudar, aqui está o cabeçalho relevante do arquivo de despejo (desculpe, é um formato binário personalizado):

$ head -n27 my_schema.sql
PGDMP0my_database13.16 (Debian 13.16-0+deb11u1)13.16 (Debian 13.16-0+deb11u1)-=0ENCODINENCODINGSET client_encoding = 'UTF8';
false=00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false=00
SEARCHPATH
SEARCHPATH8SELECT pg_catalog.set_config('search_path', '', false);
false=126217698my_databasDATABASEgCREATE DATABASE my_database WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_US.UTF-8';
"DROP DATABASE my_database;
postgresfalse2615200013my_schemaSCHEMACREATE SCHEMA my_schema;
DROP SCHEMA my_schema;
app-userfalseo12592000138my_tableTABLECREATE TABLE my_schema.my_table (
    id integer NOT NULL,
    name character varying(128),
    description text
);
!DROP TABLE my_schema.my_table;
my_schemaheaapp-userfalse14p12592000144my_table_id_seSEQUENCE}CREATE SEQUENCE my_schema.my_table_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
+DROP SEQUENCE my_schema.my_table_id_seq;
my_schemaapp-userfalse62314=00my_table_id_seqSEQUENCE OWNED BYMALTER SEQUENCE my_schema.my_table_id_seq OWNED BY my_schema.my_table.id;

Há algo que eu possa fazer e por que esse erro está ocorrendo?

Ao executar qualquer consulta no mysqlmodo interativo, a saída inclui uma linha como a seguinte:

1 row in set (0.07 sec)

Entretanto, se eu executar qualquer consulta com mysql -e "select * from foo;", essa linha estará faltando.

É possível obter essa linha mesmo quando estou executando com -e?

Estou trabalhando com um banco de dados PostgreSQL onde preciso armazenar um valor numérico associado a uma chave específica.

Com o tempo, adicionarei continuamente esse valor com base na chave.

Quero garantir que a tabela não fique inchada com várias versões de linhas ou tuplas inativas, especialmente porque essa operação de atualização será frequente (como 100 req/s).

1. Quais são as melhores práticas no PostgreSQL para fazer isso?

2. Devo usar INSERT ON CONFLICT, um gatilho ou outra abordagem?

3. Como posso garantir que minha tabela permaneça eficiente e não sofra com inchaço excessivo devido a atualizações frequentes?

Depois de mover um banco de dados de um servidor para outro, começamos a ter um problema:

> Msg 15581, Level 16, State 7, Procedure xxxxx, Line 24 [Batch Start Line 2]
Please create a master key in the database or open the master key in the session before performing this operation.

Msg 15315, Level 16, State 1, Procedure xxxxx, Line 62 [Batch Start Line 2]
The key '<CustomKeyName>' is not open. Please open the key before using it.

O procedimento em questão tem:

 OPEN SYMMETRIC KEY MySymKey
 DECRYPTION BY CERTIFICATE MySymCert; 

Posso verificar que depois de mover o banco de dados, posso ver a symmetric keye a certificateconsultando sys.symmetric_keyse sys.certificates.

Nós, sim, não temos a chave mestra. Estou curioso para saber se esse processo funcionaria:

1. No servidor antigo (no escopo do BD):

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'a_cool_pass';

2. backup do BD novamente

3. Restaurar o BD no novo servidor novamente (com substituição)

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'the_cool_Pass'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

essa seria uma solução adequada para essa situação?

OU posso simplesmente remover a symmetric_key e o cert e recriá-los?

edição: REGENERATINGa chave mestra passou no servidor antigo, backup/restauração no novo, OPEN MASTER KEY DECRYPTION BY PASSWORD =tive o problema The key is not encrypted using the specified decryptor.(o que eu achei estranho. Tentarei novamente amanhã.

(precisaria fazer uma restauração de backup do certificado?

Implementando um esquema de partição rotativa, por kejser.org/table-pattern-rotating-log-ring- . Tive um problema com DATEDIFF arredondando valores:

DECLARE @Partitions INT = 15;

SELECT
    a1.dt
    , dtTrunc 
    , dtDiff
    , PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT)
FROM
    (
    VALUES
        ('2024-08-17 23:59:59.997')
        , ('2024-08-17 23:59:59.998')
        , ('2024-08-17 23:59:59.999')
        , ('2024-08-18 00:00:00.000')
    )
    AS v(dt)
CROSS APPLY
    (
    SELECT
        dt = CAST(v.dt AS DATETIME2(3))
    ) a1
CROSS APPLY
    (
    SELECT
        dtTrunc = CAST(a1.dt AS DATE)
        , dtDiff = DATEDIFF(day, 0, a1.dt)
    ) a2

Problema resolvido com elenco até o momento:

DECLARE @Partitions INT = 15;

SELECT
    a1.dt
    , dtTrunc 
    , dtDiff
    , PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT)
FROM
    (
    VALUES
        ('2024-08-17 23:59:59.997')
        , ('2024-08-17 23:59:59.998')
        , ('2024-08-17 23:59:59.999')
        , ('2024-08-18 00:00:00.000')
    )
    AS v(dt)
CROSS APPLY
    (
    SELECT
        dt = CAST(v.dt AS DATETIME2(3))
    ) a1
CROSS APPLY
    (
    SELECT
        dtTrunc = CAST(a1.dt AS DATE)
        , dtDiff = DATEDIFF(day, 0, CAST(a1.dt AS DATE))
    ) a2

Esse é um comportamento esperado/documentado? Se sim, onde?

Vemos em um de nossos serviços que certas consultas sempre resultam em um COLLSCAN, em vez do índice apropriado ser usado. Os dados têm o formato

{_id: DocumentID, "field": {"child": "Cross-App-ID"}}

com um Índice {"field.child":1}.

Executar a consulta para "field.child", ou seja, db.collection.find({"field.child":"CA-ID"})funciona como esperado com o índice sendo usado. No entanto, por algum motivo, o aplicativo que acessa o banco de dados cria as consultas como db.collection.find({"field":{"child":"CA-ID"}}), o que resulta em um COLLSCANem vez de um IXSCAN.

2 perguntas para mim a partir disto:

• Essas consultas não deveriam ser equivalentes e, portanto, tratadas da mesma forma?
• Como posso criar um índice para o segundo caso de uso? Todos os exemplos que encontrei criam o índice como já fizemos.

Tenho alguns serviços que podem ser executados insertpara inserir novos elementos em uma tabela do meu banco de dados (Postgresql). Então, na visão do banco de dados, muitos inserts são executados concorrentemente.

Minha pergunta é se é possível manter a ordem de inserção com alguma coluna da tabela.

Por exemplo, existem três inserts:

INSERT INTO Users (UserName, Id) VALUES ('Tom', 1);
INSERT INTO Users (UserName, Id) VALUES ('Jerry', 2);
INSERT INTO Users (UserName, Id) VALUES ('Joey', 3);

Eles estão sendo executados simultaneamente.

Por algum motivo, o segundo insertestá atrasado. Neste caso, quando o terceiro insertestá sendo executado, estou esperando um dos dois resultados abaixo:

1. as insertfalhas;
2. ter insertsucesso com um novo Id 2.

Existe algum mecanismo para conseguir isso?

Por que preciso disso?

Há um gerador de ID que gera IDs com algoritmo snowflake. Concurrents insertsão executados em multi-threads. Ao ler dados da tabela, quero fazer consultas de intervalo como esta:

select * from my_table where Id > a limit 5
select * from my_table where Id > b limit 5
select * from my_table where Id > c limit 5
...

a, be csão os últimos Id vindos do anterior select.

| --- a --- | --- b --- | --- c --- |

O problema é que quando o segundo selectestá em execução, ainda pode ser possível fazer um insertna azona por causa de concurrent insert. Se isso acontecer, não terei chance de ler os dados inseridos atrasados.

Tenho um arquivo *.pgerd que quero abrir e visualizar o diagrama do banco de dados. Quais ferramentas gratuitas posso usar? Também tenho acesso ao IntellJ Ultimate Edition, então se alguém souber de plugins que eu possa usar, isso também seria útil.

Para sua informação: usei https://app.diagrams.net/ para abrir o arquivo *.pgerd, e parece funcionar (ou seja, consigo ver o diagrama do banco de dados, mas ele parece mal formatado). Então, estou pensando se há alguma ferramenta melhor por aí além do app.diagrams.net?

Quero fazer backup do meu servidor MySQL em outro servidor em outro local (para segurança dos dados). O problema é que o outro servidor tem metade da potência do servidor principal.

Não preciso que o servidor seja 100% replicado. Mesmo uma hora de diferença seria suficiente para o meu caso de uso, mas quero ter certeza de que não perderei nenhum desempenho.

Minha configuração faz sentido?

Sempre me disseram que isso REORGANIZEnão faz nada para corrigir o preenchimento deficiente da página, porque tudo o que faz é reordenar as páginas no disco. Para investigar isso, li a documentação. No entanto, descobri que isso se contradiz. Otimize a manutenção do índice para melhorar o desempenho da consulta e reduzir as reclamações de consumo de recursos

A reorganização também compacta as páginas de índice para tornar a densidade da página igual ao fator de preenchimento do índice.

e os documentos para ALTER INDEXapoiar isso

A reorganização também compacta as páginas de índice. A compactação é baseada no valor do fator de preenchimento existente.

mas isso é contradito pela documentação do fator de preenchimento

Uma configuração explícita FILLFACTORse aplica somente quando o índice é criado ou reconstruído pela primeira vez. O Mecanismo de Banco de Dados não mantém dinamicamente a porcentagem especificada de espaço vazio nas páginas.

Então, o que está acontecendo? As páginas compactam e respeitam REORGANIZEo fator de preenchimento? Ou é apenas mover páginas de folhas e não compactar?