Re: [ADMIN] Postrgres data restoration problem

Jim,

I'm pretty sure that the /etc/rc?.d scripts do not run the initdb command as you generaly do it once, when you want to
create a Postgres environment.

Basic question : Have you checked that you have some files in : /home/pgsql/data/pg_xlog
Postgres has started but can't find /home/pgsql/data/pg_xlog/0000000000000000
Perhaps they are in /var/lib/pgsql/data/xlog.
If it is the case, create a symbolic link on it and retry :
cd /oldpgsqlpath/data
pg_ctl -D $PWD start >/tmp/pgr 2>&1

Thierry.

Jim Cochrane wrote:

> Hi Thierry.
>
> Thanks very much for your help.
>
> > Hi,
> >
> > I have just simple questions :
> > 1) After Redhat and Postgres reinstall, did you run initdb ? Don't do it.
>
> I'm pretty sure I didn't run initdb by hand, but it may have been run by
> the startup script in /etc/rc?.d.
>
> > 2) What is the value of $PGDATA ?
>
> The database server was being started by the /etc/init.d/postgresql (linked
> into the appropriate /etc/rc?.d directory) and I believe it was setting
> PGDATA to either /var/lib/pgsql or /var/lib/pgsql/data. (The old database
> files are in /home/pgsql/data.)
>
> > 3) Have you try to set : export PGDATA=/oldpgsqlpath/data and pg_ctl start ?
> >
> > >From my point of view, after reinstalling Redhat and PG, be sure that the
> > filesystem /oldpgsqlpath is mounted, export PGDATA=/oldpgsqlpath/data, pg_ctl
> > start.
> > psql dbname
> > \d => you should see your tables.
>
> Using your example, I just essentially did the same thing (as the postgres
> user):
>
> cd /oldpgsqlpath/data
> pg_ctl -D $PWD start >/tmp/pgr 2>&1
>
> However the server failed to start up, giving the following error messages:
>
> postmaster successfully started
> DEBUG: database system was shut down at 2003-12-07 14:55:22 MST
> DEBUG: open of /home/pgsql/data/pg_xlog/0000000000000000 (log file 0, segment 0) failed: No such file or directory
> DEBUG: invalid primary checkpoint record
> DEBUG: open of /home/pgsql/data/pg_xlog/0000000000000000 (log file 0, segment 0) failed: No such file or directory
> DEBUG: invalid secondary checkpoint record
> FATAL 2: unable to locate a valid checkpoint record
> DEBUG: startup process (pid 31411) exited with exit code 2
> DEBUG: aborting startup due to startup process failure
>
> > Otherwise, something has been broken in /oldpgsqlpath/data.
>
> It looks like that is the case - that, perhaps, the metadata is corrupted.
>
> I suspect that this is not recoverable. Is that the case?
>
> Thanks,
> Jim
>
> >
> > Thierry
> >
> > Jim Cochrane wrote:
> >
> > > I'm looking for help restoring old postgres databases on a linux system
> > > after a reinstall of Redhat (including posgresql). It's the same version
> > > of redhat and of postgres I was using before the resinstall, but I've not
> > > been able to get the database server to see the old databases.
> > >
> > > The old data is stored in the standard postgres format. (No,
> > > unfortunately, I did not have a backup.) The postgres data is located
> > > on my system under /var/lib/pgsql. So I tried both creating a
> > > symbolic link - setting up /var/lib/pgsql/data as a symbolic link to
> > > /oldpgsqlpath/data, and, when that didn't work, I just copied the old data
> > > directory to /var/lib/pgsql (that is, it ended up residing in
> > > /var/lib/pgsql/data), which didn't work either:
> > >
> > > I was able to connect to an old database (e.g., with psql dbname), but
> > > when I listed the tables with \d, it responds with: No relations found.
> > > I suspect that the data may be unrecoverable. Is my pessimism justified or
> > > might there be a way (without spending days, that is) to recover the old
> > > data?
> > >
> > > Thanks very much!
> > > Jim Cochrane
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 9: the planner will ignore your desire to choose an index scan if your
> > > joining column's datatypes do not match
> >
> > --------------6C8F1BAB633602B72D8C53D4
> > Content-Type: text/x-vcard; charset=us-ascii;
> > name="THIERRY.MISSIMILLY.vcf"
> > Content-Description: Card for Thierry Missimilly
> > Content-Disposition: attachment;
> > filename="THIERRY.MISSIMILLY.vcf"
> > Content-Transfer-Encoding: 7bit
> >
> > begin:vcard
> > n:Missimilly;Thierry
> > tel;fax:+33 (0)4 76 29 78 78
> > tel;work:+33 (0)4 76 29 74 54
> > x-mozilla-html:FALSE
> > url:http:\\www.bull.com
> > org:BIS/R&D
> > adr:;;Bull SA, 1, rue de provence - BP 208;ECHIROLLES;;38432;FRANCE
> > version:2.1
> > email;internet:Thierry(dot)Missimilly(at)bull(dot)net
> > x-mozilla-cpt:;-18184
> > fn:Thierry Missimilly
> > end:vcard
> >
> > --------------6C8F1BAB633602B72D8C53D4--
> >