Closed maxbrueckl closed 6 months ago
can you try
docker exec -it postgres pg_dump -d djangodb -Z6 --schema=public --blobs -U djangouser > ~/tandoor.sql
I did. The logs show the same entries like above. This is the resulting tandoor.sql:
pg_dump: [archiver (db)] query failed: ERROR: could not identify an ordering operator for type oid
LINE 1: ...ssid = 'pg_extension'::regclass AND deptype = 'e' ORDER BY 3
^
HINT: Use an explicit ordering operator or modify the query.
pg_dump: [archiver (db)] query was: SELECT classid, objid, refobjid FROM pg_depend WHERE refclassid = 'pg_extension'::regclass AND deptype = 'e' ORDER BY 3
please run the original command adding -v
Container logs show the same entries as before. cat tandoor.sql:
pg_dumpall: executing SELECT pg_catalog.set_config('search_path', '', false)
--
-- PostgreSQL database cluster dump
--
-- Started on 2023-12-05 15:37:10 CET
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
pg_dumpall: executing SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2
--
-- Roles
--
pg_dumpall: executing SELECT provider, label FROM pg_catalog.pg_shseclabel WHERE classoid = 'pg_catalog.pg_authid'::pg_catalog.regclass AND objoid = '10'
CREATE ROLE djangouser;
ALTER ROLE djangouser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'XXXXX';
pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setdatabase = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = 'djangouser')
pg_dumpall: executing SELECT ur.rolname AS roleid, um.rolname AS member, a.admin_option, ug.rolname AS grantor FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid LEFT JOIN pg_authid um on um.oid = a.member LEFT JOIN pg_authid ug on ug.oid = a.grantor WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')ORDER BY 1,2,3
pg_dumpall: executing SELECT oid, spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, pg_catalog.pg_tablespace_location(oid), (SELECT array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM unnest(coalesce(spcacl,acldefault('t',spcowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM unnest(acldefault('t',spcowner)) AS init(init_acl) WHERE acl = init_acl)) AS spcacls) AS spcacl, (SELECT array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM unnest(acldefault('t',spcowner)) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM unnest(coalesce(spcacl,acldefault('t',spcowner))) AS permp(orig_acl) WHERE acl = orig_acl)) AS rspcacls) AS rspcacl, array_to_string(spcoptions, ', '),pg_catalog.shobj_description(oid, 'pg_tablespace') FROM pg_catalog.pg_tablespace WHERE spcname !~ '^pg_' ORDER BY 1
pg_dumpall: executing SELECT datname FROM pg_database d WHERE datallowconn AND datconnlimit != -2 ORDER BY (datname <> 'template1'), datname
pg_dumpall: dumping database "template1"...
\connect template1
pg_dumpall: running ""/usr/local/bin/pg_dump" -v -Fp 'user=djangouser dbname=template1'"
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning datapg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: implied data-only restore
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.22
-- Dumped by pg_dump version 11.22
-- Started on 2023-12-05 15:37:10 CET
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
-- Completed on 2023-12-05 15:37:10 CET
--
-- PostgreSQL database dump complete
--
pg_dumpall: dumping database "djangodb"...
pg_dumpall: running ""/usr/local/bin/pg_dump" -v --create -Fp 'user=djangouser dbname=djangodb'"
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: [archiver (db)] query failed: ERROR: could not identify an ordering operator for type oid
LINE 1: ...ssid = 'pg_extension'::regclass AND deptype = 'e' ORDER BY 3
^
HINT: Use an explicit ordering operator or modify the query.
pg_dump: [archiver (db)] query was: SELECT classid, objid, refobjid FROM pg_depend WHERE refclassid = 'pg_extension'::regclass AND deptype = 'e' ORDER BY 3
pg_dumpall: pg_dump failed on database "djangodb", exiting
Connect to postgres using
docker exec -it recipes_db psql -U djangouser -d djangodb
then run
reindex system djangodb
reindex database djangodb
and try again if you still get an error connect again and share the output from
SELECT classid, objid, refobjid FROM pg_depend
The output of dumpall after running the two reindex commands is still the same.
The mentioned SELECT query seems to not yield any results:
$ docker exec -it recipes_db psql -U djangouser -d djangodb
psql (11.22)
Type "help" for help.
djangodb=# SELECT classid, objid, refobjid FROM pg_depend
djangodb-#
Neither does SELECT classid, objid, refobjid FROM pg_depend WHERE refclassid = 'pg_extension'::regclass AND deptype = 'e' ORDER BY 3
.
If you join discord I can try to help you further - it's going to take some trial and error.
Hello I would appreciate help also since I am total noob. I just notice after a month that my session of Tandoor is not working anymore probably due to this :/
I had not the same problem, but after following https://docs.tandoor.dev/faq/#how-can-i-upgrade-postgres-major-versions in combination with https://kopfkrieg.dev/2022/05/06/postgres-datenbank-im-docker-container-upgraden/, because I use portainer and not docker-compose, I can startup all containers and I can login to the app, but when I click on a recipe I only get a 500-HTTP-Error and in the logs of the db container are some messages like
2023-12-10 17:14:21.862 UTC [652] ERROR: column cookbook_propertytype.fdc_id does not exist at character 252
2023-12-10 17:14:21.862 UTC [652] STATEMENT: SELECT "cookbook_propertytype"."id", "cookbook_propertytype"."name", "cookbook_propertytype"."unit", "cookbook_propertytype"."order", "cookbook_propertytype"."description", "cookbook_propertytype"."category", "cookbook_propertytype"."open_data_slug", "cookbook_propertytype"."fdc_id", "cookbook_propertytype"."space_id" FROM "cookbook_propertytype" WHERE ("cookbook_propertytype"."space_id" = 1 AND "cookbook_propertytype"."space_id" = 1) ORDER BY "cookbook_propertytype"."order" ASC
Any ideas?
Delete the contents of the database and try to import again. Also, make sure you are running the same version of tandoor as the exported version.
I'm having the same/similar problem after Postgres 11 upgrade. Web pages load, but when I click a recipe I get a similar error django.db.utils.ProgrammingError: column cookbook_propertytype.fdc_id does not exist
When I add a new column with ALTER TABLE cookbook_propertytype ADD fdc_id int;
it starts working again.
I don't know what's stored in the fdc_id
column and I don't know if it will incapacitate the tool later down the road. But we will see :)
EDIT:
OK More fallout:
Looks like I needed to add more columns:
ALTER TABLE cookbook_mealplan ADD from_date date;
ALTER TABLE cookbook_mealplan ADD to_date date;
After adding these three columns to two tables, things started to play along nice. No more errors on the logs.
EDIT 2: (At this point I'm reversing the upgrade and doing QA, I think) :D Since the from/to dates are added I think date column needed to be dropped in order to Meal Plan work. One more thing to do is:
ALTER TABLE cookbook_mealplan DROP COLUMN date;
Delete the contents of the database and try to import again. Also, make sure you are running the same version of tandoor as the exported version.
@smilerz Thanks for your suggestions. When I understand you right, you suggest that I make a pgdump of the postgres 11 db, which was used by Tandoor 1.5.6. Then I shall make an update to Postgress 12, import with psql and start still the Tandoor 1.5.6. And only after that I shall update also tandoor to the latest version. Correct? I assumed, that Tandoor 1.5.6 can not handle postgres 12 db.
I'm having the same/similar problem after Postgres 11 upgrade. Web pages load, but when I click a recipe I get a similar error
django.db.utils.ProgrammingError: column cookbook_propertytype.fdc_id does not exist
When I add a new column with
ALTER TABLE cookbook_propertytype ADD fdc_id int;
it starts working again.I don't know what's stored in the
fdc_id
column and I don't know if it will incapacitate the tool later down the road. But we will see :)EDIT: OK More fallout: Looks like I needed to add more columns:
ALTER TABLE cookbook_mealplan ADD from_date date;
ALTER TABLE cookbook_mealplan ADD to_date date;
After adding these three columns to two tables, things started to play along nice. No more errors on the logs.
EDIT 2: (At this point I'm reversing the upgrade and doing QA, I think) :D Since the from/to dates are added I think date column needed to be dropped in order to Meal Plan work. One more thing to do is:
ALTER TABLE cookbook_mealplan DROP COLUMN date;
@emresaglam: Thank you for sharing your progress. I hope, that there is a less "hacky" variant of updating.
@smilerz Thanks for your suggestions. When I understand you right, you suggest that I make a pgdump of the postgres 11 db, which was used by Tandoor 1.5.6. Then I shall make an update to Postgress 12, import with psql and start still the Tandoor 1.5.6. And only after that I shall update also tandoor to the latest version. Correct? I assumed, that Tandoor 1.5.6 can not handle postgres 12 db.
Since you are having issues I'm suggesting doing one change at a time so that we can isolate what's going wrong.
I'd also suggest you upgrade to PSQL16 eventually as 12 will stop working roughly a year from now.
Thanks, I will try it this evening and will report, what worked.
Hello I would appreciate help also since I am total noob. I just notice after a
manually adding columns is always going to lead to trouble - can you start from fresh restore and share the logs from tandoor at first start?
I tried the following procedure without luck, as I can now not login anymore in the web app (the application always changes to the setup screen, so it seems it doesn't see the data in the database):
Any ideas? Would it be possible to have a video call on discord in the beginning of January?
Please follow the directions and syntax outlined here.
@scepbjoern Do you get any problems when starting the container? For me the migration 200 was failing due to a tag with duplicate name. This lead to this exact error as following migrations are not executed when one migration is failing. But, the system started still without the applied migrations.
Issue
As the support of Postgres version <= 11 was dropped, I started the update progress. I ran
on my existing database. The resulting tandoor.sql looks like this:
Any idea how I can proceed?
Tandoor Version
1.5.10
OS Version
Ubuntu 22.04.3
Setup
Docker / Docker-Compose
Reverse Proxy
Traefik
Other
No response
Environment file
Docker-Compose file
Relevant logs
Log from database container: