每次我运行rails 4.0服务器时，都会得到这样的输出。

Started GET "/" for 127.0.0.1 at 2013-11-06 23:56:36 -0500

PG::ConnectionBad - could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (fe80::1) and accepting
TCP/IP connections on port 5432?
:
 activerecord (4.0.0) lib/active_record/connection_adapters/postgresql_adapter.rb:825:in `connect'
 activerecord (4.0.0) lib/active_record/connection_adapters/postgresql_adapter.rb:542:in `initialize'
 activerecord (4.0.0) lib/active_record/connection_adapters/postgresql_adapter.rb:41:in `postgresql_connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:440:in `new_connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:450:in `checkout_new_connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:421:in `acquire_connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:356:in `block in checkout'
 /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/monitor.rb:211:in `mon_synchronize'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:355:in `checkout'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:265:in `block in connection'
 /System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/lib/ruby/2.0.0/monitor.rb:211:in `mon_synchronize'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:264:in `connection'
 activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:546:in `retrieve_connection'
 activerecord (4.0.0) lib/active_record/connection_handling.rb:79:in `retrieve_connection'
 activerecord (4.0.0) lib/active_record/connection_handling.rb:53:in `connection'
 activerecord (4.0.0) lib/active_record/migration.rb:792:in `current_version'
 activerecord (4.0.0) lib/active_record/migration.rb:800:in `needs_migration?'
 activerecord (4.0.0) lib/active_record/migration.rb:379:in `check_pending!'
 activerecord (4.0.0) lib/active_record/migration.rb:366:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
 activesupport (4.0.0) lib/active_support/callbacks.rb:373:in `_run__1613334440513032208__call__callbacks'
 activesupport (4.0.0) lib/active_support/callbacks.rb:80:in `run_callbacks'
 actionpack (4.0.0) lib/action_dispatch/middleware/callbacks.rb:27:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/reloader.rb:64:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/remote_ip.rb:76:in `call'
 better_errors (0.9.0) lib/better_errors/middleware.rb:84:in `protected_app_call'
 better_errors (0.9.0) lib/better_errors/middleware.rb:79:in `better_errors_call'
 better_errors (0.9.0) lib/better_errors/middleware.rb:56:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
 railties (4.0.0) lib/rails/rack/logger.rb:38:in `call_app'
 railties (4.0.0) lib/rails/rack/logger.rb:21:in `block in call'
 activesupport (4.0.0) lib/active_support/tagged_logging.rb:67:in `block in tagged'
 activesupport (4.0.0) lib/active_support/tagged_logging.rb:25:in `tagged'
 activesupport (4.0.0) lib/active_support/tagged_logging.rb:67:in `tagged'
 railties (4.0.0) lib/rails/rack/logger.rb:21:in `call'
 quiet_assets (1.0.2) lib/quiet_assets.rb:18:in `call_with_quiet_assets'
 actionpack (4.0.0) lib/action_dispatch/middleware/request_id.rb:21:in `call'
 rack (1.5.2) lib/rack/methodoverride.rb:21:in `call'
 rack (1.5.2) lib/rack/runtime.rb:17:in `call'
 activesupport (4.0.0) lib/active_support/cache/strategy/local_cache.rb:83:in `call'
 rack (1.5.2) lib/rack/lock.rb:17:in `call'
 actionpack (4.0.0) lib/action_dispatch/middleware/static.rb:64:in `call'
 railties (4.0.0) lib/rails/engine.rb:511:in `call'
 railties (4.0.0) lib/rails/application.rb:97:in `call'
 rack (1.5.2) lib/rack/content_length.rb:14:in `call'
 thin (1.5.1) lib/thin/connection.rb:81:in `block in pre_process'
 thin (1.5.1) lib/thin/connection.rb:79:in `pre_process'
 thin (1.5.1) lib/thin/connection.rb:54:in `process'
 thin (1.5.1) lib/thin/connection.rb:39:in `receive_data'
 eventmachine (1.0.3) lib/eventmachine.rb:187:in `run'
 thin (1.5.1) lib/thin/backends/base.rb:63:in `start'
 thin (1.5.1) lib/thin/server.rb:159:in `start'
 rack (1.5.2) lib/rack/handler/thin.rb:16:in `run'
 rack (1.5.2) lib/rack/server.rb:264:in `start'
 railties (4.0.0) lib/rails/commands/server.rb:84:in `start'
 railties (4.0.0) lib/rails/commands.rb:78:in `block in <top (required)>'
 railties (4.0.0) lib/rails/commands.rb:73:in `<top (required)>'
 bin/rails:4:in `<main>'

我运行的是Mavericks OS X 10.9，所以我不知道这是否是问题所在。我已经尽力了，但似乎都没用。我已经卸载和安装postgres和pg gem多次了。

这是我的数据库。yml文件

development:
  adapter: postgresql
  encoding: unicode
  database: metals-directory_development
  pool: 5
  username: 
  password: 
  template: template0
  host: localhost
  port: 5432

test: &test
  adapter: postgresql
  encoding: unicode
  database: metals-directory_test
  pool: 5
  username: 
  password: 
  template: template0
  host: localhost
  port: 5432

staging:
  adapter: postgresql
  encoding: unicode
  database: metals-directory_production
  pool: 5
  username:
  password:
  template: template0
  host: localhost

production:
  adapter: postgresql
  encoding: unicode
  database: metals-directory_production
  pool: 5
  username:
  password:
  template: template0
  host: localhost

cucumber:
  <<: *test

我正在尝试备份/恢复PostgreSQL数据库，正如Docker网站上所解释的那样，但数据没有恢复。

数据库映像使用的卷是:

VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"]

CMD为:

CMD ["/usr/lib/postgresql/9.3/bin/postgres", "-D", "/var/lib/postgresql/9.3/main", "-c", "config_file=/etc/postgresql/9.3/main/postgresql.conf"]

我用下面的命令创建DB容器:

docker run -it --name "$DB_CONTAINER_NAME" -d "$DB_IMAGE_NAME"

然后我连接另一个容器手动插入一些数据:

docker run -it --rm --link "$DB_CONTAINER_NAME":db "$DB_IMAGE_NAME" sh -c 'exec bash'
psql -d test -h $DB_PORT_5432_TCP_ADDR
# insert some data in the db
<CTRL-D>
<CTRL-D>

然后创建tar存档:

$ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar cvf /backup/backup.tar /etc/postgresql /var/log/postgresql /var/lib/postgresql

现在我删除用于db的容器，并创建另一个同名的容器，并尝试恢复之前插入的数据:

$ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar xvf /backup/backup.tar 

但是表是空的，为什么数据没有正确恢复?

我有一个表，我想拉一行每个id与字段值连接。

例如，在我的表格中，我有这样的:

TM67 | 4  | 32556
TM67 | 9  | 98200
TM67 | 72 | 22300
TM99 | 2  | 23009
TM99 | 3  | 11200

我想输出:

TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3    | 23009,11200

在MySQL中，我能够使用聚合函数GROUP_CONCAT，但这似乎在这里不起作用…PostgreSQL有类似的功能吗，或者有其他方法可以实现这个功能?

我需要通过编程的方式将数千万条记录插入Postgres数据库。目前，我在一个查询中执行了数千条插入语句。

有没有更好的方法来做到这一点，一些我不知道的批量插入语句?

我一直试图通过创建自定义用户和数据库来为开发postgres实例设置一个容器。我使用的是官方postgres docker映像。在文档中，它指导你在/docker-entrypoint-initdb中插入一个bash脚本。D /文件夹，使用任何自定义参数设置数据库。

我的bash脚本:make_db.sh

su postgres -c "createuser -w -d -r -s docker"
su postgres -c "createdb -O docker docker"

Dockerfile

FROM library/postgres

RUN ["mkdir", "/docker-entrypoint-initdb.d"]
ADD make_db.sh /docker-entrypoint-initdb.d/

我从docker日志-f db (db是我的容器名)得到的错误是:

postgres:不能连接到服务器:没有这样的文件或目录

看起来/docker-entrypoint-initdb. conf文件中的命令。在postgres启动之前，正在执行D /文件夹。我的问题是，如何使用官方postgres容器以编程方式设置用户/数据库?有什么方法可以用脚本来做到这一点吗?

我想在PostgreSQL中设置一个表，这样两列在一起必须是唯一的。任何一个值都可以有多个值，只要没有两个值同时具有这两个值。

例如:

CREATE TABLE someTable (
    id int PRIMARY KEY AUTOINCREMENT,
    col1 int NOT NULL,
    col2 int NOT NULL
)

所以col1和col2可以重复，但不能同时重复。因此，这是允许的(不包括id)

1 1
1 2
2 1
2 2

但这不是:

1 1
1 2
1 1 -- would reject this insert for violating constraints

我收到了很多错误的信息:

"DatabaseError: current transaction is aborted, commands ignored until end of transaction block"

作为Django项目的数据库引擎，从python-psycopg改为python-psycopg2。

代码保持不变，只是不知道这些错误来自哪里。

每次建立一个新的SQL表或向现有表添加一个新的varchar列时，我都想知道一件事:长度的最佳值是多少。

假设，你有一个列名为name类型为varchar。所以，你必须选择长度。我想不出一个20个字符的名字，但你永远不会知道。但不是用20，我总是四舍五入到下一个2^n。在这种情况下，我将选择32作为长度。我这样做，是因为从计算机科学家的角度来看，2^n这个数字在我看来比其他数字更均匀，我只是假设下面的架构可以比其他数字更好地处理这些数字。

另一方面，以MSSQL服务器为例，当您选择创建varchar列时，将默认长度值设置为50。这让我开始思考。为什么50 ?它只是一个随机数，还是基于平均列长，还是什么?

也可能是——或者可能是——不同的SQL服务器实现(如MySQL, MSSQL, Postgres，…)有不同的最佳列长度值。

使用postgresql 9.3我可以选择JSON数据类型的特定字段，但如何使用UPDATE修改它们?我在postgresql文档或网上任何地方都找不到这样的例子。我尝试了一些显而易见的方法:

postgres=# create table test (data json);
CREATE TABLE
postgres=# insert into test (data) values ('{"a":1,"b":2}');
INSERT 0 1
postgres=# select data->'a' from test where data->>'b' = '2';
 ?column?
----------
 1
(1 row)
postgres=# update test set data->'a' = to_json(5) where data->>'b' = '2';
ERROR:  syntax error at or near "->"
LINE 1: update test set data->'a' = to_json(5) where data->>'b' = '2...

我有一个这样布局的表格:

CREATE TABLE Favorites (
  FavoriteId uuid NOT NULL PRIMARY KEY,
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  MenuId uuid
);

我想创建一个类似这样的唯一约束:

ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);

但是，如果MenuId为NULL，这将允许具有相同(UserId, RecipeId)的多行。我想在MenuId中允许NULL来存储没有关联菜单的收藏夹，但每个用户/食谱对最多只需要其中一行。

到目前为止我的想法是:

Use some hard-coded UUID (such as all zeros) instead of null. However, MenuId has a FK constraint on each user's menus, so I'd then have to create a special "null" menu for every user which is a hassle. Check for existence of a null entry using a trigger instead. I think this is a hassle and I like avoiding triggers wherever possible. Plus, I don't trust them to guarantee my data is never in a bad state. Just forget about it and check for the previous existence of a null entry in the middle-ware or in a insert function, and don't have this constraint.

我使用的是Postgres 9.0。有没有什么方法我忽略了?