Re: [ADMIN] Upgrading postgres to 10 running with patroni

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(dot)manna(at)gmail(dot)com> 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 =
>> self.controldata() 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