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 <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.