|
|
[NOVICE] ERROR: relation with OID XXXX does not exist
By David Monarchi at Aug 18, 2007, 05:10 am UTC
Hello - I'm using PG 8.2.4. I am executing a function which includes the code fragment shown below. It goes through the loop and then fails with the error message ERROR: relation with OID 591161 does not exist I'm using the serial data type as a way to record the order of the records based on a... More...
Hello -
I'm using PG 8.2.4.
I am executing a function which includes the code fragment shown below. It
goes through the loop and then fails with the error message
ERROR: relation with OID 591161 does not exist
I'm using the serial data type as a way to record the order of the records
based on a specific sort. I thought that by dropping the table and
recreating it that I would start off with a fresh counter each time. I
suspect that I do not understand something about how the SQL is compiled and
stored, and that the OID is related to this.
I have included the output below the code.
Any guidance would be appreciated.
Thank you.
david
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
FETCH classIDCursor INTO rec; -- get first record;
WHILE FOUND LOOP
numClassesRead = numClassesRead + 1;
classIdArray = rec.class_id_dom;
currentClassId = classIdArray[1];
-- drop & recreate table for sorting
drop table if exists dom1_classid_sorted_temp;
create table dom1_classid_sorted_temp (id_dom integer, class_rank_dom
serial) without oids;
RAISE NOTICE 'Table created for class id: %', CAST(currentClassId AS TEXT);
insert into dom1_classid_sorted_temp (id_dom)
select id_dom from dom1_temp
WHERE class_id_dom && classIdArray
order by fast_score_dom desc, link_score_dom desc;
RAISE NOTICE 'Data inserted for class id: %', CAST(currentClassId AS TEXT);
-- write the class rank order back into the temporary table
update dom1_temp
SET class_rank_dom = dom1_classid_sorted_temp.class_rank_dom
from dom1_classid_sorted_temp where dom1_temp.id_dom =
dom1_classid_sorted_temp.id_dom;
RAISE NOTICE 'Data updated for class id: %', CAST(currentClassId AS TEXT);
elapsedTime = clock_timestamp() - startTime;
RAISE NOTICE 'Elapsed time: % for class id: %', CAST(elapsedTime AS
TEXT), CAST(currentClassId AS TEXT);
FETCH classIDCursor INTO rec; -- get next record;
END LOOP;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
NOTICE: table "dom1_classid_sorted_temp" does not exist, skipping
CONTEXT: SQL statement "drop table if exists dom1_classid_sorted_temp"
PL/pgSQL function "deletethisversioncreateclassandsyntryxranksfordomain1"
line 29 at SQL statement
NOTICE: CREATE TABLE will create implicit sequence
"dom1_classid_sorted_temp_class_rank_dom_seq" for serial column
"dom1_classid_sorted_temp.class_rank_dom"
CONTEXT: SQL statement "create table dom1_classid_sorted_temp (id_dom
integer, class_rank_dom serial) without oids"
PL/pgSQL function "deletethisversioncreateclassandsyntryxranksfordomain1"
line 31 at SQL statement
NOTICE: Table created for class id: 1
NOTICE: Data inserted for class id: 1
NOTICE: Data updated for class id: 1
NOTICE: Elapsed time: 00:06:34.315307 for class id: 1
NOTICE: Table created for class id: 2
ERROR: relation with OID 591161 does not exist
SQL state: 42P01
Context: SQL statement "INSERT INTO dom1_classid_sorted_temp (id_dom) select
id_dom from dom1_temp WHERE class_id_dom && $1 order by fast_score_dom
desc, link_score_dom desc"
PL/pgSQL function "deletethisversioncreateclassandsyntryxranksfordomain1"
line 33 at SQL statement
1 Reply
|
|
|
[NOVICE] large duplicated files
By Ryan D. Enos at Aug 17, 2007, 1:53 pm UTC
Hi, I am very new to postgresql and am not really a programmer of any type. I use pgsql to manage very large voter databases for political science research. My problem is that my database is creating large duplicate files, i.e.: 17398.1, 17398.2, 17398.3, etc. Each is about 1g in size. I understand... More...
Hi,
I am very new to postgresql and am not really a programmer of any type.
I use pgsql to manage very large voter databases for political science
research. My problem is that my database is creating large duplicate
files, i.e.: 17398.1, 17398.2, 17398.3, etc. Each is about 1g in size.
I understand that each of these is probably a part of a file that pgsql
created because of a limit on file size and that they may be large
indexes. However, I don't know where these files came from or how to
reclaim the disk space.
I have extensively searched the archives and found that I am not the
first to have this problem. I have followed the suggestions to previous
posters, using a VACUUM FULL command and REINDEX. But nothing reclaims
the disk space. I have tried to see the type of file by using:
select * from pg_class where relfilenode =""
but this returns 0 rows.
How can I reclaim this space and prevent these files from being created
in the future?
Any help would be greatly appreciated.
Thanks.
Ryan
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly
3 Replies
|
|
|
[NOVICE] rogue process maxing cpu and unresponsive to signals
By Jon Jensen at Aug 16, 2007, 9:27 pm UTC
I've got a simple select query that runs every 10 minutes in order to update data in some external rrds (it lets us make pretty graphs and so forth). This has been working fine for months on end, when suddenly yesterday the badness happen. For some reason, this same query that normally takes a... More...
I've got a simple select query that runs every 10 minutes in order to
update data in some external rrds (it lets us make pretty graphs and so
forth). This has been working fine for months on end, when suddenly
yesterday the badness happen. For some reason, this same query that
normally takes a couple seconds has now been stuck running for over 24
hours, maxing the CPU and generally slowing other queries down.
The external script that initiates the query has been restarted, and
netstat no longer shows that connection. All subsequent calls of the
same query are quick as usual, but the renegade process lingers on,
unresponsive to signals. Some of the things I've tried so far
(unsuccessfully):
1. I've tried killing the process using kill from the command-line (INT,
TERM and HUP), as well as using pg_cancel_backend() via psql.
2. I've tried attaching gdb to the renegade process to see what it's
doing, but that hangs, forcing me to kill gdb (no problems attaching to
other postgres processes however).
Any other ideas? I'd like to avoid doing a kill -9 if at all possible.
The machine is debian (sarge) running postgres 8.1.
Jon
6 Replies
|
|
|
[NOVICE] How to create a spatial index on point data.
By s anwar at Aug 16, 2007, 9:13 pm UTC
How does once create a spatial index on point data. I did the following and got the error given below. create table foo(x point); create index foo_x on foo using gist(x); ERROR: data type point has no default operator class for access method "gist" HINT: You must specify an operator class for the... More...
How does once create a spatial index on point data. I did the
following and got the error given below.
create table foo(x point);
create index foo_x on foo using gist(x);
ERROR: data type point has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a
default operator class for the data type.
Thanks.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly
1 Reply
|
|
|
[NOVICE] horizontal clustering?
By Chirag Patel at Aug 16, 2007, 05:08 am UTC
My application is designed in such a way that the amount of data is expected to be enormous (in the terabytes). I would like to cluster the data and have the luxury of being able to slice up (denormalize) the database tables by user. In other words, users 1-50 and user 51-100 can be divided so that... More...
My application is designed in such a way that the amount of data is expected to be enormous (in the terabytes). I would like to cluster the data and have the luxury of being able to slice up (denormalize) the database tables by user. In other words, users 1-50 and user 51-100 can be divided so that queries across users is not needed.
Does PostgresSQL or any third party add-ons allow this scaling to automatically occur? When I say automatically, I mean:
1. New users are routed to a different database when the current database reaches a pre-defined capacity
2. Denormalization logic is provided so that the querying across databases is transparent to the user
I've heard this feature described as "federation" or "horizontal scaling" in the O'Reilly book "Building Scalable Web Sites"
I've never done clustering before so I'm clueless. Any ideas or better suggestions?
Thanks
Chirag
1 Reply
|
|
|
Re: [NOVICE] Install two different versions of postgres which should run in parallel
By Oliver Elphick at Aug 15, 2007, 12:42 pm UTC
If you run Debian or Ubuntu you can do this with the package system. apt-get install postgresql-8.2 postgresql-7.4 The two versions are automatically installed to run on different ports. Look at the docs and manpages for the postgresql-common package to see how it works. If you can't do that, you... More...
On Fri, 2007-08-10 at 14:33 +0300, Loredana Curugiu wrote:
> Hi all,
>
> I need to have two different vesions of postgres running in parallel
> on
> different ports. Does anyone knows how to install two different
> versions
> of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux
> operating system.
If you run Debian or Ubuntu you can do this with the package system.
apt-get install postgresql-8.2 postgresql-7.4
The two versions are automatically installed to run on different ports.
Look at the docs and manpages for the postgresql-common package to see
how it works.
If you can't do that, you can install a different version from source in
a different directory tree; then have its postmaster run on a different
port. Specify the port number when connecting so as to get to the
postmaster you want.
--
Oliver Elphick [email protected: o...@lfix.co.uk]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly2 Replies
|
|
|
[JDBC] Re: [NOVICE] Install two different versions of postgres which should run in parallel
By Sean Davis at Aug 15, 2007, 11:58 am UTC
You can install from source and provide the --prefix argument to the configure command. Sean ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not... More...
Loredana Curugiu wrote:
> Hi all,
>
> I need to have two different vesions of postgres running in parallel on
> different ports. Does anyone knows how to install two different versions
> of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux
> operating system.
You can install from source and provide the --prefix argument to the
configure command.
Sean
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
4 Replies
|
|
|
[NOVICE] foreign key constraint to multiple tables
By Kevin McCarthy at Aug 15, 2007, 01:49 am UTC
I have a table with a column that will join with one of several tables based on the value of another column. I'm not sure if this column can be set as a foreign key to any of these tables, may be simply bad design on my part. The item_type_id column of a record of 'A' will reference one of the... More...
I have a table with a column that will join with one of several tables based
on the value of another column. I'm not sure if this column can be set as a
foreign key to any of these tables, may be simply bad design on my part. The
item_type_id column of a record of 'A' will reference one of the 'type'
table records depending upon what the value of the record's item_type column
is.
Is there a way to set the item_type_id column in Table A as a foreign key to
the other tables depending? Or would there be a better way to design this
schema?
TIA
CREATE TABLE ABC(
id SERIAL PRIMARY KEY,
item_type character varying(10) NOT NULL,
item_type_id INTEGER
);
CREATE TABLE typeA(
typeA_id SERIAL PRIMARY KEY
);
CREATE TABLE typeB(
typeB_id SERIAL PRIMARY KEY
);
CREATE TABLE typeC(
typeC_id SERIAL PRIMARY KEY
);
--
Kevin McCarthy
kemccarthy1@gmail.com
3 Replies
|
|
|
[NOVICE] data storage for timestamp data type?
By Chirag Patel at Aug 14, 2007, 7:56 pm UTC
The timestamp data type in postgreSQL has the option for a parameter 'p' that defines the precision from 0-6. The documentation indicates 8 bytes as the default size for the column. If I use timestamp(3) as an example, will postgres still allocate the entire 8 bytes for the column or reduce it?... More...
The timestamp data type in postgreSQL has the option for a parameter 'p' that defines the precision from 0-6. The documentation indicates 8 bytes as the default size for the column.
If I use timestamp(3) as an example, will postgres still allocate the entire 8 bytes for the column or reduce it?
Thanks!
Chirag
PS. By the way, I think clustering question hit the list 3 times by accident last week. I apologize, I used the wrong e-mail address and assumed the first 2 didn't make it.
2 Replies
|
|
|
[NOVICE] Postmaster start up problems (can't create lock file )
By Jasbinder Singh Bali at Aug 14, 2007, 6:23 pm UTC
I'm trying to start my postgres server and it gives me the following errror FATAL: could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied Now obviously to touch the file that the error message is talking about, one needs root permission that the user account under... More...
I'm trying to start my postgres server and it gives me the following errror
FATAL: could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock":
Permission denied
Now obviously to touch the file that the error message is talking about, one
needs root permission that the user account under which my postmaster is
runnnig doesn't have.
Can anyone tell me the fix.
Thanks
3 Replies
|
|
|
[NOVICE] More of a SQL question, I guess.
By Howard Eglowstein at Aug 14, 2007, 3:11 pm UTC
I have a database with 150 fields per row. For historical reasons, it's broken into three databases, data_a, data_b and data_c. One of the fields is a timestamp and one of them is a serial number that came in from a remote machine that reports its status every day. If I use a command like 'SELECT... More...
I have a database with 150 fields per row. For historical reasons, it's
broken into three databases, data_a, data_b and data_c. One of the
fields is a timestamp and one of them is a serial number that came in
from a remote machine that reports its status every day.
If I use a command like 'SELECT MAX(Timestamp) FROM data_a WHERE field1
= '0001', I can get back the time of the latest report. I can then issue
the command 'SELECT * from data_a, data_b, data_c WHERE
data_a.id=data_b.id AND data_a.id=data_c.id and field1='0001' to get the
150 fields for that report. It works fine, but it takes a while to
respond when the database is the better part of a million records.
If it were just one serial number, the two queries would be okay because
It really only takes a minute. The problem I have is that we're hoping
to have thousands of machine in the field (this is a just a test
database) and clearly this approach won't work for thousands of serial
numbers. The second method I did was to simply use 'SE:ECT * from
data_a.....' to get all million records, and have my C code look for
serial numbers in each line and keep the latest by timestamp. That takes
about as long as doing the first procedure 3 times, but it gives me the
latest data for all of the serial numbers in the system. That's
perfectly cool, except that it won't scale nicely. If the web code that
does the search isn't on the same machine that holds the data, we'll
have to ship gigabytes of data over the network for each search.
What I'd *like* is something that uses groups and MAX() to do this in
one SQL command. It would group the data by the serial number (field1),
find the record in each group with the maximum timestamp value and
return all 150 fields. Is this possible? I thought of using unions, but
I think I have to issue a pretty long command for each group and the PG
buffers probably will max out after some relatively small number, no?
Any thoughts would be appreciated.
Howard
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
2 Replies
|
|
|
Re: [SQL] Install two different versions of postgres which should run in parallel
By gh...@fmed.uba.ar at Aug 14, 2007, 07:37 am UTC
Shure. You just have to take care about --prefix (executable path) and --with-pgport (default port) when you ./configure, and also take care of the datadir you chose when executing initdb. Good luck. Gerardo ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill... More...
Loredana Curugiu wrote:
>Hi all,
>
>I need to have two different vesions of postgres running in parallel on
>different ports. Does anyone knows how to install two different versions
>of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux
>operating system.
>
>Any information would greatly be appreciated.
>
>Loredana
>
>
> Shure. You just have to take care about --prefix (executable path) and --with-pgport (default port) when you ./configure, and also take care of the datadir you chose when executing initdb. Good luck. Gerardo
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
3 Replies
|
|
|
[NOVICE] analyzing query results
By Lonni J Friedman at Aug 13, 2007, 10:28 pm UTC
Greetings, I have a PostgreSQL-8.x database that contains several tables which hold the results of automated regression tests. Each table contains a specific class of test, however the results from different subtests of that class of test reside in a single table. For example, I have tests A, B & C... More...
Greetings,
I have a PostgreSQL-8.x database that contains several tables which
hold the results of automated regression tests. Each table contains a
specific class of test, however the results from different subtests of
that class of test reside in a single table. For example, I have
tests A, B & C stored in tables a, b & c, and then there are subtests
such as 'build a' and 'test a' in table a, etc.
Once each day, I run a report against the contents of these tables,
which then emails a report which lists only the subtests which have
failed in the previous 24 hours. For this, I'm doing a SQL query
along the lines of:
select subtest,os,arch,info FROM ${table} WHERE (SELECT now() -
interval '24 hours' < date_created::timestamp)='t' AND
current_status='FAILED' ;
The problem here is that occasionally the same subtest runs more than
once in a 24 hour period (although not often), and not all of the test
runs will have the same result (current_status). To make matters more
complicated when this happens, the additional runs will have a
current_status of PASSED, yet my automated report still grabs the
older FAILED test result, and this confuses the hell out of the people
reviewing the report, since its technically passed already.
My problem is that I can't think of a non-trivial way to adjust the
SQL query so that it will only capture the last time the subtest ran,
in the event that it ran multiple times during a 24 hour window.
There are different permutations of os, arch & info (actually about
400 in total), so explicitly iterating through all of them is going to
be an ugly and expensive hack that I really want to avoid.
If it helps any, each row in the assorted tables has a unique id
number that auto-increments at insertion, but beyond that there isn't
any other non-trivial way to differentiate one row from another as the
fields are an assortment of text, integer, boolean & char.
Does anyone have any good ideas? thanks in advance.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman [email protected: net...@gmail.com]
LlamaLand http://netllama.linux-sxs.org
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org7 Replies
|
|
|
[NOVICE] resetting superuser password
By Andrew C. Uselton at Aug 13, 2007, 5:42 pm UTC
Howdy all, We do not know the superuser (postgres) password to our database cluster. The auth configuration (pg_hba.conf) has the only auth method set to: Type db user method param local all all md5 sameuser I have root access to the machine, but I don't see how to reset the superuser password. The... More...
Howdy all,
We do not know the superuser (postgres) password to our database
cluster. The auth configuration (pg_hba.conf) has the only auth method
set to:
Type db user method param
local all all md5 sameuser
I have root access to the machine, but I don't see how to reset the
superuser password. The thing I tried was to briefly replace the above
auth configuration with
local all all trust
and then reset the password (after the required SIGHUP). The password
update apparently succeeded, but when I put the md5 auth method back in
place I still did not have access. I conclude that "regular" and md5
passwords are not stored in the same place or in the same way, which
isn't all that surprising. Any tricks you can send my way would be
most helpful.
Cheers,
Andrew
1 Reply
|
|
|
[NOVICE] Free designable front end for PostgreSQL
By Peter N. Ernst at Aug 11, 2007, 2:13 pm UTC
To Whom It May Concern: Wanted to set up a database in a small network with PostgreSQL on Windows. = Before I start there is one question: Is there free/opensource-ware to set up a self designed user-interfaces on = the =93workstationss=94 with forms and buttons, in which mere mortal people= can... More...
To Whom It May Concern:
Wanted to set up a database in a small network with PostgreSQL on Windows. =
Before I start there is one question:
Is there free/opensource-ware to set up a self designed user-interfaces on =
the =93workstationss=94 with forms and buttons, in which mere mortal people=
can put data in and print of some predefined reports?
Can somebody give me a hint please?
Thanks in advance
Peter
_____________________________________________________________________
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.web.de/?mc=3D100071&distributionid=3D000000000066
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
4 Replies
|
|
|
[NOVICE] Adding columns dynamically to a table
By Avinash Lakshman at Aug 11, 2007, 04:16 am UTC
Hi I am new to PostGres and I am evaluating it for our purposes. The biggest requirement I have is that I need the ability to constantly add columns dynamically to a table. How is the efficiency of such an operation ? Basically the number of columns differ per key. Are there any techniques that are... More...
Hi
I am new to PostGres and I am evaluating it for our purposes. The biggest
requirement I have is that I need the ability to constantly add columns
dynamically to a table. How is the efficiency of such an operation ?
Basically the number of columns differ per key. Are there any techniques
that are available with Postgres for such operations ?
Thanks
A
1 Reply
|
|
|
[NOVICE] pg_dump/pg_dumpall
By Andrew C. Uselton at Aug 11, 2007, 04:10 am UTC
Greetings, I was just trying to get a live backup of my database and had the following error: uselton]# pg_dumpall > /tmp/osp5_pgsql.dump Password: pg_dumpall: could not connect to database "template1": FATAL: Password authentication failed for user "root" If it really is asking for the 'root'... More...
Greetings,
I was just trying to get a live backup of my database and had the following error:
uselton]# pg_dumpall > /tmp/osp5_pgsql.dump
Password:
pg_dumpall: could not connect to database "template1": FATAL: Password authentication failed for user "root"
If it really is asking for the 'root' password I am certainly giving it correctly. I am root as I issue the command as well. I've tried a few other guesses at what it might _really_ be wanting (including using an explicit '-h host -p port'), but with no better luck. Any hints would be most appreciated.
Cheers,
Andrew
6 Replies
|
|
|
[NOVICE] could not serialize access due to concurrent update
By Brian Hurt at Aug 10, 2007, 9:34 pm UTC
Actually, I have something of a weird problem. I'm trying to test some code of mine to handle cases where a retry is needed when working with serializable transactions. My problem is that I can *not* figure out how to trigger this error: from:... More...
Actually, I have something of a weird problem. I'm trying to test some
code of mine to handle cases where a retry is needed when working with
serializable transactions. My problem is that I can *not* figure out
how to trigger this error:
> UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands
> behave the same as SELECT in terms of searching for target rows: they
> will only find target rows that were committed as of the transaction
> start time. However, such a target row may have already been updated
> (or deleted or locked) by another concurrent transaction by the time
> it is found. In this case, the serializable transaction will wait for
> the first updating transaction to commit or roll back (if it is still
> in progress). If the first updater rolls back, then its effects are
> negated and the serializable transaction can proceed with updating the
> originally found row. But if the first updater commits (and actually
> updated or deleted the row, not just locked it) then the serializable
> transaction will be rolled back with the message
>
>ERROR: could not serialize access due to concurrent update
>
> because a serializable transaction cannot modify or lock rows changed
> by other transactions after the serializable transaction began.
>
from:
http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-SERIALIZABLE
I know I need at least two different transactions on the same database
stepping on each other somehow, but the worst case I can come up with is
a deadlock case (which I want to retry, but that I know works). But I
want to trigger the above error to make sure it works. Most of the
examples I've come up with don't even generate an error, one transaction
simply blocks until the other completes.
Could somebody please send me a simple example of how I can trigger this
error? I'm on postgres 8.1.4.
For the record, Postgresql is amazingly, sometimes even annoyingly, robust.
Brian
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly
1 Reply
|
|
|
[NOVICE] Configuring Postgresql to use PAM
By Marcin Porwit at Aug 10, 2007, 7:58 pm UTC
Hi, I'm trying to set up my DB to authenticate against a PAM back-end (in this case just a plain old local password DB). Here's my configuration as it currently stands: positionwhile[pg]% grep pam /var/lib/pgsql/metadata/pg_hba.conf # "krb4", "krb5", "ident", or "pam". Note that "password" sends... More...
Hi,
I'm trying to set up my DB to authenticate against a PAM back-end (in
this case just a plain old local password DB).
Here's my configuration as it currently stands:
positionwhile[pg]% grep pam /var/lib/pgsql/metadata/pg_hba.conf
# "krb4", "krb5", "ident", or "pam". Note that "password" sends
passwords
local authtest all pam
postgresql
positionwhile[pg]% more /etc/pam.d/postgresql
#%PAM-1.0
auth required pam_stack.so service=system-auth
account required pam_stack.so service=system-auth
The system is a CentOS 4.4 box, and I'm running PostgreSQL 8.0.6.
When I test the core pam functionality using pamtester, I get the
following:
positionwhile[pamtester-0.1.2]% src/pamtester -v postgresql
mporwit authenticate
pamtester: invoking pam_start(postgresql, mporwit, ...)
pamtester: performing operation - authenticate
Password:
pamtester: successfully authenticated
Looks like a success to me.
However, when I try to get psql to do password authentication, I get the
following:
positionwhile[postgresql-8.0.13]% psql authtest
Password:
psql: FATAL: PAM authentication failed for user "mporwit"
And this shows up in my logs:
LOG: connection received: host=[local] port=
DEBUG: forked new backend, pid=1430 socket=11
DEBUG: received password packet
LOG: pam_authenticate failed: Authentication failure
FATAL: PAM authentication failed for user "mporwit"
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
DEBUG: reaping dead processes
DEBUG: server process (PID 1430) exited with exit code 0
Any insight here would be appreciated. Are there any other tweaks I may
have missed?
Thanks,
Marcin
mporwit [AT] yahoo-inc [DOT] com
0 Replies
|
|
|
[NOVICE] restoring with sql script
By Demian Quinonez at Aug 10, 2007, 07:58 am UTC
Help! I am creating a fail over server and one element of my system is using postgresql 8.0. I created a dump of the current DB and I want to restore "as is" on the fail over server. On the fail over I am using the command "psql -U <username> < <sql script>" to try to restore the the DB but when I... More...
Help!
I am creating a fail over server and one element of my system is using
postgresql 8.0. I created a dump of the current DB and I want to
restore "as is" on the fail over server. On the fail over I am using
the command "psql -U <username> < <sql script>" to try to restore the
the DB but when I press enter it does not ask me for my password and
goes directly to the FATAL error of password is not authenticated.
Any idea what I am doing wrong? I am following the instructions given
by our vendor in how to back up and restore the pg DB.
System Info:
Win2003 server
Demian Qui=F1onez
Systems Developer
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly
2 Replies
|
|
 | |
