Upgrading from 2.22.21 to Most Recent

General support regarding TimeTrex, such as
configuring policies/taxes or processing payroll.
Locked
JeffersonKim
Posts: 26
Joined: Wed Aug 20, 2008 12:33 pm
Contact:

Upgrading from 2.22.21 to Most Recent

Post by JeffersonKim »

Yes, I know I haven't upgraded for over two years.

I tried following this post and am now installing a fresh install on a new machine:

http://forums.timetrex.com/viewtopic.php?p=6573#p6573

So I did a fresh install on a new machine, and quit after seeing the terms and conditions.

I tried running Restore_Backup.bat for my old database that backed up using backup_database.bat, and I get errors.

I'm guessing this has to do with very incompatible databases due to my lack of upgrading :[

Any ideas on how I can get past the error messages when trying to run Restore_backup.bat ?
Attachments
restore backup error.jpg
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Upgrading from 2.22.21 to Most Recent

Post by shaunw »

Did you modify the new restore_database.bat file as described in the thread you linked to?
JeffersonKim
Posts: 26
Joined: Wed Aug 20, 2008 12:33 pm
Contact:

Re: Upgrading from 2.22.21 to Most Recent

Post by JeffersonKim »

shaunw wrote:Did you modify the new restore_database.bat file as described in the thread you linked to?
Yes.
JeffersonKim
Posts: 26
Joined: Wed Aug 20, 2008 12:33 pm
Contact:

Re: Upgrading from 2.22.21 to Most Recent

Post by JeffersonKim »

JeffersonKim wrote:
shaunw wrote:Did you modify the new restore_database.bat file as described in the thread you linked to?
Yes.
For clarification, I only saw one instance of "template1" that I had to replace.

I think there's some sort of password incompatibility issue.
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Upgrading from 2.22.21 to Most Recent

Post by shaunw »

Can you post the restore_database.bat file here and the first 50 lines of your .SQL file.
JeffersonKim
Posts: 26
Joined: Wed Aug 20, 2008 12:33 pm
Contact:

Re: Upgrading from 2.22.21 to Most Recent

Post by JeffersonKim »

shaunw wrote:Can you post the restore_database.bat file here and the first 50 lines of your .SQL file.

restore_database.bat [I just uninstalled and reinstalled so I haven't changed "template1" to "timetrex"]

@echo off
SET PGPASSWORD=2de8176f7
SET PGDATABASE=timetrex

IF "%1" == "" GOTO NOFILE
echo.
echo Attempting to restore SQL dump file: %1...
echo.
echo.
echo -----WARNING-----WARNING-----WARNING-----WARNING-----
echo.
echo RESTORING THIS FILE WILL COMPLETELY OVERWRITE ALL EXISTING DATA...
echo.
echo ARE YOU SURE YOU WANT TO DO THIS?
echo.
echo -----WARNING-----WARNING-----WARNING-----WARNING-----
echo.

pause
echo.
echo NO REALLY, ALL EXISTING DATA WILL BE DELETED.
echo.
echo ARE YOU REALLY SURE YOU WANT TO DO THIS?
echo.
pause

"C:/TimeTrex/postgresql\bin\psql.exe" -1 -f "%1" -h localhost -U postgres -p 5433 template1
echo Done.
echo.

GOTO END
:NOFILE
echo.
echo ERROR: Please specify the SQL dump file that you wish to restore.
:END


timetrex_database_20100116.sql
--
-- PostgreSQL database cluster dump
--

\connect postgres

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET escape_string_warning = 'off';

--
-- Roles
--

DROP ROLE postgres;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN PASSWORD 'md5e0e104429ca8b62d47fc1edb59cf80de';
DROP ROLE timetrex;
CREATE ROLE timetrex;
ALTER ROLE timetrex WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD 'md57311789ec78fb27611c56a65bf331ad1' VALID UNTIL 'infinity';






--
-- Database creation
--

DROP DATABASE timetrex;
CREATE DATABASE timetrex WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'SQL_ASCII';


\connect postgres

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

\connect template1

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

\connect timetrex

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: absence_policy; Type: TABLE; Schema: public; Owner: timetrex; Tablespace:
--

CREATE TABLE absence_policy (
id integer NOT NULL,
company_id integer NOT NULL,
name character varying NOT NULL,
type_id integer NOT NULL,
over_time smallint DEFAULT 0 NOT NULL,
accrual_policy_id integer,
premium_policy_id integer,
pay_stub_entry_account_id integer,
created_date integer,
created_by integer,
updated_date integer,
updated_by integer,
deleted_date integer,
deleted_by integer,
deleted smallint DEFAULT 0 NOT NULL
);


ALTER TABLE public.absence_policy OWNER TO timetrex;

--
-- Name: absence_policy_id_seq; Type: SEQUENCE; Schema: public; Owner: timetrex
--

CREATE SEQUENCE absence_policy_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;


ALTER TABLE public.absence_policy_id_seq OWNER TO timetrex;

--
-- Name: absence_policy_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: timetrex
--

ALTER SEQUENCE absence_policy_id_seq OWNED BY absence_policy.id;


--
-- Name: absence_policy_id_seq; Type: SEQUENCE SET; Schema: public; Owner: timetrex
--

SELECT pg_catalog.setval('absence_policy_id_seq', 1, false);


--
-- Name: accrual; Type: TABLE; Schema: public; Owner: timetrex; Tablespace:
--

CREATE TABLE accrual (
id integer NOT NULL,
user_id integer NOT NULL,
accrual_policy_id integer NOT NULL,
type_id integer NOT NULL,
user_date_total_id integer,
time_stamp timestamp with time zone NOT NULL,
amount numeric DEFAULT 0 NOT NULL,
created_date integer,
created_by integer,
updated_date integer,
updated_by integer,
deleted_date integer,
deleted_by integer,
deleted smallint DEFAULT 0 NOT NULL
);


ALTER TABLE public.accrual OWNER TO timetrex;

--
-- Name: accrual_balance; Type: TABLE; Schema: public; Owner: timetrex; Tablespace:
--

CREATE TABLE accrual_balance (
id integer NOT NULL,
user_id integer NOT NULL,
accrual_policy_id integer NOT NULL,
balance numeric DEFAULT 0 NOT NULL,
banked_ytd integer DEFAULT 0 NOT NULL,
used_ytd integer DEFAULT 0 NOT NULL,
awarded_ytd integer DEFAULT 0 NOT NULL,
created_date integer,
deleted smallint DEFAULT 0 NOT NULL
);


ALTER TABLE public.accrual_balance OWNER TO timetrex;

--
-- Name: accrual_balance_id_seq; Type: SEQUENCE; Schema: public; Owner: timetrex
--

CREATE SEQUENCE accrual_balance_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Upgrading from 2.22.21 to Most Recent

Post by shaunw »

Have you tried the restore without modifying the restore_database.bat at all? Does it still fail?

Assuming you didn't get far enough into the installation process to have any tables created, if you remove the first 50 or so lines from your .SQL file, up to but not including this line:
\connect timetrex
That should help too.
JeffersonKim
Posts: 26
Joined: Wed Aug 20, 2008 12:33 pm
Contact:

Re: Upgrading from 2.22.21 to Most Recent

Post by JeffersonKim »

shaunw wrote:Have you tried the restore without modifying the restore_database.bat at all? Does it still fail?

Assuming you didn't get far enough into the installation process to have any tables created, if you remove the first 50 or so lines from your .SQL file, up to but not including this line:
\connect timetrex
That should help too.
Even without modifying the restore_database.bat it gives the same message.


I tried removing the 50 or so lines up to but NOT including the line you indicated and it looks like it worked because it was on "INSERT 0 1" and kept repeating that line for a long time.

I just changed the SALT to be the same as the old file.

Then I restarted the service.

Logged on, hit submit, and it went to 500 Internal Error.

Then I ran the Installer web based shortcut on the Start menu to upgrade the database.

Then. . . was able to log in! Crossing my fingers no future problems.

Thanks for your help!
Locked