Thread: Moving pg_xlog problem

Moving pg_xlog problem

From
Joost Kraaijeveld
Date:
Hi,

I am trying to move the pg_xlog directory to another disk by using a
symbolic link. But I keep getting the following error message:

Panoramix:/var/lib/postgresql/8.0/main# /etc/init.d/postgresql-8.0 start
Starting PostgreSQL 8.0 database server: main(FAILED)

The PostgreSQL server failed to start. Please check the log output:
LOG:  could not load root certificate file
"/var/lib/postgresql/8.0/main/root.crt": No SSL error reported
DETAIL:  Will not verify client certificates.
LOG:  database system was shut down at 2005-11-08 08:01:12 CET
PANIC:  could not open file
"/var/lib/postgresql/8.0/main/pg_xlog/00000001.history": Permission
denied
LOG:  startup process (PID 14242) was terminated by signal 6
LOG:  aborting startup due to startup process failure


I followed this procedure:

As root:
 /etc/init.d/postgresql-8.0 stop

As postgres
 cd /var/lib/postgres/8.0/main
 mv pg_xlog/ pg_xlog_orig
 mkdir /opt/postgres/logs/wal
 ln -s /opt/postgres/logs/wal pg_xlog
 cp -r pg_xlog_orig/ pg_xlog

As root:
 /etc/init.d/postgresql-8.0 start



What did I do wrong, and even more important, what should I do?

TIA

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



Re: Moving pg_xlog problem

From
Jeff Frost
Date:
So, what happens if you do this as the postgres user:

ls -l /var/lib/postgresql/8.0/main/pg_xlog/

I'm going to guess the output indicates you have a directory inside pg_xlog
called pg_xlog_orig.

You probably just need to cd /opt/postgres/logs/wal/pg_xlog_orig as the
postgres user and 'mv * .. ; cd .. ; rmdir pg_xlog_orig' to resolve your
problem.

On Tue, 8 Nov 2005, Joost Kraaijeveld wrote:

> As postgres
> cd /var/lib/postgres/8.0/main
> mv pg_xlog/ pg_xlog_orig
> mkdir /opt/postgres/logs/wal
> ln -s /opt/postgres/logs/wal pg_xlog
> cp -r pg_xlog_orig/ pg_xlog
>
> As root:
> /etc/init.d/postgresql-8.0 start
>
>
>
> What did I do wrong, and even more important, what should I do?
>
> TIA
>
>

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: Moving pg_xlog problem

From
Joost Kraaijeveld
Date:
On Mon, 2005-11-07 at 23:46 -0800, Jeff Frost wrote:
> So, what happens if you do this as the postgres user:
>
> ls -l /var/lib/postgresql/8.0/main/pg_xlog/
>
> I'm going to guess the output indicates you have a directory inside pg_xlog
> called pg_xlog_orig.
Sorry, copied and pasted the wrong part of the history file: the
directory structure is OK as far as I can see, but there must something
wrong...



--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



Re: Moving pg_xlog problem

From
MaXX
Date:
Joost Kraaijeveld wrote:
> On Mon, 2005-11-07 at 23:46 -0800, Jeff Frost wrote:
>> So, what happens if you do this as the postgres user:
>> ls -l /var/lib/postgresql/8.0/main/pg_xlog/
>> I'm going to guess the output indicates you have a directory inside
>> pg_xlog called pg_xlog_orig.
> Sorry, copied and pasted the wrong part of the history file: the
> directory structure is OK as far as I can see, but there must something
> wrong...
I ran in the same problem a while before, Check permissions and how the
symlink was created, it must be 'ln -s /full/path /full/dst/path' ,no
'./' ...

That solved my problem...

HTH,
--
MaXX

Re: Moving pg_xlog problem

From
Tom Lane
Date:
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> PANIC:  could not open file
> "/var/lib/postgresql/8.0/main/pg_xlog/00000001.history": Permission
> denied

Read the error message!  You have a permissions problem --- most likely,
some directory in the path to the new xlog directory isn't readable by
postgres.

            regards, tom lane

Re: Moving pg_xlog problem

From
"Joost Kraaijeveld"
Date:
Hi Tom,

> Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> > PANIC:  could not open file
> > "/var/lib/postgresql/8.0/main/pg_xlog/00000001.history": Permission
> > denied
>
> Read the error message!  You have a permissions problem ---
> most likely,
> some directory in the path to the new xlog directory isn't readable by
> postgres.
No, that is not the case as far as I can see: I could do a "touch myfile" in all directories in the path to the pg_xlog
directory,as root and as postgres. I also could do "ls -al" in every directory in the path.  

I checked all this with another Debian box which has a slightly other PostgreSQL version and there it works as
advertised.So PostgreSQL 8.0.3-7 works with symlinked pg_xlog directory and PostgreSQL 8.0.3-15 does not. 

Can I downgrade to 8.0.3-7 without dump/restore?

TIA

Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Re: Moving pg_xlog problem

From
Tom Lane
Date:
"Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:
> I checked all this with another Debian box which has a slightly other
> PostgreSQL version and there it works as advertised. So PostgreSQL
> 8.0.3-7 works with symlinked pg_xlog directory and PostgreSQL 8.0.3-15
> does not.

Seems *highly* unlikely, though I suppose you could ask the Debian
maintainer what he changed between -7 and -15.

            regards, tom lane

Re: Moving pg_xlog problem

From
Joost Kraaijeveld
Date:
On Tue, 2005-11-08 at 10:59 -0500, Tom Lane wrote:
> Seems *highly* unlikely, though I suppose you could ask the Debian
> maintainer what he changed between -7 and -15.
I have triple checked, I even let someone else do the same procedure but
history repeats itself. It does not work. I have send the Debain
maintainer an email.

Thanks

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



Re: Moving pg_xlog problem

From
Jeff Frost
Date:
On Tue, 8 Nov 2005, Joost Kraaijeveld wrote:

>> Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
>>> PANIC:  could not open file
>>> "/var/lib/postgresql/8.0/main/pg_xlog/00000001.history": Permission
>>> denied

So what happens if you do:

ls -l /var/lib/postgresql/8.0/main/pg_xlog/00000001.history

Can you post the output?

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: Moving pg_xlog problem

From
Joost Kraaijeveld
Date:
Hi Jeff,

On Tue, 2005-11-08 at 09:11 -0800, Jeff Frost wrote:
> On Tue, 8 Nov 2005, Joost Kraaijeveld wrote:
>
> >> Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> >>> PANIC:  could not open file
> >>> "/var/lib/postgresql/8.0/main/pg_xlog/00000001.history": Permission
> >>> denied
>
> So what happens if you do:
>
> ls -l /var/lib/postgresql/8.0/main/pg_xlog/00000001.history
>
> Can you post the output?
There is not a file with that name. Never has been. Not even with a
running version of PostgreSQL. The full listing of the directories
(edited a bit for readability, removed size, date and part of the
symlink for the certicate stuff):

Panoramix:/var/lib/postgresql/8.0/main# ls -al
total 36
drwx------   7 postgres postgres .
drwxr-xr-x   3 postgres postgres ..
drwx------  10 postgres postgres base
drwx------   2 postgres postgres global
drwx------   2 postgres postgres pg_clog
drwx------   2 postgres postgres pg_subtrans
drwx------   2 postgres postgres pg_tblspc
-rw-------   1 postgres postgres PG_VERSION
lrwxrwxrwx   1 root     root     pg_xlog -> /opt/pg_xlog
-rw-------   1 postgres postgres postmaster.opts
lrwxrwxrwx   1 root     root     root.crt -> /etc/postgre...
lrwxrwxrwx   1 root     root     server.crt -> /etc/postg...
lrwxrwxrwx   1 root     root     server.key -> /etc/postg...

Panoramix:/var/lib/postgresql/8.0/main# ls /opt/ -al
total 56
drwxrwxrw-  14 root     staff    .
drwxr-xr-x  22 root     root     ..
drwx------   3 postgres postgres pg_xlog

Panoramix:/var/lib/postgresql/8.0/main# ls /opt/pg_xlog/ -al
total 295284
drwx------   3 postgres postgres .
drwxrwxrw-  14 root     staff    ..
-rw-------   1 postgres postgres 0000000100000033000000A4
-rw-------   1 postgres postgres 0000000100000033000000A5
-rw-------   1 postgres postgres 0000000100000033000000A6
-rw-------   1 postgres postgres 0000000100000033000000A7
-rw-------   1 postgres postgres 0000000100000033000000A8
-rw-------   1 postgres postgres 0000000100000033000000A9
-rw-------   1 postgres postgres 0000000100000033000000AA
-rw-------   1 postgres postgres 0000000100000033000000AB
-rw-------   1 postgres postgres 0000000100000033000000AC
-rw-------   1 postgres postgres 0000000100000033000000AD
-rw-------   1 postgres postgres 0000000100000033000000AE
-rw-------   1 postgres postgres 0000000100000033000000AF
-rw-------   1 postgres postgres 0000000100000033000000B0
-rw-------   1 postgres postgres 0000000100000033000000B1
-rw-------   1 postgres postgres 0000000100000033000000B2
-rw-------   1 postgres postgres 0000000100000033000000B3
-rw-------   1 postgres postgres 0000000100000033000000B4
-rw-------   1 postgres postgres 0000000100000033000000B5
drw-------   2 postgres postgres archive_status



--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



Re: Moving pg_xlog problem

From
Tom Lane
Date:
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
>>> PANIC:  could not open file
>>> "/var/lib/postgresql/8.0/main/pg_xlog/00000001.history": Permission
>>> denied

>> So what happens if you do:
>> ls -l /var/lib/postgresql/8.0/main/pg_xlog/00000001.history

> There is not a file with that name.

Not surprising; the problem is that it's getting "Permission denied"
instead of "No such file".

            regards, tom lane

Re: Moving pg_xlog problem

From
Joost Kraaijeveld
Date:
Hi Tom,

On Tue, 2005-11-08 at 13:05 -0500, Tom Lane wrote:
> Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> >>> PANIC:  could not open file
> >>> "/var/lib/postgresql/8.0/main/pg_xlog/00000001.history": Permission
> >>> denied
>
> >> So what happens if you do:
> >> ls -l /var/lib/postgresql/8.0/main/pg_xlog/00000001.history
>
> > There is not a file with that name.
>
> Not surprising; the problem is that it's getting "Permission denied"
> instead of "No such file".
It does not suprise me either . But in an attempt to be complete I added
the (almost) full directory listing of the permissions and contents of
the relevant directories. *I* cannot find any "access denied" reason.
Can you? (do I sound desperate enough ;-)?)

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



Re: Moving pg_xlog problem

From
Tom Lane
Date:
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> Panoramix:/var/lib/postgresql/8.0/main# ls /opt/ -al
> total 56
> drwxrwxrw-  14 root     staff    .
           ^
> drwxr-xr-x  22 root     root     ..
> drwx------   3 postgres postgres pg_xlog

There's your problem --- postgres can't do lookups in /opt.

(Surely it's not a good idea for /opt to be world writable, either?)

            regards, tom lane

Re: Moving pg_xlog problem

From
Bricklen Anderson
Date:
Joost Kraaijeveld wrote:
> lrwxrwxrwx   1 root     root     pg_xlog -> /opt/pg_xlog

Maybe this one here? Try chown'ing it to postgres:postgres and see what happens


--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________

Re: Moving pg_xlog problem

From
Jeff Frost
Date:
On Tue, 8 Nov 2005, Joost Kraaijeveld wrote:

> Panoramix:/var/lib/postgresql/8.0/main# ls /opt/pg_xlog/ -al
> total 295284
> drwx------   3 postgres postgres .
> drwxrwxrw-  14 root     staff    ..
> -rw-------   1 postgres postgres 0000000100000033000000B4
> -rw-------   1 postgres postgres 0000000100000033000000B5
> drw-------   2 postgres postgres archive_status

Here's another problem besides the one Tom pointed out..why is your
archive_status directory missing the execute bit?

I noticed you ran all the ls commands as root, probably best to run them as
postgres when trying to track down a problem of this nature.


--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: Moving pg_xlog problem

From
Joost Kraaijeveld
Date:
On Tue, 2005-11-08 at 13:29 -0500, Tom Lane wrote:
> Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> > Panoramix:/var/lib/postgresql/8.0/main# ls /opt/ -al
> > total 56
> > drwxrwxrw-  14 root     staff    .
>            ^
> > drwxr-xr-x  22 root     root     ..
> > drwx------   3 postgres postgres pg_xlog
>
> There's your problem --- postgres can't do lookups in /opt.
>
> (Surely it's not a good idea for /opt to be world writable, either?)
Oh yes. You really helped me here. Whenever uyou are in the Netherlands,
don't book a hotel, but stay with me. Free meals and all the beer you
can manage. Whisky. Whatever.

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl