windows 是否可以使用 Postgresql 的独立实例

Are Independent instances of Postgresql possible

提问by jjb

I want to install postgresql for use as the backend to a Windows application.

我想安装 postgresql 以用作 Windows 应用程序的后端。

This seems to be no problem if postgresql is not already installed on the system.

如果系统上尚未安装 postgresql，这似乎没有问题。

If postgresql is already installed then unless the command line parameters contain the superpassword etc of the existing installation then the install fails. As I will likely never know the superpassword or other account details of any pre-existing postgresql instances and the machine owners may not either it seems that this will frustrate any attempt to install postgresql in such a situation.

如果已经安装了 postgresql，那么除非命令行参数包含现有安装的超级密码等，否则安装将失败。因为我可能永远不会知道任何预先存在的 postgresql 实例的超级密码或其他帐户详细信息，并且机器所有者可能也不知道这似乎会挫败在这种情况下安装 postgresql 的任何尝试。

I believe it is possible to install completely independent instances of sql server but is this possible for postgresql?

我相信可以安装完全独立的 sql server 实例，但这对于 postgresql 是否可行？

BTW:If the command line does contain the correct superpassword then the install just seems to overwrite the existing install and ignores parameters like --prefix etc . I used init db to create a new database cluster before doing a second install but this new cluster was ignored?

顺便说一句：如果命令行确实包含正确的超​​级密码，那么安装似乎只是覆盖了现有的安装并忽略了 --prefix 等参数。在进行第二次安装之前，我使用 init db 创建了一个新的数据库集群，但是这个新集群被忽略了？

回答by Grzegorz Szpetkowski

In general you can have multiple independentinstances of PostgreSQL. Strictly speaking it's database cluster with separate:

通常，您可以拥有多个独立的 PostgreSQL 实例。严格来说，它是具有单独的数据库集群：

• data directory
• configuration (e.g. postgresql.conf, pg_hba.conf)
• listening TCP/UDP port (default 5432+)
• owner user and superuser role
• locale and default encoding
• log file
• postmaster server process (on Windows postgres.exe)

• 数据目录
• 配置（例如postgresql.conf，pg_hba.conf）
• 监听 TCP/UDP 端口（默认 5432+）
• 所有者用户和超级用户角色
• 语言环境和默认编码
• 日志文件
• postmaster 服务器进程（在 Windows postgres.exe 上）

Perfect well-done example is Debian with easy to use postgresql-commoninfrastructure (pg_ctlcluster, pg_lsclusters, pg_createcluster, pg_dropcluster, included SSL, log rotation and so on).

完美的做得好的例子是 Debian，它具有易于使用的postgresql-common基础设施（pg_ctlcluster、pg_lsclusters、pg_createcluster、pg_dropcluster，包括 SSL、日志轮换等）。

EDIT:

编辑：

I found it's rather easy to install second, third, etc. instance of same versioned PostgreSQL under Windows with EnterpriseDB's installer, no needto use initdband pg_ctl(assuming 64-bit installation, probably you need to use Program Files (x86)for 32-bit installation):

我发现使用EnterpriseDB 的安装程序在 Windows 下安装相同版本的 PostgreSQL 的第二、第三等实例相当容易，无需使用initdb和pg_ctl（假设 64 位安装，可能需要使用Program Files (x86)32 位安装）：

1. Open cmdwith admin privileges (Run as Administrator)
2. Execute: cd "C:\Program Files\PostgreSQL\9.0\installer\server"
3. Create new database cluster (press Enter on every step): initcluster.vbs postgres postgres 12345 "C:\Program Files\PostgreSQL\9.0" "C:\Program Files\PostgreSQL\9.0\data2" 5433 DEFAULT
4. Register as Windows Service: startupcfg.vbs 9.0 postgres 12345 "C:\Program Files\PostgreSQL\9.0" "C:\Program Files\PostgreSQL\9.0\data2" postgresql-x64-9.0-2
5. Run newly created service postgresql-x64-9.0-2using services.mscand you have second server

1. cmd以管理员权限打开（以管理员身份运行）
2. 执行： cd "C:\Program Files\PostgreSQL\9.0\installer\server"
3. 创建新的数据库集群（每一步都按 Enter）： initcluster.vbs postgres postgres 12345 "C:\Program Files\PostgreSQL\9.0" "C:\Program Files\PostgreSQL\9.0\data2" 5433 DEFAULT
4. 注册为 Windows 服务： startupcfg.vbs 9.0 postgres 12345 "C:\Program Files\PostgreSQL\9.0" "C:\Program Files\PostgreSQL\9.0\data2" postgresql-x64-9.0-2
5. 运行新创建的服务postgresql-x64-9.0-2使用services.msc，你有第二个服务器

Change 12345to your password specified during PostgreSQL installation. You don't have to use data2directory, use whatever you like (but of course not existing datadirectory).

更改12345为您在 PostgreSQL 安装期间指定的密码。您不必使用data2目录，使用您喜欢的任何内容（但当然不是现有data目录）。

回答by Matthew Plourde

On Windows 7 I had success following these steps. You'll need the PsExec.exe utility available in the Sysinternals Suite. I assume here that the path to the Sysinternals Suite and the path to the bin folder of your existing PostgreSQL installation are in your PATH environment variable.

在 Windows 7 上，我按照这些步骤成功了。您将需要Sysinternals Suite 中提供的 PsExec.exe 实用程序。我在这里假设 Sysinternals Suite 的路径和现有 PostgreSQL 安装的 bin 文件夹的路径在您的 PATH 环境变量中。

1. Open a cmd.exe window and enter the following command to open a prompt as the Network Service account.

   psexec -i -u "nt authority\network service" cmd.exe

2. The Network Service account won't have access to your PATH, so cd 'C:\PostgreSQL\9.3\bin'and then enter the following command to initialize a data directory for your new instance. I've called mine "data2". It doesn't have to be in the postgres directory, but that's where the default data directory goes, so it's a reasonable choice.

   initdb "C:\PostgreSQL\9.3\data2"

3. Edit C:\PostgreSQL\9.3\data2\postgresql.conf so that port = 5433(the default instance uses 5432, and you shouldn't have two instances on the same port)

4. Leave the Network Service cmd prompt and in your standard prompt enter the following command to register the new service. Here I've named my new instance "pg_test"

   pg_ctl register -N pg_test -U "nt authority\network service" -D "C:\PostgreSQL\9.3\data2"

5. Run the following command to start the service.

   net start pg_test

6. The database owner role will be 'YOURMACHINENAME$'. If you want to change this to the standard 'postgres', you have to first create a new super user role that can rename the owner. From the command prompt, enter the following to create this super user.

   createuser -s -r -l -i -P -h localhost -p 5433 -U YOURMACHINENAME$ mysuperuser

7. Finally, connect to the server with psql (psql -U mysuperuser -h localhost -p 5433 postgres) and enter the following commands to rename your database owner and add a password.

   ALTER USER "YOURMACHINENAME$" RENAME TO postgres;

   ALTER USER postgres WITH PASSWORD 'yourpassword';

1. 打开 cmd.exe 窗口并输入以下命令以打开网络服务帐户的提示。

   psexec -i -u "nt authority\network service" cmd.exe

2. 网络服务帐户将无法访问您的 PATH，因此cd 'C:\PostgreSQL\9.3\bin'然后输入以下命令为您的新实例初始化数据目录。我称我的为“data2”。它不必在 postgres 目录中，但这是默认数据目录所在的位置，因此它是一个合理的选择。

   initdb "C:\PostgreSQL\9.3\data2"

3. 编辑 C:\PostgreSQL\9.3\data2\postgresql.conf 以便port = 5433（默认实例使用 5432，并且您不应该在同一端口上有两个实例）

4. 离开网络服务 cmd 提示符并在您的标准提示符中输入以下命令来注册新服务。在这里，我将我的新实例命名为“pg_test”

   pg_ctl register -N pg_test -U "nt authority\network service" -D "C:\PostgreSQL\9.3\data2"

5. 运行以下命令启动服务。

   net start pg_test

6. 数据库所有者角色将是“YOURMACHINENAME$”。如果要将其更改为标准的“postgres”，则必须首先创建一个可以重命名所有者的新超级用户角色。在命令提示符下，输入以下内容以创建此超级用户。

   createuser -s -r -l -i -P -h localhost -p 5433 -U YOURMACHINENAME$ mysuperuser

7. 最后，使用 psql ( psql -U mysuperuser -h localhost -p 5433 postgres)连接到服务器并输入以下命令以重命名您的数据库所有者并添加密码。

   ALTER USER "YOURMACHINENAME$" RENAME TO postgres;

   ALTER USER postgres WITH PASSWORD 'yourpassword';

回答by Milen A. Radev

Something like this should work (if not it's probably bug):

这样的事情应该可以工作（如果不是，那可能是错误）：

postgresql-9.0.4-1-windows_x64.exe ^
  --mode unattended ^
  --prefix c:\postgres.0-second ^
  --servicename postgresql-x64-9.0-second ^
  --serviceaccount postgres2 ^
  --servicepassword <password> ^
  --serverport 5433 ^
  --superaccount postgres ^
  --superpassword <password>

EDIT: after a couple of tests I believe it's not possible to create different Postgres instances of the same version using the One-click installer. Sorry.

编辑：经过几次测试后，我相信不可能使用一键安装程序创建相同版本的不同 Postgres 实例。对不起。

OTOH you could always play with initdband pg_ctland use the existing installation to create a new instance. It would not be as easy as just starting the installer but it's doable.

OTOH，您可以随时使用initdb并pg_ctl使用现有安装来创建新实例。这不像只是启动安装程序那么容易，但它是可行的。