Re: Upgrading postgres to 10 running with patroni

Alex Kliukin <alexk@xxxxxxxxxxxx> · Fri, 01 Dec 2017 11:17:52 +0100

Hello,

On Thu, Nov 16, 2017, at 08:32, Debraj Manna wrote:

I am attaching my patroni.yml for reference.

On Thu, Nov 16, 2017 at 12:59 PM, Debraj Manna <subharaj.manna@xxxxxxxxx> wrote:

Hi

I am trying to upgrade postgres 9.5 to postgres 10 running with patroni. I am using patroni 1.0 with single node postgres.

Patroni 1.0 is very old. Use the latest version.

The simplest course of actions is to shut down Patroni altogether (it will also shut down PostgreSQL), upgrade your database using pg_upgrade, making sure it starts afterwards and then rewrite your patroni.yaml to match the new cluster (changing at least the data directory and the bin_dir pointing to the server binaries, but also any other configuration options from the older version that are incompatible with the new ones). 

Once you do this, start Patroni - it will start your PostgreSQL cluster. 

The steps that I have been following -

Stop patroni

sudo kill -9 <patroni process id>

Don't do it. If you want Patroni to stop managing your cluster your can put it into the standby mode (patronict pause cluster_name). It will not do auto failovers for your afterwards, until you resume it. But given you are running a single node and you need to stop PostgreSQL anyway - there is no reason to detach Patroni instead of just shutting it down (it will also shut down PostgreSQL).

Install postgres-10

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main"

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

sudo apt-get install postgresql-10

Stop postgresql cluster

Init data directory for the new upgrade

sudo -H -u postgres mkdir -p /var/lib/postgresql/10/data/postgresql0

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/data/postgresql0 initdb -o '--data-checksums' -o '--locale=C' -o '--encoding=utf8'

Change data_directory in postgresq.conf for both 9.5 & 10

data_directory - /var/lib/postgresql/data/postgresql0         # For postgresql-9.5

data_directory - /var/lib/postgresql/10/data/postgresql0    # For postgresql-10

Perform the upgrade

cd /tmp

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \

   -b /usr/lib/postgresql/9.5/bin \

   -B /usr/lib/postgresql/10/bin \

   -d /var/lib/postgresql/data/postgresql0 \

   -D /var/lib/postgresql/10/data/postgresql0 \

   -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \

   -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf'  -k

./analyze_new_cluster.sh reported success.

Copy conf files

Copied postgresql.base.conf, postgresql.conf , pg_hba.conf & postmaster.opts from 9.5 data directory /var/lib/postgresql/data/postgresql0 to the 10 data directory /var/lib/postgresql/10/data/postgresql0

The last step is definitely wrong. You should not copy postmaster.opts, postgresql.base.conf and postgresql.conf. Instead, you should configure the postgresql.conf in the PostgreSQL 10 data directory to match the parameters of your old cluster. Copying pg_hba.conf seems alright, since the one from 9.5 is compatible with 10.

After doing so, make sure your PostgreSQL 10 cluster starts on its own, without Patroni, and there are no complaints about invalid PostgreSQL parameters.

Change postmaster.opts to match postgres-10 locations

/usr/lib/postgresql/10/bin/postgres "-D" "/var/lib/postgresql/10/data/postgresql0" "-p" "50432" "-b" "-c" "config_file=/etc/postgresql/10/main/postgresql.conf" "-c" "listen_addresses=" "-c" "unix_socket_permissions=0700" "-c" "unix_socket_directories=/tmp"

Do not meddle with postmaster.opts altogether.

Change data_dir in patroni.yml to/var/lib/postgresql/10/data/postgresql0

You should use your old patroni.yaml as a reference and write the new one. Namely, the data_dir and the bin_dir, and the config_dir (if set in the old one) should point to the new version.

On starting patroni I am getting exception like below and also the DB is not accessible. 

2017-11-16 07:11:18,622 INFO: Connecting to localhost:2181
2017-11-16 07:11:18,624 INFO: Zookeeper connection established, state: CONNECTED
2017-11-16 07:11:19,132 INFO: Closing connection to localhost:2181
2017-11-16 07:11:19,132 INFO: Zookeeper session lost, state: CLOSED
2017-11-16 07:11:19,133 INFO: Connecting to localhost:2181
2017-11-16 07:11:19,135 INFO: Zookeeper connection established, state: CONNECTED
Traceback (most recent call last):
  File "/usr/local/bin/patroni", line 11, in <module>
    load_entry_point('patroni==1.0', 'console_scripts', 'patroni')()
  File "/usr/local/lib/python2.7/dist-packages/patroni/__init__.py", line 128, in main
    patroni.run()
  File "/usr/local/lib/python2.7/dist-packages/patroni/__init__.py", line 102, in run
    logger.info(self.ha.run_cycle())
  File "/usr/local/lib/python2.7/dist-packages/patroni/ha.py", line 502, in run_cycle
    return self._run_cycle()
  File "/usr/local/lib/python2.7/dist-packages/patroni/ha.py", line 470, in _run_cycle
    if self.sysid_valid(self.cluster.initialize) and self.cluster.initialize != self.state_handler.sysid:
  File "/usr/local/lib/python2.7/dist-packages/patroni/postgresql.py", line 257, in sysid
    data = ""
  File "/usr/local/lib/python2.7/dist-packages/patroni/postgresql.py", line 675, in controldata
    result = {l.split(':')[0].replace('Current ', '', 1): l.split(':')[1].strip() for l in data if l}
  File "/usr/local/lib/python2.7/dist-packages/patroni/postgresql.py", line 675, in <dictcomp>
    result = {l.split(':')[0].replace('Current ', '', 1): l.split(':')[1].strip() for l in data if l}
IndexError: list index out of range
~                                      

I was able to access the DB before starting patroni. So I think I missed something on patroni side.

I am not sure if this is the right forum to ask this question. If anyone has tried this please do let me know your thoughts.

The right place is to ask is https://github.com/zalando/patroni/

--

Sincerely,

Alex