How to upgrade the pg_restore in docker postgres image 10.3 to 10.5

Question

I use tableplus for my general admin.

Currently using the docker postgres image at 10.3 for both production and localhost development.

Because tableplus upgraded their postgres 10 drivers to 10.5, I can no longer use pg_restore to restore the backup files which are dumped using 10.5 --format=custom

See image for how I backup using tableplus. And how it uses 10.5 driver

The error message I get is pg_restore: [archiver] unsupported version (1.14) in file header

What i tried

I tried in localhost to simply change the tag for postgres in my dockerfile from 10.3 to 10.5 and it didn't work

original dockerfile

FROM postgres:10.3

COPY ./maintenance /usr/local/bin/maintenance
RUN chmod +x /usr/local/bin/maintenance/*
RUN mv /usr/local/bin/maintenance/* /usr/local/bin \
    && rmdir /usr/local/bin/maintenance

to

FROM postgres:10.5

COPY ./maintenance /usr/local/bin/maintenance
RUN chmod +x /usr/local/bin/maintenance/*
RUN mv /usr/local/bin/maintenance/* /usr/local/bin \
    && rmdir /usr/local/bin/maintenance

My host system for development is macOS.

I have many existing databases and schemas in my development docker postgres. So I am currently stumped as to how to upgrade safely without destroying old data.

Can advise?

Also I think a long term is to figure out how to have data files outside the docker (i.e. inside my host system) so that everytime I want to upgrade my docker image for postgres I can do so safely without fear.

I like to ask about how to switch to such a setup as well.

Was that a typo: "the backup files which are dumped using 10.5"? Did you mean to say "10.3"? — Laurenz Albe
For host based data files, you can use a custom pg_data folder inside the container, and read-write bind mount it to a host folder. Keep in mind that Docker file IO is not that good, if you have many IO tasks. You can also have a scheduled pg_dumpall command, and just docker cp it outside regularly. — VikingPingvin
@LaurenzAlbe I do mean 10.5. I backuped the files using tableplus which in itself uses 10.5 driver. See the updated question with image — Kim Stacks
@VikingPingvin oh... hmm what would be a good practice then? Still better off to keep the data files inside the docker? — Kim Stacks
We use just simple named volumes for data storage in the postgres container in production. It sits inside a VM which has daily backups. I would keep the data files inside, and make regular backups just in case. Keeping them in volumes (or bind mounts) will mean that even if your pg container fails, it can restart easily and the volume will be there. Just don't accidentally prune the volume. — VikingPingvin

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-07-08T07:09:13

If I understand you correctly, you want to restore a custom format dump taken with 10.5 into a 10.3 database.

That won't be possible if the archive format has changed between 10.3 and 10.5.

As a workaround, you could use a “plain format” dump (option --format=plain) which does not have an “archive version”. But any problems during restore are yours to deal with, since downgrading PostgreSQL isn't supported.

You should always use the same version for development and production, and you should always use the latest minor release (currently 10.13). Everything else is asking for trouble.

backup as plain text like this: warning! the file will be huge. Around 17x more than regular custom format. My typical 90mb is now 1.75Gb
copy the backup file into the postgres container docker cp ~/path/to/dump/in-host-system/2020-07-08-1.dump <name_of_postgres_container>:/backups
go to the bash of your postgres container docker exec -it <name_of_postgres_container> bash
inside the bash of postgres container: psql -U username -d dbname < backups/2020-07-08-1.dump

That will work

How to upgrade the pg_restore in docker postgres image 10.3 to 10.5

What i tried

1 Answers