Thread: CREATEDB failure
Hello, I am trying to create a database on FreeBSD 5.2-RELEASE, This is inside a jail. I had it working in 5.1 and now am having a problem. The error is as follows cp: /usr/local/pgsql/data/base/1/16386: Invalid argument cp: /usr/local/pgsql/data/base/1/16388: Invalid argument cp: /usr/local/pgsql/data/base/1/16404: Invalid argument cp: /usr/local/pgsql/data/base/1/16414: Invalid argument cp: /usr/local/pgsql/data/base/1/16654: Invalid argument cp: /usr/local/pgsql/data/base/1/16663: Invalid argument cp: /usr/local/pgsql/data/base/1/16657: Invalid argument cp: /usr/local/pgsql/data/base/1/16669: Invalid argument cp: /usr/local/pgsql/data/base/1/16678: Invalid argument ERROR: CREATE DATABASE: could not initialize database directory I have noted some discusion recently about cp -r vs cp -R but am not sure this is the problem. cp -r does not exist on FreeBSD but may just be confusing my problem. If anyone has any suggestions, I would appreciate it. postgres 7.3.5 is the version I am trying to run. initdb works and I can connect to template1. creating tables in the template1 database works as does adding user. Thank you, Paul -- ####################################### # It does not matter how slowly you go so long as you do not stop. # Confucius (551-479 BC)
Attachment
Hi, Paul Bauer wrote: > Hello, I am trying to create a database on FreeBSD 5.2-RELEASE, > This is inside a jail. I had it working in 5.1 and now am having a > problem. The error is as follows > > cp: /usr/local/pgsql/data/base/1/16386: Invalid argument > [...] Is that NFS-mounted? I remember having similar problems when we had the database volume mounted via NFS (from a NetApp filer) with the "intr" mount option. If I'm not confusing things, then removing that option solved the problem for us. Also, do not use the "soft" mount option. Never ever. If you're not using NFS -- then sorry, I have no idea. Regards Oliver -- Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München Any opinions expressed in this message may be personal to the author and may not necessarily reflect the opinions of secnetix in any way. With Perl you can manipulate text, interact with programs, talk over networks, drive Web pages, perform arbitrary precision arithmetic, and write programs that look like Snoopy swearing.
I've been trying to fix a problem with my pg_hba.conf file. For some reason, when using an external file when declaring a list of users that can see a database, postgress is giving me a syntax error. This line: host db1 user1,user2,user3 10.0.0.0 255.255.255.0 md5 works well, but this does not: host db1 @user_list 10.0.0.0 255.255.255.0 md5 The postgres docs say this is the right syntax, but the error I get is: Jan 21 11:30:01 csl-db postgres[21377]: [3] LOG: parse_hba: invalid syntax in pg_hba.conf file at line 64, token "(end of line)" Changing it back to a comma separated list removes the error, and everything works fine. I think the problem may be the file itself, but there's nothing in the postgres docs that say how this file should be formatted. The old 7.2 user_list file looks like this: user1:+ user2:+ user3:+ Is this still the right syntax? It would seem not, since the + now referrs to groups. However, this format did not work: user1 user2 user3 neither did a comma separated list on one line. The file user_list is in the same directory as my pg_hba.conf. A search on the novice site came up empty (no hits). Any help would be grately appreciated. :-) April Carvalho Community Software Engineer Community Software Lab http://csl.ltc.org
April Carvalho <acarvalh@brave.cs.uml.edu> writes:
> I've been trying to fix a problem with my pg_hba.conf file.
> For some reason, when using an external file when declaring
> a list of users that can see a database, postgress is giving
> me a syntax error.
It seems to work for me with 7.4.1 --- but the @-inclusion syntax
is fairly recent. I'm not sure if it is supported in 7.3.*.
What PG version are you using?
> I think the problem may be the file itself, but there's nothing in the
> postgres docs that say how this file should be formatted.
It's just a free-format list of names. Either of these should work:
user1 user2 user3
user1
user2
user3
I think you can have comments too.
[ thinks... ] Are you remembering to SIGHUP the postmaster after
changing the file contents? It won't notice a change otherwise.
regards, tom lane
Thanks for answering. It's (PostgreSQL) 7.3.4, from the Debian testing suite. The syntax is right, but my co worker found out what was wrong-- The files seem to be in the wrong place, and thus they couldn't be parsed. The docs say they should be in the same directory as pg_hba.conf, but they're now in /var/lib/postgres/data (our pg_hba.conf file is in /etc/postgresql). I'm curious why he got a different error message than I, which enabled him to find the problem quickly. Thanks again. April On Wed, 21 Jan 2004, Tom Lane wrote: > April Carvalho <acarvalh@brave.cs.uml.edu> writes: > > I've been trying to fix a problem with my pg_hba.conf file. > > For some reason, when using an external file when declaring > > a list of users that can see a database, postgress is giving > > me a syntax error. > > It seems to work for me with 7.4.1 --- but the @-inclusion syntax > is fairly recent. I'm not sure if it is supported in 7.3.*. > What PG version are you using? > > > I think the problem may be the file itself, but there's nothing in the > > postgres docs that say how this file should be formatted. > > It's just a free-format list of names. Either of these should work: > > user1 user2 user3 > > user1 > user2 > user3 > > I think you can have comments too. > > [ thinks... ] Are you remembering to SIGHUP the postmaster after > changing the file contents? It won't notice a change otherwise. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
April Carvalho <acarvalh@brave.cs.uml.edu> writes:
> It's (PostgreSQL) 7.3.4, from the Debian testing suite.
> The syntax is right, but my co worker found out what was wrong--
> The files seem to be in the wrong place, and thus they couldn't be
> parsed.
> The docs say they should be in the same directory as pg_hba.conf,
> but they're now in /var/lib/postgres/data (our pg_hba.conf file is
> in /etc/postgresql).
Hmm. I would call that a bug in the Debian distribution. All those
files are kept in $PGDATA in a stock Postgres installation. The Debian
version patches things to keep pg_hba.conf in /etc, but evidently has
overlooked the possibility of @-included files. Oliver?
regards, tom lane
On Fri, 2004-01-23 at 17:02, Tom Lane wrote: > April Carvalho <acarvalh@brave.cs.uml.edu> writes: > > It's (PostgreSQL) 7.3.4, from the Debian testing suite. > > The syntax is right, but my co worker found out what was wrong-- > > The files seem to be in the wrong place, and thus they couldn't be > > parsed. > > > The docs say they should be in the same directory as pg_hba.conf, > > but they're now in /var/lib/postgres/data (our pg_hba.conf file is > > in /etc/postgresql). > > Hmm. I would call that a bug in the Debian distribution. All those > files are kept in $PGDATA in a stock Postgres installation. The Debian > version patches things to keep pg_hba.conf in /etc, but evidently has > overlooked the possibility of @-included files. Oliver? In accordance with Debian policy, pg_hba.conf and the other configuration files are in /etc/postgresql; there are symlinks to $PGDATA. I do not change where the program looks for them (in other words, it still expects to find them in $PGDATA), so this does not seem a likely explanation. Are permissions OK? Is PGDATA set to /var/lib/postgres/data ? Or does the user's description above mean that the included files were in /etc/postgresql (where the program would not look for them) and have now been moved to $PGDATA? Since the PostgreSQL version is 7.3.4, the question remains whether the file inclusion mechanism is even supported. Can someone answer that before I go looking for bugs in the Debian packaging? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Look not every man on his own interests, but every man also on the things of others. Let this mind be in you, which was also in Christ Jesus" Philippians 2:4,5
Oliver Elphick wrote:
> > Hmm. I would call that a bug in the Debian distribution. All those
> > files are kept in $PGDATA in a stock Postgres installation. The Debian
> > version patches things to keep pg_hba.conf in /etc, but evidently has
> > overlooked the possibility of @-included files. Oliver?
>
> In accordance with Debian policy, pg_hba.conf and the other
> configuration files are in /etc/postgresql; there are symlinks to
> $PGDATA. I do not change where the program looks for them (in other
> words, it still expects to find them in $PGDATA), so this does not seem
> a likely explanation. Are permissions OK? Is PGDATA set to
> /var/lib/postgres/data ? Or does the user's description above mean that
> the included files were in /etc/postgresql (where the program would not
> look for them) and have now been moved to $PGDATA?
>
> Since the PostgreSQL version is 7.3.4, the question remains whether the
> file inclusion mechanism is even supported. Can someone answer that
> before I go looking for bugs in the Debian packaging?
The features was added by me in 7.3:
* "pg_hba.conf" now has a column for the user name and additional
features. Existing files need to be adjusted.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Btw, thanks for all the help. :-) I think that permissions are ok, since it was working fine when the files were moved; it would still be broken if the perms were not right. However $PGDATA was not set, assuming that the debconf (the debian package configuration program) had already set it properly. April Carvalho On Fri, 23 Jan 2004, Oliver Elphick wrote: > On Fri, 2004-01-23 at 17:02, Tom Lane wrote: > > April Carvalho <acarvalh@brave.cs.uml.edu> writes: > > > It's (PostgreSQL) 7.3.4, from the Debian testing suite. > > > The syntax is right, but my co worker found out what was wrong-- > > > The files seem to be in the wrong place, and thus they couldn't be > > > parsed. > > > > > The docs say they should be in the same directory as pg_hba.conf, > > > but they're now in /var/lib/postgres/data (our pg_hba.conf file is > > > in /etc/postgresql). > > > > Hmm. I would call that a bug in the Debian distribution. All those > > files are kept in $PGDATA in a stock Postgres installation. The Debian > > version patches things to keep pg_hba.conf in /etc, but evidently has > > overlooked the possibility of @-included files. Oliver? > > In accordance with Debian policy, pg_hba.conf and the other > configuration files are in /etc/postgresql; there are symlinks to > $PGDATA. I do not change where the program looks for them (in other > words, it still expects to find them in $PGDATA), so this does not seem > a likely explanation. Are permissions OK? Is PGDATA set to > /var/lib/postgres/data ? Or does the user's description above mean that > the included files were in /etc/postgresql (where the program would not > look for them) and have now been moved to $PGDATA? > > Since the PostgreSQL version is 7.3.4, the question remains whether the > file inclusion mechanism is even supported. Can someone answer that > before I go looking for bugs in the Debian packaging? > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight, UK http://www.lfix.co.uk/oliver > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "Look not every man on his own interests, but every man > also on the things of others. Let this mind be in you, > which was also in Christ Jesus" > Philippians 2:4,5 >
On Mon, 2004-01-26 at 20:18, April Carvalho wrote: > Btw, thanks for all the help. :-) > > I think that permissions are ok, since it was working fine when > the files were moved; it would still be broken if the perms were > not right. Where were the files when it didn't work, and where did you move them to? > However $PGDATA was not set, assuming that the debconf (the debian > package configuration program) had already set it properly. In the environment of the 'postgres' login, it should be set. This is the user under which the postmaster is (should be) run. If it weren't set, the postmaster couldn't start, because it wouldn't know where to find the database. If an ordinary user needs it set, he should source /etc/postgresql/postgresql.env in his .bashrc or other startup script. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Wash me thoroughly from mine iniquity, and cleanse me from my sin. For I acknowledge my transgressions; and my sin is ever before me. Against thee, thee only, have I sinned, and done this evil in thy sight..." Psalms 51:2-4
Yes, that would help. <feeling stupid> The files in question were the ones referenced in pg_hba.conf: host db1 @db1_users 10.0.0.0 255.255.255.0 md5 There are actually several lines (and several user lists) in this file. Files like @db1_users, were in /etc/postgresql/, since pg_hba.conf was there. This made sense at the time, because, according to the postgres docs, they should be in the same folder as pg_hba.conf. However, the debian package has $PGDATA/pg_hba.conf has a link to /etc/postgresql/pg_hba.conf. Postgres is probably expecting pg_hba.conf to be in $PGDATA, and Debain expects it in /etc/postgresql. If I knew nothing of Debian, perhaps this wouldn't have happened. Funny huh? Btw, the $PGDATA variable *was* set after all, but just for user postgres. Thanks for all your help. April On Mon, 26 Jan 2004, Oliver Elphick wrote: > On Mon, 2004-01-26 at 20:18, April Carvalho wrote: > > Btw, thanks for all the help. :-) > > > > I think that permissions are ok, since it was working fine when > > the files were moved; it would still be broken if the perms were > > not right. > > Where were the files when it didn't work, and where did you move them > to? > > > However $PGDATA was not set, assuming that the debconf (the debian > > package configuration program) had already set it properly. > > In the environment of the 'postgres' login, it should be set. This is > the user under which the postmaster is (should be) run. If it weren't > set, the postmaster couldn't start, because it wouldn't know where to > find the database. If an ordinary user needs it set, he should source > /etc/postgresql/postgresql.env in his .bashrc or other startup script. > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight, UK http://www.lfix.co.uk/oliver > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "Wash me thoroughly from mine iniquity, and cleanse me > from my sin. For I acknowledge my transgressions; and > my sin is ever before me. Against thee, thee only, > have I sinned, and done this evil in thy sight..." > Psalms 51:2-4 >
On Mon, 2004-01-26 at 21:30, April Carvalho wrote: > The files in question were the ones referenced in pg_hba.conf: > > host db1 @db1_users 10.0.0.0 255.255.255.0 md5 > > There are actually several lines (and several user lists) in this file. > Files like @db1_users, were in /etc/postgresql/, since pg_hba.conf was > there. This made sense at the time, because, according to the postgres > docs, they should be in the same folder as pg_hba.conf. > However, the debian package has $PGDATA/pg_hba.conf has a link > to /etc/postgresql/pg_hba.conf. Postgres is probably expecting > pg_hba.conf to be in $PGDATA, and Debain expects it in /etc/postgresql. > If I knew nothing of Debian, perhaps this wouldn't have happened. > > Funny huh? I need to add some documentation about this. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Wash me thoroughly from mine iniquity, and cleanse me from my sin. For I acknowledge my transgressions; and my sin is ever before me. Against thee, thee only, have I sinned, and done this evil in thy sight..." Psalms 51:2-4
Can't thank you all enough for the help. :-) Here's another problem I'm having. I'm using pg_dumpall in a micro sized perl script to dump the entire db cluster. (All it did was save the password to an environment variable, and modified the backup file's name to include the date.) This script has been working on our old server for over a year. We're trying to get it to work with our new one, but with stricter security measures. (Our older one used ident, which our boss has now banned for the new one.) Unfortunately, I'm getting the following message back when trying to run it manually: pg_dump: [archiver (db)] connection to database "db1" failed: FATAL: No pg_hba.conf entry for host localhost, user postgres, database db1 and dies on the first database: pg_dumpall: pg_dump failed on db1, exiting The script is owned by postgres, pg_dumpall is owned by postgres, and is being run as user postgres with postgres' environment variables (i.e., su - postgres before execution.). So if it is a permissions issue with a file, it isn't obvious. Here's part of my pg_hba.conf file: local template1 postgres md5 local template1 all reject host all postgres 127.0.0.1 255.0.0.0 md5 hostssl template1 all 0.0.0.0 0.0.0.0 reject line #3 should be the one that pg_dumpall should be using. What am I doing wrong? The docs give examples, but not enough to get a handle on this problem. Again, any help would be greatly appreciated. April Carvalho