O problema

Eu PostgreSQL 9.3instalei no meu MacBook Pro. Não me lembro como o instalei ( existem várias maneiras ) e agora quero atualizar para o PostgreSQL 9.5.

forense

Os PostgreSQLprocessos em execução são:

  503    89     1   0 Fri09PM ??         0:06.57 /Library/PostgreSQL/9.3/bin/postmaster -D/Library/PostgreSQL/9.3/data
  503   236    89   0 Fri09PM ??         0:00.02 postgres: logger process
  503   249    89   0 Fri09PM ??         0:01.10 postgres: checkpointer process
  503   251    89   0 Fri09PM ??         0:03.06 postgres: writer process
  503   252    89   0 Fri09PM ??         0:03.09 postgres: wal writer process
  503   253    89   0 Fri09PM ??         0:05.79 postgres: autovacuum launcher process
  503   254    89   0 Fri09PM ??         0:15.80 postgres: stats collector process

O que significa que os binários estão localizados em:

/Library/PostgreSQL/9.3/bin/postmaster
/Library/PostgreSQL/9.3/bin/psql
...

e os dados em

/Library/PostgreSQL/9.3/data

O que eu tentei

Eu me certifiquei de que não foi instalado usando Homebrew.

Alguma ideia de qual instalação PostgreSQLestá instalada no meu mac e como atualizá-la?

Considere um banco de dados de produção com algumas dezenas de tabelas de tamanho normal e um punhado de tabelas enormes.

Estou procurando uma maneira conveniente de acessar pg_dumpo conteúdo do banco de dados, excluindo as poucas tabelas enormes, para meu próprio laptop local. Posso despejar facilmente uma pequena amostra das tabelas enormes usando \COPY, se ocasionalmente precisar endereçar essas tabelas.

Se eu usar o --exclude-table=tableargumento, tanto o esquema quanto os dados da tabela enorme serão omitidos, o que interrompe as consultas que esperam essas tabelas no ambiente de desenvolvimento local.

Existe uma maneira de despejar um banco de dados com um esquema completo, excluindo o conteúdo de algumas tabelas fornecidas?

Tenho brincado com arrays em um dos meus bancos de dados PostgreSQL.

Eu criei uma tabela com uma matriz de geometria com pelo menos um elemento:

CREATE TABLE test_arrays (
    polygons geometry(Polygon,4326)[],
    CONSTRAINT non_empty_polygons_chk 
        CHECK ((COALESCE(array_length(polygons, 1), 0) <> 0))
);

Eu adicionei algumas linhas e consultei a tabela para o primeiro elemento em cada matriz de geometria:

SELECT polygons[0] FROM test_arrays;

Para minha surpresa, recebi uma lista de linhas vazias!

Após algumas pesquisas, descobriu-se que os arrays do PostgreSQL são baseados em um :

Os números subscritos da matriz são escritos entre colchetes. Por padrão, o PostgreSQL usa uma convenção de numeração baseada em um para arrays, isto é, um array de n elementos começa com array[1] e termina com array[n].

Então SELECT polygons[0] FROM test_arrays;trabalhei e devolvi o primeiro polygonde cada carreira.

Se o PostgreSQL usa uma convenção de numeração baseada em um, por que a consulta do elemento 0 é permitida e o resultado tem algum significado?

Estou usando o PostgreSQL 9.3 no RDS. De vez em quando, executo uma operação VACUUM FULL no banco de dados. No entanto, essa operação pode demorar bastante e bloquear outras tabelas, portanto, pode surgir a necessidade de interromper a operação.

É seguro interromper uma VACUUM FULLoperação que demora muito?

Eu tenho uma configuração clássica de 3 ambientes (desenvolvimento, preparação e produção), cada um com seu próprio RDS PostgreSQL 9.3 . As versões cliente/servidor são psql (9.3.5, server 9.3.3).

Para manter os esquemas alinhados, tenho um trabalho periódico de comparação de esquemas, que me notifica quando os esquemas diferem.

Alguns dias atrás, comecei a ter muitas dessas diferenças:

CREATE TABLE ...
...
<     title pg_catalog.text,
<     subtitle pg_catalog.text,
---
>     title text,
>     subtitle text,

Os textcampos em staging foram de alguma forma convertidos pg_catalog.textem prod.

Qual é a diferença entre texte pg_catalog.text? Como posso igualar os dois esquemas?

Considere o seguinte cenário:

CREATE DOMAIN dom_zipcode AS text;
ALTER DOMAIN dom_zipcode
  ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);

Agora, se eu quiser eliminar essa restrição com ALTER DOMAIN, o manual diz :

ALTER DOMAIN name
    DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

Mas como podemos encontrar constraint_name? \dDmostra apenas a definição da restrição ( CHECKinstrução).

\dD dom_zipcode ;
                             List of domains
 Schema |    Name     | Type | Modifier |             Check
--------+-------------+------+----------+--------------------------------
 public | dom_zipcode | text |          | CHECK (char_length(VALUE) = 5)
(1 row)

Posso despejar o esquema usando pg_dump, mas acredito que deve existir uma maneira mais elegante de estabelecer isso usando o psqlterminal.

Gostaria de adicionar uma restrição exclusiva a um campo de banco de dados em um dos meus bancos de dados de produção. Infelizmente, a seguinte declaração está bloqueando:

ALTER TABLE package ADD CONSTRAINT unique_package_id UNIQUE (package_id);

Lendo a parte relevante do manual do PostgreSQL :

Observação: adicionar uma restrição usando um índice existente pode ser útil em situações em que uma nova restrição precisa ser adicionada sem bloquear as atualizações da tabela por muito tempo. Para fazer isso, crie o índice usando CREATE INDEX CONCURRENTLY e instale-o como uma restrição oficial usando esta sintaxe. Veja o exemplo abaixo.

Eu tentei:

CREATE UNIQUE INDEX CONCURRENTLY package_tmp_id_idx ON answer (package_id);

Seguido por:

prod_db=> ALTER TABLE package ADD CONSTRAINT 
          unique_package_id UNIQUE (package) USING INDEX package_tmp_id_idx;

Que falhou com:

ERROR:  syntax error at or near "package_tmp_id_idx"
LINE 1: ...T unique_package_id UNIQUE (answer_id) USING INDEX package_tmp_id_idx...
                                                             ^

Tentei brincar com a sintaxe, mas não consegui fazê-la funcionar.

Qual é a sintaxe correta para criar uma restrição UNIQUE usando um índice UNIQUE existente?

Seguindo o conselho dado em outra resposta , tentei usar a digest()função no PostgreSQL 9.3 no AWS RDS:

devdb=> SELECT digest('blah', 'sha1');
ERROR:  function digest(unknown, unknown) does not exist
LINE 1: SELECT digest('blah', 'sha1');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Mesmo que o pgcrypto exista:

devdb=> SHOW rds.extensions;

btree_gin,btree_gist,chkpass,citext,cubo,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_trgm,plperl,plpgsql,pltcl,postgis,postgis_tiger_geocoder,postgis_topology,sslinfo, tablefunc,tsearch2,unaccent,uuid-ossp

Como posso habilitar a digest()função?

Atualizações

Eu co-perguntei no fórum AWS RDS , as postagens serão atualizadas mutuamente.

digest()não parece existir no banco de dados:

devdb=> \df digest
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

Considere um .sqlarquivo com algumas seleções e atualizações. Eu gostaria de executar este arquivo a partir de um script usando o psqlutilitário:

psql -h whatever.rds.amazonaws.com -U user dbname -f commands.sql

O problema é que recebo uma tela interativa com os resultados da consulta e um (0 rows)(END), que requer pressionar escpara voltar ao shell normal.

Eu tentei usar --echo-alle/ou --single-transactionseguir o manual do psql , sem sorte.

Como executo meu sqlarquivo no modo não interativo, exibindo apenas os resultados e saindo do script?

Como posso indexar to_char()uma coluna?

Eu tentei:

adam_db=> CREATE INDEX updates_hourly_idx 
          ON updates (to_char(update_time, 'YYYY-MM-DD HH24:00'));

Mas deu o erro:

ERRO: as funções na expressão de índice devem ser marcadasIMMUTABLE

O que parece estranho, já que o to_char()carimbo de data/hora é razoavelmente imutável.

Alguma ideia de como gerar esse índice?

Acabei de encontrar a seguinte função em dois DBs RDS PostgreSQL 9.3:

--
-- Name: exec(text); Type: FUNCTION; Schema: public; Owner: adam
--

CREATE FUNCTION exec(text) RETURNS text
    LANGUAGE plpgsql
    AS $_$ BEGIN EXECUTE $1; RETURN $1; END; $_$;


ALTER FUNCTION public.exec(text) OWNER TO adam;

Tenho certeza de que não escrevi esta função e, como execparece um risco à segurança (ela simplesmente executa comandos?) Gostaria de saber do que se trata. Pesquisar a assinatura do método não me ajudou.

O que essa função faz?
É algum tipo de risco de segurança?
Qual programa/estrutura pode tê-lo criado?

Estou tentando despejar o esquema de um dos meus bancos de dados PostgreSQL 9.3+PostGIS:

/usr/lib/postgresql/9.3/bin/pg_dump --schema-only 
                                    -h stg-db.....rds.amazonaws.com 
                                    -U user_name db_name > stg_db_schema.sql

O comando funciona bem no meu dev db, mas falha em stg e prod devido a:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation direction_lookup
pg_dump: [archiver (db)] query was: LOCK TABLE tiger.direction_lookup IN ACCESS SHARE MODE

O problema parece ser permissões para arquivos tiger.direction_lookup. Pesquisei um pouco no Google e esta tabela parece estar relacionada ao PostGIS.

Eu tentei --exclude-table-data=tiger.direction_lookup, sem efeito (não deveria funcionar para esquemas de qualquer maneira).

Alguma idéia de como poder despejar o esquema do meu banco de dados?

Meu problema

Considere uma tabela tcom muitas atualizações frequentes de usuários, das quais apenas as últimas são relevantes.

Para manter o tamanho da tabela razoável, sempre que uma nova linha é inserida, as linhas antigas da mesma user_idsão excluídas. Para manter um arquivo, a linha também é gravada em t_history.

Ambos te t_historytêm o mesmo esquema, no qual idé a bigserialcom uma restrição de chave primária.

Implementação

Procedimento armazenado

CREATE FUNCTION update_t_history()
RETURNS trigger
AS
$$
declare
BEGIN
    -- Insert the row to the t_history table. `id` is autoincremented
    INSERT INTO t_history (a, b, c, ...)
    VALUES (NEW.a, NEW.b, NEW.c, ...);

    -- Delete old rows from the t table, keep the newest 10 
    DELETE FROM t WHERE id IN (
                  SELECT id FROM t 
                  WHERE user_id = NEW.user_id 
                  ORDER BY id DESC
                  OFFSET 9);
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Gatilho de inserção correspondente:

CREATE TRIGGER t_insertion_trigger
AFTER INSERT ON t
FOR EACH ROW
EXECUTE PROCEDURE update_t_history();

O erro

O gatilho funciona bem, mas quando executo algumas dezenas de inserções em uma única transação, recebo o seguinte erro:

BEGIN
ERROR:  duplicate key value violates unique constraint "t_history_pkey"
DETAIL:  Key (id)=(196) already exists.

Atualizações

• O idcampo em ambas as tabelas (de \d+ t):
  • id|bigint|not null default nextval('t_id_seq'::regclass)
  • "t_pkey" PRIMARY KEY, btree (id)
• A versão do PostgreSQL é 9.3.

Alguma ideia de por que o procedimento armazenado quebra a restrição de chave primária nas transações?

De acordo com o manual do PostGIS , as colunas geométricas não devem ser adicionadas diretamente, mas usando AddGeometryColumn() :

AddGeometryColumn — Adiciona uma coluna de geometria a uma tabela de atributos existente.

Estou copiando um esquema de uma instância do PostgreSQL para outra. Eu me pergunto se o SQL criado por pg_dumpé bom o suficiente - ele criará as colunas geométricas da mesma forma que AddGeometryColumn () faz?

Quando me conecto a um banco de dados Postgresql usando psql, geralmente recebo estas mensagens:

=> SELECT * FROM question_view ;
ERROR:  character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in encoding "LATIN1"

Seguindo esta resposta SO , entendo que devo alterar a codificação do cliente de acordo:

SET client_encoding = 'UTF8';

Alterar o client_encodingtoda vez que me conecto ao banco de dados é complicado. Existe uma maneira de configurar permanentemente essa configuração, seja no .pgpassarquivo ou em qualquer outro lugar?

Qual seria o tipo de dados correto para armazenar endereços de e-mail no PostgreSQL?

Posso usar varchar(ou até text), mas gostaria de saber se existe um tipo de dado mais específico para emails.

Gostaria de executar algumas instruções e confirmar o lote inteiro no final. Eu tentei a seguinte abordagem:

connection.setAutoCommit(false);            // Don't commit for each statement
int[] returnCodes = pstmt.executeBatch();   // Execute all statements
connection.setAutoCommit(true);             // Back to normal state - future statements
                                            // will be committed instantly
connection.commit();                        // Commit our batch

Que falha miseravelmente:

java.sql.SQLException: Can't call commit when autocommit=true
--reference to the line with connection.commit()--

Qual é o caminho certo a seguir? faz connection.setAutoCommit(true)commit de todas as instruções em lote executadas?

Depois de me recuperar de um problema de disco cheio, comecei a receber estes erros em meus logs:

java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1684)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)
    at com.dappit.Dapper.adminer.pixelMaster.storage.RemotePixelStorage.flushBufferToDb(RemotePixelStorage.java:120)
    ...

O código Java ( RemotePixelStorage.java:120):

(119) connection.setAutoCommit(false);
(120) int[] returnCodes = pstmt.executeBatch();
(121) connection.setAutoCommit(true);
(122) connection.commit();

Cada instrução é uma chamada para o procedimento armazenadoadd_pixel , que adiciona um elemento a uma tabela e remove os elementos mais antigos, se necessário.

O que essa exceção jdbc significa? Como posso superar esse problema? Devo simplesmente tentar confirmar algumas vezes com um bloco catch?

Eu SHOW processlistconsultei como root após uma reinicialização do servidor e obtive uma longa lista de Sleepthreads:

+-----+-------------+-----------------+---------+---------+------+-------+------------------+
| Id  | User        | Host            | db      | Command | Time | State | Info             |
+-----+-------------+-----------------+---------+---------+------+-------+------------------+
| 217 | root        | localhost       | masters | Sleep   |  371 |       | NULL             |
| 251 | mastersuser | localhost:50883 | masters | Sleep   |  297 |       | NULL             |
| 252 | mastersuser | localhost:50884 | masters | Sleep   |  297 |       | NULL             |
| 253 | mastersuser | localhost:50885 | masters | Sleep   |  297 |       | NULL             |
| 254 | mastersuser | localhost:50891 | masters | Sleep   |  296 |       | NULL             |
| 261 | mastersuser | localhost:50897 | masters | Sleep   |  281 |       | NULL             |
| 260 | mastersuser | localhost:50896 | masters | Sleep   |  281 |       | NULL             |
| 262 | mastersuser | localhost:50899 | masters | Sleep   |  281 |       | NULL             |
| 263 | mastersuser | localhost:50900 | masters | Sleep   |  280 |       | NULL             |
| 272 | mastersuser | localhost:50908 | masters | Sleep   |  263 |       | NULL             |
| 273 | mastersuser | localhost:50909 | masters | Sleep   |  263 |       | NULL             |
| 274 | mastersuser | localhost:50911 | masters | Sleep   |  263 |       | NULL             |
| 275 | mastersuser | localhost:50912 | masters | Sleep   |  263 |       | NULL             |
| 287 | mastersuser | localhost:52434 | masters | Sleep   |  242 |       | NULL             |
| 288 | mastersuser | localhost:52435 | masters | Sleep   |  242 |       | NULL             |
| 289 | mastersuser | localhost:52436 | masters | Sleep   |  242 |       | NULL             |
| 290 | mastersuser | localhost:52437 | masters | Sleep   |  242 |       | NULL             |
| 296 | mastersuser | localhost:52445 | masters | Sleep   |  233 |       | NULL             |
| 298 | mastersuser | localhost:52446 | masters | Sleep   |  232 |       | NULL             |
| 299 | mastersuser | localhost:52447 | masters | Sleep   |  232 |       | NULL             |
| 300 | mastersuser | localhost:52448 | masters | Sleep   |  232 |       | NULL             |
| 301 | mastersuser | localhost:52449 | masters | Sleep   |  231 |       | NULL             |
| 308 | mastersuser | localhost:52453 | masters | Sleep   |  219 |       | NULL             |
| 315 | mastersuser | localhost:52457 | masters | Sleep   |  210 |       | NULL             |
| 316 | mastersuser | localhost:52458 | masters | Sleep   |  210 |       | NULL             |
| 317 | mastersuser | localhost:52459 | masters | Sleep   |  210 |       | NULL             |
| 319 | mastersuser | localhost:52460 | masters | Sleep   |  210 |       | NULL             |
| 330 | mastersuser | localhost:52468 | masters | Sleep   |  196 |       | NULL             |
| 331 | mastersuser | localhost:52469 | masters | Sleep   |  196 |       | NULL             |
...
| 436 | mastersuser | localhost:52547 | masters | Sleep   |   47 |       | NULL             |
| 468 | root        | localhost       | masters | Query   |    0 | NULL  | SHOW processlist |
+-----+-------------+-----------------+---------+---------+------+-------+------------------+

Depois de um tempo, os processos de sono desaparecem.

Devo considerar isso como um problema?

SELECTing os n principais elementos de uma tabela é fácil:

SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000;

Encontrar o n-ésimo elemento com uma subconsulta é bastante simples:

SELECT MIN(id) FROM 
    (SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000) s;

Supondo que exista um BTREEíndice nas colunas relevantes ( id), essa abordagem é eficiente? Existem alternativas melhores?