Thread: Moving pg_xlog problem
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
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
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
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
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
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
"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
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
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
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
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
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
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
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. _______________________________
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
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