Re: [Fwd: [Fwd: Re: UK BST Time Settings]]

> Adam Tauno Williams wrote:
> > On Mon, 2009-04-06 at 19:33 +0100, Ken Smith wrote:
> >> Adam Tauno Williams wrote:
> >> {Snippage}
> >> Thank you for this. Its very confusing.
> >> I've looked at the trace created by enabling logging and its looking to 
> >> me as if the database actually has entries that are an hour ahead of 
> >> what they should be.
> >> My SQL is very basic. Could someone please suggest an sql statement that 
> >> will subtract an hour from every entry.
> > I recently used SQLs like:
> > UPDATE date_x
> >   SET start_date = start_date - '1 HOUR'::INTERVAL,
> >       object_version = object_version + 1
> > WHERE (start_date BETWEEN '2009-03-11' AND '2009-03-31') 
> >  AND (date_part('Hour', start_date) -5) != 0;
> >
> > UPDATE date_x
> >   SET end_date = end_date - '1 HOUR'::INTERVAL,
> >       object_version = object_version + 1
> > WHERE (end_date BETWEEN '2009-03-11' AND '2009-03-31') 
> >   AND (date_part('Hour', end_date) -5) != 0;
> >  - to fix a database where the timezone/DST was incorrect.
> > BACKUP YOUR DATA BEFORE MAKING MANUAL DATABASE CHANGES!  
> > If you have one-hour-off appointments in the DST revision range
> > [2009-03-11' AND '2009-03-31'] created via ZideLook I'd strongly suspect
> > that your PC/Workstation has outdated time zone tables (i.e. Outlook
> > made the error).
> Thank you for this Adam. I have set up another OGo system on which to 
> test this before I risk the live system. I have a backup of the database 
> created with
> su postgres -c "pg_dump -Ft -f /var/lib/pgsql/ogo_backup.tar OGo"
> I have moved the file to the other machine and am attempting to restore 
> it to the database there by using
> su postgres
> bash-3.2$ pg_restore -v -c ogo_backup.tar > restore.log
> The restore log shows lots of encouraging entries suggesting that data 
> is being restored to the database but

I usually restore to my test server with:

Backup:
pg_dump --username=OGo --host=localhost --format=t --file=OGo.Ft.pg OGo

Restore:
dropdb OGo
createdb -E UTF-8 -O OGo OGo
pg_restore --verbose --format=t --dbname=OGo /home/awilliam/OGo.Ft.pg

If the versions are the same you can use format "c" (binary) which is
faster and produces a smaller file.

I believe this is documented in the "PostgreSQL" chapter of WMOGAG.

> OGo=> select * from date_x;
> date_id | object_version | owner_id | access_team_id | parent_date_id | 
> start_date | end_date | cycle_end_date | type | title | location | 
> absence | resource_names | write_access_list | is_absence | 
> is_attendance | is_conflict_disabled | travel_duration_before | 
> travel_duration_after | notification_time | db_status | apt_type | 
> calendar_name | source_url | fbtype | sensitivity | busy_type | 
> importance | last_modified | evo_reminder | ol_reminder | online_meeting 
> | associated_contacts | keywords
> ---------+----------------+----------+----------------+----------------+------------+----------+----------------+------+-------+----------+---------+----------------+-------------------+------------+---------------+----------------------+------------------------+-----------------------+-------------------+-----------+----------+---------------+------------+--------+-------------+-----------+------------+---------------+--------------+-------------+----------------+---------------------+----------
> (0 rows)
> OGo=>
> Reveals an empty database.
> and
> OGo=> \l
>       List of databases
>  Name    |  Owner   | Encoding
> -----------+----------+----------
> OGo       | postgres | UTF8
> postgres  | postgres | UTF8
> template0 | postgres | UTF8
> template1 | postgres | UTF8
> (4 rows)
> OGo=>
> Shows that the data hasn't gone into the wrong database.
> So what gives. I'm not a Postres guru, as must be obvious. So what 
> subtle point am I missing here??

-- 
OpenGroupware developer: [email protected]
<http://whitemiceconsulting.blogspot.com/>
OpenGroupare & Cyrus IMAPd documenation @
<http://docs.opengroupware.org/Members/whitemice/wmogag/file_view>

-- 
OpenGroupware.org Users
[email protected]
http://mail.opengroupware.org/mailman/listinfo/users