Hi Louis,

On Sun, Oct 17, 2021 at 7:49 AM Louis <lbmlist@hethcote.com> 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.