The foundation for your business
Fork me on GitHub
[ledgersmb-users] Re: Problem moving from 1.7.35 to 1.8.13
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[ledgersmb-users] Re: Problem moving from 1.7.35 to 1.8.13

I am running PostgreSQL 11.13 on my system, so the tarball instance database is 11.13.

The original docker-compose.yml uses PostgreSQL 9.6. I modified docker-compose.yml to use postgres:11.13-alpine. This works.

I have a few details to clean up, but this looks like it will get me into the 1.8 line.

Thanks for the help, I'll report more data as I progress.

Thanks again,


On Sun, 17 Oct 2021, Erik Huelsmann wrote:

Hi Louis,

On Sun, Oct 17, 2021 at 7:49 AM Louis <..hidden..> wrote:


      I found a couple issues with my process, which I believe are resolved.

      Now I generate both a roles backup and a db backup from the tarball
      instance and save them. I then add:

         CREATE DATABASE hethcote WITH TEMPLATE = template0 ENCODING = 'UTF8'
         LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

         ALTER DATABASE hethcote OWNER TO lbmoore;

         \connect hethcote

      to the beginning of the db.sqlc from the backup.  (Without this the
      database gets added to postgres' default database postgres.)

This is something "pg_dump" can do for you with the -C option. See
      then I execute (via script):

         docker-compose pull

         docker-compose up -d

         docker exec -i ledgersmb-docker_postgres_1 psql -Upostgres -W

         docker exec -i ledgersmb-docker_postgres_1 psql -Upostgres -W

         docker cp ROLES ledgersmb-docker_postgres_1:/docker-entrypoint-initdb.d
         docker cp DB  ledgersmb-docker_postgres_1:/docker-entrypoint-initdb.d

         docker exec -i ledgersmb-docker_postgres_1 psql -Upostgres -W
           --command="\i /docker-entrypoint-initdb.d/ROLES"

         docker exec -i ledgersmb-docker_postgres_1 psql -Upostgres -W
           --command="\i /docker-entrypoint-initdb.d/DB"  2>&1 | tee log

         docker exec -i ledgersmb-docker_postgres_1 psql -Upostgres -W

         docker exec -i ledgersmb-docker_postgres_1 psql -Upostgres -W

      Everything runs as expected.

From the log fragment you show below, I'm not sure it did. That error is really severe as it's failing to create a
critical component (a sequence).

      Then I connect to setup.pl and see the database "hethcote" and select it.

      Then I have it upgrade the db from 1.7.35 to 1.7.36. While it is doing the
      upgrade it fails with:

         psql:./sql/modules/Roles.sql:193: ERROR: relation
            "exchangerate_type_id_seq" does not exist

         CONTEXT: SQL statement "GRANT ALL ON exchangerate_type_id_seq TO

         PL/pgSQL function lsmb__grant_perms(text,text,text) line 6 at EXECUTE

         dbversion: hethcote, company: 1.7.36

Yes. A critical component is missing. Even more: I expect *all* sequences to be missing (this just being the first
one you ran into), due to the syntax error you've shown below.
      Attempting to login via login.pl still fails with "Access denied: Bad

      Looking into the log from the database build it contains:

      psql:/docker-entrypoint-initdb.d/db.sqlc:24821: ERROR:  syntax error at or
      near "AS"
      LINE 2:     AS integer
      psql:/docker-entrypoint-initdb.d/db.sqlc:24824: ERROR:  relation
         "public.exchangerate_type_id_seq" does not exist
      psql:/docker-entrypoint-initdb.d/db.sqlc:24830: ERROR:  relation
         "public.exchangerate_type_id_seq" does not exist

      the command that failed is:

      -- Name: exchangerate_type_id_seq; Type: SEQUENCE; Schema: public; Owner:

      CREATE SEQUENCE public.exchangerate_type_id_seq
           AS integer
           START WITH 1
           INCREMENT BY 1
           NO MINVALUE
           NO MAXVALUE
           CACHE 1;

There's a hint in the docs regarding this statement and the error you've gotten:

If you check for comparison pg10: https://www.postgresql.org/docs/10/sql-createsequence.html vs pg9.6:
https://www.postgresql.org/docs/9.6/sql-createsequence.html, you'll find that the "AS integer" syntax has been added
in Pg 10. Did you create the dump file using a Pg10+ version of pg_dump and you're now trying to load it into <Pg10?


      I am still stuck but have a better process for consistency in rebuilding
      the environment.

Hope this helps!


http://efficito.com -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.

users mailing list -- ..hidden..
To unsubscribe send an email to ..hidden..