Re: Problem moving from 1.7.35 to 1.8.13
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.)
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.
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
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;
I am still stuck but have a better process for consistency in rebuilding the environment.
Louis
On Fri, 15 Oct 2021, lsmbdev wrote:
Hi Louis,
Are you able to login to setup.pl with the pg admin user (normally postgres in the containers) with no company name?
Once logged in like that you should see a list of databases present on the cluster.
Regards David Godfrey
Sent from my Galaxy
-------- Original message -------- From: Louis lbmlist@hethcote.com Date: 15/10/21 02:11 (GMT+08:00) To: lsmbdev lsmbdev@sbts.com.au Cc: users@lists.ledgersmb.org Subject: RE: [ledgersmb-users] Problem moving from 1.7.35 to 1.8.13
Ok, I've been trying various iterations of this without success.
I have scripted it so that I can replicate with accuracy. Basically I
- Log in to existing tarball instance with a known good username and
password
Log out of tarball instance and shutdown the tarball starman.
ensure all of previously existing docker instance is removed
use the docker-compose method of starting postges and lsmb
containers.
do a pg_dump of tarball instance "hethcote" database
create the same good username and password in the container's postgres
and check that the md5sum passwords match.
copy the pg_dump into the postgres container
docker exec psql with \i to load the pg_dump from tarball instance to
docker instance
- log in using known good username from tarball instance into the docker
instance and get a bad username/password message.
Is there something I missing?
Louis
On Sat, 18 Sep 2021, lsmbdev wrote:
Hi Louis,
If you have no objections to trying our docker containers I'd suggest doing a backup and, after installing docker-compose, restore to the containerised setup.
Most development work, and many production instance are now using docker containers, so it's considered to be quite stable and easily supported.
Regards David Godfrey
Sent from my Galaxy
-------- Original message -------- From: Louis lbmlist@hethcote.com Date: 18/9/21 23:43 (GMT+08:00) To: lsmbdev lsmbdev@sbts.com.au Cc: Louis lbmlist@hethcote.com Subject: RE: [ledgersmb-users] Problem moving from 1.7.35 to 1.8.13
This is installed from source. The database has been rolled along since SQLedger 2.x and migrated to LedgerSMB at the time of the fork. PostgreSQL itself is at v11 but has rolled along from v6 (don't recall orig version from the SQLedger days.)
The hba file has always trusted local socket connections (pause for moment of shame) so password was blank until the 1.6 series. Since 1.6 password was just a random character since it was ignored. This is how 1.7.35 has been working. This is true for postgres as well as my own lbmoore.
So decades of bad practices have finally caught up to me with 1.8.
I'm at a loss as to where to start with this mess.
Louis
On Sat, 18 Sep 2021, lsmbdev wrote:
Morning Louis,
The most common cause of this would be using the incorrect credentials with setup.pl
Remember that the username is not a username you use to log into your company. In many cases it is simply "postgres"
Is your installation via a container, or did you install from source?
Regards David Godfrey
Sent from my Galaxy
-------- Original message -------- From: Louis lbmlist@hethcote.com Date: 18/9/21 09:28 (GMT+08:00) To: users@lists.ledgersmb.org Subject: [ledgersmb-users] Problem moving from 1.7.35 to 1.8.13
Hey all,
I have not been able to move to the 1.8 series from the 1.7 series. I think I must not have authentication correct and the 1.8 is expecting a different setup than previous version.
This is a starman installation. I will backup the DB, move the code into place and run setup.pl. Setup pops open an error reading:
Localhost:5762 says
Access denied (500): bad username/password
I'm using the same credentials as with 1.7.35.
I can roll back and forth easily, but I don't see what the debug path should be.
Any help would be appreciated.
Louis
users mailing list -- users@lists.ledgersmb.org To unsubscribe send an email to users-leave@lists.ledgersmb.org
Hi Louis,Re the error, that is one I ran into as well.Although Erik didn't. I'm a few thousand km away from my system (with only a mobile phone) right now so can't easily grab a copy of the fix for you.I will chat with Erik and ask him to apply a fix for the next release, along with sending a patch file to you.As for not being able to log in via setup.pl, I'm not surprised. There are a couple of things that are likely to be an issue.- due to the upgrade not completing it's likely the correct roles haven't been established yet.- you may need to reset your user password via setup.pl, then once logged in set it again from preferences.This correctly sets the user password timeout.Also regarding usernames....- Does lbmoore exist?- Does it have a password set ideally via the setup.pl tool initially. - is lbmoore the user for setup.pl and the user for login.plNormally the database admin user (the one the db is owned by is left as the default `postgres` using something else requires manual config to both create the user and grant it correct permissions. Also, the login.pl user should never be the same as the setup.pl user.If using containers, the admin (setup.pl) user should be configured in the docker-compose fileIf you have any questions just yell out.Somewhat more "realtime" assistance can be obtained by using our chat room on the matrix platform.A web client can be found at https://app.element.io/#/room/#ledgersmb:matrix.orgRegardsDavid Godfrey SB Tech Services Sent from my Galaxy -------- Original message --------From: Louis lbmlist@hethcote.com Date: 17/10/21 13:49 (GMT+08:00) To: lsmbdev lsmbdev@sbts.com.au Cc: users@lists.ledgersmb.org Subject: RE: [ledgersmb-users] Problem moving from 1.7.35 to 1.8.13 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 hethcoteto the beginning of the db.sqlc from the backup. (Without this the database gets added to postgres' default database postgres.)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.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.36Attempting to login via login.pl still fails with "Access denied: Bad username/password".Looking into the log from the database build it contains:...COMMENTpsql:/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 existpsql:/docker-entrypoint-initdb.d/db.sqlc:24830: ERROR: relation "public.exchangerate_type_id_seq" does not existCREATE SEQUENCEALTER 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;I am still stuck but have a better process for consistency in rebuilding the environment.LouisOn Fri, 15 Oct 2021, lsmbdev wrote:> Hi Louis, > > Are you able to login to setup.pl with the pg admin user (normally postgres in the containers) with no company name?> > Once logged in like that you should see a list of databases present on the cluster.> > Regards> David Godfrey > > > > Sent from my Galaxy> > > -------- Original message --------> From: Louis lbmlist@hethcote.com> Date: 15/10/21 02:11 (GMT+08:00)> To: lsmbdev lsmbdev@sbts.com.au> Cc: users@lists.ledgersmb.org> Subject: RE: [ledgersmb-users] Problem moving from 1.7.35 to 1.8.13> > > > Ok, I've been trying various iterations of this without success.> > I have scripted it so that I can replicate with accuracy. Basically I> > 1) Log in to existing tarball instance with a known good username and> password> > 2) Log out of tarball instance and shutdown the tarball starman.> > 3) ensure all of previously existing docker instance is removed> > 4) use the docker-compose method of starting postges and lsmb> containers.> > 5) do a pg_dump of tarball instance "hethcote" database> > 6) create the same good username and password in the container's postgres> and check that the md5sum passwords match.> > 7) copy the pg_dump into the postgres container> > 8) docker exec psql with \i to load the pg_dump from tarball instance to> docker instance> > 9) log in using known good username from tarball instance into the docker> instance and get a bad username/password message.> > Is there something I missing?> > Louis> > > On Sat, 18 Sep 2021, lsmbdev wrote:> > > Hi Louis,> >> > If you have no objections to trying our docker containers I'd suggest doing a backup and, after> > installing docker-compose, restore to the containerised setup.> >> >> > Most development work, and many production instance are now using docker containers, so it's> > considered to be quite stable and easily supported.> >> > Regards> > David Godfrey> >> >> > Sent from my Galaxy> >> >> > -------- Original message --------> > From: Louis lbmlist@hethcote.com> > Date: 18/9/21 23:43 (GMT+08:00)> > To: lsmbdev lsmbdev@sbts.com.au> > Cc: Louis lbmlist@hethcote.com> > Subject: RE: [ledgersmb-users] Problem moving from 1.7.35 to 1.8.13> >> >> > This is installed from source. The database has been rolled along since> > SQLedger 2.x and migrated to LedgerSMB at the time of the fork. PostgreSQL> > itself is at v11 but has rolled along from v6 (don't recall orig version> > from the SQLedger days.)> >> > The hba file has always trusted local socket connections (pause for moment> > of shame) so password was blank until the 1.6 series. Since 1.6 password> > was just a random character since it was ignored. This is how 1.7.35 has> > been working. This is true for postgres as well as my own lbmoore.> >> > So decades of bad practices have finally caught up to me with 1.8.> >> > I'm at a loss as to where to start with this mess.> >> > Louis> >> >> >> > On Sat, 18 Sep 2021, lsmbdev wrote:> >> > > Morning Louis,> > >> > > The most common cause of this would be using the incorrect credentials with setup.pl> > >> > > Remember that the username is not a username you use to log into your company.> > > In many cases it is simply "postgres"> > >> > > Is your installation via a container, or did you install from source?> > >> > > Regards> > > David Godfrey> > >> > > Sent from my Galaxy> > >> > >> > > -------- Original message --------> > > From: Louis lbmlist@hethcote.com> > > Date: 18/9/21 09:28 (GMT+08:00)> > > To: users@lists.ledgersmb.org> > > Subject: [ledgersmb-users] Problem moving from 1.7.35 to 1.8.13> > >> > >> > > Hey all,> > >> > > I have not been able to move to the 1.8 series from the 1.7 series. I> > > think I must not have authentication correct and the 1.8 is expecting a> > > different setup than previous version.> > >> > > This is a starman installation. I will backup the DB, move the code into> > > place and run setup.pl. Setup pops open an error reading:> > >> > > Localhost:5762 says> > >> > > Access denied (500): bad username/password> > >> > > I'm using the same credentials as with 1.7.35.> > >> > > I can roll back and forth easily, but I don't see what the debug path> > > should be.> > >> > > Any help would be appreciated.> > >> > > Louis> > >> > >> > > _______________________________________________> > > users mailing list -- users@lists.ledgersmb.org> > > To unsubscribe send an email to users-leave@lists.ledgersmb.org> > >> > >> > >> >>
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!
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.36Yes. 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%C2%A0-- Hosted accounting and ERP. Robust and Flexible. No vendor lock-in.
participants (3)
-
Erik Huelsmann -
Louis -
lsmbdev