LedgerSMB
The foundation for your business
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, Louis On Sun, 17 Oct 2021, Erik Huelsmann wrote:
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.
_______________________________________________ users mailing list -- ..hidden.. To unsubscribe send an email to ..hidden..