Re: PostgreSQL upgrade.

Mark Kirkwood <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <[email protected]> 
On 15/04/19 2:26 PM, Mark Kirkwood wrote:
>
> On 10/04/19 8:20 PM, Andreas Kretschmer wrote:
>>
>>
>> Am 10.04.19 um 07:40 schrieb Daulat Ram:
>>> We have two node postgresql database version 9.6 with streaming
>>> replication which is running on docker environment, os Linux
>>> (Ubuntu) and we have to migrate on PostgresQL11. I need your
>>> suggestions & steps to compete the upgrade  process successfully.
>>
>> there are exists several ways to do that. You can take a normal dump
>> and replay it in the new version, you can use pg_upgrade, and you can
>> use a logical replication (using slony, londiste or pg_logical from
>> 2ndQuadrant). There is no 'standard way' to do that, all depends on
>> your requirements and knowledge how to work with that tools.
>>
>>
>>
>
> The docker environment makes using pg_upgrade more difficult, as you
> need to modify (or build a new) container with the old and new
> Postgres versions installed. I'm interested in seeing how hard that
> would be (will update this thread if I find anything useful).
>
>
>

It transpires that it is not too tricky to build a 'migration' container:

- get relevant Postgres Dockerfile from https://hub.docker.com/_/postgres

- Amend it to install 2 versions of Postgres

- Change ENTRYPOINT to run something non Postgres related (I used 'top')

- Build it


To use pg_upgrade the process is:

- stop your original Postgres container

- run the migration one, attaching volume from the Postgres container +
a new one

- enter the migration container and initialize the new version's datadir

- run pg_upgrade from old to new version

- tidy up config and pg_hba for the upgraded datadir

- exit and stop the migration container


(see attached for notes and Dockerfile diff)


You can then run a new Postgres container (of the new version) using the
new volume.

While the process is a bit fiddly, it is probably still way faster than
a dump and restore.

regards

Mark
DOCKER-UPGRADE-POSTGRES (text/plain, 1.7 KB) 
Look At Upgrading Postgres In Docker 
====================================

The standard Docker images for Postgres have the following issues wrt
in place upgrades:
- Only 1 version of Postgres binaries
- Cannot stop Postgres (stops container)

To get around this a custom image that has:
- 2 versions of Postgres binaries (e.g version 10 and 11)
- runs top (or tail -f etc) as its ENTRYPOINT so that Postgres is stopped

Run a container with this image using 2 volumes (one for the old install
one for the new);

$ docker run \
         --detach \
         --name=mig-postgres \
         --volume=/srv/docker/volumes/test-postgres/datadir10:/var/lib/postgresql/data \
         --volume=/srv/docker/volumes/test-postgres/datadir11:/var/lib/postgresql/data11 \ 
         migpostgres

$ docker exec -it mig-postgres bash
# chown postgres:postgres /var/lib/postgresql/data11
# su - postgres
postgres $ /usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/data11
postgres $ /usr/lib/postgresql/11/bin/pg_upgrade \
           --old-bindir=/usr/lib/postgresql/10/bin \
           --new-bindir=/usr/lib/postgresql/11/bin \
           --old-datadir=/var/lib/postgresql/data \
           --new-datadir=/var/lib/postgresql/data11 \
           --old-port=5432 \
           --new-port=5433 \
           --check

(run again w/o --check if ok)

postgres $ echo "listen_addresses='*'" >> /var/lib/postgresql/data11/postgresql.conf 
postgres $ cp /var/lib/postgresql/data/pg_hba.conf \
              /var/lib/postgresql/data11
postgres $ exit
# exit

$ docker container stop mig-postgres
$ docker run \
         --detach \
         --name=test-postgres11 \
         --volume=/srv/docker/volumes/test-postgres/datadir11:/var/lib/postgresql/data \
         --publish 5432:5432 \
         postgres:11
Dockerfile.diff (text/x-patch, 3.6 KB) 
--- Dockerfile.orig	2019-04-16 10:45:07.924000000 +1200
+++ Dockerfile	2019-04-16 12:38:32.556000000 +1200
@@ -28,7 +28,7 @@
 	&& wget -O /usr/local/bin/gosu "https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$(dpkg --print-architecture)" \
 	&& wget -O /usr/local/bin/gosu.asc "https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$(dpkg --print-architecture).asc" \
 	&& export GNUPGHOME="$(mktemp -d)" \
-	&& gpg --batch --keyserver ha.pool.sks-keyservers.net --recv-keys B42F6819007F00F88E364FD4036A9C25BF357DD4 \
+	&& gpg --batch --keyserver keyserver.ubuntu.com --recv-keys B42F6819007F00F88E364FD4036A9C25BF357DD4 \
 	&& gpg --batch --verify /usr/local/bin/gosu.asc /usr/local/bin/gosu \
 	&& { command -v gpgconf > /dev/null && gpgconf --kill all || :; } \
 	&& rm -rf "$GNUPGHOME" /usr/local/bin/gosu.asc \
@@ -64,14 +64,16 @@
 # uid                  PostgreSQL Debian Repository
 	key='B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8'; \
 	export GNUPGHOME="$(mktemp -d)"; \
-	gpg --batch --keyserver ha.pool.sks-keyservers.net --recv-keys "$key"; \
+	gpg --batch --keyserver keyserver.ubuntu.com --recv-keys "$key"; \
 	gpg --batch --export "$key" > /etc/apt/trusted.gpg.d/postgres.gpg; \
 	command -v gpgconf > /dev/null && gpgconf --kill all; \
 	rm -rf "$GNUPGHOME"; \
 	apt-key list
 
 ENV PG_MAJOR 10
+ENV PG_NEXT_MAJOR 11
 ENV PG_VERSION 10.7-1.pgdg90+1
+ENV PG_NEXT_VERSION 11.2-1.pgdg90+1
 
 RUN set -ex; \
 	\
@@ -83,12 +85,14 @@
 		amd64|i386|ppc64el) \
 # arches officialy built by upstream
 			echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main $PG_MAJOR" > /etc/apt/sources.list.d/pgdg.list; \
+			echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main $PG_NEXT_MAJOR" >> /etc/apt/sources.list.d/pgdg.list; \
 			apt-get update; \
 			;; \
 		*) \
 # we're on an architecture upstream doesn't officially build for
 # let's build binaries from their published source packages
 			echo "deb-src http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main $PG_MAJOR" > /etc/apt/sources.list.d/pgdg.list; \
+			echo "deb-src http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main $PG_NEXT_MAJOR" >> /etc/apt/sources.list.d/pgdg.list; \
 			\
 			case "$PG_MAJOR" in \
 				9.* | 10 ) ;; \
@@ -115,6 +119,15 @@
 					postgresql-common pgdg-keyring \
 					"postgresql-$PG_MAJOR=$PG_VERSION" \
 			; \
+			apt-get build-dep -y \
+				postgresql-common pgdg-keyring \
+				"postgresql-$PG_NEXT_MAJOR=$PG_NEXT_VERSION" \
+			; \
+			DEB_BUILD_OPTIONS="nocheck parallel=$(nproc)" \
+				apt-get source --compile \
+					postgresql-common pgdg-keyring \
+					"postgresql-$PG_NEXT_MAJOR=$PG_NEXT_VERSION" \
+			; \
 # we don't remove APT lists here because they get re-downloaded and removed later
 			\
 # reset apt-mark's "manual" list so that "purge --auto-remove" will remove all build dependencies
@@ -141,6 +154,10 @@
 		"postgresql-$PG_MAJOR=$PG_VERSION" \
 	; \
 	\
+	apt-get install -y \
+		"postgresql-$PG_NEXT_MAJOR=$PG_NEXT_VERSION" \
+	; \
+	\
 	rm -rf /var/lib/apt/lists/*; \
 	\
 	if [ -n "$tempDir" ]; then \
@@ -167,9 +184,12 @@
 RUN mkdir -p "$PGDATA" && chown -R postgres:postgres "$PGDATA" && chmod 777 "$PGDATA" # this 777 will be replaced by 700 at runtime (allows semi-arbitrary "--user" values)
 VOLUME /var/lib/postgresql/data
 
-COPY docker-entrypoint.sh /usr/local/bin/
-RUN ln -s usr/local/bin/docker-entrypoint.sh / # backwards compat
-ENTRYPOINT ["docker-entrypoint.sh"]
+#COPY docker-entrypoint.sh /usr/local/bin/
+#RUN ln -s usr/local/bin/docker-entrypoint.sh / # backwards compat
+#ENTRYPOINT ["docker-entrypoint.sh"]
 
 EXPOSE 5432
-CMD ["postgres"]
+#CMD ["postgres"]
+
+ENTRYPOINT ["top", "-b"]
+CMD ["-c"]
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.