Re: [ADMIN] Diagnose Database Corruption

[Bruce Momjian]

We don't have a checker because if it is corrupt, it will mention
something in the server logs or correct it automatically.  Corruption is
very rare, usually hardware flakiness.

---

[EMAIL PROTECTED] wrote:
> Does PostgreSQL have a tool to diagnose and fix database corruption?  Is
> there any kind of database consistency checker?  I couldn't find anything in
> the doc...
> 
> Thanks!
> 
> Alex Pollock
> WFSC Data Services District
> Database Administration
> MAC A0186-032
> ( 415) 243-5931
> 
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Disk I/O Monitoring

[Mendola Gaetano]

wrote "Kris Kiger" <[EMAIL PROTECTED]>
> Hi all, I'm looking for a good way to monitor disk I/O on a linux 
> system.  Specifically, I would like to know when postgres is making 
> page-ins and page-outs.  If anyone knows of a good utility, please let 
> me know!  Thank you for the help

try  iostat and vmstat

Regards
Gaetano Mendola


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [ADMIN] Uninitialized page - fixing

[Björn Metzdorf]

>> recently we are suffering from repeated
>> WARNING:  Rel pg_toast_77798203: Uninitialized page 25489 - fixing
>
> Hmm.  There is a known mechanism for that to happen in 7.2 and before,
> but not in 7.3.  Can you see any pattern in the messages?

Here is another example:

2003-06-05 21:27:08 WARNING:  Rel psessions: Uninitialized page 185 - fixing

psessions is often read from and written to, but it is not very big
(thousands, perhaps tenthousands of rows) and it is regularly vacuumed
(every 5 to 15 minutes). These messages appear under heavy load only (more
than 700 concurrent connections to the backend).

Since then (2003-06-05) we didn't get this message again.

I cannot see any pattern there, but at least with our old server, those
messages came just ahead some hardware problems (pg_clog problems, tables
were gone). But our new server right now is running very smoothly.

I will upgrade to 7.3.3 as soon as I find some time.

Regards,
Bjoern


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] Uninitialized page - fixing

[Tom Lane]

=?iso-8859-1?Q?Bj=F6rn_Metzdorf?= <[EMAIL PROTECTED]> writes:
> recently we are suffering from repeated
> WARNING:  Rel pg_toast_77798203: Uninitialized page 25489 - fixing

Hmm.  There is a known mechanism for that to happen in 7.2 and before,
but not in 7.3.  Can you see any pattern in the messages?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] DUMPall PG7.1.3 restore to PG7.3.2

[Mendola Gaetano]

"Eko Pranoto" <[EMAIL PROTECTED]> wrote:
> I have error when restoring file DUMPall PG7.1.3 to 
> PG7.3.2
>   with error message "function varchar(integer) does not 
> exist"
>   
> any one can help ?

Postgres 7.3.X is missing of a lot implicit cast present before
so I suggest you to define your cast integer => varchar before 
to import the DB.


Regards
Gaetano


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[ADMIN] about pg_hba.conf ?

[utomo restu]

Dear All.

At this moment I want to make the connection with client among server database, 
where is needed by password to carry out connection 
At the pg_hba.conf file, on AUTH_type i type MD5.
when program is implemented, to emerge orders the error,  like this :
"The authentication types 5 is NOT SUPPORTED. Check That is you have CONFIGURED of the 
pg_hba.conf file to include the OR SUBNET's ip address CLIENTS, and that is it To is 
to using on behalf of authentication schemes SUPPORTED by Driver. 
Driver that I possess: 
1. JDBC7. 0-1 0 1. 1. JAR 
2. JDBC7. 0-1 0 1. 2. JAR 
File this driver was in when to process POSTGRESQL's installation 7.2 at directory / 
usr/share/PGSQL 
please help me to solve my problem.

best regards
-- 
__
http://www.linuxmail.org/
Now with e-mail forwarding for only US$5.95/yr

Powered by Outblaze

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [ADMIN] Problem with tcp/ip connection, postgresql.conf

[Tom Lane]

"Nick Fankhauser" <[EMAIL PROTECTED]> writes:
> If he starts the postmaster with the -i flag, he can get a connection.
> If he sets tcpip_socket = 1 in postgresql.conf, but doesn't use the -i flag
> on startup, it doesn't work.

> So far, my suggestions have been to make sure the postgresql.conf file
> is in the data directory and is readable. Beyond this I can't think of
> anything,

A couple of simple mistakes we've seen recently are to forget to remove
the '#' comment leader from the line, or to be editing the wrong copy of
the file (not the one in $PGDATA).

While experimenting with this, I noted that the postmaster will not
complain if postgresql.conf is not found --- though it will complain if
it finds the file but can't read it (eg permission failures).  It seems
to me this is a bug, or at any rate a bad idea.  There should be at least
a warning message if the postgresql.conf file isn't found, and quite
possibly the postmaster should refuse to start at all, on the grounds
that you may have pointed it to a a bogus data directory.

Any opinions about this out there?

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Help! Error when calling a function

[Mendola Gaetano]

"Ben Schneider" <[EMAIL PROTECTED]> wrote:


> I am receiving the following error when I call a simple function:

> Warning: pg_exec() [function.pg-exec 
> ]: Query failed: ERROR: fmgr_info: function 3036754: cache lookup failed .
> in /var/www/html/cmwareben/db_pgsql.php on line 93
> Database error: Invalid SQL: SELECT note_num,
> fn_concat(rtrim(substring(note,0,40)), '...') AS note FROM feedback_notes
> ORDER BY note_num
> PostgreSQL Error: 1 (ERROR: fmgr_info: function 3036754: cache lookup
failed
)
> Session halted.

> Here is the fn_concat function that I am calling


> CREATE FUNCTION "fn_concat"(text, text) RETURNS text AS 'begin
> return $1 || $2;
> end;' LANGUAGE 'plpgsql'

>I have the same function in another DB on the same server and it is working
> fine. The only change I made to the DB was to drop the feedback_notes
table
> and recreate it with an additional column (New column = 'status'). The
> select was working before I made this change and now it doesn't. No
changes
> were made to my PHP scripts at all! I hadn't don't even call the new
column
> in my select.

> What the heck happened?


Hi, I guess that you are using a prior the 7.3.X, this error happen each
time
that a DB object ( view table function ) use another DB object and the
object used
was deleted and recreated. Using the version 7.3.X you can specify for the
view or
eather for function the RECREATE  ability ( the internal object ID does not
change ).

Upgrading to version 7.3. X ( I suggest you the 7.3.3 ) you avoid the
deletetion of an
object if other object are depending on it and also you can specify the
"REPLACE"
when you modify a view or a function.


Regards
Gaetano Mendola









---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [ADMIN] White Paper

[Robert Treat]

That's a pretty vague question, what kind of information do you need in
the white paper?

Robert Treat

On Thu, 2003-06-05 at 15:49, Terry Hampton wrote:
> 
>   All,
> 
>   My CEO has asked for a PostgreSQL White Paper
>   to give to a Client.Before I start typing - anyone
>   have one or can point me to one ?
> 
>   Many thanks ( and thanks for the info this group provides ),
> 
>   Terry Hampton
>   Cincinnati, Ohio
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] unsubscribe

[Jonathan Gardner]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Thursday 05 June 2003 14:38, Vlad Efman wrote:
> unsubscribe

send an email to [EMAIL PROTECTED], and follow the 
instructions in the reply.

In the future, don't ever post an "unsubscribe" message. You were the one who 
wanted to subscribe. You were the one who sent the confirmation. You are the 
one who should take the time to learn how to do it yourself.

- -- 
Jonathan Gardner <[EMAIL PROTECTED]>
(was [EMAIL PROTECTED])
Live Free, Use Linux!
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE+5LwbWgwF3QvpWNwRAsIaAKCnyEXxd3VCZZJHhZQi207Bd/JEDgCglD1j
hRdyeXdHjVHHhjcZnZfr8VE=
=GsBo
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Problem with tcp/ip connection, postgresql.conf

[Nick Fankhauser]

> While experimenting with this, I noted that the postmaster will not
> complain if postgresql.conf is not found --- though it will complain if
> it finds the file but can't read it (eg permission failures).  It seems
> to me this is a bug, or at any rate a bad idea.  There should be at least
> a warning message if the postgresql.conf file isn't found, and quite
> possibly the postmaster should refuse to start at all, on the grounds
> that you may have pointed it to a a bogus data directory.
>
> Any opinions about this out there?

I agree that there should at least be a warning if the file is not found.
Ironically, the problem in this case turned out to be the permissions, so
apparently the existing warning didn't clue one person into what was
happening until I made the suggestion about permissions more explicitly.

-Nick


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: [ADMIN] Diagnose Database Corruption

[Mendola Gaetano]

"Bruce Momjian" <[EMAIL PROTECTED]> wrote:
> We don't have a checker because if it is corrupt, it will mention
> something in the server logs or correct it automatically.  Corruption is
> very rare, usually hardware flakiness.

I agree about the fact that corruption is very rare but sometimes I got
a "duplicate primary key" and the only solution was dump the DB, 
edit the dump and restore the DB.
If the dump is uge ( like 400 MB ) is a lot of down time :-(
The problem disappeared after removing the reindex on that table
scheduled each morning.

Regards
Gaetano Mendola.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] What could be the problem?

[Mendola Gaetano]

"Rafael Mauricio González Palacios" <[EMAIL PROTECTED]> wrote:
> Hello.
>
> We had a very serious trouble last week with our databases in Postgres and
we
> wanna know if maybe someone can help us to find the problem.
>
> The trouble was that a day some databases in Postgres appear without any
row
> of information; some other, doesn't led us describe the tables (\d), or
list
> the data bases (\l), or when we try to run an invalid command Postgres
gave us
> a 'segmentation fault' message.

Did you had power failure recently ?

> To get back the information was necessary to recover the backup we had
made
> the night before and recompile Postgres.
>
> We have Postgres 7.1.1. and Red Hat 7.2

Try to upgrade to Postgres 7.3.3 you'll not find the RPM for RH 7.2
but look this my recent post:
http://archives.postgresql.org/pgsql-admin/2003-05/msg00409.php


Regards
Gaetano Mendola



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[ADMIN] Syncing DBs prior to RServ replication

[Michael A Nachbaur]

I'm looking at syncronizing two database servers with RServ (production / "hot 
standby"), and I'm trying to figure out a way to get the initial database on 
the slave server syncronized with the master.

Normally, I would 1) turn off all clients, 2) dump the database from the 
master, 3) restore it into the slave, 4) turn clients back on, and 5) 
replicate regularly.

Unfortunately I'm running the master in a production environment where 
anything more than 5 minutes of downtime is a really "Bad Thing®".  The 
database dump is about 5G, and so leaving the master down for that entire 
time isn't possible.  Is there a recommended way for performing such a "sync" 
without any lengthy downtime?

I'm guessing I could start my dump, and then immediately afterward create the 
replication tables in the master.  Since the dump is transactional (is it?), 
it shouldn't include the replication tables or the replication "snapshot" 
information in it's dump, and by the time I finally finish importing the dump 
in the new server, I should be able to perform the replication and pick up 
all the new / changed records since I started the DB dump.

Will this work?  Is there a better recommended way?  Thanks.

-- 
Michael A Nachbaur <[EMAIL PROTECTED]>


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [ADMIN] Disk I/O Monitoring

[Michael A Nachbaur]

On Friday 06 June 2003 09:42 am, Kris Kiger wrote:
> Hi all, I'm looking for a good way to monitor disk I/O on a linux
> system.  Specifically, I would like to know when postgres is making
> page-ins and page-outs.  If anyone knows of a good utility, please let
> me know!  Thank you for the help

If you're on Linux, try KSysGuard, either the stand-alone app or the Kicker 
applet.

-- 
Michael A Nachbaur <[EMAIL PROTECTED]>


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html