Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eKiON-0006SZ-Vo for pgsql-admin@arkaria.postgresql.org; Fri, 01 Dec 2017 10:18:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eKiON-0003Tv-Cj for pgsql-admin@arkaria.postgresql.org; Fri, 01 Dec 2017 10:17:59 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eKiOM-0003Tk-M5 for pgsql-admin@lists.postgresql.org; Fri, 01 Dec 2017 10:17:59 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eKiOI-0002dN-Pn for pgsql-admin@postgresql.org; Fri, 01 Dec 2017 10:17:57 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id 179A820CA7; Fri, 1 Dec 2017 05:17:53 -0500 (EST) Received: from web4 ([10.202.2.214]) by compute7.internal (MEProxy); Fri, 01 Dec 2017 05:17:53 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hintbits.com; h= cc:content-transfer-encoding:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-sender :x-me-sender:x-sasl-enc; s=mesmtp; bh=UNwJRrq4/abMcE6z8tH+UQYwx+ SkPmnFidPEhvu/1JU=; b=VPJoK/xtyhJdzoCX3jBMZubDgQ2hzEWUEa1cbZjgji jtJA+xolbpJuDqySOWnO2jAN8U383z/ludPhIZ3mHiQWnROJ5ndTLuNAEEzfZi+9 8t/t7kUY7QpyJ1RNZS1wVNBd3X/w43NloNIEuGBSzotcDccvJ9kQSly6nL1uw2V6 M= DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=UNwJRr q4/abMcE6z8tH+UQYwx+SkPmnFidPEhvu/1JU=; b=r/4oPc8Hz9NfuTy4eE3Jtp srAoAMyp/P7dDiDwOCL5FtV5ssvU1QkD/swfd4YRsjRfJoeWw3hoOStaFKHGjxVt EqFMrugeHgYTUQlS2/R/1+voeznzKCW3jU9zsfMwACB/DzfFs8T/7l9rY7LMV25u vwp6Sa+MKHndd8nzJIONeqm/UIPiXc6qnfnDb6G4xvIG+t7a/tNOQI6iJ7yBcxrC r6KUEs4NCHpWjBQ3NywyNz7yeh0AKgYo9ZFISnRawDvjIZ3GXur0LAzD0Jw2G2ry vi8H/yKSRfI5IMGLdk0MFVYbsuzKMC3xRdPB/E0CMRwOxxzXhlciMsrwIF256qeA == X-ME-Sender: Received: by mailuser.nyi.internal (Postfix, from userid 99) id CE9FFBA798; Fri, 1 Dec 2017 05:17:52 -0500 (EST) Message-Id: <1512123472.1347799.1190472760.463DC314@webmail.messagingengine.com> From: Alex Kliukin To: Debraj Manna , Magnus Hagander Cc: "pgsql-admin" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary="_----------=_151212347213477991" X-Mailer: MessagingEngine.com Webmail Interface - ajax-a169161c Date: Fri, 01 Dec 2017 11:17:52 +0100 In-Reply-To: References: Subject: Re: [ADMIN] Upgrading postgres to 10 running with patroni List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --_----------=_151212347213477991 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="utf-8" 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 > 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 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 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 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 --_----------=_151212347213477991 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"
Hello,

On Thu, Nov 16, 2017, at 08:32, Debraj Manna wrote:
I am attaching my patroni.yml fo= r reference.

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

I am trying to upgrade postgres 9.5 to postgres 10 running with patron= i. 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, ma= king 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 point= ing to the server binaries, but also any other configuration options from t= he older version that are incompatible with the new ones). 

Once you do this, start Patroni - it will start your PostgreSQL cluste= r. 


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 ca= n 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 a= re 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 m= ain"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo ap= t-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= =3DC' -o '--encoding=3Dutf8'

Change data_directory in postgresq.conf for both 9.5 & 10
data_directory - /var/lib/postgresql/data/postgresql0   &nbs= p;     # For postgresql-9.5
data_directory - /var/lib/postgresql/10/data/postgresql0   &= nbsp;# 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=3D/etc/postgresql/9.5/main/postgresql= .conf' \
   -O ' -c config_file=3D/etc/postgresql/10/main/postgresql.= conf'  -k

./analyze_new_cluster.sh reported success.
<= /div>

Copy conf files
Copied postgresql.base.conf, postgresql.conf , pg_hba.conf &  from 9.5 data directory /var/lib/postgresql/data/= postgresql0 to the 10 data directory /var/lib/postgresql/10/data/post= gresql0

The last step is definitely wrong. You should not copy postmaster.opts= , postgresql.base.conf and postgresql.conf. Instead, you should configure t= he postgresql.conf in the PostgreSQL 10 data directory to match the paramet= ers of your old cluster. Copying pg_hba.conf seems alright, since the one f= rom 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 par= ameters.




Change postmaster.opts = ;to match postgres-10 locati= ons
/usr/lib/postgresql/10/bin/postgres "-D" "/var/lib/postgresql/10/dat= a/postgresql0" "-p" "50432" "-b" "-c" "config_file=3D/etc/postgresql/10/mai= n/postgresql.conf" "-c" "listen_addresses=3D" "-c" "unix_socket_permissions= =3D0700" "-c" "unix_socket_directories=3D/tmp"

Do not meddle with postmaster.opts altogether.


Change data_dir in patroni.yml to/var/lib/postgresql/10/data/po= stgresql0

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 th= e old one) should point to the new version.



On st= arting patroni I am getting exception like below and also the DB is not acc= essible. 

2017-11-16 07:11:18,622 INFO: Conne=
cting to localhost:2181
2017-11-16 07:11:18,624 INFO: Zookeeper connection established, state: CONN=
ECTED
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: CONN=
ECTED
Traceback (most recent call last):
  File "/usr/local/bin/patroni", line 11, in <module>
    load_entry_point('patroni=3D=3D1.0', 'console_scripts', 'patroni')()
  File "/usr/local/lib/python2.7/dist-packages/patroni/__init__.py", line 1=
28, in main
    patroni.run()
  File "/usr/local/lib/python2.7/dist-packages/patroni/__init__.py", line 1=
02, 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.initializ=
e !=3D self.state_handler.sysid:
  File "/usr/local/lib/python2.7/dist-packages/patroni/postgresql.py", line=
 257, in sysid
    data =3D self.controldata()
  File "/usr/local/lib/python2.7/dist-packages/patroni/postgresql.py", line=
 675, in controldata
    result =3D {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 =3D {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 miss= ed something on patroni side.

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

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

--
Sincerely,
Alex


--_----------=_151212347213477991--