Re: Postgres Dropped DB have recovered files how to restore

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.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 6 June 2016 at 16:57, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(at)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(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>