Re: [GENERAL] postgreSQL : duplicate DB names
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, May 26, 2014 5:35 PM To: Alban Hertroys Cc: Khangelani Gama; Thomas Kellerer; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names Alban Hertroys writes: > On 26 May 2014, at 10:28, Khangelani Gama wrote: >> It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike > 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You're 22 bugfix releases behind, which likely includes fixes for several data-corruption issues. > See: http://www.postgresql.org/support/versioning/ 8.0.x did not have any real defenses against transaction ID wraparound (the behavior of forcing a shutdown when wraparound gets too close was added in 8.1, and there was no built-in autovacuum back then either). So my money is on this being a wraparound problem, ie, some dead but never-vacuumed pg_database row has returned to visibility because its deleting transaction is now "in the future". If you're very lucky, vacuuming pg_database will fix it. But take a backup *first*, in case you're not lucky and vacuuming makes things worse. I'd shut down the postmaster and then make a physical copy (tarball) of the whole data directory tree, just to be sure you can get back to where you are. As a former Red Hatter, I cannot resist making the point that your OS version is even hoarier than your PG version, and is certainly full of since-fixed bugs. You are *years* overdue for some serious attention to software updates. regards, tom lane Thanks a lot Tom, And Thanks to everyone who responded. It looks like the best option we have is to restore from the latest backup. And then plan to do the upgrade in the near future. CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- 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] new index type with clustering in mind.
> The discussions at PGCon pointed out that with the posting-list compression logic added in 9.4, GIN indexes are pretty close to this already. Multiple items on the same heap page will typically only take one byte of index space per item; but there is an identifiable entry, so you don't get into these questions of when VACUUM should remove entries, and it's not lossy so you're not forced to pay the overhead of rechecking every entry on the linked-to page. > Not to say that 9.4 GIN is necessarily the last word on the subject, but it would be worth testing it out before deciding that we need something better. (beta1 is out. It needs testing. Hint hint.) Hint taken, and first impressions are positive: the compression is very efficient for the kind of scenario I'm imagining where the key is deliberately chosen so that the average page has one distinct key. I have a 25Mb gin 'cluster' index on a table where an equivalent regular btree index is 10 times as large. So the questions are, a) is this kind of clustering broadly useful (ie not just to me), b) how much effort will it be to implement a 'vacuum-like' operation that scans a designated index and performs the relevant delete/inserts to achieve this kind of clustering? And c) if it is broadly useful and not a major implementation mountain to climb, is it something that might be added to the todo list? If someone can tell me how to decode a `ctid` into a page number (discarding the row number portion - is there a better way than ` (replace(replace(ctid::text,'(','{'),')','}')::integer[])[1]`), I should be able to show some analysis demonstrating this working, albeit inefficiently as I'll have to scan the table itself for the page/key statistics. Would that sort of analysis be helpful? Kindest regards Jack PS It occurs to me that the btree_gin documentation page for 9.4, http://www.postgresql.org/docs/9.4/static/btree-gin.html, might benefit from including some mention of index compression when discussing the relative performance of regular and gin btree indexes. -- 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] Trouble running nested select - Join query
Rushi wrote > copy (select * from (select s1.head as h1,s1.tail as t1 ,s2.tail as > neighbor from miami_2d s1, miami_directednetwork s2 where s1.tail=s2.head > and s2.tail not in (select tail from miami_2d where head=s1.head)) as O > where O.h1!=O.neighbor) to '/tmp/tmp.txt' Some thoughts: The O sub-query could probably be top-level A correlated sub-query in the where clause should generally use EXISTS, not IN ... ... Though if you can get rid of the whole thing that would be better You might want to use the statement_timeout GUC to kill the query if it takes too long You could separate the the select and the copy into two statements via use of a temporary table - depending on how large the output is that would split the action into two statements that could be independently monitored. Though, to be honest now that I re-read I have no clue how this query inter-plays with PostgresXC... Can you run a smaller input size and use that estimate how long a 100M^2 query should take to complete? Can you calculate in smaller block sizes on the input? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Trouble-running-nested-select-Join-query-tp5804938p5804995.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] postgreSQL : duplicate DB names
Alban Hertroys writes: > On 26 May 2014, at 10:28, Khangelani Gama wrote: >> It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike > 8.0.4? That went EOL almost 4 years ago and even then you should be running a > version around 8.0.26. Youre 22 bugfix releases behind, which likely > includes fixes for several data-corruption issues. > See: http://www.postgresql.org/support/versioning/ 8.0.x did not have any real defenses against transaction ID wraparound (the behavior of forcing a shutdown when wraparound gets too close was added in 8.1, and there was no built-in autovacuum back then either). So my money is on this being a wraparound problem, ie, some dead but never-vacuumed pg_database row has returned to visibility because its deleting transaction is now "in the future". If you're very lucky, vacuuming pg_database will fix it. But take a backup *first*, in case you're not lucky and vacuuming makes things worse. I'd shut down the postmaster and then make a physical copy (tarball) of the whole data directory tree, just to be sure you can get back to where you are. As a former Red Hatter, I cannot resist making the point that your OS version is even hoarier than your PG version, and is certainly full of since-fixed bugs. You are *years* overdue for some serious attention to software updates. 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] postgreSQL : duplicate DB names
-Original Message- From: Alban Hertroys [mailto:haram...@gmail.com] Sent: Monday, May 26, 2014 11:11 AM To: Khangelani Gama Cc: Thomas Kellerer; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names On 26 May 2014, at 10:28, Khangelani Gama wrote: > On 26 May 2014, at 10:09, Khangelani Gama wrote: >>> Khangelani Gama, 26.05.2014 09:20: Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. Name| Owner | Encoding -+--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE >>> >>> Maybe there are some trailing spaces at one of the names. >>> >>> What do you see when you run this: >>> >>> select '"'||datname||'"' as name >>> from pg_database >>> >>> By adding the quotes, you can see if there are any trailing (or >>> leading) in the name. >>> >>> Regards >>> Thomas > >> Hi, >> >> I get: >> >>name >> -- >> "a9004" >> "template1" >> "template0" >> "a9004" >> (4 rows) > > Looks like you might have some index corruption, do those databases > have the same oid?: > > select datname, oid from pg_database > > What version of the database are you running? > What OS? > Did anything of notice happen on that server before this showed up? > > P.S. Please don't top-post on this list. > > Alban Hertroys > -- > If you can't see the forest for the trees, cut the trees and you'll > find there is no forest. > > > > It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike > > datname |oid > + > a9004| 4215680815 > template1 | 1 > template0 | 17229 > a9004| 1335509510 > (4 rows) 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You're 22 bugfix releases behind, which likely includes fixes for several data-corruption issues. See: http://www.postgresql.org/support/versioning/ If that database contains anything of value, plan some time for an upgrade to a more recent - supported - version. Take your time to read the release notes though, as there have been some changes that probably affect you; the change that disabled implicit type-casting between several types of dubious compatibility, for example. That said, from the look of things your duplicate databases do have different oid's, meaning they are different objects in your database. Are you sure they don't just _look_ identical due to case-folding or something? Before you start medling with it, make sure you have a recent database dump. Aside from giving you something to restore if things go sideways, the contents of that dump could be illuminating as to what is actually going on here. Can you find both instances of the CREATE DATABASE statements for those databases? The first thing to try with that dump is whether the situation can be restored on a test-system. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. I try different things, Many Thanks CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- 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] postgreSQL : duplicate DB names
On 26 May 2014, at 10:28, Khangelani Gama wrote: > On 26 May 2014, at 10:09, Khangelani Gama wrote: >>> Khangelani Gama, 26.05.2014 09:20: Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. Name| Owner | Encoding -+--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE >>> >>> Maybe there are some trailing spaces at one of the names. >>> >>> What do you see when you run this: >>> >>> select '"'||datname||'"' as name >>> from pg_database >>> >>> By adding the quotes, you can see if there are any trailing (or >>> leading) in the name. >>> >>> Regards >>> Thomas > >> Hi, >> >> I get: >> >>name >> -- >> "a9004" >> "template1" >> "template0" >> "a9004" >> (4 rows) > > Looks like you might have some index corruption, do those databases have > the same oid?: > > select datname, oid from pg_database > > What version of the database are you running? > What OS? > Did anything of notice happen on that server before this showed up? > > P.S. Please don't top-post on this list. > > Alban Hertroys > -- > If you can't see the forest for the trees, cut the trees and you'll find > there is no forest. > > > > It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike > > datname |oid > + > a9004| 4215680815 > template1 | 1 > template0 | 17229 > a9004| 1335509510 > (4 rows) 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You’re 22 bugfix releases behind, which likely includes fixes for several data-corruption issues. See: http://www.postgresql.org/support/versioning/ If that database contains anything of value, plan some time for an upgrade to a more recent - supported - version. Take your time to read the release notes though, as there have been some changes that probably affect you; the change that disabled implicit type-casting between several types of dubious compatibility, for example. That said, from the look of things your duplicate databases do have different oid’s, meaning they are different objects in your database. Are you sure they don’t just _look_ identical due to case-folding or something? Before you start medling with it, make sure you have a recent database dump. Aside from giving you something to restore if things go sideways, the contents of that dump could be illuminating as to what is actually going on here. Can you find both instances of the CREATE DATABASE statements for those databases? The first thing to try with that dump is whether the situation can be restored on a test-system. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- 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] postgreSQL : duplicate DB names
-Original Message- From: Alban Hertroys [mailto:haram...@gmail.com] Sent: Monday, May 26, 2014 10:21 AM To: Khangelani Gama Cc: Thomas Kellerer; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names On 26 May 2014, at 10:09, Khangelani Gama wrote: >> Khangelani Gama, 26.05.2014 09:20: >>> Hi >>> >>> Something wrong happened in our server, for some strange and unknown >>> reason (s) the server created another DB, there is two same DB names >>> in one server. >>> >>> Name| Owner | Encoding >>> -+--+-- >>> a9004| postgres | UNICODE >>> a9004| postgres | UNICODE >>> >> >> Maybe there are some trailing spaces at one of the names. >> >> What do you see when you run this: >> >> select '"'||datname||'"' as name >> from pg_database >> >> By adding the quotes, you can see if there are any trailing (or >> leading) in the name. >> >> Regards >> Thomas > Hi, > > I get: > > name > -- > "a9004" > "template1" > "template0" > "a9004" > (4 rows) Looks like you might have some index corruption, do those databases have the same oid?: select datname, oid from pg_database What version of the database are you running? What OS? Did anything of notice happen on that server before this showed up? P.S. Please don't top-post on this list. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike datname |oid + a9004| 4215680815 template1 | 1 template0 | 17229 a9004| 1335509510 (4 rows) CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- 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] postgreSQL : duplicate DB names
On 26 May 2014, at 10:09, Khangelani Gama wrote: >> Khangelani Gama, 26.05.2014 09:20: >>> Hi >>> >>> Something wrong happened in our server, for some strange and unknown >>> reason (s) the server created another DB, there is two same DB names in >>> one server. >>> >>> Name| Owner | Encoding >>> -+--+-- >>> a9004| postgres | UNICODE >>> a9004| postgres | UNICODE >>> >> >> Maybe there are some trailing spaces at one of the names. >> >> What do you see when you run this: >> >> select '"'||datname||'"' as name >> from pg_database >> >> By adding the quotes, you can see if there are any trailing (or leading) in >> the name. >> >> Regards >> Thomas > Hi, > > I get: > > name > -- > "a9004" > "template1" > "template0" > "a9004" > (4 rows) Looks like you might have some index corruption, do those databases have the same oid?: select datname, oid from pg_database What version of the database are you running? What OS? Did anything of notice happen on that server before this showed up? P.S. Please don’t top-post on this list. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- 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] postgreSQL : duplicate DB names
Hi, I get: name -- "a9004" "template1" "template0" "a9004" (4 rows) -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Thomas Kellerer Sent: Monday, May 26, 2014 9:42 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names Khangelani Gama, 26.05.2014 09:20: > Hi > > Something wrong happened in our server, for some strange and unknown > reason (s) the server created another DB, there is two same DB names in > one server. > > Name| Owner | Encoding > -+--+-- > a9004| postgres | UNICODE > a9004| postgres | UNICODE > Maybe there are some trailing spaces at one of the names. What do you see when you run this: select '"'||datname||'"' as name from pg_database By adding the quotes, you can see if there are any trailing (or leading) in the name. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- 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] postgreSQL : duplicate DB names
Khangelani Gama, 26.05.2014 09:20: > Hi > > Something wrong happened in our server, for some strange and unknown reason > (s) the server created another DB, > there is two same DB names in one server. > > Name| Owner | Encoding > -+--+-- > a9004| postgres | UNICODE > a9004| postgres | UNICODE > Maybe there are some trailing spaces at one of the names. What do you see when you run this: select '"'||datname||'"' as name from pg_database By adding the quotes, you can see if there are any trailing (or leading) in the name. Regards Thomas -- 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] Shared memory changes in 9.4?
Hi, On 2014-05-25 12:26:20 -0700, Maciek Sakrejda wrote: > I've been trying to take the 9.4 beta for a spin, but seem to have run into > a shared memory issue on startup: > > FATAL: could not open shared memory segment "/PostgreSQL.1804289383": > Permission denied > > We're running Postgres inside LXC, which I suspect is causing problems > here, but 9.0 through 9.3 run just fine in the same configuration. Any chance you're using a 9.3 configuration file instead of the one generated by initdb? dynamic_shared_memory_type defaults to 'posix' if not specified in the config file (on platforms supporting it). If initdb detects that 'posix' can't be used it'll emit a different value. If you're copying the config from 9.3 and your environment doesn't support posix shm that'll cause the above error. I still think dynamic_shared_memory_type should default to 'none' because of such problems. > Any idea > what may have changed? I suspect the solution here is tweaking some LXC > setting, but I was hoping someone can point me in the right direction. It sounds like you're not allowing posix shared memory. Possibly just by not mounting /dev/shm. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgreSQL : duplicate DB names
Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. How I can I fix this. Please help Name| Owner | Encoding +--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.