Обсуждение: PostgreSQL 9.6.1 - Windows 64bit - HDD crash due to power outage

Hi All,

One of my test systems had electricity problem and operating system is not
booting now. I can recover data by connecting that HDD to another computer
as a USB disc. I confirm that I did a file copy complete data directory of
several GB in size just fine. I have no idea about their structural
integrity though.

Since this computer was used for tests I did not setup any backup plan on
it. However, some of my colleagues seems to have some important database
structure *and* data saved in that system. Of course, I learn that after
disaster happens, but that's another story.

* There was only one database on the server.
* No replication of any kind was setup. Server was a standalone.
* Auto vacuum setting was not changed after server setup, and I do not know
its defaults. I do know that it is was set to do auto vacuum.
* New data was added to database after midnight just once a day. Data to be
inserted was fetched over internet. If first trial fails there were
additional trials set for every 30 minutes for 5 hours max (10 trials
total). If any data insert trial is a success other trials were executed,
but did not insert any data.
* System otherwise is used for select queries. So, I am guessing  that there
was no data inserted for several hours at the time of power outage. Do not
know about vacuum status or if database server was doing something else.

My questions are;
- Is there a backup tool/way to have some kind of a backup from raw
PostgreSQL database files (data read from data directory rather than
database server) which can be restored on a different PostgreSQL server?
- If I setup a new PostgreSQL server using identical binaries as the crashed
one, and copy rescued data directory onto that new server data directory,
overwriting new system data directory when server is down. Will that work
assuming recovered data is structurally fine? If that method works, how can
I test that database files are fine and there is no problem with them?
- I am open to all other suggestions here.

Thanks & regards,
Ertan Küçükoğlu

Hi All,

One of my test systems had electricity problem and operating system is not
booting now. I can recover data by connecting that HDD to another computer
as a USB disc. I confirm that I did a file copy complete data directory of
several GB in size just fine. I have no idea about their structural
integrity though.

Since this computer was used for tests I did not setup any backup plan on
it. However, some of my colleagues seems to have some important database
structure *and* data saved in that system. Of course, I learn that after
disaster happens, but that's another story.

* There was only one database on the server.
* No replication of any kind was setup. Server was a standalone.
* Auto vacuum setting was not changed after server setup, and I do not know
its defaults. I do know that it is was set to do auto vacuum.
* New data was added to database after midnight just once a day. Data to be
inserted was fetched over internet. If first trial fails there were
additional trials set for every 30 minutes for 5 hours max (10 trials
total). If any data insert trial is a success other trials were executed,
but did not insert any data.
* System otherwise is used for select queries. So, I am guessing that there
was no data inserted for several hours at the time of power outage. Do not
know about vacuum status or if database server was doing something else.

My questions are;
- Is there a backup tool/way to have some kind of a backup from raw
PostgreSQL database files (data read from data directory rather than
database server) which can be restored on a different PostgreSQL server?
- If I setup a new PostgreSQL server using identical binaries as the crashed
one, and copy rescued data directory onto that new server data directory,
overwriting new system data directory when server is down. Will that work
assuming recovered data is structurally fine? If that method works, how can
I test that database files are fine and there is no problem with them?
- I am open to all other suggestions here.

Thanks & regards,
Ertan Küçükoğlu



 --
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

- If I setup a new PostgreSQL server using identical binaries as the crashed
one, and copy rescued data directory onto that new server data directory,
overwriting new system data directory when server is down. Will that work
assuming recovered data is structurally fine?

 If that method works, how can

I test that database files are fine and there is no problem with them?

- Using identical binary distribution,
- Setting data folder permissions
- Copying over data directory on a new server
did the job.

Everything is back in track at the moment. Including backup plan.

Thank you all.