2

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

enter image description here

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.

Kim Stacks
  • 10,202
  • 35
  • 151
  • 282
  • Was that a typo: "the backup files which are dumped using 10.5"? Did you mean to say "10.3"? – Laurenz Albe Jul 08 '20 at 06:49
  • 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 Jul 08 '20 at 06:54
  • @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 Jul 08 '20 at 06:54
  • @VikingPingvin oh... hmm what would be a good practice then? Still better off to keep the data files inside the docker? – Kim Stacks Jul 08 '20 at 06:56
  • 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 Jul 08 '20 at 06:57
  • I am bad at Docker so chances are good I am going to say the wrong thing here. So you're saying you have one extra docker volume just to store the data and this volume is separate from the volume used by the docker postgres container? – Kim Stacks Jul 08 '20 at 07:46
  • @VikingPingvin thanks to Laurenz's answer I think i temporarily resolve my issue. but I think I want a more permanent solution by upgrading my postgres in localhost and production somehow. Do you mind if I create a new question more targeted for that and have your answer there? – Kim Stacks Jul 08 '20 at 08:02
  • my more "how to upgrade postgres in docker container" focused question is here https://stackoverflow.com/questions/62790302/how-to-upgrade-my-postgres-in-docker-container-while-maintaining-my-data-10-3-t @VikingPingvin – Kim Stacks Jul 08 '20 at 08:10
  • @VikingPingvin I now realize I already have a named volume for data storage. And what I did was I spin up another postgres container but using an updated postgres version as image. But this new postgres container is pointing to the same volume. Then, everything seems to work as per normal. Can I ask how do you do backups? Do you backup the postgres into custom format files or do you simply backup the volume folder in the host OS? – Kim Stacks Jul 18 '20 at 15:38
  • For manual backups, I use the pg_dumpall command, which creates an SQL file. For scheduled backups, we back up the complete VM, including the docker volume which the database uses. No custom formats used. But then again, I am not a database expert, don't really know the best practices, but these methods are simple and they work. – VikingPingvin Jul 19 '20 at 17:12

1 Answers1

1

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.

  1. 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 enter image description here
  2. 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
  3. go to the bash of your postgres container docker exec -it <name_of_postgres_container> bash
  4. inside the bash of postgres container: psql -U username -d dbname < backups/2020-07-08-1.dump

That will work

Kim Stacks
  • 10,202
  • 35
  • 151
  • 282
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Sorry, but what's "a plain format dump which does not have an "archive version"."? – Kim Stacks Jul 08 '20 at 07:31
  • Simply omit the `--format=custom`. You restore it with `psql`. – Laurenz Albe Jul 08 '20 at 07:34
  • Oh my god, it actually worked. hahaha. i will update your answer with more notes and mark it as correct – Kim Stacks Jul 08 '20 at 07:53
  • casting my net wide for help. I have posted a separate question https://stackoverflow.com/questions/62790302/how-to-upgrade-my-postgres-in-docker-container-while-maintaining-my-data-10-3-t dealing with the more long term issue of how to upgrade safely between minor versions while maintaining data and schema within postgres docker containers. If you can help that would be great. No obligations of course. Thank you once again – Kim Stacks Jul 08 '20 at 08:12
  • Out of curiosity, what's the need for the custom format? I usually just use pg_dumpall or pg_dump. And that creates an SQL file basically. – VikingPingvin Jul 08 '20 at 11:43
  • 1
    @VikingPingvin The custom format is much better. 1) data are compressed 2) you can selectively extract parts of the dump 3) you can restore the dump parallelized with the `-j` option. – Laurenz Albe Jul 08 '20 at 11:49
  • This does *not* answer the question that @KimStacks asked. – pablete Jan 25 '21 at 20:20
  • @pablete Hm? Then why was the answer accepted? – Laurenz Albe Jan 25 '21 at 20:28
  • @LaurenzAlbe becaused it solved the problem that the op had, and thus the op *mistankenly* (imo) accepted the answer (it was helpful to him). Still, it does not answer "How to upgrade the pg_restore in docker postgres image 10.3 to 10.5" – pablete Jan 26 '21 at 02:29
  • 1
    @pablete You may be right. Perhaps OP wasn't trying to downgrade at all. – Laurenz Albe Jan 26 '21 at 07:30