在Ubuntu 16.10上将PostgreSQL从9.6升级到10.0 [英] Upgrade PostgreSQL from 9.6 to 10.0 on Ubuntu 16.10

问题描述

我的数据库超过600 GB，当前容量仅为1 TB，因此可能限制了我的选择。

我的配置文件在这里：

  /etc/postgresql/9.6/main 
  

我的数据库在这里：

  /mnt/1TB/postgresql/9.6/main 
  

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

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

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

解决方案

分步指南

1. 进行备份。确保您的数据库没有被更新。

     pg_dumpall>输出文件
     




2. 安装Postgres 10 。请遵循此页面上的指示信息： https://www.postgresql.org/download/linux/ubuntu/

   
   
   

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




3. 运行 pg_lsclusters ：

     Ver群集端口状态所有者数据目录日志文件
    9.6 main 5432在线postgres /var/lib/postgresql/9.6 / main /var/log/postgresql/postgresql-9.6-main.log 
    10 main 5433在线postgres / var / lib / postgresql / 10 / main /var/log/postgresql/postgresql-10-main.log 
     

   已经有一个集群 main （因为这是在软件包安装时默认创建的）。这样做是为了使全新安装可以立即使用，而无需首先创建集群，但是当您尝试在 9.6 / main 时升级 10 / main 也存在。推荐的过程是使用 pg_dropcluster 删除10个群集，然后使用 pg_upgradecluster 升级。




4. 停止10个群集并将其删除：

     sudo pg_dropcluster 10 main --stop 
     




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

     sudo systemctl stop postgresql 
     




6. 升级9.6群集：

     sudo pg_upgradecluster -m升级9.6 main 
     




7. 再次启动PostgreSQL

     sudo systemctl start postgresql 
     




8. 运行 pg_lsclusters 。现在，您的9.6群集应处于关闭状态，而10群集应在 5432 上在线：

     Ver群集端口状态所有者数据目录日志文件
    9.6 main 5433 down postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log 
    10个主要的5432在线postgres / var / lib / postgresql / 10 / main /var/log/postgresql/postgresql-10-main.log 
     




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

     sudo pg_dropcluster 9.6 main --stop 
     

pg_upgradecluster

的一些注意事项本指南适用于从9.5升级到10.1。从旧版本升级时，请考虑在第6步中省略 -m升级：

  sudo pg_upgradecluster 9.6 main 
  

如果您的集群非常大，则可以使用 pg_upgradecluster 和-link 选项，以便升级就位。但是，这很危险-如果发生故障，您可能会丢失群集。只是不必使用此选项，因为 -m升级已经足够快了。

基于：

• 文档：升级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手册页

更新

该指南适用于从9.6升级到11以及从10升级到11的情况。

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

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:

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

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

解决方案

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 main for 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/main when 10/main also exists. The recommended procedure is to remove the 10 cluster with pg_dropcluster and 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
   

Some notes on pg_upgradecluster

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

sudo pg_upgradecluster 9.6 main

If you have a really big cluster, you may use pg_upgradecluster with a --link option, 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 upgrade is already fast enough.

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

Update

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

这篇关于在Ubuntu 16.10上将PostgreSQL从9.6升级到10.0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！