initdb error

On 12/13/2012 07:38 AM, David Noel wrote:

I'm running into the following error message when running initdb (FreeBSD host):

ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locales
COLLATE: C
CTYPE: en_US.UTF-8
MESSAGES: en_US.UTF-8
MONETARY: en_US.UTF-8
NUMERIC: en_US.UTF-8
TIME: en_US.UTF-8
The default text search configuration will be set to "english".

creating directory /zdb/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory
child process exited with exit code 1
initdb: removing data directory "/zdb/pgsql/data"

My best guess is that it has something to do with permissions, but I
really have no idea. Has anyone seen this before and found a way
around it?

Not quite what is in the the init script in rc.d, have you tried running
the initdb command directly? As you say I believe there are permissions
problems. So following the instructions found below may solve your problem:

http://www.postgresql.org/docs/9.2/interactive/app-initdb.html

-David

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/13/12, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 12/13/2012 07:38 AM, David Noel wrote:

I'm running into the following error message when running initdb (FreeBSD
host):

ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
The files belonging to this database system will be owned by user
"pgsql".
This user must also own the server process.

The database cluster will be initialized with locales
COLLATE: C
CTYPE: en_US.UTF-8
MESSAGES: en_US.UTF-8
MONETARY: en_US.UTF-8
NUMERIC: en_US.UTF-8
TIME: en_US.UTF-8
The default text search configuration will be set to "english".

creating directory /zdb/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory
child process exited with exit code 1
initdb: removing data directory "/zdb/pgsql/data"

My best guess is that it has something to do with permissions, but I
really have no idea. Has anyone seen this before and found a way
around it?

Not quite what is in the the init script in rc.d, have you tried running
the initdb command directly? As you say I believe there are permissions
problems. So following the instructions found below may solve your problem:

http://www.postgresql.org/docs/9.2/interactive/app-initdb.html

-David

--
Adrian Klaver
adrian.klaver@gmail.com

I've tried initdb directly:

initdb -D /zdb/pgsql/data

...and also through pg_ctl:

pg_ctl initdb -D /zdb/pgsql/data

...and still seem to wind up with the error:

creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory

-David

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/13/2012 08:18 AM, David Noel wrote:

On 12/13/12, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 12/13/2012 07:38 AM, David Noel wrote:

I'm running into the following error message when running initdb (FreeBSD
host):

ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
The files belonging to this database system will be owned by user
"pgsql".
This user must also own the server process.

The database cluster will be initialized with locales
COLLATE: C
CTYPE: en_US.UTF-8
MESSAGES: en_US.UTF-8
MONETARY: en_US.UTF-8
NUMERIC: en_US.UTF-8
TIME: en_US.UTF-8
The default text search configuration will be set to "english".

creating directory /zdb/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory
child process exited with exit code 1
initdb: removing data directory "/zdb/pgsql/data"

My best guess is that it has something to do with permissions, but I
really have no idea. Has anyone seen this before and found a way
around it?

Not quite what is in the the init script in rc.d, have you tried running
the initdb command directly? As you say I believe there are permissions
problems. So following the instructions found below may solve your problem:

http://www.postgresql.org/docs/9.2/interactive/app-initdb.html

-David

--
Adrian Klaver
adrian.klaver@gmail.com

I've tried initdb directly:

initdb -D /zdb/pgsql/data

...and also through pg_ctl:

pg_ctl initdb -D /zdb/pgsql/data

...and still seem to wind up with the error:

creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory

You are doing the above as the database user ex:postgres?

The database user has permissions on /zdb/pgsql/data?

-David

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David Noel <david.i.noel@gmail.com> writes:

I've tried initdb directly:
initdb -D /zdb/pgsql/data
...and still seem to wind up with the error:
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory

Hm, that eliminates my first theory that the rc.d script is using
initdb's --xlogdir switch to try to put pg_xlog someplace that doesn't
exist on your system. But it seems like this must be some variant of
that. A simple permissions problem is not very credible, because at
this point initdb has successfully made the data directory and a bunch
of files within it already.

You could get more information by using the --noclean switch to prevent
removal of the datadir after failure, and then having a look at the
debris. Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
so what permissions has it got? Perhaps it is not a directory, but a
symlink to somewhere else?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Dec 13, 2012 at 10:00 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On 12/13/2012 08:18 AM, David Noel wrote:

On 12/13/12, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 12/13/2012 07:38 AM, David Noel wrote:

I'm running into the following error message when running initdb
(FreeBSD
host):

ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
The files belonging to this database system will be owned by user
"pgsql".
This user must also own the server process.

The database cluster will be initialized with locales
COLLATE: C
CTYPE: en_US.UTF-8
MESSAGES: en_US.UTF-8
MONETARY: en_US.UTF-8
NUMERIC: en_US.UTF-8
TIME: en_US.UTF-8
The default text search configuration will be set to "english".

creating directory /zdb/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/**000000010000000000000001" (log file 0,
segment 1): No such file or directory
child process exited with exit code 1
initdb: removing data directory "/zdb/pgsql/data"

My best guess is that it has something to do with permissions, but I
really have no idea. Has anyone seen this before and found a way
around it?

Not quite what is in the the init script in rc.d, have you tried running
the initdb command directly? As you say I believe there are permissions
problems. So following the instructions found below may solve your
problem:

http://www.postgresql.org/**docs/9.2/interactive/app-**initdb.html&lt;http://www.postgresql.org/docs/9.2/interactive/app-initdb.html&gt;

-David

--
Adrian Klaver
adrian.klaver@gmail.com

I've tried initdb directly:

initdb -D /zdb/pgsql/data

...and also through pg_ctl:

pg_ctl initdb -D /zdb/pgsql/data

...and still seem to wind up with the error:

creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/**000000010000000000000001" (log file 0,
segment 1): No such file or directory

You are doing the above as the database user ex:postgres?

The database user has permissions on /zdb/pgsql/data?

-David

--
Adrian Klaver
adrian.klaver@gmail.com

The rc.d script executes the command as the correct user. This is the line
in rc.d script that runs the initdb command:
su -l -c ${postgresql_class} ${postgresql_user} -c "exec
/usr/local/bin/initdb ${postgresql_initdb_flags} -D ${postgresql_data}"

Not sure about his permission though

On 12/13/2012 08:35 AM, Tom Lane wrote:

David Noel <david.i.noel@gmail.com> writes:

I've tried initdb directly:
initdb -D /zdb/pgsql/data
...and still seem to wind up with the error:
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory

You could get more information by using the --noclean switch to prevent
removal of the datadir after failure, and then having a look at the
debris. Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
so what permissions has it got? Perhaps it is not a directory, but a
symlink to somewhere else?

Always learning, did not know --noclean existed. That is handy. Thanks.

regards, tom lane

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Dec 13, 2012 at 10:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Noel <david.i.noel@gmail.com> writes:

I've tried initdb directly:
initdb -D /zdb/pgsql/data
...and still seem to wind up with the error:
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory

Hm, that eliminates my first theory that the rc.d script is using
initdb's --xlogdir switch to try to put pg_xlog someplace that doesn't
exist on your system. But it seems like this must be some variant of
that. A simple permissions problem is not very credible, because at
this point initdb has successfully made the data directory and a bunch
of files within it already.

You could get more information by using the --noclean switch to prevent
removal of the datadir after failure, and then having a look at the
debris. Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
so what permissions has it got? Perhaps it is not a directory, but a
symlink to somewhere else?

regards, tom lane

Tom

rc.d script doesn't seem to be using --xlogdir anywhere. I have uploaded
the script here:
http://pastebin.com/ahAyZ2tP .

Amitabh

You are doing the above as the database user ex:postgres?

confirmed:

ygg:/usr/home/ygg> whoami
pgsql

The database user has permissions on /zdb/pgsql/data?

confirmed:

ygg:/usr/home/ygg> ll /zdb/
total 3
drwxrwxrwx 3 cvs wheel 3 Dec 12 15:33 cvsroot
drwxrwxrwx 2 pgsql wheel 2 Dec 13 10:17 pgsql

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/13/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Noel <david.i.noel@gmail.com> writes:

I've tried initdb directly:
initdb -D /zdb/pgsql/data
...and still seem to wind up with the error:
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory

Hm, that eliminates my first theory that the rc.d script is using
initdb's --xlogdir switch to try to put pg_xlog someplace that doesn't
exist on your system. But it seems like this must be some variant of
that. A simple permissions problem is not very credible, because at
this point initdb has successfully made the data directory and a bunch
of files within it already.

You could get more information by using the --noclean switch to prevent
removal of the datadir after failure, and then having a look at the
debris. Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
so what permissions has it got? Perhaps it is not a directory, but a
symlink to somewhere else?

regards, tom lane

ygg:/usr/home/ygg> initdb -D /zdb/pgsql/data --noclean
...
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/000000010000000000000001" (log file 0,
segment 1): No such file or directory
child process exited with exit code 1
initdb: data directory "/zdb/pgsql/data" not removed at user's request

ygg:/usr/home/ygg> ll /zdb/pgsql/
total 2
drwx------ 14 pgsql wheel 18 Dec 13 12:42 data

ygg:/usr/home/ygg> ll /zdb/pgsql/data/
total 53
-rw------- 1 pgsql wheel 4 Dec 13 12:42 PG_VERSION
drwx------ 3 pgsql wheel 3 Dec 13 12:42 base
drwx------ 2 pgsql wheel 2 Dec 13 12:42 global
drwx------ 2 pgsql wheel 2 Dec 13 12:42 pg_clog
-rw------- 1 pgsql wheel 4467 Dec 13 12:42 pg_hba.conf
-rw------- 1 pgsql wheel 1636 Dec 13 12:42 pg_ident.conf
drwx------ 4 pgsql wheel 4 Dec 13 12:42 pg_multixact
drwx------ 2 pgsql wheel 3 Dec 13 12:42 pg_notify
drwx------ 2 pgsql wheel 2 Dec 13 12:42 pg_serial
drwx------ 2 pgsql wheel 2 Dec 13 12:42 pg_snapshots
drwx------ 2 pgsql wheel 2 Dec 13 12:42 pg_stat_tmp
drwx------ 2 pgsql wheel 2 Dec 13 12:42 pg_subtrans
drwx------ 2 pgsql wheel 2 Dec 13 12:42 pg_tblspc
drwx------ 2 pgsql wheel 2 Dec 13 12:42 pg_twophase
drwx------ 3 pgsql wheel 3 Dec 13 12:42 pg_xlog
-rw------- 1 pgsql wheel 19713 Dec 13 12:42 postgresql.conf

ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/
total 2
drwx------ 2 pgsql wheel 2 Dec 13 12:42 archive_status

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/13/2012 10:42 AM, David Noel wrote:

You are doing the above as the database user ex:postgres?

confirmed:

ygg:/usr/home/ygg> whoami
pgsql

The database user has permissions on /zdb/pgsql/data?

confirmed:

ygg:/usr/home/ygg> ll /zdb/
total 3
drwxrwxrwx 3 cvs wheel 3 Dec 12 15:33 cvsroot
drwxrwxrwx 2 pgsql wheel 2 Dec 13 10:17 pgsql

Hmmm. Saw the subsequent post also.

More questions:(
The whoami above shows running from ygg home directory.
How did you become pgsql?
Does the pgsql user have a login account you could use?
Where is /zdb ?

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/13/2012 10:47 AM, David Noel wrote:

On 12/13/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:

ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/
total 2
drwx------ 2 pgsql wheel 2 Dec 13 12:42 archive_status

Different train of thought, away from permissions.

How was Postgres installed?

Where there any errors during the install process?--

Is there another version of Postgres on this machine?

Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/13/12, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 12/13/2012 10:47 AM, David Noel wrote:

On 12/13/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:

ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/
total 2
drwx------ 2 pgsql wheel 2 Dec 13 12:42 archive_status

Different train of thought, away from permissions.

How was Postgres installed?

Where there any errors during the install process?--

Is there another version of Postgres on this machine?

Adrian Klaver
adrian.klaver@gmail.com

I'd logged in as ygg via `su ygg`.

/zdb is a zfs volume I've created for cvs and postgres.

Postgres was installed via `make install` and there were no errors
during the install process.

I could try deinstalling and installing it again... maybe that will
fix something.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Unfortunately no luck there. Still stick with the same error.

On 12/13/12, David Noel <david.i.noel@gmail.com> wrote:

On 12/13/12, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 12/13/2012 10:47 AM, David Noel wrote:

On 12/13/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:

ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/
total 2
drwx------ 2 pgsql wheel 2 Dec 13 12:42 archive_status

Different train of thought, away from permissions.

How was Postgres installed?

Where there any errors during the install process?--

Is there another version of Postgres on this machine?

Adrian Klaver
adrian.klaver@gmail.com

I'd logged in as ygg via `su ygg`.

/zdb is a zfs volume I've created for cvs and postgres.

Postgres was installed via `make install` and there were no errors
during the install process.

I could try deinstalling and installing it again... maybe that will
fix something.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David Noel <david.i.noel@gmail.com> writes:

On 12/13/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:

You could get more information by using the --noclean switch to prevent
removal of the datadir after failure, and then having a look at the
debris. Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
so what permissions has it got? Perhaps it is not a directory, but a
symlink to somewhere else?

[ nothing obviously wrong there... ]

Well, it looks like there are no easy answers here, so we'll have to
start digging. Is this a purely stock version of Postgres? Which
release exactly? Where did you get it from, or if you built it
yourself, what configuration options did you use? What's the platform
exactly?

What I'd suggest as a next step is to use strace or local equivalent on
the initdb run, to capture a log of everything it does with pg_xlog.
(And everything else too --- the log is likely to be bulky, so please
don't post it here. You could send it to me off-list if it makes no
sense to you.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David Noel <david.i.noel@gmail.com> writes:

/zdb is a zfs volume I've created for cvs and postgres.

zfs eh? What happens if you point initdb at a non-zfs volume?

(I"m wondering if zfs has issues with the O_DIRECT flag that we'll
probably try to use with pg_xlog files.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/13/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Noel <david.i.noel@gmail.com> writes:

/zdb is a zfs volume I've created for cvs and postgres.

zfs eh? What happens if you point initdb at a non-zfs volume?

(I"m wondering if zfs has issues with the O_DIRECT flag that we'll
probably try to use with pg_xlog files.)

I /boot off of a UFS volume so I created a directory there, chown and
chmod'ed it, then ran initdb again. Same error, unfortunately.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David Noel <david.i.noel@gmail.com> writes:

On 12/13/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:

(I"m wondering if zfs has issues with the O_DIRECT flag that we'll
probably try to use with pg_xlog files.)

I /boot off of a UFS volume so I created a directory there, chown and
chmod'ed it, then ran initdb again. Same error, unfortunately.

Hm. So far as I can find from googling, ZFS doesn't support O_DIRECT,
or if it does the support is very new. However, if that were the
problem then you ought to be seeing EINVAL or similar, not ENOENT;
and what's more the file should have gotten created before we'd try to
open it with O_DIRECT, so it should have been there after --noclean.

So I'm still baffled. Back to wondering if strace will show anything
interesting.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/13/2012 01:40 PM, David Noel wrote:

On 12/13/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Noel <david.i.noel@gmail.com> writes:

/zdb is a zfs volume I've created for cvs and postgres.

zfs eh? What happens if you point initdb at a non-zfs volume?

(I"m wondering if zfs has issues with the O_DIRECT flag that we'll
probably try to use with pg_xlog files.)

I /boot off of a UFS volume so I created a directory there, chown and
chmod'ed it, then ran initdb again. Same error, unfortunately.

Well this has definitely moved up a support level and past anything I
know about.
For the record what version of FreeBSD are you running in case someone
is searching the archives?

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Dec 14, 2012 at 3:10 AM, David Noel <david.i.noel@gmail.com> wrote:

On 12/13/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Noel <david.i.noel@gmail.com> writes:

/zdb is a zfs volume I've created for cvs and postgres.

zfs eh? What happens if you point initdb at a non-zfs volume?

(I"m wondering if zfs has issues with the O_DIRECT flag that we'll
probably try to use with pg_xlog files.)

I /boot off of a UFS volume so I created a directory there, chown and
chmod'ed it, then ran initdb again. Same error, unfortunately.

David

Did you use ports to install postgresql? What is the version of postgresql
and freebsd you are using? I am getting a different output while running
the initdb command through the rc script, and it's not using the -D path to
initialize the cluster, it falls back to the default location
/usr/local/pgsql/data . Setting postgresql_data="/usr/local/pgsql1/data"
in /etc/rc.conf makes it initialize at the right location . I did not had
to mess with an permissions and the rc commands were all run as root. The
above was tested on FreeBSD 9.1 with Postgresql 9.2 on UFS.

On FreeBSD 8.1 with Postgresql 9.1 on UFS installed though ports, it was
the same story, except that I had to change ownership to user pgsql for the
/usr/local/pgsql1 directory

Amitabh