Re: [GENERAL] Typecast

[Nick Raj]

Thanks dude

On Mon, Apr 18, 2011 at 2:25 PM, Chetan Suttraway <
chetan.suttra...@enterprisedb.com> wrote:

>
>
> On Fri, Apr 15, 2011 at 10:29 PM, Nick Raj  wrote:
>
>> Hi,
>> Can anybody tell me how to typecast data type Point into Datum?
>>
>> Thanks
>> Nick
>>
>
> Assuming you are referring to  c-code,
>
> Point somepoint;
> Datum result;
>
> result = PointPGetDatum(&somepoint)
>
> You can also checkout src/backend/utils/adt/geo_ops.c which has code for
> conversion between
> datum and point and vice-versa.
>
>
>
> --
> Regards,
> Chetan Suttraway
> EnterpriseDB , The Enterprise 
> PostgreSQL
>  company.
>
>
>
>

Re: [GENERAL] Help - corruption issue?

[Scott Marlowe]

On Mon, Apr 18, 2011 at 9:23 PM, Phoenix Kiula  wrote:
>> System logs maybe?  Something about a process getting killed?  Have
>> you tried turning up the verbosity of the pg logs?
>
>
> Syslog has to be compiled with PG? How do I enable it? Where should I
> look for it?
>
> The documentation, whenever it mentions "syslog", always just assumes
> the expression "If syslog is enabled". Well where do I enable it?  -
> http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html
>
> Would appreciate some guidance on this.

No I meant the system logs, the ones in /var/log/yadayada.  Like
/var/log/message, things like that.  See if any of them have anything
interesting happening when things go badly.

syslog is logging using the syslog system which puts logs from various
processes into the /var/log dir, like /var/log/pgsql.  Assuming you
have a stock RHEL install I'd expect the pgsql logs to be in
/var/log/pgsql or thereabouts.

>> We gotta check one thing at a time really.
>>
>> If you copy the dir off to another machine and run pgsql 8.2.latest or
>> thereabouts, can you then create the index?
>
>
> I will try this. Transferring 106GB of data, even zipped, is a huge
> ask and just the management will take over a day or so. I was hoping
> we could do without this.

On a fast network it should only take a few minutes.  Now rsyncing
live 2.4 TB databases, that takes time. :)  Your raptors, if they're
working properly, should be able to transfer at around 80 to
100Megabytes a second.  10 to 15 seconds a gig.  30 minutes or so via
gig ethernet.  I'd run iostat and see how well my drive array was
performing during a large, largely sequential copy.

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

Re: [GENERAL] Help - corruption issue?

[Phoenix Kiula]

> System logs maybe?  Something about a process getting killed?  Have
> you tried turning up the verbosity of the pg logs?


Syslog has to be compiled with PG? How do I enable it? Where should I
look for it?

The documentation, whenever it mentions "syslog", always just assumes
the expression "If syslog is enabled". Well where do I enable it?  -
http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html

Would appreciate some guidance on this.


> We gotta check one thing at a time really.
>
> If you copy the dir off to another machine and run pgsql 8.2.latest or
> thereabouts, can you then create the index?


I will try this. Transferring 106GB of data, even zipped, is a huge
ask and just the management will take over a day or so. I was hoping
we could do without this.

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

Re: [GENERAL] Help - corruption issue?

[Scott Marlowe]

On Mon, Apr 18, 2011 at 8:52 PM, Phoenix Kiula  wrote:
> On Tue, Apr 19, 2011 at 8:35 AM, Scott Marlowe  
> wrote:
>> On Mon, Apr 18, 2011 at 5:44 PM, Tomas Vondra  wrote:
>>>
>>> Still, do the file backup as described in the previous posts. You could
>>> even do an online backup using pg_backup_start/pg_backup_stop etc.
>>
>> As soon as you have a working file system backup, get the tw_cli
>> utility for the 3ware cards downloaded and LOOK at what it has to say
>> about your RAID controller, drives, and array health.
>
>
>
> I am with SoftLayer. They're a very professional bunch. They even
> changed my BBU last night. The RAID card is working. The memory and
> the hardware are also tested.

So, RAID is good for sure?  As in someone logged into the machine, and
went to the tw_cli utility and asked it about the status of the
physical drives and virtual RAID array and the card said yes they're
good?  No bad sectors being remapped?  Hmmm.  One of my old tests when
things were acting up was to see if the server could compile the linux
kernel or pgsql back when it took 1.5 hours to do.  If you keep
getting sig 11s on production kernel compiles something's wrong with
the system, software or hardware.

> I have now upgraded to 8.2.19.
>
> Then I restarted the server, and dropped indexes. When I recreate the
> first index, the same thing happens:
>
> --
> # CREATE INDEX idx_links_userid ON links (user_id);
> server closed the connection unexpectedly
>        This probably means the server terminated abnormally
>        before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> --

What do the Postgresql logs say at this time?  oh wait...

> There is nothing going on in the server other than this command. All
> other users are blocked!
>
> Logging is enabled but does not have anything!

System logs maybe?  Something about a process getting killed?  Have
you tried turning up the verbosity of the pg logs?

> I am now worried. What is this problem?

We gotta check one thing at a time really.

If you copy the dir off to another machine and run pgsql 8.2.latest or
thereabouts, can you then create the index?

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

Re: [GENERAL] replay doesn't catch up with receive on standby

[Steven Parkes]

> Was there idle-in-transaction in the master when the problem happened?

Shouldn't have been, but that's what I was wondering, too. I didn't check. Not 
sure I know how to check.

That was my guess and I mostly wanted to confirm that that could happen. Does 
seem like an edge case. I don't expect uncommitted transactions to be hanging 
around in general, or even long periods between some kind of write.

Thanks for the help.



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

Re: [GENERAL] Help - corruption issue?

[Phoenix Kiula]

On Tue, Apr 19, 2011 at 8:35 AM, Scott Marlowe  wrote:
> On Mon, Apr 18, 2011 at 5:44 PM, Tomas Vondra  wrote:
>>
>> Still, do the file backup as described in the previous posts. You could
>> even do an online backup using pg_backup_start/pg_backup_stop etc.
>
> As soon as you have a working file system backup, get the tw_cli
> utility for the 3ware cards downloaded and LOOK at what it has to say
> about your RAID controller, drives, and array health.



I am with SoftLayer. They're a very professional bunch. They even
changed my BBU last night. The RAID card is working. The memory and
the hardware are also tested.

I have now upgraded to 8.2.19.

Then I restarted the server, and dropped indexes. When I recreate the
first index, the same thing happens:

--
# CREATE INDEX idx_links_userid ON links (user_id);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
--


There is nothing going on in the server other than this command. All
other users are blocked!

Logging is enabled but does not have anything!

I am now worried. What is this problem?

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

Re: [GENERAL] replay doesn't catch up with receive on standby

[Fujii Masao]

On Tue, Apr 19, 2011 at 10:28 AM, Steven Parkes  wrote:
>> Did you run query on the standby?
>
> Yup. Both standbys. They both responded the same way.
>
>> If yes, I guess that query conflict prevented
>> the reply location from advancing.
>> http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT
>
> The standbys were idle and this was a persistent state. I restarted the 
> standbys and they stayed in this state. Am I missing something? I thought 
> these conflicts were related to queries against the standbys but there 
> shouldn't have been any that I'm aware. Certainly none should survive a 
> restart ...
>
> Am I missing something about the conflict?
>
> It also seems notable that a new commit on the master cleared the issue ... 
> Does that seem like the hot standby conflict case?

Probably no.

Was there idle-in-transaction in the master when the problem happened?
If yes, this can happen. In that case, only half of WAL record can be written
to the disk by walwriter and sent to the standby by walsender. The rest
will be written and sent after you'll have finished the transaction. In this
case, the receive location indicates the end of that WAL record obviously.
OTOH, since that half-baked WAL record cannot be replayed, the replay
location cannot advance and still has to indicate the end of previous WAL
record.

If you issue new commit, all of the WAL record is flushed to the standby.
So that WAL record was replayed and the replay location advanced. I guess
you observed the above situation.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [GENERAL] replay doesn't catch up with receive on standby

[Steven Parkes]

> Did you run query on the standby?

Yup. Both standbys. They both responded the same way.

> If yes, I guess that query conflict prevented
> the reply location from advancing.
> http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT

The standbys were idle and this was a persistent state. I restarted the 
standbys and they stayed in this state. Am I missing something? I thought these 
conflicts were related to queries against the standbys but there shouldn't have 
been any that I'm aware. Certainly none should survive a restart ...

Am I missing something about the conflict?

It also seems notable that a new commit on the master cleared the issue ... 
Does that seem like the hot standby conflict case?


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

Re: [GENERAL] replay doesn't catch up with receive on standby

[Fujii Masao]

On Tue, Apr 19, 2011 at 9:00 AM, Steven Parkes  wrote:
> This is on 9.0.3: I've got two dbs running as standby to a main db. They 
> start up fine and seem to think they're all caught up (by /var/log logs), but
>
> SELECT pg_last_xlog_receive_location() AS receive, 
> pg_last_xlog_replay_location() AS replay;
>
> reports replay behind receive and it doesn't change. This is on both dbs.
>
> Notably the main db isn't (wasn't) doing anything, so no new commits were 
> causing things to move forward. I did a write to it and both slaves moved 
> both their recieved and replay serial numbers up.
>
> Is there a valid situation where an idle master/standby setup could remain 
> with replay behind received indefinitely? (My nagios monitor isn't very happy 
> with that (at present)) and before changing that I'd like to understand 
> better what's going on.)

Did you run query on the standby? If yes, I guess that query conflict prevented
the reply location from advancing.
http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [GENERAL] Help - corruption issue?

[Scott Marlowe]

On Mon, Apr 18, 2011 at 5:44 PM, Tomas Vondra  wrote:
>
> Still, do the file backup as described in the previous posts. You could
> even do an online backup using pg_backup_start/pg_backup_stop etc.

As soon as you have a working file system backup, get the tw_cli
utility for the 3ware cards downloaded and LOOK at what it has to say
about your RAID controller, drives, and array health.

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

[GENERAL] replay doesn't catch up with receive on standby

[Steven Parkes]

This is on 9.0.3: I've got two dbs running as standby to a main db. They start 
up fine and seem to think they're all caught up (by /var/log logs), but

SELECT pg_last_xlog_receive_location() AS receive, 
pg_last_xlog_replay_location() AS replay;

reports replay behind receive and it doesn't change. This is on both dbs.

Notably the main db isn't (wasn't) doing anything, so no new commits were 
causing things to move forward. I did a write to it and both slaves moved both 
their recieved and replay serial numbers up.

Is there a valid situation where an idle master/standby setup could remain with 
replay behind received indefinitely? (My nagios monitor isn't very happy with 
that (at present)) and before changing that I'd like to understand better 
what's going on.)
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help - corruption issue?

[Tomas Vondra]

Dne 18.4.2011 20:27, Phoenix Kiula napsal(a):

>>>
>>> What am I to do now? Even reindex is not working. I can try to drop
>>> indexes and create them again. Will that help?
>>
>> It might help, but as someone already pointed out, you're running a
>> version that's 3 years old. So do a hot file backup (stop the db and copy
>> the data directory to another machine), check the hardware (especially the
>> RAID controller and RAM), upgrade to the latest 8.2.x version and then try
>> again.
>>
>> I'll post a bit more info into the other thread, as it's related to the
>> reindex performance and not to this issue.
>>
>> regards
>> Tomas
> 
> Thanks. For CentOS (RedHat?) the latest is 8.2.19 right? Not the
> 8.2.20 that's mentioned on front page of PG.org.

Centos is probably a bit delayed behind the source version. If you want
to stick with the binary version, go with the 8.2.19.

> http://www.pgrpms.org/8.2/redhat/rhel-4-i386/repoview/
> 
> Question:  will upgrading from 8.2.9 to 8.2.19 have some repercussions
> in terms of huge changes or problems?

Those minor versions are mostly bugfixes and small improvements. So no,
I wouldn't expect huge problems.

> I know 9.x had some new additions including "casting" etc (or is that
> irrelevant to me?) but if 8.2.19 is safe in terms of not requiring
> anything new from my side, then I can do the upgrade quickly.

Don't do that right now. When doing 'minor' upgrades, you don't need to
dump/restore the database - you can just replace the binaries and it
should work as the file format does not change between minor versions
(and 8.2.9 -> 8.2.19 is a minor upgrade).

Still, do the file backup as described in the previous posts. You could
even do an online backup using pg_backup_start/pg_backup_stop etc.

To upgrade from 8.2 to 9.0 you'd need to do pg_dump backup and then
restore the database. Which is of scope right now, I guess.

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

Re: [GENERAL] pg_reorg

[Vibhor Kumar]

On Apr 19, 2011, at 3:40 AM, Jens Wilke wrote:

> yes, we used it to reduce massive bloat after a database split and 
> were very satisfied.
> IIRC "vacuum full" mode rewrites the indexes as well.

Till 8.4 no. From 9.0 onwards yes. However VACUUM FULL still locks the table.


> There's a proposal about an alternative to pg_reorg on depesz' Blog: 
> http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/

For huge bloats, mentioned Method is slower.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.ku...@enterprisedb.com
Blog:http://vibhork.blogspot.com


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

Re: [GENERAL] Migrating Data Across Major Versions

[Shawn Gennaria]

So I was forced to re-do the migration since we had to prepare for a
power outage anyway.  Upon re-running the command, I found out that
the reason I was receiving the notices in STDERR was due to the the
long string of ordered schemas in my search path.  It had a "SET
search_path TO public, x, y, z, ..." at the beginning of the file and
of course schemas x, y, z, etc hadn't been created yet.  So there's no
real cause for alarm after all.


On Fri, Apr 15, 2011 at 10:23 AM, Adrian Klaver  wrote:
> On Friday, April 15, 2011 7:10:00 am Shawn Gennaria wrote:
>
>>
>> Yes, it's running as a postgres superuser.  Unfortunately I didn't
>> dump the output to a file so I could read it after it exceeds the
>> terminal's buffer.
>>
>> It just dawned on me that I may have misinterpreted the messages.  I
>> just took a look through the early parts of the file I'm using to
>> restore everything and noticed that many of my stored functions
>> contain references to other stored functions.  Naturally, one wouldn't
>> be able to create a function unless all dependent referenced functions
>> were created first.  It would be rather time-consuming to sort that
>> out manually and re-add each function in the correct order.  Does
>> anyone have experience working that out with some kind of tool?  Does
>> postgres have anything built-in to help with this?
>
>
> So do the schemas exist in the 9.0.3 database?
> As you stated there are dependency issues but I have not seen that be a 
> problem.
> The error messages you saw are not unusual and are probably more of the
> informative kind, not the fatal kind. I don't suppose you had logging turned 
> on
> in the 9.0.3 server when you did the restore?
>
>> No modifications were made to the 8.4 cluster after I put the pgdata
>> folder back in place.  All I did was quickly login with PgAdmin to
>> verify that it wasn't completely empty and then I logged out.  I
>> wasn't thorough about it and didn't even run a query.  I do have logs
>> in my pgdata/pg_log folder from each day.  Would those help track down
>> issues with getting the server to start, or are they only used once
>> the server's already up?  I took a look through the recent ones, and
>> the only thing that looks odd from that time period are repeating
>> messages:
>
> There some parts of the server start that may not make it into the logs but 
> most
> does, so yes they would be useful.
>
>>
>> FATAL: unrecognized configuration parameter "application_name"
>
> Have you looked in postgresql.conf or pg_hba.conf to see if the above is 
> there?
>
>>
>> In either case, the issue seems to have worked itself out after the
>> server was rebooted.
>>
>> >> Thanks!
>> >> sg
>> >
>> > --
>> > Adrian Klaver
>> > adrian.kla...@gmail.com
>
> --
> Adrian Klaver
> adrian.kla...@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

Re: [GENERAL] pg_reorg

[Jens Wilke]

On Montag, 18. April 2011, Scott Mead wrote:

> I've seen it, but catalog hacks always make me nervous.  Anybody
> else have good / bad experience to share?

Hi,

yes, we used it to reduce massive bloat after a database split and 
were very satisfied.
IIRC "vacuum full" mode rewrites the indexes as well.

There's a proposal about an alternative to pg_reorg on depesz' Blog: 
http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/

regards,
Jens

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

Re: [GENERAL] SSDs with Postgresql?

[Vick Khera]

On Sun, Apr 17, 2011 at 5:17 AM, Henry C.  wrote:

> When the super-fast SSD-based machine fails, switching to a (slower)
> standard
> hard-drive based machine provides continuity and buys time while we get the
> primary machine back online.
>

The question you should ask yourself is: can my business run at needed
capacity on the slower spinning media?  Our answer was "no", so we ended up
with a second SSD array for the backup server as well.   It was *still*
totally worth the metric gazillion dollars that they cost.

Re: [GENERAL] Help - corruption issue?

[Phoenix Kiula]

On Mon, Apr 18, 2011 at 11:02 PM,   wrote:
>> Thanks Filip.
>>
>> I know which table it is. It's my largest table with over 125 million
>> rows.
>>
>> All the others are less than 100,000 rows. Most are in fact less than
>> 25,000.
>>
>> Now, which specific part of the table is corrupted -- if it is row
>> data, then can I dump specific parts of that table? How? Pg_dumpall
>> does not seem to have an option to have a "WHERE" clause?
>>
>> If the lead index is corrupt, then issuing a reindex should work. So I
>> disconnected all other users. The DB was doing nothing. And then I
>> started a psql session and issued the command "reindex database MYDB".
>> After 3 hours, I see this error:
>>
>>
>>
>> [QUOTE]
>> server closed the connection unexpectedly
>>       This probably means the server terminated abnormally
>>       before or while processing the request.
>> The connection to the server was lost. Attempting reset: WARNING:
>> terminating connection because of crash of another server process
>> DETAIL:  The postmaster has commanded this server process to roll back
>> the current transaction and exit, because another server process
>> exited abnormally and possibly corrupted shared memory.
>> HINT:  In a moment you should be able to reconnect to the database and
>> repeat your command.
>> Failed.
>> !>
>> [/UNQUOTE]
>>
>>
>> What am I to do now? Even reindex is not working. I can try to drop
>> indexes and create them again. Will that help?
>
> It might help, but as someone already pointed out, you're running a
> version that's 3 years old. So do a hot file backup (stop the db and copy
> the data directory to another machine), check the hardware (especially the
> RAID controller and RAM), upgrade to the latest 8.2.x version and then try
> again.
>
> I'll post a bit more info into the other thread, as it's related to the
> reindex performance and not to this issue.
>
> regards
> Tomas



Thanks. For CentOS (RedHat?) the latest is 8.2.19 right? Not the
8.2.20 that's mentioned on front page of PG.org.

http://www.pgrpms.org/8.2/redhat/rhel-4-i386/repoview/

Question:  will upgrading from 8.2.9 to 8.2.19 have some repercussions
in terms of huge changes or problems?

I know 9.x had some new additions including "casting" etc (or is that
irrelevant to me?) but if 8.2.19 is safe in terms of not requiring
anything new from my side, then I can do the upgrade quickly.

Welcome any advice.

Thanks!

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

Re: [GENERAL] Help - corruption issue?

[Merlin Moncure]

2011/4/18 Phoenix Kiula :
> Thanks Filip.
>
> I know which table it is. It's my largest table with over 125 million rows.
>
> All the others are less than 100,000 rows. Most are in fact less than 25,000.
>
> Now, which specific part of the table is corrupted -- if it is row
> data, then can I dump specific parts of that table? How? Pg_dumpall
> does not seem to have an option to have a "WHERE" clause?
>
> If the lead index is corrupt, then issuing a reindex should work. So I
> disconnected all other users. The DB was doing nothing. And then I
> started a psql session and issued the command "reindex database MYDB".
> After 3 hours, I see this error:
>
>
>
> [QUOTE]
> server closed the connection unexpectedly
>        This probably means the server terminated abnormally
>        before or while processing the request.
> The connection to the server was lost. Attempting reset: WARNING:
> terminating connection because of crash of another server process
> DETAIL:  The postmaster has commanded this server process to roll back
> the current transaction and exit, because another server process
> exited abnormally and possibly corrupted shared memory.
> HINT:  In a moment you should be able to reconnect to the database and
> repeat your command.
> Failed.
> !>
> [/UNQUOTE]
>
>
> What am I to do now? Even reindex is not working. I can try to drop
> indexes and create them again. Will that help?

it might.  take a full file system backup first and drop the indexes.
before recreating them, take a regular dump (with pg_dump) and if it
goes through, you're golden, rebuild the indexes, *update the
postmaster to latest 8.2*, and you can go back online.

merllin

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

Re: [GENERAL] pg_reorg

[Vibhor Kumar]

On Apr 19, 2011, at 2:29 AM, Raghavendra wrote:

> 
> I've seen it, but catalog hacks always make me nervous.  Anybody else have 
> good / bad experience to share?
> 
> --scott
> 
> 
> It is observed, double the space required for this utility.

I have used it many times. Yes it requires double space. However it has given 
me what I needed. Online VACUUM FULL (for primary key table), Online CLUSTER.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.ku...@enterprisedb.com
Blog:http://vibhork.blogspot.com


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

Re: [GENERAL] pg_reorg

[Raghavendra]

>
>
> I've seen it, but catalog hacks always make me nervous.  Anybody else have
> good / bad experience to share?
>
> --scott
>
>
It is observed, double the space required for this utility.

Eg:-
If the database is 4 gig, there should be 8gigs space.

Best Regards,
Raghavendra
EnterpriseDB Corporation

>



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

Re: [GENERAL] pg_reorg

[Scott Mead]

On Mon, Apr 18, 2011 at 4:39 PM, Merlin Moncure  wrote:

> ...is an amazing tool!
>
>
I've seen it, but catalog hacks always make me nervous.  Anybody else have
good / bad experience to share?

--scott



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

[GENERAL] pg_reorg

[Merlin Moncure]

...is an amazing tool!

merlin

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

Re: [GENERAL] REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it

[Tom Lane]

Frank van Vugt  writes:
> * upon issuing 'REASSIGN OWNED BY A TO postgres', all tables and _most_ 
> functions changed ownership, but not all a number of functions stay 
> marked 
> as owned by A, nothing weird in the logs, the reassign looked like it 
> completed successfully

Hmmm  look into pg_shdepend to see if there are entries linking
those functions to an owner.  For instance, after

regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> create function foo() returns int as 'select 1' language sql;
CREATE FUNCTION

I get

regression=> select * from pg_shdepend;
  dbid  | classid | objid  | objsubid | refclassid | refobjid | deptype 
+-++--++--+-
 ...
 123822 |1255 | 148691 |0 |   1260 |   148690 | o
 ...

1255 = pg_proc, 1260 = pg_authid, and the other numbers are the OIDs of
the database, function, and role (user) respectively.  This data is what
REASSIGN OWNED works off of, and I suppose that some rows must be wrong
or missing in your pg_shdepend.

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

Re: [GENERAL] Help - corruption issue?

[tv]

> Thanks Filip.
>
> I know which table it is. It's my largest table with over 125 million
> rows.
>
> All the others are less than 100,000 rows. Most are in fact less than
> 25,000.
>
> Now, which specific part of the table is corrupted -- if it is row
> data, then can I dump specific parts of that table? How? Pg_dumpall
> does not seem to have an option to have a "WHERE" clause?
>
> If the lead index is corrupt, then issuing a reindex should work. So I
> disconnected all other users. The DB was doing nothing. And then I
> started a psql session and issued the command "reindex database MYDB".
> After 3 hours, I see this error:
>
>
>
> [QUOTE]
> server closed the connection unexpectedly
>   This probably means the server terminated abnormally
>   before or while processing the request.
> The connection to the server was lost. Attempting reset: WARNING:
> terminating connection because of crash of another server process
> DETAIL:  The postmaster has commanded this server process to roll back
> the current transaction and exit, because another server process
> exited abnormally and possibly corrupted shared memory.
> HINT:  In a moment you should be able to reconnect to the database and
> repeat your command.
> Failed.
> !>
> [/UNQUOTE]
>
>
> What am I to do now? Even reindex is not working. I can try to drop
> indexes and create them again. Will that help?

It might help, but as someone already pointed out, you're running a
version that's 3 years old. So do a hot file backup (stop the db and copy
the data directory to another machine), check the hardware (especially the
RAID controller and RAM), upgrade to the latest 8.2.x version and then try
again.

I'll post a bit more info into the other thread, as it's related to the
reindex performance and not to this issue.

regards
Tomas


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

Re: [GENERAL] stymied on postgresql upgrade

[Devrim GÜNDÜZ]

On Mon, 2011-04-18 at 06:28 -0700, bkwiencien wrote:
> I have postgresql 8.4 installed, and I would like to install 9.0.3.
> But when I try I get:
> 
> error: Failed dependencies:
> postgresql < 7.4 conflicts with postgresql-
> server-8.4.4-2PGDG.el5.i386
> 
> Any help would be appreciated. 

You cannot install PostgreSQL 9.0 and 8.4 with RPMs on the same machine.
That functionality is available for RHEL 6 and Fedora 14+. Either
upgrade OS, or use sources for one of them.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

[GENERAL] REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it

[Frank van Vugt]

L.S.


# select version();
version 
   
---
 PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 
4.4.4, 64-bit
(1 row)


In a production database we experienced the following:

* the database contains a number of tables and functions owned by user A

* one is logged in as postgres

* upon issuing 'REASSIGN OWNED BY A TO postgres', all tables and _most_ 
functions changed ownership, but not all a number of functions stay marked 
as owned by A, nothing weird in the logs, the reassign looked like it 
completed successfully

* there doesn't seem to be anything 'special' about the functions that did not 
change ownership

* changing ownership for one of these functions by using 'alter function' 
succeeds without any problem


Mind you, we tested the reassign in a trial database first, a freshly restored 
dump, and there the reassign worked properly / completely.


Any hints as to what could cause this? We left the production db as-is, so we 
still have a number of these functions 'still owned by A' for which we could 
check things in pg_* tables or something.





-- 

Best,




Frank.

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

[GENERAL] stymied on postgresql upgrade

[bkwiencien]

I have postgresql 8.4 installed, and I would like to install 9.0.3.
But when I try I get:

error: Failed dependencies:
postgresql < 7.4 conflicts with postgresql-
server-8.4.4-2PGDG.el5.i386

Any help would be appreciated.

Bob Kwiencien

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

Re: [GENERAL] Help - corruption issue?

[Phoenix Kiula]

Thanks Filip.

I know which table it is. It's my largest table with over 125 million rows.

All the others are less than 100,000 rows. Most are in fact less than 25,000.

Now, which specific part of the table is corrupted -- if it is row
data, then can I dump specific parts of that table? How? Pg_dumpall
does not seem to have an option to have a "WHERE" clause?

If the lead index is corrupt, then issuing a reindex should work. So I
disconnected all other users. The DB was doing nothing. And then I
started a psql session and issued the command "reindex database MYDB".
After 3 hours, I see this error:



[QUOTE]
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: WARNING:
terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
Failed.
!>
[/UNQUOTE]


What am I to do now? Even reindex is not working. I can try to drop
indexes and create them again. Will that help?





2011/4/18 Filip Rembiałkowski :
> Phoenix,
>
> how large (in total) is this database)?
>
> can you copy (cp -a) the data directory somewhere? I would do this
> just in case :-)
>
>
> regarding the manual recovery process:
>
> 1. you'll have to isolate corrupted table.
> you can do this by dumping all tables one-by-one (pg_dump -t TABLE)
> until you get the error.
>
> 2. find the record which is corupted... approach like this might work:
> select count(*) from the_corrupted_table where PK_column <= some_value.
>
> 3 .you should try to dump the table by chunks - skipping the corrupted
> row(s) if possible
>
> 4. if above method does not work, you can try manually hex-editing
> (zeroing) some bytes (with postgres shut down) to make dump work
> again.
>
>
> PS. obligatory note:
>
> 8.2.9 Release Date: 2008-06-12; 8.2.21 Release Date: 2011-04-18
> seems like you were running almost three years without bugfixes.
> aside from fixing your current problem, I would first do the upgrade
> to avoid more corruption.
>
>
>
>
>
>
> 2011/4/18 Phoenix Kiula 
>>
>> While doing a PG dump, I seem to have a problem:
>>
>> ERROR: invalid memory alloc request size 4294967293
>>
>> Upon googling, this seems to be a data corruption issue!
>>
>> ( Came about while doing performance tuning as being discussed on the
>> PG-PERFORMANCE list:
>> http://postgresql.1045698.n5.nabble.com/REINDEX-takes-half-a-day-and-still-not-complete-td4005943.html
>> )
>>
>> One of the older messages suggests that I do "file level backup and
>> restore the data".
>> http://archives.postgresql.org/pgsql-admin/2008-05/msg00191.php
>>
>> How does one do this -- should I copy the data folder? What are the
>> specific steps?
>>
>> I'm on PG 8.2.9, CentOS 5, with 8GB of RAM. The disks are four SATAII
>> disks on RAID 1.
>>
>> Thanks!
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>

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

[GENERAL] problem with parent/child table and FKs

[Karsten Hilbert]

Hello all,

since (according to the docs) PostgreSQL does not propagate
INSERTs from child tables unto parent tables the below does
not work, unfortunately.

What is the suggested approach for this situation ? (there
will be more tables like "icd10" holding other coding
systems of fairly diverse nature but all of them sharing
.code and .term: LOINC, ATC, ICPC-2, ICD-9, ...).

Thanks,
Karsten
(www.gnumed.de)


begin;

create table code_root (
pk_code_root serial primary key,
code text not null,
term text not null
);

create table icd10 (
pk serial primary key,
version text not null
) inherits (code_root);

create table disease (
pk serial primary key,
disease_name text not null
);

create table lnk_codes2epi (
pk serial primary key,
fk_disease integer not null
references disease(pk)
on update cascade
on delete cascade,
fk_code integer not null
references code_root(pk_code_root)
on update restrict
on delete restrict
);

insert into icd10 (code, term, version) values ('J99.9', 'Grippe', 'ICD-10-GM');
insert into disease (disease_name) values ('URTI/flu');
select * from code_root;
 pk_code_root | code  |  term  
--+---+
1 | J99.9 | Grippe
(1 Zeile)

select * from icd10;
 pk_code_root | code  |  term  | pk |  version  
--+---+++---
1 | J99.9 | Grippe |  1 | ICD-10-GM
(1 Zeile)

select * from disease;
 pk | disease_name 
+--
  1 | URTI/flu
(1 Zeile)

insert into lnk_codes2epi (fk_disease, fk_code) values (
(select pk from disease where disease_name = 'URTI/flu'),
(select pk_code_root from code_root where code = 'J99.9')
);
psql:x-check-delete.sql:47: ERROR:  insert or update on table "lnk_codes2epi" 
violates foreign key constraint "lnk_codes2epi_fk_code_fkey"
DETAIL:  Key (fk_code)=(1) is not present in table "code_root".

rollback;

-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] Help - corruption issue?

[Filip Rembiałkowski]

Phoenix,

how large (in total) is this database)?

can you copy (cp -a) the data directory somewhere? I would do this
just in case :-)


regarding the manual recovery process:

1. you'll have to isolate corrupted table.
you can do this by dumping all tables one-by-one (pg_dump -t TABLE)
until you get the error.

2. find the record which is corupted... approach like this might work:
select count(*) from the_corrupted_table where PK_column <= some_value.

3 .you should try to dump the table by chunks - skipping the corrupted
row(s) if possible

4. if above method does not work, you can try manually hex-editing
(zeroing) some bytes (with postgres shut down) to make dump work
again.


PS. obligatory note:

8.2.9 Release Date: 2008-06-12; 8.2.21 Release Date: 2011-04-18
seems like you were running almost three years without bugfixes.
aside from fixing your current problem, I would first do the upgrade
to avoid more corruption.






2011/4/18 Phoenix Kiula 
>
> While doing a PG dump, I seem to have a problem:
>
> ERROR: invalid memory alloc request size 4294967293
>
> Upon googling, this seems to be a data corruption issue!
>
> ( Came about while doing performance tuning as being discussed on the
> PG-PERFORMANCE list:
> http://postgresql.1045698.n5.nabble.com/REINDEX-takes-half-a-day-and-still-not-complete-td4005943.html
> )
>
> One of the older messages suggests that I do "file level backup and
> restore the data".
> http://archives.postgresql.org/pgsql-admin/2008-05/msg00191.php
>
> How does one do this -- should I copy the data folder? What are the
> specific steps?
>
> I'm on PG 8.2.9, CentOS 5, with 8GB of RAM. The disks are four SATAII
> disks on RAID 1.
>
> Thanks!
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

Re: [GENERAL] Postgres Start up Error

[Craig Ringer]

On 04/18/2011 04:01 PM, Adarsh Sharma wrote:


Thanks Chetan,

The problem is solved by the below command :-:-)

[postgres@ws-test 8.4SS]$ bin/pg_resetxlog -f /hdd-1/PostgresPlus/8.4SS/data


It's a really good idea to pg_dump, initdb, and restore after doing 
that. You don't know your database is in a good state.


--
Craig Ringer

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

Re: [GENERAL] Using column aliasses in the same query

[Tore Halvorsen]

On Sun, Apr 17, 2011 at 6:00 PM, Tom Lane  wrote:
> Yes, we've heard that before.  Many times.  It's not going to happen,
> and here's why: it's flat out contrary to the SQL specification, as well
> as to the basic intuitive semantics of SQL.  The SELECT list is supposed
> to be evaluated as the last step of a query (well, last except for ORDER
> BY, which is why there's an exception for that).  It's nonsensical for
> WHERE etc to depend on the results of the SELECT list.
>
> As an example of why this is important, consider
>
>        SELECT x/y AS z FROM tab WHERE y <> 0
>
> If the WHERE clause doesn't act before the SELECT list is computed,
> the query is going to fail with divisions-by-zero, exactly what the
> WHERE clause is trying to prevent.  So it'd be nonsensical to refer
> to z in the WHERE clause.

Well, refering to the computed value may be nonsensical, but
couldn't it be some sort of query rewrite? So that...

SELECT x/y AS z FROM tab WHERE y <> 0 AND z > 2

... is a shorthand for

SELECT x/y AS z FROM tab WHERE y <> 0 AND x/y > 2

No big deal, since there are lots of other ways to do this.

-- 
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
 2011 Tore Halvorsen || +052 0553034554

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

[GENERAL] Help - corruption issue?

[Phoenix Kiula]

While doing a PG dump, I seem to have a problem:

ERROR: invalid memory alloc request size 4294967293

Upon googling, this seems to be a data corruption issue!

( Came about while doing performance tuning as being discussed on the
PG-PERFORMANCE list:
http://postgresql.1045698.n5.nabble.com/REINDEX-takes-half-a-day-and-still-not-complete-td4005943.html
)

One of the older messages suggests that I do "file level backup and
restore the data".
http://archives.postgresql.org/pgsql-admin/2008-05/msg00191.php

How does one do this -- should I copy the data folder? What are the
specific steps?

I'm on PG 8.2.9, CentOS 5, with 8GB of RAM. The disks are four SATAII
disks on RAID 1.

Thanks!

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

Re: [GENERAL] Typecast

[Chetan Suttraway]

On Fri, Apr 15, 2011 at 10:29 PM, Nick Raj  wrote:

> Hi,
> Can anybody tell me how to typecast data type Point into Datum?
>
> Thanks
> Nick
>

Assuming you are referring to  c-code,

Point somepoint;
Datum result;

result = PointPGetDatum(&somepoint)

You can also checkout src/backend/utils/adt/geo_ops.c which has code for
conversion between
datum and point and vice-versa.



-- 
Regards,
Chetan Suttraway
EnterpriseDB , The Enterprise
PostgreSQL
 company.

Re: [GENERAL] Postgres Start up Error

[Adarsh Sharma]

Thanks Chetan,

The problem is solved by the below command :- :-)

[postgres@ws-test 8.4SS]$  bin/pg_resetxlog -f 
/hdd-1/PostgresPlus/8.4SS/data



Best Regards,
Adarsh Sharma


Chetan Suttraway wrote:



On Mon, Apr 18, 2011 at 11:00 AM, Adarsh Sharma 
mailto:adarsh.sha...@orkash.com>> wrote:


Dear all,

Today I by mistake deleted all the contents of pg_log & pg_xlog
directory and my Postgresql refuses to start due to the below error :-

2011-04-18 10:51:39 ISTLOG:  database system was interrupted; last
known up at 2011-04-18 10:06:42 IST
2011-04-18 10:51:39 ISTLOG:  could not open file
"pg_xlog/000100060007" (log file 6, segment 7): No
such file or directory
2011-04-18 10:51:39 ISTLOG:  invalid primary checkpoint record
2011-04-18 10:51:39 ISTLOG:  could not open file
"pg_xlog/0001000500C9" (log file 5, segment 201): No
such file or directory
2011-04-18 10:51:39 ISTLOG:  invalid secondary checkpoint record
2011-04-18 10:51:39 ISTPANIC:  could not locate a valid checkpoint
record
2011-04-18 10:51:39 ISTLOG:  startup process (PID 11021) was
terminated by signal 6: Aborted
2011-04-18 10:51:39 ISTLOG:  aborting startup due to startup
process failure

I try to reinstall with the same previous data directory, but it
fails again.

Any suggestions to solve it without losing the previous data.


Thanks & best Regards,
Adarsh Sharma

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



A quick search on message archives pointed to below link:
http://archives.postgresql.org/pgsql-admin/2000-10/msg0.php
http://archives.postgresql.org/pgsql-admin/2002-04/msg00013.php
http://archives.postgresql.org/pgsql-bugs/2009-03/msg00118.php

Maybe someone here can point the exact steps to be followed.

--
Regards,
Chetan Suttraway
EnterpriseDB , The Enterprise PostgreSQL 
 company.