TIMESTAMP difference

On Dec 10, 2007 2:13 PM, rihad <rihad@mail.ru> wrote:

Hi, is there a way to get the difference in hours between two
timestamps?

SELECT (EXTRACT (EPOCH FROM TIMESTAMP '20071211 00:00') - EXTRACT
(EPOCH FROM TIMESTAMP '20071209 01:00')) * INTERVAL '1 second';

Hi first post here, I hope you can help.

We are running 8.1 on Windows 2003 server and have had a server crash
over the weekend. A virus is suspected - we maintain an app server on
someone else's network, though we do have anti-virus running, the
symptoms were worrying - so we had to wipe and reinstall the OS and all
programs. We had recovered the 8.1 folder and the data off the crashed
computer prior to the wipe, but the last good .backup file is a few days
older than that.

Are there step by step instructions on restoring from the folder itself
instead of a backup file? Is it even possible?

Thanks,

Keith

am Mon, dem 10.12.2007, um 12:30:14 -0800 mailte Keith Turner folgendes:

Hi first post here, I hope you can help.

We are running 8.1 on Windows 2003 server and have had a server crash
over the weekend. A virus is suspected - we maintain an app server on

Please don't hijack other threads, the original thread was 'TIMESTAMP
difference'.

(don't answer to an arbitrary other mail and change the subject. Every
mail contains references-header)

Are there step by step instructions on restoring from the folder itself
instead of a backup file? Is it even possible?

not really...

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On 12/10/07, Keith Turner <kturner@cloudsystems.com> wrote:

We are running 8.1 on Windows 2003 server and have had a server crash
over the weekend. A virus is suspected - we maintain an app server on
someone else's network, though we do have anti-virus running, the
symptoms were worrying - so we had to wipe and reinstall the OS and all
programs. We had recovered the 8.1 folder and the data off the crashed
computer prior to the wipe, but the last good .backup file is a few days
older than that.

Are there step by step instructions on restoring from the folder itself
instead of a backup file? Is it even possible?

I would try installing 8.1 again, stop the service, _copy_ your data
and config over the top of it, then start it again and see what
happens. Any problems with that should show up immediately in the
logs.

And if all else fails, you can just nuke the attempt and restore from
the older backups.

On 12/10/07, Keith Turner <kturner@cloudsystems.com> wrote:

We are running 8.1 on Windows 2003 server and have had a server crash
over the weekend. A virus is suspected - we maintain an app server on
someone else's network, though we do have anti-virus running, the
symptoms were worrying - so we had to wipe and reinstall the OS and

all

programs. We had recovered the 8.1 folder and the data off the crashed
computer prior to the wipe, but the last good .backup file is a few

days

older than that.

Are there step by step instructions on restoring from the folder

itself

instead of a backup file? Is it even possible?

Trevor Talbot wrote:

I would try installing 8.1 again, stop the service, _copy_ your data
and config over the top of it, then start it again and see what
happens. Any problems with that should show up immediately in the
logs.

And if all else fails, you can just nuke the attempt and restore from
the older backups.

I did this - it took a couple of tries, but it worked.

For the record, here's a description of the problem and solution:

Problem (before I became involved): Someone had turned off RPC service
while trying to fix something. Someone else rebooted the server and all
the windows services failed on restart. Panicked and suspecting a virus
they reinstalled the OS, but had the wit to savethe postgres folders but
couldn't make a .backup file. And the last backup file was the night
before, and they had done a lot of work that would be lost if that was
used.

Solution:
It wasn't a simple matter of just copying over the data folder. There
are reasons having to do with not letting the client see you sweat and
to do it in the BG. So on a fresh system we did as suggested above,
copied over the files. One step that seems to be required (at least in
this case) was a clean new install of postgres, stopping it immediately
after the install, not one that had been used in any way.

Required: The postgres files from the crashed server.

Step 1.
Clean install of same version of Postgres (8.1) on second computer using
same superuser and password.

Step 2.
Stop postgtres service

Step 3.
Copy over Data folder replacing the one that was created on install

Step 4.
Start postgres

Step 5.
Log in to pgadmin, confirm that the data is there

Step 6.
Create a .backup that can be restored on the live site.

Step 7.
Create the database on the live site and restore from the .backup file.

Thanks for your suggestion. We had tried it and failed, but this
reinforced that it might work, so we then tried with a fresh install as
well, and were delighted to have our data back.

Regards,

Keith