Migration / update problem new SSD

[edbo]

@herrdeh

with @bert’s instructions

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.

[herrdeh (https://tryton-dach.org)]

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

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…

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

Cheers,
Wolf

[dave (David Harper)]

@herrdeh

Now I can log into my tryton - but do not find any of my data. No articles, no parties, no bookings…

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

@herrdeh

Finally I gave in with docker,

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.

@herrdeh

Can the above error be resolved easily ?

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

@herrdeh

What is the recommended strategy to move and existing tryton docker to another machine?

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.

@herrdeh

Or should I install tryton 5.4 at the new docker to avoid the update trouble (for the moment)?

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 How to run Tryton using Docker 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/

---

3. 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 - #2 by dave.

    # 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
   

[edbo]

@herrdeh

Well, I had a running tryton, installed with pip, followed these instructions

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

@herrdeh

but do not find any of my data. No articles, no parties, no bookings…

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.

[herrdeh (https://tryton-dach.org)]

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

[edbo]

@herrdeh

It looks as if I do not write to the right database. True?

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 - #24 by dave 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. If you are going to modify your sql dump, make sure you have a backup!

[edbo]

@herrdeh

Threre are ~/.config/tryton/5.6/profiles.cfg and tryton.conf as well…

Those are the configuration files for your desktop client.

[herrdeh (https://tryton-dach.org)]

Thank you for your care.
I checked with pgadmin. Actually, there are now several databases available:

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;

[dave (David Harper)]

@herrdeh

Actually, I do not really understand what names the standard docker install procedure creates.

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?

[herrdeh (https://tryton-dach.org)]

@dave

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.

[dave (David Harper)]

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?

[edbo]

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.

[herrdeh (https://tryton-dach.org)]

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.

[herrdeh (https://tryton-dach.org)]

This is how databases look at the source side:

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

[edbo]

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?

[dave (David Harper)]

@herrdeh

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

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

[herrdeh (https://tryton-dach.org)]

@edbo

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

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…

[edbo]

Yeah I think you have to start from scratch again. Drop your tryton_test database and recreate it. Then import the dump.

[edbo]

I think you have to go even further back. You probably also have removed your newly created tryton user. So I would suggest to start Migration / update problem new SSD here.

1. Create the new user
2. Create the new database
3. Read your dump file

[herrdeh (https://tryton-dach.org)]

Hm. Something must be wrong. I created a new database and a new role (should be the same as “user”). Tried the import as above - and end up with a billion of errors, very similar to those above.

How can we deal with that more efficiently?