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
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.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"]
