PostgreSQL – PG_UPGRADE tool failed: invalid “unknown” user column

FROM postgres:9.6
# my databases need German locale; 
# if you just need en_US, comment the next two lines out.
RUN localedef -i de_DE -c -f UTF-8 -A /usr/share/locale/locale.alias de_DE.UTF-8< br />ENV LANG de_DE.utf8

root@host: cd /home/hj/pg-problem/docker
root@host: service docker start
root@host: docker build -t hj/failed-update -f Dockerfile .
root@host: docker run -it --rm -p 5431:5432 -v /home/hj/pg-problem/data :/var/lib/postgresql/data:z --name failed-update -e POSTGRES_PASSWORD=secret hj/failed-update

hj@host: docker exec -it failed-update bash

root@container: su postgres
postgres@container: psql 
postgres@container: alter table  alter column  type text ;
postgres@container: \q
postgres@container: dump_db  /var/lib/postgresql/data/dbREPAIRED.sql

postgres@host: createdb 
postgres@host: psql  
Because I was on a laptop with limited disk space, I deleted the docker stuff:
 < p>
root@host: docker rm $(docker ps -a -q)
root@host: docker rmi $(docker images -q)

Postgresql update from 9.6 to 10.4 (in Fedora 28) makes me confused: a table in a database has a column of data type "unknown". I am happy to delete the column, but because I can not start the postgresql service (because "found the old version of the database format") , I don’t have permission to access the database. More details:

postgresql-setup –upgrade failed.

/var/lib/pgsql/upgrade_postgresql.log attributed this failure to For a column with a data type of "unknown": "...Check invalid'unknown' user column: fatal.... Check tables_using_unknown.txt". And "tables_using_unknown.txt" specifies a column in a table I hope I can delete, but not, Because I can’t get the server to start:

systemctl start postgresql.service failed
systemctl status postgresql.service complains about "old version of the database"

I didn’t find it on Fedora 28 The obvious way to install postgresql 9.6.

Is there a way to delete columns without a running server? Or at least produce a database dump? Or can I force the upgrade tool to delete columns whose data type is "unknown"? Or am I missing other obvious solutions?

This is what worked for me in the end:

>I used a Docker container (on the same machine ) And postgres 9.6 to access the "old" database directory,
> convert the problematic column from "unknown" to "text" in the container, and
> dump the relevant database to a file on the container host , And then
>load the dumped database into the postgres 10.4 environment.

Not pretty, but working. More detailed:

I will postgresql data directory (Fedora /Var/lib/pgsql/data/) to a new empty directory /home/hj/pg-problem/.

I created a read called "Docker-pg-problem" Dockerfile (text file)

FROM postgres:9.6
# my databases need German locale; 
# if you just need en_US, comment the next two lines out.
RUN localedef -i de_DE -c -f UTF-8 -A /usr/share/locale/locale.alias de_DE.UTF-8
ENV LANG de_DE.utf8

And save it as the only file in the new empty folder /home/hj/pg-problem/docker/.

I started the docker daemon and ran a container, the container Use the data in my copy of the problematic data (in /home/hj/pg-problem/data/) as the data directory of the postgres 9.6 server in the container. (Note: The "docker build" command in the third line requires one A valid internet connection, it takes a while, and it should finish saying "Successful Build").

root@host: cd /home/hj/pg-problem/docker
root@host: service docker start
root@host: docker build -t hj/failed-update -f Dockerfile .< br />root@host: docker run -it --rm -p 5431:5432 -v /home/hj/pg-problem/data:/var/lib/postgresql/data:z --name failed-update -e POSTGRES_PASSWORD=secret hj/failed-update

Then, I opened a terminal in the container to repair the database:

hj@host: docker exec -it failed-update bash

Inside the container, I repaired and dumped the database:

root@container: su postgres
postgres@container : psql 
postgres@container: alter table  alter column  type text;
postgres@container: \q
postgres@container: dump_db  /var/lib/postgresql/data/dbREPAIRED.sql

I dump the db directly into the data directory, so that I can easily access the dump file from the docker host.

On the docker host, the dumped database is obviously located in /home/hj/pg-problem/data/dbREPAIRED.sql, from there I can load it into postgresql 10:

postgres@host: createdb 
postgres@host: psql   
Because I was on a laptop with limited disk space, I deleted the docker stuff:
 
 
root@host: docker rm $(docker ps -a -q)
root@host: docker rmi $(docker images -q)