Обсуждение: Postgres Dropped DB have recovered files how to restore
This morning I accidently deleted my database for my OpenERP accounting. I did not have a good backup system setup so I was unable to do a system restore. i did manage to recover the files the drop command removed but I dont know how to get the DB back into postgres? Can anyone help me with this. I realy need this db to be restored otherwise I am screwed. These are the folders I recovered(i think my db was in the \base\19456 folder): C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\base\19456\ * C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\base\19456\ * C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\base\311296 C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ * C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ * I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much apreciated I am kind of desperate. -- View this message in context: http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 06/04/2016 01:20 PM, lifetronics wrote: > This morning I accidently deleted my database for my OpenERP accounting. I > did not have a good backup system setup so I was unable to do a system > restore. i did manage to recover the files the drop command removed but I > dont know how to get the DB back into postgres? Can anyone help me with > this. I realy need this db to be restored otherwise I am screwed. > > These are the folders I recovered(i think my db was in the \base\19456 > folder): > > C:\Program Files (x86)\OpenERP > 7.0-20141104-021038\PostgreSQL\data\base\19456\ * > > C:\Program Files (x86)\OpenERP > 7.0-20141104-021038\PostgreSQL\data\base\19456\ * > > C:\Program Files (x86)\OpenERP > 7.0-20141104-021038\PostgreSQL\data\base\311296 > > C:\Program Files (x86)\OpenERP > 7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ * > > C:\Program Files (x86)\OpenERP > 7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ * > > I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much > apreciated I am kind of desperate. This is what a complete $DATADIR for 9.3 looks like on a Linux source install: postgres@killi:/usr/local/pgsql93/data> l total 140 drwx------ 16 postgres users 4096 Jan 11 09:06 ./ drwxr-xr-x 7 root root 4096 Jun 27 2013 ../ drwx------ 14 postgres users 4096 Jun 4 2015 base/ drwx------ 2 postgres users 4096 Nov 18 2015 global/ drwx------ 2 postgres users 4096 Jun 27 2013 pg_clog/ -rw------- 1 postgres users 4476 Jun 27 2013 pg_hba.conf -rw------- 1 postgres users 1636 Jun 27 2013 pg_ident.conf drwxr-xr-x 3 postgres users 36864 Jan 11 08:42 pg_log/ drwx------ 4 postgres users 4096 Jun 27 2013 pg_multixact/ drwx------ 2 postgres users 4096 Jan 11 08:42 pg_notify/ drwx------ 2 postgres users 4096 Jun 27 2013 pg_serial/ drwx------ 2 postgres users 4096 Jun 27 2013 pg_snapshots/ drwx------ 2 postgres users 4096 Jan 11 09:06 pg_stat/ drwx------ 2 postgres users 4096 Jan 11 09:06 pg_stat_tmp/ drwx------ 2 postgres users 4096 Apr 26 2015 pg_subtrans/ drwx------ 2 postgres users 4096 Feb 23 2015 pg_tblspc/ drwx------ 2 postgres users 4096 Jun 27 2013 pg_twophase/ -rw------- 1 postgres users 4 Jun 27 2013 PG_VERSION drwx------ 3 postgres users 4096 Jun 4 2015 pg_xlog/ -rw------- 1 postgres users 20329 Jan 11 09:06 postgresql.conf -rw------- 1 postgres users 63 Jan 11 08:42 postmaster.opts So if what you show is everything then you are short some important directories. > > > > > -- > View this message in context: http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Ok,
1) back up the file system now as it is. So you can start over when this doesn't work.
2) create an empty database again with no data, but all of the DDL.
3) figure out the names of the new database files, and copy your old ones in with the same name as the new ones
4) cross your fingers. I have no idea if this will work
5) pg_dump everything and reload it into a new db.
Let me know how it goes.
On 6 June 2016 at 16:57, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 06/04/2016 01:20 PM, lifetronics wrote:This morning I accidently deleted my database for my OpenERP accounting. I
did not have a good backup system setup so I was unable to do a system
restore. i did manage to recover the files the drop command removed but I
dont know how to get the DB back into postgres? Can anyone help me with
this. I realy need this db to be restored otherwise I am screwed.
These are the folders I recovered(i think my db was in the \base\19456
folder):
C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\base\19456\ *
C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\base\19456\ *
C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\base\311296
C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ *
C:\Program Files (x86)\OpenERP
7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ *
I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much
apreciated I am kind of desperate.
This is what a complete $DATADIR for 9.3 looks like on a Linux source install:
postgres@killi:/usr/local/pgsql93/data> l
total 140
drwx------ 16 postgres users 4096 Jan 11 09:06 ./
drwxr-xr-x 7 root root 4096 Jun 27 2013 ../
drwx------ 14 postgres users 4096 Jun 4 2015 base/
drwx------ 2 postgres users 4096 Nov 18 2015 global/
drwx------ 2 postgres users 4096 Jun 27 2013 pg_clog/
-rw------- 1 postgres users 4476 Jun 27 2013 pg_hba.conf
-rw------- 1 postgres users 1636 Jun 27 2013 pg_ident.conf
drwxr-xr-x 3 postgres users 36864 Jan 11 08:42 pg_log/
drwx------ 4 postgres users 4096 Jun 27 2013 pg_multixact/
drwx------ 2 postgres users 4096 Jan 11 08:42 pg_notify/
drwx------ 2 postgres users 4096 Jun 27 2013 pg_serial/
drwx------ 2 postgres users 4096 Jun 27 2013 pg_snapshots/
drwx------ 2 postgres users 4096 Jan 11 09:06 pg_stat/
drwx------ 2 postgres users 4096 Jan 11 09:06 pg_stat_tmp/
drwx------ 2 postgres users 4096 Apr 26 2015 pg_subtrans/
drwx------ 2 postgres users 4096 Feb 23 2015 pg_tblspc/
drwx------ 2 postgres users 4096 Jun 27 2013 pg_twophase/
-rw------- 1 postgres users 4 Jun 27 2013 PG_VERSION
drwx------ 3 postgres users 4096 Jun 4 2015 pg_xlog/
-rw------- 1 postgres users 20329 Jan 11 09:06 postgresql.conf
-rw------- 1 postgres users 63 Jan 11 08:42 postmaster.opts
So if what you show is everything then you are short some important directories.
--
View this message in context: http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
lifetronics wrote: > This morning I accidently deleted my database for my OpenERP accounting. I > did not have a good backup system setup so I was unable to do a system > restore. i did manage to recover the files the drop command removed but I > dont know how to get the DB back into postgres? Can anyone help me with > this. I realy need this db to be restored otherwise I am screwed. I have no idea about Windows filesystems but you may be able to "undelete" the files, as long as you don't touch the partition for anything else; search the web for "undelete ntfs". Once you undelete you will need to put them back in the right places. I suggest you get a disk with twice as much space as the original; make an image copy of the original disk and don't touch the original anymore. Then try to undelete the files from the image. Use the list Adrian provided as a guide for what you're missing. You *may* be able to recover the data, if you're lucky and careful. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 6/6/2016 4:09 PM, Alvaro Herrera wrote:
I have no idea about Windows filesystems but you may be able to "undelete" the files, as long as you don't touch the partition for anything else; search the web for "undelete ntfs". Once you undelete you will need to put them back in the right places. I suggest you get a disk with twice as much space as the original; make an image copy of the original disk and don't touch the original anymore. Then try to undelete the files from the image. Use the list Adrian provided as a guide for what you're missing. You *may* be able to recover the data, if you're lucky and careful.
that won't work if he did a DROP DATABASE as all the metadata is gone.
-- john r pierce, recycling bits in santa cruz
John R Pierce wrote: > On 6/6/2016 4:09 PM, Alvaro Herrera wrote: > >I have no idea about Windows filesystems but you may be able to > >"undelete" the files, as long as you don't touch the partition for > >anything else; search the web for "undelete ntfs". Once you undelete > >you will need to put them back in the right places. I suggest you get a > >disk with twice as much space as the original; make an image copy of the > >original disk and don't touch the original anymore. Then try to > >undelete the files from the image. Use the list Adrian provided as a > >guide for what you're missing. > > > >You*may* be able to recover the data, if you're lucky and careful. > > that won't work if he did a DROP DATABASE as all the metadata is gone. It's only very difficult -- not impossible. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services