Migration / update problem new SSD

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.

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

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.

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.

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

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

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

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

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

Those are the configuration files for your desktop client.

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;

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?

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.

Using the web client, when you connect to Tryton, what database is shown on the login screen where it asks for your user name, and is this read-only, or can you change which database to use?

Ahhh, crap … that’s the problem you are dumping ALL databases. That should not be happening. You have to use

$ sudo docker exec tryton-postgres pg_dump <your_database_name> > <your_backupname>.sql

So you are only dumping your database and nothing else.

The GUI says I’m connecting to “tryton-test” - which is the DB I set up in PIP.
When I try to use “postgres” or “template1”, it says that “ir_cache” does not exist.

This is how databases look at the source side:
Bildschirmfoto von 2020-09-14 13-53-39

Actually, there is a DB called “template1” Should I just throw that one out?

elbo’s dump command does this:

$ sudo docker exec tryton-postgres pg_dump postgres > dump-nur-postgres1409.sql
pg_dump: error: connection to database "postgres" failed: FATAL:  role "root" does not exist

Can you try:

$ sudo docker exec tryton-postgres pg_dump -U postgres postgres > dump-nur-postgres1409.sql

Or does pgAdmin not have a possiblity to backup your database?

I wouldn’t worry too much about it, it is a standard template database.

This one produced a 7MB file (good size…), readable etc.

Then I did:

  • start postgres

and

mymachine ~$  sudo psql -U postgres -d tryton_test -f 1409-dump-nur-postgres.sql 
    [sudo] Passwort für wd: 
    Password for user postgres: 
    SET
    SET
    SET
    SET
    SET
     set_config
------------
     
    (1 row)

    SET
    SET
    SET
    SET
    psql:1409-dump-nur-postgres.sql:28: ERROR:  relation "account_account_id_seq" already exists
    ALTER TABLE
    SET
    SET
    psql:1409-dump-nur-postgres.sql:66: ERROR:  relation "account_account" already exists
    ALTER TABLE
    COMMENT
    psql:1409-dump-nur-postgres.sql:87: ERROR:  relation "account_account_deferral_id_seq" already exists
    ALTER TABLE
    psql:1409-dump-nur-postgres.sql:108: ERROR:  relation "account_account_deferral" already exists

Carrying on to create lots of “already exists” errors. Looks as if the command does not overwrite existing entries…