Migration / update problem new SSD

Support System Administrator
#1

Some days ago, I did a clean install of ubuntu on a new SSD. Alongside came a new docker install with tryton 5.6 onboard. I tried to find a simple enough way to just move the dockerfile, but all instructions I found were awfully complicated. In the end, I tried to update my old (5.4) database to 5.6, export a sqldump and import that to the new docker. Now when I log into the docker webif, I get this error (no matter which password I type):

Traceback (most recent call last):
  File "/usr/local/lib/python37/dist-packages/trytond/wsgipy", line 109, in dispatch_request
return endpoint(request, **requestview_args)
  File "/usr/local/lib/python37/dist-packages/trytond/protocols/dispatcherpy", line 48, in rpc
request, database_name, *requestrpc_params)
  File "/usr/local/lib/python37/dist-packages/trytond/protocols/dispatcherpy", line 63, in login
database_name, user, parameters, context=context)
  File "/usr/local/lib/python37/dist-packages/trytond/securitypy", line 33, in login
user_id = Userget_login(loginname, parameters)
  File "/usr/local/lib/python37/dist-packages/trytond/res/userpy", line 639, in get_login
LoginAttemptadd(login)
  File "/usr/local/lib/python37/dist-packages/trytond/res/userpy", line 761, in wrapper
return func(cls, login[:clsloginsize], *args, **kwargs)
  File "/usr/local/lib/python37/dist-packages/trytond/res/userpy", line 775, in add
'ip_network': str(ip_network),
  File "/usr/local/lib/python37/dist-packages/trytond/model/modelsqlpy", line 159, in wrapper
return func(cls, *args, **kwargs)
  File "/usr/local/lib/python37/dist-packages/trytond/model/modelsqlpy", line 676, in create
clstrigger_create(records)
  File "/usr/local/lib/python37/dist-packages/trytond/model/modelstoragepy", line 76, in wrapper
return func(*args, **kwargs)
  File "/usr/local/lib/python37/dist-packages/trytond/model/modelstoragepy", line 166, in trigger_create
triggers = Triggerget_triggers(cls__name__, 'create')
  File "/usr/local/lib/python37/dist-packages/trytond/ir/triggerpy", line 178, in get_triggers
('on_%s' % mode, '=', True),
  File "/usr/local/lib/python37/dist-packages/trytond/model/modelsqlpy", line 1317, in search
cursorexecute(*select)
  File "/usr/local/lib/python37/dist-packages/trytond/backend/postgresql/databasepy", line 69, in execute
cursorexecute(self, sql, args)
psycopg2ProgrammingError: column aaction does not exist
LINE 1: SELECT "a""id" AS "id", "a""action" AS "action", "a""acti
                             ^
HINT:  Perhaps you meant to reference the column "aactive"

My questions are:

  • Can the above error be resolved easily ?
  • What is the recommended strategy to move and existing tryton docker to another machine?
  • Or should I install tryton 5.4 at the new docker to avoid the update trouble (for the moment)?

Sorry to cause so much problems again…
Wolf

PS: At the first glance, the docker solution looked promising. But after some time of dealing with it I get the impression that you really should have some experience with docker if you want to use it with tryton. Tryton is not so easy - but docker adds another thick layer of complexity… ):

Migration 5.4 > 5.6, sqldump import
#2

I thinks this is the same issue as in Migration 5.4 > 5.6, sqldump import It seems you forgot to manually update the database with trytond-admin

Just copy-and-paste? :wink: … they call it a container so it should be easy to move. In practice it’s more complicated. As I’m not using Docker but Python virtual environments I cannot help you further with this, but https://appfleet.com/blog/how-to-transfer-move-a-docker-image-to-another-system/ can maybe help you.

Indeed Docker adds an extra layer of complexity and for me it doesn’t give me any extra’s. I’m using Python virtual environments and are up-and-running in minutes. Installing extra modules is a pip install away. The same for updating Tryton. When I want to dig into the database I don’t need to enter docker-commands etc. Once you have a good working environment you do a pip freeze > requirements.txt which spits out all the packages you have. The main reason to use a Python virtual environment is to keep the original ecosystem clean. When you messed up things, you throw it away and start again.

You are on Ubuntu and if you know your way around with the commandline a bit IMO it’s easier to setup Tryton with a virtual environment than using Docker.

#3

Hm.
In the end, I ran
sudo docker run --link tryton-postgres:postgres -e DB_PASSWORD=[mysecretpw] -it tryton/tryton trytond-admin -d tryton --all
and the system didnt complain.

I can import the dump into the tryton-postgres container without complains, but when I try to login, I get the error message of message #1.
But what can be wrong?

I tried that - but in the end I did not succeed in restarting the containers. My expectation in docker was that I at least should be able just to move the whole dockerfile. But this seems not possible. Really frustrating.

Now, I’m on the way to set up an environment with pip. Docker needs so much knowledge, after some months of struggle, if asked, I’d not recommend to install tryton in docker to somebody who is not familiar with it.

#4

You are almost there. You import the dump and then you have to run the trytond-admin command to get the database upgraded to version 5.6 because your database is for version 5.4. Because it’s all Docker I cannot help you any further with this.

If you have any question about this, don’t hesitate to ask.

#5

Ah. This is interesting - thank you so much. I thought that the update work needs to be carried out at the SOURCE machine. You tell me it needs to be done at the TARGET machine. True?

There are two parts:

  • manual adaptions described here,
  • and the trytond-admin thing

Do both need to be carried out at the target system?

I’ll never hesitate… (;

#6

Completely true :clap:

I assume that SOURCE = Tryton version 5.4 and TARGET = Tryton version 5.6

What I always do:

  1. setup a new virtual environment (TARGET) with the requirements file from the production (SOURCE) version and install the newest version of Tryton
  2. dump the production (SOURCE) database to a sql-file
  3. create a new database (TARGET) and import the sql-file
  4. do the manual thing if needed (most of the time this is directly done on the database (TARGET))
  5. execute the trytond-admin thing to upgrade the database (TARGET), use the -vv flag for logging
1 Like
#7

@edbo
Thank you so much you’re spending so much time and effort. Thus my hope to finally manage it did not drop below zero…(:

This was the point I struggled. Sorry having to ask: What is the “requirements file”?

#8

Hopefully you are heating up now :fire:

When you have a working version of Tryton installed in a virtual environment you can store the list of installed packages with pip freeze --local > requirements.txt When you open that file you’ll see the package and their version. With that file you can create an exact copy of the virtual environment. Just create a new empty virtual environment and copy the requirements.txt into it. Then activate the environment and do pip install -r requirements.txt. Pip will install all the packages listed at the right version. I use this as part of my backup so I can recreate my environment very easily.

#9

Thank you very much. So it’s just about the modules installed? - As I came from docker, I could not do it your way, but it was not so much trouble to do this manually. Is it enough just to install my modules?

Finally I gave in with docker, and - with @bert’s instructions - succeeded in installing the pip version and installed my modules and language. Easy!

Staggered through the next steps, executed

trytond-admin -c /etc/tryton/trytond.conf -d tryton_test --all

and started trytond with

trytond -c /etc/tryton/trytond.conf

(without the -c option, the client throws an error “no sqlite.db found”.

But in the end…

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/tryton/jsonrpc.py", line 279, in __request
    response = self.__transport.request(
  File "/usr/lib/python3.8/xmlrpc/client.py", line 1153, in request
    return self.single_request(host, handler, request_body, verbose)
  File "/usr/lib/python3.8/xmlrpc/client.py", line 1183, in single_request
    raise ProtocolError(
xmlrpc.client.ProtocolError: <ProtocolError for localhost:8000/tryton_test/: 404 NOT FOUND>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/tryton/gui/main.py", line 277, in do_activate
    common.Login()
  File "/usr/local/lib/python3.8/dist-packages/tryton/common/common.py", line 900, in __init__
    func(parameters)
  File "/usr/local/lib/python3.8/dist-packages/tryton/rpc.py", line 87, in login
    result = connection.common.db.login(username, parameters, language)
  File "/usr/lib/python3.8/xmlrpc/client.py", line 1109, in __call__
    return self.__send(self.__name, args)
  File "/usr/local/lib/python3.8/dist-packages/tryton/jsonrpc.py", line 298, in __request
    raise Fault(str(e.errcode), e.errmsg)
tryton.jsonrpc.Fault: 404

Fault: 404

    $ trytond -c /etc/tryton/trytond.conf
        32331 139823660414784 [2020-09-11 14:19:05,501] INFO werkzeug  * Running on http://0.0.0.0:8000/ (Press CTRL+C to quit)

looks o.k., but the web client says “method not allowed”.

What’s going on now…?

#10

Please don’t be scared with the details below …

  1. Do you have a running PosgreSQL database-server?
    • check with sudo systemctl status postgresql is should output some information and saying that it’s active
    • If not, install it with apt-get, initialize it and see if it’s running
    • Create a tryton-user with password in PostgreSQL
    • Create an empty database which will be filled by Tryton (createdb -O <tryton-user> tryton_test
    • Update your trytond.conf and add the tryton-user and password in the database uri like `uri = postgresql://<tryton-user>:<password>@localhost:5432/
  2. If you cannot connect to the database-server change /var/lib/pgsql/data/pg_hba.conf and change the word ident to md5
  3. Use the desktop-client first before using the webclient. The webclient needs to be installed separately. Using the desktop-client makes sure that Tryton-server is running properly. Then you can move to the next step.
#11

Can you give a link to those instructions? And on which version of Ubuntu are you? If I know the version, I can spin up a clean VM and walk through the steps of installing Tryton with pip in a virtual environment.

#12

Well, I had a running tryton, installed with pip, followed these instructions
provided by @bert. :+1:t3: :+1:t3: :+1:t3:

I found that I didn’t start postgres correctly. Now I can log into my tryton - but do not find any of my data. No articles, no parties, no bookings… :frowning_face:

Allright. Enough for today - it’s weekend…
Thank you so much for now - very much appreciated!

Cheers,
Wolf

#13

This suggests the backup you restored was from when you were experiencing a Backup problem - booking data missing.

If you want to give it another try, and you are able to find a backup with your booking data in, then hopefully the instructions below will help. If not, I understand, and perhaps these instructions will be useful to someone else.

Yes, it is caused by using a series 5.4 database with a series 5.6 server.

I will try and write a topic in How To/System Administrator soon on Backing Up and Restoring Tryton when Running on Docker. Briefly, one way is to dump the database from the postgres container, and backup the files from the /var/lib/tryton/db directory from inside the tryton container. Then restore them on the new system.

Yes, that probably would have avoided the problem. Then you could have worried about upgrading later.

To clean out all Docker containers, images and volumes from your system, and then get Tryton running again:

(Note: see Using Docker to run Tryton for detailed explainations of what some of these commands are doing)

  1. Clean up you docker containers, images and volumes, so you are back to a fresh docker install.

    WARNING don’t do this if you are running any other docker containers you want to keep as these next few instructions will remove them and all the data associated with them.

     sudo docker container rm --force $( sudo docker container ls --all -q )
 sudo docker image rm --force $( sudo docker image ls -q )
 sudo docker volume rm $( sudo docker volume ls -q )

  2. Get Tryton up and running using version you were running before (5.4 in this case), Note: change [your_secret_postgres_password] to a suitably secure password, and replace tryton-backup.sql with the file that contains the correct backup:

     # Create a network and volumes for use by the tryton containers
 sudo docker network create tryton
 sudo docker volume create tryton-data
 sudo docker volume create tryton-database

 # Start postgres
 POSTGRES_PASSWORD=[your_secret_postgres_password]
 sudo docker run \
     --name tryton-postgres \
     --env PGDATA=/var/lib/postgresql/data/pgdata \
     --env POSTGRES_DB=tryton \
     --env POSTGRES_PASSWORD=${POSTGRES_PASSWORD} \
     --mount source=tryton-database,target=/var/lib/postgresql/data \
     --network tryton \
     --detach \
     postgres:12

 # Restore your database
 sudo docker exec \
     --interactive \
     tryton-postgres \
     psql -U postgres <tryton-backup.sql

 # Start tryton version 5.4
 sudo docker run \
     --name tryton \
     --env DB_HOSTNAME=tryton-postgres \
     --env DB_PASSWORD=${POSTGRES_PASSWORD} \
     --mount source=tryton-data,target=/var/lib/trytond/db \
     --network tryton \
     --publish 127.0.0.1:8000:8000 \
     --detach \
     tryton/tryton:5.4

    You should now be able to connect to your Tryton system at http://localhost:8000/

  1. You are now ready to update to Tryton version 5.6 (if you want):

     # Stop tryton version 5.4
 sudo docker stop tryton
 sudo docker rm tryton

    Run any manual updates that are required, see How to apply SQL query on docker.

     # Update the database to 5.6
 sudo docker run \
     --env DB_HOSTNAME=tryton-postgres \
     --env DB_PASSWORD=${POSTGRES_PASSWORD} \
     --network tryton \
     --interactive \
     --tty \
     --rm \
     tryton/tryton:5.6 \
     trytond-admin -d tryton --all

 # Start tryton version 5.6
 sudo docker run \
     --name tryton \
     --env DB_HOSTNAME=tryton-postgres \
     --env DB_PASSWORD=${POSTGRES_PASSWORD} \
     --mount source=tryton-data,target=/var/lib/trytond/db \
     --network tryton \
     --publish 127.0.0.1:8000:8000 \
     --detach \
     tryton/tryton:5.6
#14

Personally I wouldn’t recommend that. Using pip to globally (system-wide) install packages can make your system unstable or even worse.

Did you read in the dumped sql file into the database?

  1. copy you sql dump to /tmp
  2. stop Tryton
  3. sudo su - postgres
  4. psql -d tryton_test < /tmp/<name of your sql dump file>
  5. exit
  6. trytond-admin -c <your trytond.conf> -d tryton_test --all -v
  7. start Tryton

When you did this and don’t see any data, then there is something wrong with your sql dump.

1 Like
#15

Thank you so much for these detailed instructions. I tried:

postgres@Simux-380:~$ psql -d tryton_test < /tmp/13.09.2020-bis\ KD-RE069.sql 
Password for user postgres: 
SET
SET
SET
DROP DATABASE
ERROR:  current user cannot be dropped
ERROR:  role "postgres" already exists
ALTER ROLE
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
UPDATE 1
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
COMMENT
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
REVOKE
GRANT
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

It looks as if I do not write to the right database. True?
Actually, I’m not sure whether I should use /etc/tryton/trytond.conf . Threre are ~/.config/tryton/5.6/profiles.cfg and tryton.conf as well…

Cheers,
Wolf

#16

Hmm indeed, the output is very weird. When you open the file with a text editor can you read it? It seems that you are dropping the existing database to create a new one. If you used Delete invoice, table of accounts etc I think you have to make a new sql dump. You can use point 3 for that, but for the second command just use:

$ sudo docker exec tryton-postgres pg_dump > <your backupname>.sql

Using the -c -C adds some extra data into your dump which you don’t need. You can also edit your existing dump and remove any lines which says something about dropping or creating databases and users. This lines are probably at the top of the file. :warning: If you are going to modify your sql dump, make sure you have a backup!

1 Like
#17

Those are the configuration files for your desktop client.

#18

Thank you for your care.
I checked with pgadmin. Actually, there are now several databases available:
Bildschirmfoto von 2020-09-14 12-21-41
AKAIK, DB-name “tryton-test” with owner “postgres” in the role “tryton-test” is what pip created.

Actually, I do not really understand what names the standard docker install procedure creates. So I first need to know what the standard procedure actually creates, and then decide what is more easy - change the names in the docker or in the pip install. Guess the latter.

The dump is readable and actually contains contents I recently added. So I’d say it’s proper. First lines below.

Cheers,
Wolf

--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Drop databases (except postgres and template1)
--

DROP DATABASE tryton;




--
-- Drop roles
--

DROP ROLE postgres;


--
-- Roles
--

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md537ba9751ad003a0a947d556c884e13aa';






--
-- Databases
--

--
-- Database "template1" dump
--

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.2 (Debian 12.2-2.pgdg100+1)
-- Dumped by pg_dump version 12.2 (Debian 12.2-2.pgdg100+1)

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;

UPDATE pg_catalog.pg_database SET datistemplate = false WHERE datname = 'template1';
DROP DATABASE template1;
--
-- Name: template1; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';


ALTER DATABASE template1 OWNER TO postgres;

\connect template1

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;

--
-- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE template1 IS 'default template for new databases';


--
-- Name: template1; Type: DATABASE PROPERTIES; Schema: -; Owner: postgres
--

ALTER DATABASE template1 IS_TEMPLATE = true;


\connect template1

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;

--
-- Name: DATABASE template1; Type: ACL; Schema: -; Owner: postgres
--

REVOKE CONNECT,TEMPORARY ON DATABASE template1 FROM PUBLIC;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;


--
-- PostgreSQL database dump complete
--

--
-- Database "postgres" dump
--

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.2 (Debian 12.2-2.pgdg100+1)
-- Dumped by pg_dump version 12.2 (Debian 12.2-2.pgdg100+1)

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;

DROP DATABASE postgres;
--
-- Name: postgres; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE postgres WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';


ALTER DATABASE postgres OWNER TO postgres;

\connect postgres

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;

--
-- Name: DATABASE postgres; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE postgres IS 'default administrative connection database';


--
-- Name: account_account_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.account_account_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.account_account_id_seq OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: account_account; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.account_account (
    id integer DEFAULT nextval('public.account_account_id_seq'::regclass) NOT NULL,
    closed boolean DEFAULT false,
    code character varying,
    company integer NOT NULL,
    create_date timestamp(6) without time zone,
    create_uid integer,
    end_date date,
    general_ledger_balance boolean DEFAULT false,
    "left" integer NOT NULL,
    name character varying NOT NULL,
    note text,
    parent integer,
    party_required boolean DEFAULT false,
    reconcile boolean DEFAULT false,
    replaced_by integer,
    "right" integer NOT NULL,
    second_currency integer,
    start_date date,
    template integer,
    template_override boolean DEFAULT false,
    type integer,
    write_date timestamp(6) without time zone,
    write_uid integer,
    CONSTRAINT account_account_id_positive CHECK ((id >= 0))
);


ALTER TABLE public.account_account OWNER TO postgres;

--
-- Name: TABLE account_account; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.account_account IS 'Account';


--
-- Name: account_account_deferral_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.account_account_deferral_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.account_account_deferral_id_seq OWNER TO postgres;

--
-- Name: account_account_deferral; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.account_account_deferral (
    id integer DEFAULT nextval('public.account_account_deferral_id_seq'::regclass) NOT NULL,
    account integer NOT NULL,
    amount_second_currency numeric NOT NULL,
    create_date timestamp(6) without time zone,
    create_uid integer,
    credit numeric NOT NULL,
    debit numeric NOT NULL,
    fiscalyear integer NOT NULL,
    write_date timestamp(6) without time zone,
    write_uid integer,
    CONSTRAINT account_account_deferral_id_positive CHECK ((id >= 0))
);


ALTER TABLE public.account_account_deferral OWNER TO postgres;
#19

It should create a database user called postgres (the default) and a database called tryton (based on the -e POSTGRES_DB=tryton part). Although from the first part of your database dump it looks like your data might be in a database called postgres?

#20

When I pgAdmin the (source) docker install, I see both, a DB called “tryton”, one called “postgres”. The latter holds all my bookings, parties etc.