Обсуждение: How do I convert a timestamp with time zone to local time?

On Tuesday 29 July 2008 15:07:46 Rob Richardson wrote:
> Greetings!
>
> What is the best way to convert a time expressed as a timestamp with
> time zone into a timestamp in the local time zone without knowing what
> the local time zone is?
>
> Thank you.
>
> RobR
SELECT extract (epoch from your_time_field) from your_table;
SELECT to_timestamp(your_epoch_field) from your_table;

--
Valter Douglas Lisbôa Jr.
Sócio-Diretor
Trenix - IT Solutions
"Nossas Idéias, suas Soluções!"
www.trenix.com.br
contato@trenix.com.br
Tel. +55 19 3402.2957
Cel. +55 19 9183.4244

Thank you very much, sir.

After posting I realized that my question did not cover my problem.  I
also need to calculate if a given time is within daylight savings time
or not.

The actual situation is this:  I have a table that contains the time at
which an event occurred and an estimate of how long it will be before a
second event occurs.  That interval is likely to be in the range of 2-3
days.  I need to handle the case in which that span includes the moment
at which daylight savings time status changes.  The table stores the
event time in both local time and UTC time.  So, I need a way to
calculate whether the time at the end of the interval will be DST or
not.

I also have a table named "sys_info" that contains a single record for
system-wide information.  I can expand that table if I want.  The
easiest way I can think of to do what I need is merely to store the DST
start and end dates in that table, and then see if the date I'm checking
is between them or not.  But that won't work either, since the dates
change.  In the US, DST runs from the second Sunday in March to the
first Sunday in November.  I can hard-code to that rule (which is what
I'm doing for now), but I can't very well store it in a database.  And
do other countries use DST, and if so, how do they define it?

I think that, if I'm careful, I can write my function so I'll be
converting everything I need to UTC before I start, and then I won't
convert anything back until the very end, and I'll never have to worry
about whether I'm spanning the DST change or not.

It would still be nice to have a function that will check DST for a
given date, but I hope I can avoid needing it.

Thanks again!

RobR

Greetings again!

A few days ago, I visited a customer's site to talk about administering
our system, which is developed around a PostGres database.  One of the
topics was how to back up the database.  I described the process of
using PgAdmin to back up and restore a database, and I said a backup
should be done every night.  I was asked how to automate the procedure,
and I couldn't answer.  A database administrator said, "There's got to
be a way.  Otherwise, PostGres wouldn't have survived".  I agree with
him.  The only answers I've found on the Internet involve creating a
password-less account and using that to run pg_dump.  What is the
official best way to automatically back up a PostGres database?

Thank you very much.

RobR

On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote:
> I was asked how to automate the procedure,
> and I couldn't answer.

The options are manifold!

    http://www.postgresql.org/docs/8.3/interactive/backup.html

On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote:

> Greetings again!
>
> A few days ago, I visited a customer's site to talk about
> administering
> our system, which is developed around a PostGres database.  One of the
> topics was how to back up the database.  I described the process of
> using PgAdmin to back up and restore a database, and I said a backup
> should be done every night.  I was asked how to automate the
> procedure,
> and I couldn't answer.  A database administrator said, "There's got to
> be a way.  Otherwise, PostGres wouldn't have survived".  I agree with
> him.  The only answers I've found on the Internet involve creating a
> password-less account and using that to run pg_dump.  What is the
> official best way to automatically back up a PostGres database?

There's no one best way.

A simple way is to use pg_dump or pg_dumpall, running on the same
machine
as the database connecting via a unix socket using ident authentication
to dump a consistent view of the database out to a file.

http://www.postgresql.org/docs/8.3/interactive/backup.html discusses
several
other ways.

Cheers,
   Steve

On Tue, Jul 29, 2008 at 1:28 PM, Christophe <xof@thebuild.com> wrote:

>> I was asked how to automate the procedure,
>> and I couldn't answer.
>        http://www.postgresql.org/docs/8.3/interactive/backup.html


Regarding the SQL backup option for small databases, I use an OS task
scheduler ( *nix Cron-job, MS task-scheduler) to automatically call my
custom script file designed to handle that backing up of my databases.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Slony-I replication is also a viable choice for backups.

On Tue, Jul 29, 2008 at 1:34 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Tue, Jul 29, 2008 at 1:28 PM, Christophe <xof@thebuild.com> wrote:
>
>>> I was asked how to automate the procedure,
>>> and I couldn't answer.
>>        http://www.postgresql.org/docs/8.3/interactive/backup.html
>
>
> Regarding the SQL backup option for small databases, I use an OS task
> scheduler ( *nix Cron-job, MS task-scheduler) to automatically call my
> custom script file designed to handle that backing up of my databases.
>
>
> --
> Regards,
> Richard Broersma Jr.
>
> Visit the Los Angeles PostgreSQL Users Group (LAPUG)
> http://pugs.postgresql.org/lapug
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
- John L Cheng

On Tue, Jul 29, 2008 at 2:24 PM, Rob Richardson
<Rob.Richardson@rad-con.com> wrote:
> Greetings again!
>
> A few days ago, I visited a customer's site to talk about administering
> our system, which is developed around a PostGres database.  One of the
> topics was how to back up the database.  I described the process of
> using PgAdmin to back up and restore a database, and I said a backup
> should be done every night.  I was asked how to automate the procedure,
> and I couldn't answer.  A database administrator said, "There's got to
> be a way.  Otherwise, PostGres wouldn't have survived".  I agree with
> him.  The only answers I've found on the Internet involve creating a
> password-less account and using that to run pg_dump.  What is the
> official best way to automatically back up a PostGres database?

For future reference, you'll get less scattered ansewrs if you tell us
what OS you're running on, specifically whether or not it's unix or
windows.  In unix you can write a handy dandy bash shell script like
this:

#!/bin/bash
if (! (pg_dump dbname > /dir/filename.sql)); then
    echo "Backup failed"|sendmail -s "admin alert" "myname@mydomain";
fi;

or something like that to run as a crontab job.

On 2008-07-29 23:47, John Cheng wrote:
> Slony-I replication is also a viable choice for backups.

No, it's not. Redundancy is not a substitute for backups. Slony will not
help you if you do by mistake "delete from important_table" - as a copy
will also have all rows deleted.

For backups I'd recommend for example pg_dump and duplicity run from cron.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

On Tue, Jul 29, 2008 at 04:24:08PM -0400, Rob Richardson wrote:
> him.  The only answers I've found on the Internet involve creating a
> password-less account and using that to run pg_dump.  What is the
> official best way to automatically back up a PostGres database?

using pg_dump - yes.
making it's account passwordless - not necessarily.
you can use various methods of getting access to database without
password. you can use "ident sameuser" authentication method in
pg_hba.conf. you can use pgpass file, use can use PGPASSWORD environment
variable. there are also another ways, but these look the best for me.

depesz

--
Linked in: http://www.linkedin.com/in/depesz
jid/gtalk: depesz@depesz.com
aim:       depeszhdl
skype:     depesz_hdl

Tomasz Ostrowski wrote:
> On 2008-07-29 23:47, John Cheng wrote:
>> Slony-I replication is also a viable choice for backups.
>
> No, it's not. Redundancy is not a substitute for backups. Slony will not
> help you if you do by mistake "delete from important_table" - as a copy
> will also have all rows deleted.
>
> For backups I'd recommend for example pg_dump and duplicity run from cron.

Yep ... there's nothing like having a plain-text, or at least stable
format (-Fc) copy of your data around for disaster recovery. It's not
particularly time and space efficient, but it's certainly reassuring.

For anything important, especially where you cannot afford to lose a
whole day's work, I'd also want to consider using log shipping based
PITR. That way, you lose at most (depending on configuration) say 15
minutes.

Real-time replication to a remote site with slony or similar can help
protect you against fire, theft, etc though.

--
Craig Ringer

I created this script sometime ago and it works fine for me and
others.. Maybe it might work for you

http://www.zeroaccess.org/postgresql-backup

1.0RC1 is pretty stable I have ran it for 3 weeks without any problems



Quoting "Rob Richardson" <Rob.Richardson@rad-con.com>:

> Greetings again!
>
> A few days ago, I visited a customer's site to talk about administering
> our system, which is developed around a PostGres database.  One of the
> topics was how to back up the database.  I described the process of
> using PgAdmin to back up and restore a database, and I said a backup
> should be done every night.  I was asked how to automate the procedure,
> and I couldn't answer.  A database administrator said, "There's got to
> be a way.  Otherwise, PostGres wouldn't have survived".  I agree with
> him.  The only answers I've found on the Internet involve creating a
> password-less account and using that to run pg_dump.  What is the
> official best way to automatically back up a PostGres database?
>
> Thank you very much.
>
> RobR
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.