Re: [ADMIN] Postrgres data restoration problem - Mailing list pgsql-general

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.Missimilly@bull.net
> x-mozilla-cpt:;-18184
> fn:Thierry Missimilly
> end:vcard
>
> --------------6C8F1BAB633602B72D8C53D4--
>