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

Hi Louis,

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

Ok,

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 https://www.postgresql.org/docs/12/app-pgdump.html#PG-DUMP-OPTIONS

then I execute (via script):

docker-compose pull

docker-compose up -d

docker exec -i ledgersmb-docker_postgres_1 psql -Upostgres -W
--command='\l'

docker exec -i ledgersmb-docker_postgres_1 psql -Upostgres -W
--command='\du'

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
--command='\l'

docker exec -i ledgersmb-docker_postgres_1 psql -Upostgres -W
--command='\du'

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:

Error!
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
lsmb_hethcote__exchangerate_edit"

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
username/password".

Looking into the log from the database build it contains:

...
COMMENT
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
CREATE SEQUENCE
ALTER TABLE
...

the command that failed is:

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

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!

Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.

Robust and Flexible. No vendor lock-in.