pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL 9.6.1 - Windows 64bit - HDD crash due to power outage
5+ messages / 5 participants
[nested] [flat]
* PostgreSQL 9.6.1 - Windows 64bit - HDD crash due to power outage
@ 2017-04-03 20:04 Ertan Küçükoðlu <ertan.kucukoglu@1nar.com.tr>
0 siblings, 2 replies; 5+ messages in thread
From: Ertan Küçükoðlu @ 2017-04-03 20:04 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PostgreSQL 9.6.1 - Windows 64bit - HDD crash due to power outage
@ 2017-04-03 20:20 Samed YILDIRIM <samed@reddoc.net>
parent: Ertan Küçükoðlu <ertan.kucukoglu@1nar.com.tr>
1 sibling, 1 reply; 5+ messages in thread
From: Samed YILDIRIM @ 2017-04-03 20:20 UTC (permalink / raw)
To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; pgsql-admin
<div>Hi Ertan,</div><div> </div><div>Most probably, It will work by copying your old data directory to a new identical PostgreSQL server's data directory. But, you should be sure that you have copied your data directory correctly, not only base directory, etc.</div><div> </div><div>1. Install an identical PostgreSQL server</div><div>2. Copy your old data directory to your new installation. Please do not move, copy them. Be sure that you have always a backup without any modification of your failed server.</div><div>3. Check folders' and files' permissions. As I remember, in Windows PostgreSQL works with Network Service user/permissions. But I'm not sure.</div><div>4. Check your logs before starting PostgreSQL Server</div><div>5. If everything is ok, delete postmaster.pid if exists.</div><div>6. Start your PostgreSQL server and follow your logs. You may need to start with recovery.conf.</div><div> </div><div>Best regards.</div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>03.04.2017, 23:06, "Ertan Küçükoğlu" <ertan.kucukoglu@1nar.com.tr>:</div><blockquote type="cite"><p>Hi All,<br /><br />One of my test systems had electricity problem and operating system is not<br />booting now. I can recover data by connecting that HDD to another computer<br />as a USB disc. I confirm that I did a file copy complete data directory of<br />several GB in size just fine. I have no idea about their structural<br />integrity though.<br /><br />Since this computer was used for tests I did not setup any backup plan on<br />it. However, some of my colleagues seems to have some important database<br />structure *and* data saved in that system. Of course, I learn that after<br />disaster happens, but that's another story.<br /><br />* There was only one database on the server.<br />* No replication of any kind was setup. Server was a standalone.<br />* Auto vacuum setting was not changed after server setup, and I do not know<br />its defaults. I do know that it is was set to do auto vacuum.<br />* New data was added to database after midnight just once a day. Data to be<br />inserted was fetched over internet. If first trial fails there were<br />additional trials set for every 30 minutes for 5 hours max (10 trials<br />total). If any data insert trial is a success other trials were executed,<br />but did not insert any data.<br />* System otherwise is used for select queries. So, I am guessing that there<br />was no data inserted for several hours at the time of power outage. Do not<br />know about vacuum status or if database server was doing something else.<br /><br />My questions are;<br />- Is there a backup tool/way to have some kind of a backup from raw<br />PostgreSQL database files (data read from data directory rather than<br />database server) which can be restored on a different PostgreSQL server?<br />- If I setup a new PostgreSQL server using identical binaries as the crashed<br />one, and copy rescued data directory onto that new server data directory,<br />overwriting new system data directory when server is down. Will that work<br />assuming recovered data is structurally fine? If that method works, how can<br />I test that database files are fine and there is no problem with them?<br />- I am open to all other suggestions here.<br /><br />Thanks & regards,<br />Ertan Küçükoğlu<br /><br /><br /><br /> </p><span>--<br />Sent via pgsql-admin mailing list (<a href="mailto:pgsql-admin@postgresql.org">pgsql-admin@postgresql.org</a>)<br />To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-admin">http://www.postgresql.org/mailpref/pgsql-admi...;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PostgreSQL 9.6.1 - Windows 64bit - HDD crash due to power outage
@ 2017-04-03 20:21 David G. Johnston <david.g.johnston@gmail.com>
parent: Ertan Küçükoðlu <ertan.kucukoglu@1nar.com.tr>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2017-04-03 20:21 UTC (permalink / raw)
To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; +Cc: pgsql-admin
On Monday, April 3, 2017, Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>
wrote:
>
> - 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 you can copy over the last versions of the WAL and data files (including
non-default tablespaces) (and ideally configuration files) and point the
same binaries to those it will work in theory.
> If that method works, how can
>
I test that database files are fine and there is no problem with them?
>
Performing a full pg_dump on the database/cluster is the only routine I can
think of - but my experience here is limited.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PostgreSQL 9.6.1 - Windows 64bit - HDD crash due to power outage
@ 2017-04-05 12:49 Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ertan Küçükoğlu @ 2017-04-05 12:49 UTC (permalink / raw)
To: pgsql-admin
- 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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PostgreSQL 9.6.1 - Windows 64bit - HDD crash due to power outage
@ 2017-04-06 01:20 Steven Chang <stevenchang1213@gmail.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 5+ messages in thread
From: Steven Chang @ 2017-04-06 01:20 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; pgsql-admin
Hello Samed ,
No need recovery.conf due to no wal archive , I guess.
From Ertan's words, the testing environment was setup by default.
Crash recovery mechanism would cope with recovery from WAL as shutdown
immediate.
Hello Ertan,
Just proceed your recovery operations.
Good luck.
Best Regards,
Steven
2017-04-04 4:20 GMT+08:00 Samed YILDIRIM <samed@reddoc.net>:
> Hi Ertan,
>
> Most probably, It will work by copying your old data directory to a new
> identical PostgreSQL server's data directory. But, you should be sure that
> you have copied your data directory correctly, not only base directory, etc.
>
> 1. Install an identical PostgreSQL server
> 2. Copy your old data directory to your new installation. Please do not
> move, copy them. Be sure that you have always a backup without any
> modification of your failed server.
> 3. Check folders' and files' permissions. As I remember, in Windows
> PostgreSQL works with Network Service user/permissions. But I'm not sure.
> 4. Check your logs before starting PostgreSQL Server
> 5. If everything is ok, delete postmaster.pid if exists.
> 6. Start your PostgreSQL server and follow your logs. You may need to
> start with recovery.conf.
>
> Best regards.
>
> İyi çalışmalar.
> Samed YILDIRIM
>
>
>
> 03.04.2017, 23:06, "Ertan Küçükoğlu" <ertan.kucukoglu@1nar.com.tr>:
>
> 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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-04-06 01:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-03 20:04 PostgreSQL 9.6.1 - Windows 64bit - HDD crash due to power outage Ertan Küçükoðlu <ertan.kucukoglu@1nar.com.tr>
2017-04-03 20:20 ` Samed YILDIRIM <samed@reddoc.net>
2017-04-06 01:20 ` Steven Chang <stevenchang1213@gmail.com>
2017-04-03 20:21 ` David G. Johnston <david.g.johnston@gmail.com>
2017-04-05 12:49 ` Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox