如何在不丢失数据的情况下将 PostgreSQL 从 9.6 版升级到 10.1 版？

Question

提问by user3675188

I'm using the PostgreSQL database for my Ruby on Rails application (on Mac OS X 10.9).

我将 PostgreSQL 数据库用于我的 Ruby on Rails 应用程序（在 Mac OS X 10.9 上）。

Are there any detailed instructions on how to upgrade PostgreSQL database?

有没有关于如何升级PostgreSQL数据库的详细说明？

I'm afraid I will destroy the data in the database or mess it up.

恐怕我会破坏数据库中的数据或将其弄乱。

Answer 1

回答by Donovan

Assuming you've used home-brew to install and upgrade Postgres, you can perform the following steps.

假设您已经使用 home-brew 安装和升级 Postgres，您可以执行以下步骤。

Stop current Postgres server:
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Initialize a new 10.1 database:
initdb /usr/local/var/postgres10.1 -E utf8
run pg_upgrade(note: change bin version if you're upgrading from something other than below):
```
pg_upgrade -v \
    -d /usr/local/var/postgres \
    -D /usr/local/var/postgres10.1 \
    -b /usr/local/Cellar/postgresql/9.6.5/bin/ \
    -B /usr/local/Cellar/postgresql/10.1/bin/
```
-vto enable verbose internal logging
-dthe old database cluster configuration directory
-Dthe new database cluster configuration directory
-bthe old PostgreSQL executable directory
-Bthe new PostgreSQL executable directory

Move new data into place:

cd /usr/local/var
mv postgres postgres9.6
mv postgres10.1 postgres

Restart Postgres:
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Check /usr/local/var/postgres/server.logfor details and to make sure the new server started properly.
Finally, re-install the rails pggem
```
gem uninstall pg
gem install pg
```

停止当前的 Postgres 服务器：
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
初始化一个新的 10.1 数据库：
initdb /usr/local/var/postgres10.1 -E utf8
运行pg_upgrade（注意：如果您从下面的其他版本升级，请更改 bin 版本）：
```
pg_upgrade -v \
    -d /usr/local/var/postgres \
    -D /usr/local/var/postgres10.1 \
    -b /usr/local/Cellar/postgresql/9.6.5/bin/ \
    -B /usr/local/Cellar/postgresql/10.1/bin/
```
-v启用详细的内部日志记录
-d旧的数据库集群配置目录
-D新的数据库集群配置目录
-b旧的 PostgreSQL 可执行目录
-B新的 PostgreSQL 可执行目录

将新数据移动到位：

cd /usr/local/var
mv postgres postgres9.6
mv postgres10.1 postgres

重启 Postgres：
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
检查/usr/local/var/postgres/server.log详细信息并确保新服务器正确启动。
最后，重新安装 rails pggem
```
gem uninstall pg
gem install pg
```

I suggest you take some time to read the PostgreSQL documentationto understand exactly what you're doing in the above steps to minimize frustrations.

我建议您花一些时间阅读PostgreSQL 文档，以准确了解您在上述步骤中所做的工作，以最大程度地减少挫折。

Answer 2

回答by Christian

Despite all answers above, here goes my 5 cents.

尽管上面有所有答案，但我的 5 美分来了。

It works on any OS and from any-to-any postgres version.

它适用于任何操作系统和任何对任何 postgres 版本。

Stop any running postgres instance;
Install the new version and start it; Check if you can connect to the new version as well;
Change old version's postgresql.conf-> portfrom 5432to 5433;
Start the old version postgres instance;
Open a terminal and cdto the new version binfolder;
Run pg_dumpall -p 5433 -U <username> | psql -p 5432 -U <username>
Stop old postgres running instance;

停止任何正在运行的 postgres 实例；
安装新版本并启动；检查您是否也可以连接到新版本；
将旧版本的postgresql.conf->port从更改5432为5433;
启动旧版 postgres 实例；
打开终端并cd进入新版本bin文件夹；
跑 pg_dumpall -p 5433 -U <username> | psql -p 5432 -U <username>
停止旧的 postgres 运行实例；

Answer 3

回答by pramod

Here is the solution for Ubuntuusers

这是Ubuntu用户的解决方案

First we have to stop postgresql

首先我们必须停止 postgresql

sudo /etc/init.d/postgresql stop

Create a new file called /etc/apt/sources.list.d/pgdg.list and add below line

创建一个名为 /etc/apt/sources.list.d/pgdg.list 的新文件并添加以下行

deb http://apt.postgresql.org/pub/repos/apt/ utopic-pgdg main

Follow below commands

按照下面的命令

wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.4
sudo pg_dropcluster --stop 9.4 main 
sudo /etc/init.d/postgresql start

Now we have everything, just need to upgrade it as below

现在我们拥有了一切，只需要如下升级

sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

That's it. Mostly upgraded cluster will run on port number 5433. Check it with below command

就是这样。大多数升级的集群将在端口号 5433 上运行。使用以下命令检查它

sudo pg_lsclusters

Answer 4

回答by Ben Johnson

Update: This process is the same for upgrading 9.5 through at least 11.5; simply modify the commands to reflect versions 9.6and 10, where 9.6is the oldversion and 10is the newversion. Be sure to adjust the "old" and "new" directories accordingly, too.

更新：从 9.5 升级到至少 11.5，这个过程是一样的；只需修改，以反映版本的命令9.6和10，其中9.6是旧版本，10是新版本。一定要相应地调整“旧”和“新”目录。

I just upgraded PostgreSQL 9.5 to 9.6 on Ubuntu and thought I'd share my findings, as there are a couple of OS/package-specific nuances of which to be aware.

我刚刚在 Ubuntu 上将 PostgreSQL 9.5 升级到 9.6，并认为我会分享我的发现，因为有几个操作系统/包特定的细微差别需要注意。

(I didn't want to have to dump and restore data manually, so several of the other answers here were not viable.)

（我不想手动转储和恢复数据，所以这里的其他几个答案都不可行。）

In short, the process consists of installing the new version of PostgreSQL alongside the old version (e.g., 9.5 and 9.6), and then running the pg_upgradebinary, which is explained in (some) detail at https://www.postgresql.org/docs/9.6/static/pgupgrade.html.

简而言之，该过程包括在旧版本（例如 9.5 和 9.6）旁边安装新版本的 PostgreSQL，然后运行pg_upgrade二进制文件，这在https://www.postgresql.org/中的（一些）详细说明中进行了解释docs/9.6/static/pgupgrade.html。

The only "tricky" aspect of pg_upgradeis that failure to pass the correct value for an argument, or failure to be logged-in as the correct user or cdto the correct location before executing a command, may lead to cryptic error messages.

唯一“棘手”的方面pg_upgrade是未能传递正确的参数值，或未能以正确的用户身份登录或cd在执行命令之前无法登录到正确的位置，可能会导致神秘的错误消息。

On Ubuntu (and probably Debian), provided you are using the "official" repo, deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main, and provided you haven't changed the default filesystem paths or runtime options, the following procedure should do the job.

在 Ubuntu（也可能是 Debian）上，如果您使用的是“官方”存储库，deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main并且没有更改默认文件系统路径或运行时选项，则以下过程应该可以完成这项工作。

Install the new version (note that we specify the 9.6, explicitly):

安装新版本（注意我们明确指定了9.6, ）：

sudo apt install postgresql-9.6

Once installation succeeds, both versions will be running side-by-side, but on different ports. The installation output mentions this, at the bottom, but it's easy to overlook:

安装成功后，两个版本将并行运行，但在不同的端口上。安装输出在底部提到了这一点，但很容易忽略：

Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433

Stop both server instances (this will stop both at the same time):

停止两个服务器实例（这将同时停止）：

sudo systemctl stop postgresql

Switch to the dedicated PostgreSQL system user:

切换到专用的 PostgreSQL 系统用户：

su postgres

Move into his home directory (failure to do this will cause errors):

进入他的主目录（不这样做会导致错误）：

cd ~

pg_upgraderequiresthe following inputs (pg_upgrade --helptells us this):

pg_upgrade需要以下输入（pg_upgrade --help告诉我们这一点）：

When you run pg_upgrade, you must provide the following information:
  the data directory for the old cluster  (-d DATADIR)
  the data directory for the new cluster  (-D DATADIR)
  the "bin" directory for the old version (-b BINDIR)
  the "bin" directory for the new version (-B BINDIR)

These inputs may be specified with "long names", to make them easier to visualize:

这些输入可以用“长名称”指定，使它们更容易可视化：

  -b, --old-bindir=BINDIR       old cluster executable directory
  -B, --new-bindir=BINDIR       new cluster executable directory
  -d, --old-datadir=DATADIR     old cluster data directory
  -D, --new-datadir=DATADIR     new cluster data directory

We must also pass the --new-optionsswitch, because failure to do so results in the following:

我们还必须通过--new-optionsswitch，因为不这样做会导致以下结果：

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/lib/postgresql/.s.PGSQL.50432"?

This occurs because the default configuration options are applied in the absence of this switch, which results in incorrect connection options being used, hence the socket error.

发生这种情况是因为在没有此开关的情况下应用了默认配置选项，这会导致使用的连接选项不正确，从而导致套接字错误。

Execute the pg_upgradecommand from the newPostgreSQL version:

从新的PostgreSQL 版本执行pg_upgrade命令：

/usr/lib/postgresql/9.6/bin/pg_upgrade --old-bindir=/usr/lib/postgresql/9.5/bin --new-bindir=/usr/lib/postgresql/9.6/bin --old-datadir=/var/lib/postgresql/9.5/main --new-datadir=/var/lib/postgresql/9.6/main --old-options=-cconfig_file=/etc/postgresql/9.5/main/postgresql.conf --new-options=-cconfig_file=/etc/postgresql/9.6/main/postgresql.conf

Logout of the dedicated system user account:

注销专用系统用户帐户：

exit

The upgrade is now complete, but, the new instance will bind to port 5433(the standard default is 5432), so keep this in mind if attempting to test the new instance before "cutting-over" to it.

升级现已完成，但是，新实例将绑定到端口5433（标准默认值为5432），因此如果在“切入”之前尝试测试新实例，请记住这一点。

Start the server as normal (again, this will start both the old and new instances):

正常启动服务器（同样，这将启动旧实例和新实例）：

systemctl start postgresql

If you want to make the new version the default, you will need to edit the effective configuration file, e.g., /etc/postgresql/9.6/main/postgresql.conf, and ensure that the port is defined as such:

如果要使新版本成为默认版本，则需要编辑有效的配置文件，例如/etc/postgresql/9.6/main/postgresql.conf，并确保端口定义如下：

port = 5432

If you do this, either change the old version's port number to 5433at the same time (before starting the services), or, simply remove the old version (this will notremove your actual database content; you would need to use apt --purge remove postgresql-9.5for that to happen):

如果您这样做，请同时将旧版本的端口号更改5433为（在启动服务之前），或者简单地删除旧版本（这不会删除您的实际数据库内容；您需要使用apt --purge remove postgresql-9.5它来实现)：

apt remove postgresql-9.5

The above command will stop all instances, so you'll need to start the new instance one last time with:

上面的命令将停止所有实例，因此您需要最后一次启动新实例：

systemctl start postgresql

As a final point of note, don't forget to consider pg_upgrade's good advice:

最后一点，不要忘记考虑pg_upgrade的好建议：

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Answer 5

回答by jnmandal

If you are using homebrew and homebrew services, you can probably just do:

如果您使用自制软件和自制软件服务，您可能只需执行以下操作：

brew services stop postgresql
brew upgrade postgresql
brew postgresql-upgrade-database
brew services start postgresql

I think this might not work completely if you are using advanced postgres features, but it worked perfectly for me.

我认为如果您使用高级 postgres 功能，这可能无法完全正常工作，但它对我来说非常有效。

Answer 6

回答by Craig Ringer

The user manualcovers this topic in depth. You can:

该用户手册涵盖了深入这个话题。你可以：

pg_upgradein-place; or
pg_dumpand pg_restore.

pg_upgrade到位; 或者
pg_dump和pg_restore。

If in doubt, do it with dumps. Don't delete the old data directory, just keep it in case something goes wrong / you make a mistake; that way you can just go back to your unchanged 9.3 install.

如果有疑问，请使用转储。不要删除旧的数据目录，保留它以防出现问题/你犯了错误；这样你就可以回到你没有改变的 9.3 安装。

For details, see the manual.

有关详细信息，请参阅手册。

If you're stuck, post a detailed question explaining how you're stuck, where, and what you tried first. It depends a bit on how you installed PostgreSQL too, as there are several different "distributions" of PostgreSQL for OS X (unfortunately). So you'd need to provide that info.

如果您卡住了，请发布一个详细的问题，说明您是如何卡住的，在哪里以及您首先尝试了什么。这也取决于您如何安装 PostgreSQL，因为 OS X 有几种不同的 PostgreSQL“发行版”（不幸的是）。所以你需要提供这些信息。

Answer 7

回答by Josh

Standing on the shoulders of the other poor creatures trodding through this muck, I was able to follow these steps to get back up and running after an upgrade to Yosemite:

站在其他可怜的生物的肩膀上，我能够按照以下步骤在升级到优胜美地后重新开始运行：

Assuming you've used home-brew to install and upgrade Postgres, you can perform the following steps.

假设您已经使用 home-brew 安装和升级 Postgres，您可以执行以下步骤。

Stop current Postgres server:
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Initialize a new 9.4 database:
initdb /usr/local/var/postgres9.4 -E utf8
Install postgres 9.3 (as it was no longer present on my machine):
brew install homebrew/versions/postgresql93
Add directories removed during Yosemite upgrade:
mkdir -p /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/touch /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/.keep
run pg_upgrade:
pg_upgrade -v -d /usr/local/var/postgres -D /usr/local/var/postgres9.4 -b /usr/local/Cellar/postgresql93/9.3.5/bin/ -B /usr/local/Cellar/postgresql/9.4.0/bin/

Move new data into place:

cd /usr/local/var
mv postgres postgres9.3
mv postgres9.4 postgres

Restart Postgres:
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Check /usr/local/var/postgres/server.logfor details and to make sure the new server started properly.

Finally, re-install related libraries?

pip install --upgrade psycopg2
gem uninstall pg
gem install pg

停止当前的 Postgres 服务器：
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
初始化一个新的 9.4 数据库：
initdb /usr/local/var/postgres9.4 -E utf8
安装 postgres 9.3（因为它不再出现在我的机器上）：
brew install homebrew/versions/postgresql93
添加在优胜美地升级期间删除的目录：
mkdir -p /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/touch /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/.keep
运行pg_upgrade：
pg_upgrade -v -d /usr/local/var/postgres -D /usr/local/var/postgres9.4 -b /usr/local/Cellar/postgresql93/9.3.5/bin/ -B /usr/local/Cellar/postgresql/9.4.0/bin/

将新数据移动到位：

cd /usr/local/var
mv postgres postgres9.3
mv postgres9.4 postgres

重启 Postgres：
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
检查/usr/local/var/postgres/server.log详细信息并确保新服务器正确启动。

最后，重新安装相关库？

pip install --upgrade psycopg2
gem uninstall pg
gem install pg

Answer 8

回答by Steven Shaw

Looks like the solution has been baked into Homebrew now:

看起来该解决方案现在已经融入 Homebrew 中：

$ brew info postgresql
...
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database
....

Answer 9

回答by Andrew

On WindowsI kept facing different errors messages when trying to use pg_upgrade.

在Windows 上，我在尝试使用pg_upgrade.

Saved a lot of time for me to just:

为我节省了大量时间：

Backup DB
Uninstall all copies of PostgreSQL
Install 9.5
Restore DB

备份数据库
卸载 PostgreSQL 的所有副本
安装 9.5
恢复数据库

Answer 10

回答by dimitarvp

This did it for me.

这为我做到了。

https://gist.github.com/dideler/60c9ce184198666e5ab4

Short and to the point. I honestly don't aim to understand the guts of PostgreSQL, I want to get stuff done.

精炼到位。老实说，我并不是想了解 PostgreSQL 的本质，我想完成工作。

如何在不丢失数据的情况下将 PostgreSQL 从 9.6 版升级到 10.1 版？

提问by user3675188

回答by Donovan

回答by Christian

回答by pramod

回答by Ben Johnson

回答by jnmandal

回答by Craig Ringer

回答by Josh

回答by Steven Shaw

回答by Andrew

回答by dimitarvp

相关推荐

最近更新

标签

如何在不丢失数据的情况下将 PostgreSQL 从 9.6 版升级到 10.1 版？

提问by user3675188

回答by Donovan

回答by Christian

回答by pramod

回答by Ben Johnson

回答by jnmandal

回答by Craig Ringer

回答by Josh

回答by Steven Shaw

回答by Andrew

回答by dimitarvp

相关推荐

PostgreSQL 中基于游标的记录

postgresql 在执行 postgreql 函数时提交事务

postgresql 在 psql 中垂直显示选择结果，就像 MySQL 的 \G 所做的那样

postgresql 扩展存在但 uuid_generate_v4 失败

相关推荐

最近更新

标签