在 Ubuntu 16.10 上将 PostgreSQL 从 9.6 升级到 10.0

Upgrade PostgreSQL from 9.6 to 10.0 on Ubuntu 16.10

提问by trench

My database is over 600 GB and my current volume is only 1 TB, so that probably limits my options.

我的数据库超过 600 GB，我当前的容量只有 1 TB，所以这可能限制了我的选择。

My config files are here:

我的配置文件在这里：

/etc/postgresql/9.6/main

My database is here:

我的数据库在这里：

/mnt/1TB/postgresql/9.6/main

Edit - This guide worked for me. The only addition I needed to make was to download libicu55 manually and install it, and I had to grant postgres 1777 permission for my /tmp/ folder. I was also saving the data folder to a different drive, so I had to use the command:

编辑 - 本指南对我有用。我需要做的唯一添加是手动下载 libicu55 并安装它，我必须为我的 /tmp/ 文件夹授予 postgres 1777 权限。我还将数据文件夹保存到不同的驱动器，所以我不得不使用命令：

pg_upgradecluster -m upgrade 10 main /mnt/1TB/postgresql/10

https://gist.github.com/delameko/bd3aa2a54a15c50c723f0eef8f583a44

https://gist.github.com/delameko/bd3aa2a54a15c50c723f0eef8f583a44

回答by Max Malysh

A Step-by-Step Guide

分步指南

1. Make a backup. Make sure that your database is not being updated.

   pg_dumpall > outputfile
   

2. Install Postgres 10. Follow instructions on this page: https://www.postgresql.org/download/linux/ubuntu/

   Then run sudo apt-get install postgresql-10. A newer version will be installed side-by-side with the earlier version.

3. Run pg_lsclusters:

   Ver Cluster Port Status Owner    Data directory               Log file
   9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
   10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log
   

   There already is a cluster mainfor 10 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 9.6/mainwhen 10/mainalso exists. The recommended procedure is to remove the 10 cluster with pg_dropclusterand then upgrade with pg_upgradecluster.

4. Stop the 10 cluster and drop it:

   sudo pg_dropcluster 10 main --stop
   

5. Stop all processes and services writing to the database. Stop the database:

   sudo systemctl stop postgresql 
   

6. Upgrade the 9.6 cluster:

   sudo pg_upgradecluster -m upgrade 9.6 main
   

7. Start PostgreSQL again

   sudo systemctl start postgresql
   

8. Run pg_lsclusters. Your 9.6 cluster should now be "down", and the 10 cluster should be online at 5432:

   Ver Cluster Port Status Owner    Data directory               Log file
   9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
   10  main    5432 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log
   

9. First, check that everything works fine.After that, remove the 9.6 cluster:

    sudo pg_dropcluster 9.6 main --stop
   

1. 做一个备份。确保您的数据库没有被更新。

   pg_dumpall > outputfile
   

2. 安装 Postgres 10。按照此页面上的说明进行操作：https: //www.postgresql.org/download/linux/ubuntu/

   然后运行sudo apt-get install postgresql-10。较新的版本将与较早的版本并排安装。

3. 运行pg_lsclusters：

   Ver Cluster Port Status Owner    Data directory               Log file
   9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
   10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log
   

   已经有一个可main容纳 10 人的集群（因为这是在安装包时默认创建的）。这样做是为了让全新安装开箱即用，无需先创建集群，但当然当您尝试升级9.6/main时10/main也会发生冲突。推荐的过程是使用 删除 10 集群，pg_dropcluster然后使用pg_upgradecluster.

4. 停止 10 集群并删除它：

   sudo pg_dropcluster 10 main --stop
   

5. 停止写入数据库的所有进程和服务。停止数据库：

   sudo systemctl stop postgresql 
   

6. 升级 9.6 集群：

   sudo pg_upgradecluster -m upgrade 9.6 main
   

7. 再次启动 PostgreSQL

   sudo systemctl start postgresql
   

8. 运行pg_lsclusters。您的 9.6 集群现在应该“关闭”，而 10 集群应该在线5432：

   Ver Cluster Port Status Owner    Data directory               Log file
   9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
   10  main    5432 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log
   

9. 首先，检查一切是否正常。之后，删除 9.6 集群：

    sudo pg_dropcluster 9.6 main --stop
   

Some notes on pg_upgradecluster

一些注意事项 pg_upgradecluster

This guide works fine for upgrading from 9.5 to 10.1. When upgrading from an older version, consider omitting -m upgradeon the step #6:

本指南适用于从 9.5 升级到 10.1。从旧版本升级时，请考虑省略-m upgrade第 6 步：

sudo pg_upgradecluster 9.6 main

If you have a really big cluster, you may use pg_upgradeclusterwith a --linkoption, so that the upgrade will be in-place. However, this is dangerous — you can lose the cluster?in an event of failure. Just don't use this option if not necessary, as -m upgradeis already fast enough.

如果您有一个非常大的集群，您可以使用pg_upgradecluster一个--link选项，以便就地升级。然而，这很危险——如果发生故障，您可能会丢失集群。如果没有必要，请不要使用此选项，因为-m upgrade它已经足够快了。

Based on:

基于：

• Docs: Upgrading a PostgreSQL Cluster
• Gist #1: delameko/upgrade-postgres-9.5-to-9.6.md
• Gist #2: johanndt/upgrade-postgres-9.3-to-9.5.md
• What happens if I interrupt or cancel pg_upgradecluster?
• Ubuntu manpage for pg_upgradecluster

• 文档：升级 PostgreSQL 集群
• 要点 #1：delameko/upgrade-postgres-9.5-to-9.6.md
• 要点#2：johanndt/upgrade-postgres-9.3-to-9.5.md
• 如果我中断或取消会发生什么pg_upgradecluster？
• pg_upgradecluster 的 Ubuntu 联机帮助页

Update

更新

This guide works fine for upgrading from 9.6 to 11 and from 10 to 11.

本指南适用于从 9.6 升级到 11 以及从 10 升级到 11。

回答by datakid

Almost worked out of the box. I'm on 17.10 artful. While you canput that in the .deb file, it wont work - Postgres only update the non LTS releases if they break the LTS release. So put Zesty into that deb and you are good to go.

几乎开箱即用。我在 17.10 巧妙。虽然你可以把它放在 .deb 文件中，但它不会工作 - Postgres 只更新非 LTS 版本，如果它们破坏了 LTS 版本。所以把 Zesty 放到那个 deb 中，你就可以开始了。