问题

我已经PostgreSQL 9.3在我的 MacBook Pro 上安装了。我不记得我是怎么安装的（有很多方法），现在我想升级到PostgreSQL 9.5.

法医学

正在运行的PostgreSQL进程是：

  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

这意味着二进制文件位于：

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

和数据在

/Library/PostgreSQL/9.3/data

我试过什么

我确定它不是使用安装的Homebrew。

知道PostgreSQL我的 Mac 上安装了哪个安装，以及如何升级它？

考虑一个有几十个正常大小的表和几个大表的生产数据库。

我正在pg_dump为我自己的本地笔记本电脑寻找一种方便的方式来获取数据库内容，不包括几个大表。\COPY如果我偶尔需要处理这些表，我可以使用 轻松转储大表的一小部分样本。

如果我使用这个--exclude-table=table参数，那么大表的模式和数据都会被省略，这会破坏在本地开发环境中期望这些表的查询。

有没有办法转储具有完整模式的数据库，不包括某些给定表的内容？

我一直在我的一个 PostgreSQL 数据库中使用数组。

我创建了一个包含至少一个元素的几何数组的表：

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

我添加了几行，并在表中查询每个几何数组中的第一个元素：

SELECT polygons[0] FROM test_arrays;

令我惊讶的是，我得到了一个空行列表！

经过一番研究，事实证明PostgreSQL 数组是基于 1 的：

数组下标数字写在方括号内。默认情况下，PostgreSQL 对数组使用从 1 开始的编号约定，即一个包含 n 个元素的数组以 array[1] 开头并以 array[n] 结尾。

所以SELECT polygons[0] FROM test_arrays;工作并返回polygon每一行的第一行。

如果 PostgreSQL 使用从 1 开始的编号约定，为什么允许查询第 0 个元素，结果是否有任何意义？

我在 RDS 上使用 PostgreSQL 9.3。有时，我会在数据库上运行VACUUM FULL操作。但是，这样的操作可能需要相当长的时间，并且会阻塞其他表，因此可能需要停止操作。

停止VACUUM FULL耗时过长的手术是否安全？

我有一个经典的 3 环境设置（开发、登台和生产），每个环境都有自己的RDS PostgreSQL 9.3。客户端/服务器版本是psql (9.3.5, server 9.3.3).

为了保持模式对齐，我有一个定期模式差异作业，当模式不同时它会通知我。

几天前，我开始得到许多这样的差异：

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

staging 中的text字段以某种方式转换为pg_catalog.textprod。

text和 和有什么不一样pg_catalog.text？我怎样才能将这两种模式等同起来？

考虑以下场景：

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

现在，如果我想用 放弃该约束ALTER DOMAIN，手册说：

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

但是我们怎么能找到constraint_name呢？\dD仅显示约束的定义（CHECK语句）。

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

我可以使用 转储模式pg_dump，但我相信必须存在一种更优雅的方式来使用psql终端建立它。

我想在我的一个生产数据库中的数据库字段中添加一个唯一约束。不幸的是，以下语句被阻止：

ALTER TABLE package ADD CONSTRAINT unique_package_id UNIQUE (package_id);

从 PostgreSQL 手册中阅读相关部分：

注意：在需要添加新约束而又不长时间阻塞表更新的情况下，使用现有索引添加约束可能会很有帮助。为此，请使用 CREATE INDEX CONCURRENTLY 创建索引，然后使用此语法将其安装为官方约束。请参见下面的示例。

我试过了：

CREATE UNIQUE INDEX CONCURRENTLY package_tmp_id_idx ON answer (package_id);

其次是：

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

失败了：

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

我尝试过使用语法，但无法使其正常工作。

使用现有 UNIQUE 索引创建 UNIQUE 约束的正确语法是什么？

按照另一个答案给出的建议，我尝试digest()在 AWS RDS 上使用 PostgreSQL 9.3 上的函数：

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.

即使 pgcrypto 存在：

devdb=> SHOW rds.extensions;

btree_gin,btree_gist,chkpass,citext,cube,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

如何启用该digest()功能？

更新

我已经在 AWS RDS 论坛上共同提问，将相互更新帖子。

digest()db上似乎不存在：

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

考虑一个.sql包含一些选择和更新的文件。我想使用该psql实用程序从脚本运行此文件：

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

问题是我得到了一个带有查询结果的交互式屏幕和一个(0 rows)(END)，这需要按下esc才能返回到正常的 shell。

我尝试使用--echo-all和/或--single-transaction遵循psql 手册，但没有成功。

如何sql在非交互模式下运行我的文件，只显示结果并退出脚本？

我怎样才能索引to_char()一列？

我努力了：

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

但是得到了错误：

错误：必须标记索引表达式中的函数IMMUTABLE

这看起来很奇怪，因为to_char()时间戳是相当不可变的。

任何想法如何生成该索引？

我刚刚在两个 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;

我很确定我没有写这个函数，因为exec它看起来像一个安全隐患（它只是执行命令吗？）它想知道它的全部内容。搜索方法签名对我没有帮助。

这个函数有什么作用？
这是某种安全风险吗？
哪个程序/框架可能创建了它？

我正在尝试从我的 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

该命令在我的开发数据库上运行良好，但在 stg 和 prod 上失败，原因是：

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

问题似乎是tiger.direction_lookup. 我用谷歌搜索了一下，这张表似乎与 PostGIS 有关。

我试过--exclude-table-data=tiger.direction_lookup了，但没有效果（无论如何它不应该适用于模式）。

知道如何能够转储我的数据库的架构吗？

我的问题

考虑一个t包含许多来自用户的频繁更新的表，其中只有最后几个是相关的。

为了保持表的大小合理，每当插入新行时，旧行都会user_id被删除。为了保留存档，该行也被写入t_history.

t和都有t_history相同的模式，其中id有bigserial一个主键约束。

执行

存储过程

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;

对应的插入触发器：

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

错误

触发器运行良好，但是当我在单个事务中运行几十个插入时，出现以下错误：

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

更新

• 两个表中的id字段（来自\d+ t）：
  • id|bigint|not null default nextval('t_id_seq'::regclass)
  • "t_pkey" PRIMARY KEY, btree (id)
• PostgreSQL 版本为 9.3。

知道为什么存储过程会破坏事务中的主键约束吗？

根据PostGIS 手册，不应直接添加几何列，而应使用AddGeometryColumn()：

AddGeometryColumn — 将几何列添加到现有的属性表中。

我正在将模式从一个 PostgreSQL 实例复制到另一个实例。我想知道由 创建的 SQLpg_dump是否足够好 - 它会像AddGeometryColumn()一样创建几何列吗？

当我使用 连接到 Postgresql 数据库psql时，我经常收到以下消息：

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

按照这个SO answer，我知道我应该相应地更改客户端编码：

SET client_encoding = 'UTF8';

client_encoding每次连接到数据库时更改都很麻烦。有没有办法在.pgpass文件或其他任何地方永久配置此设置？

在 PostgreSQL 中存储电子邮件地址的正确数据类型是什么？

我可以使用varchar（甚至text），但我想知道是否有更具体的电子邮件数据类型。

我想执行一些语句，并在最后提交整个批次。我尝试了以下方法：

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

失败得很惨：

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

正确的方法是什么？是否connection.setAutoCommit(true)提交所有已执行的批处理语句？

从磁盘已满问题中恢复后，我的日志中开始出现以下错误：

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

Java 代码（RemotePixelStorage.java:120）：

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

每个语句都是对存储过程add_pixel的调用，它将向表中添加一个元素并在必要时删除旧元素。

这个jdbc异常是什么意思？我该如何克服这个问题？我是否应该尝试使用 catch 块提交几次？

服务器重新启动后，我SHOW processlist以 root 身份查询，并得到一长串Sleep线程：

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

过了一会儿，睡眠过程消失了。

我应该把这当作一个问题吗？

SELECT从表中获取前 n 个元素很容易：

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

使用子查询查找第 n 个元素非常简单：

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

假设BTREE相关列上有一个索引 ( id)，这种方法是否有效？有没有更好的选择？