如何将docker postgres镜像10.3中的pg_restore升级到10.5 [英] How to upgrade the pg_restore in docker postgres image 10.3 to 10.5

问题描述

我将tableplus用于一般管理员.

I use tableplus for my general admin.

当前使用10.3版的docker postgres映像进行生产和本地主机开发.

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

由于tableplus已将其postgres 10驱动程序升级至10.5，所以我不能再使用pg_restore还原使用10.5 --format=custom转储的备份文件了.

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

有关我如何使用tableplus进行备份的图像，请参见.以及它如何使用10.5驱动程序

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

我收到的错误消息是pg_restore: [archiver] unsupported version (1.14) in file header

我在localhost中尝试将dockerfile中postgres的标签从10.3更改为10.5，但这没有用

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

原始dockerfile

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

到

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

我用于开发的主机系统是macOS.

My host system for development is macOS.

我的开发docker postgres中有许多现有的数据库和架构.因此，我目前对如何安全升级而不破坏旧数据感到困惑.

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.

可以建议吗?

我还认为，从长远来看，是要弄清楚如何在Docker外部(即主机系统内部)保存数据文件，以便每次我想为PostgreSQL升级Docker映像时，都可以安全地进行操作而不必担心.

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.

推荐答案

如果我对您的理解正确，那么您想将以10.5格式保存的自定义格式转储还原到10.3数据库中.

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

如果存档格式在10.3和10.5之间更改，则无法实现.

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

作为一种解决方法，您可以使用没有归档版本"的纯格式"转储(选项--format=plain).但是，由于不支持降级PostgreSQL，因此还原过程中遇到的任何问题都是您要处理的.

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.

您应始终使用相同的版本进行开发和生产，并且应始终使用最新的次要发行版(当前为10.13).其他一切都在麻烦.

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. 以纯文本格式备份，如下所示:警告！该文件将是巨大的.比常规自定义格式高17倍左右.我通常的90mb现在是1.75Gb
2. 将备份文件复制到postgres容器 docker cp ~/path/to/dump/in-host-system/2020-07-08-1.dump <name_of_postgres_container>:/backups
3. 转到您的postgres容器 docker exec -it <name_of_postgres_container> bash
的重击中
4. 在postgres容器的bash内:psql -U username -d dbname < backups/2020-07-08-1.dump

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
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

那行得通

这篇关于如何将docker postgres镜像10.3中的pg_restore升级到10.5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！