Re: [GENERAL] WAL receive process dies
On 08/28/2014 09:39 AM, Patrick Krecker wrote: We have a periodic network connectivity issue (unrelated to Postgres) that is causing the replication to fail. We are running Postgres 9.3 using streaming replication. We also have WAL archives available to be replayed with restore_command. Typically when I bring up a slave it copies over WAL archives for a while before connecting via streaming replication. When I notice the machine is behind in replication, I also notice that the WAL receiver process has died. There didn't seem to be any information in the logs about it. What did you search for? Do you have core dumps enabled? That'd be a good first step. (Exactly how to do this depends on the OS/distro/version, but you basically want to set ulimit -c unlimited on some ancestor of the postmaster). 1. It seems that Postgres does not fall back to copying WAL archives with its restore_command. I just want to confirm that this is what Postgres is supposed to do when its connection via streaming replication times out. It should fall back. 2. Is it possible to restart replication after the WAL receiver process has died without restarting Postgres? PostgreSQL should do so its self. Please show your recovery.conf (appropriately redacted) and postgresql.conf for the replica, and complete logs for the time period of interest. You'll want to upload the logs somewhere then link to them, do not attach them to an email to the list. -- Craig Ringer 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
Re: [GENERAL] Atomicity of INSERT INTO ... SELECT ... WHERE NOT EXISTS ...
On 08/28/2014 06:22 AM, Jim Garrison wrote: Given (pseudocode) CREATE TABLE kvstore ( k varchar primary key, v varchar); CREATE OR REPLACE FUNCTION store_key_value_pair(k varchar, v varchar) returns boolean as $$ BEGIN INSERT INTO kvstore (k, v) SELECT :k, :v WHERE NOT EXISTS (select 1 from kvstore where k = :k); RETURN FOUND; END; $$ LANGUAGE plpgsql; I have a few questions: 1) Does INSERT statement set FOUND based on whether or not the row was inserted? 2) If this is invoked without a transaction in progress, is there any guarantee of atomicity between checking the EXISTS and attempting to insert the row? If this is being executed in two (or more) sessions, can the SELECT succeed but then have the INSERT fail with a duplicate-key exception? This code can still fail with a unique violation, yes, as the select can occur in both transactions then the insert in both. 3) Will the behavior be different if the invoking processes have a transaction in progress? No, because all functions run in transactions. There is no such thing as not in a transaction in PostgreSQL (except for a few special system management commands). If it's in a SERIALIZABLE transaction instead of the default READ COMMITTED then it might fail with a serialization failure instead of a unique violation, but it'll still fail. Please read the detailed guidance on this problem that already exists: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql -- Craig Ringer 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] Failback to old master
Hello, I swear I have read a couple of old threads. Yet I am not sure if it safe to failback to the old master in case of async replication without base backup. Considering: I have the latest 9.3 server A: master B: slave B is actively connected to A I shut down A manually with -m fast (it's the default FreeBSD init script setting) I remove the recovery.conf from B I restart B I create a recovery.conf on A I start A I see nothing wrong in the logs I go for a lunch I shut down B I remove the recovery.conf on A I restart A I restore the recovery.conf on B I start B I see nothing wrong in the logs and I see that replication is working Can I say that my data is safe in this case? If the answer is yes, is it safe to do this if there was a power outage on A instead of manual shutdown? Considering that the log says nothing wrong. (Of course if it complains I'd do base backup from B). Thank you, M.
Re: [GENERAL] select top_countries and for each country, select top_cities in that country, in 1 query
On Mon, Aug 18, 2014 at 10:52 AM, John McKown john.archie.mck...@gmail.com wrote: SELECT avg(b.countcountry)::int as CountryCount, b.country, a.city, count(a.city) as CityCount FROM t AS a INNER JOIN (SELECT COUNT(country) AS countcountry, country FROM t GROUP BY country) AS b ON a.country = b.country GROUP BY b.country, a.city ORDER BY 1 DESC,4 DESC; I am curious that nobody pointed out that the above might work but is really poor code. Given that, I wonder what the people here think of the following code. It seems better to me, even if it is more wordy. WITH CountryCount AS ( SELECT COUNT(country) as countryCount, country FROM t GROUP BY country ), CityCount AS ( SELECT COUNT(city) as cityCount, city, country FROM t GROUP BY country, city ) SELECT b.countryCount, b.country, a.city, a.cityCount FROM CityCount as a INNER JOIN CountryCount AS b ON a.country = b.country ORDER BY b.countcountry DESC, a.city DESC -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! John McKown -- 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] select top_countries and for each country, select top_cities in that country, in 1 query
Thanks John. On Thu, Aug 28, 2014 at 2:35 PM, John McKown john.archie.mck...@gmail.com wrote: On Mon, Aug 18, 2014 at 10:52 AM, John McKown john.archie.mck...@gmail.com wrote: SELECT avg(b.countcountry)::int as CountryCount, b.country, a.city, count(a.city) as CityCount FROM t AS a INNER JOIN (SELECT COUNT(country) AS countcountry, country FROM t GROUP BY country) AS b ON a.country = b.country GROUP BY b.country, a.city ORDER BY 1 DESC,4 DESC; I am curious that nobody pointed out that the above might work but is really poor code. Given that, I wonder what the people here think of the following code. It seems better to me, even if it is more wordy. WITH CountryCount AS ( SELECT COUNT(country) as countryCount, country FROM t GROUP BY country ), CityCount AS ( SELECT COUNT(city) as cityCount, city, country FROM t GROUP BY country, city ) SELECT b.countryCount, b.country, a.city, a.cityCount FROM CityCount as a INNER JOIN CountryCount AS b ON a.country = b.country ORDER BY b.countcountry DESC, a.city DESC -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! John McKown -- 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 related to Postgresql for RHEL 6.5
Dear All, I want to upgrade RHEL 5.4 to RHEL 6.5. So, could you please let me know, which postgresql version is stable for RHEL 6.5? Regards, Yogesh DISCLAIMER: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or NEC or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of NEC or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. . ---
Re: [GENERAL] Deletion
OK.. i created a function for delete customer from different tables in single database. i want rollback.. my question: where i need to place rollback ,with in a function along with deletion statements..? or after run the function ..? i do not need commit.. please let me know.. On Thu, Aug 28, 2014 at 1:20 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 08/27/2014 11:59 AM, Ramesh T wrote: In oracle I ran the deletion script to clean up the particular database using custid.custid is the parameter .1 is used in the oracle Inthe same way tried but $1 not possible ?but using function is possible is their any problem with fun to Clean the database I have 100 statements in script ... First as has been pointed out before Oracle != Postgres. Also as been pointed out before you have two options: 1) Pay for the EDB Postgres Advanced Sever + and get the Oracle compatibility built ins. 2) Spend the time to convert your Oracle code. As always it comes down to that magic combination of time and/or money. Second I do not understand what you are trying to say above. Maybe if you showed some actual code it would help. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] Help related to Postgresql for RHEL 6.5
Hi, On Thu, 2014-08-28 at 03:22 +, Yogesh. Sharma wrote: I want to upgrade RHEL 5.4 to RHEL 6.5. So, could you please let me know, which postgresql version is stable for RHEL 6.5? Any supported PostgreSQL version is available in the yum repository: http://yum.postgresql.org You can use 9.3, for example. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Deletion
On Thu, Aug 28, 2014 at 9:59 AM, Ramesh T [via PostgreSQL] ml-node+s1045698n5816744...@n5.nabble.com wrote: OK.. i created a function for delete customer from different tables in single database. i want rollback.. my question: where i need to place rollback ,with in a function along with deletion statements..? or after run the function ..? i do not need commit.. please let me know.. Functions in PostgreSQL do not have their own transaction environment - they always operate in the transaction of the caller (and cannot control it). BEGIN; SELECT function_call(); ROLLBACK; David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Deletion-tp5816588p5816764.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Deletion
On 08/28/2014 04:22 AM, Ramesh T wrote: OK.. i created a function for delete customer from different tables in single database. i want rollback.. my question: where i need to place rollback ,with in a function along with deletion statements..? or after run the function ..? i do not need commit.. If you want to invoke an explicit ROLLBACK then you will need an explicit BEGIN, which means you will need an explicit COMMIT to have the operation succeed. For the reasons why see here: http://www.postgresql.org/docs/9.3/static/tutorial-transactions.html please let me know.. First, the function code would be nice to see or at least a heads up on what language you are using. Second, there are no transactions inside functions. In the case of plpgsql you can use EXCEPTIONs to mimic the behavior: http://www.postgresql.org/docs/9.3/static/plpgsql-structure.html t is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. For more about that see Section 40.6.6. http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Adrian Klaver adrian.kla...@aklaver.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] Deletion
On 08/28/2014 04:22 AM, Ramesh T wrote: OK.. i created a function for delete customer from different tables in single database. i want rollback.. my question: where i need to place rollback ,with in a function along with deletion statements..? or after run the function ..? i do not need commit.. Realized in my last post the below was not entirely clear: If you want to invoke an explicit ROLLBACK then you will need an explicit BEGIN, which means you will need an explicit COMMIT to have the operation succeed. should have been: If you want to invoke an explicit ROLLBACK when you have a error then you will need an explicit BEGIN, which means you will need an explicit COMMIT when the operation succeeds in order for the transaction to complete. please let me know.. -- Adrian Klaver adrian.kla...@aklaver.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] Help related to Postgresql for RHEL 6.5
Dear All, Compatibility issues of 9.3 are more. So, please guide which version is suitable. Regards, Yogesh -Original Message- From: Devrim Gündüz [mailto:dev...@gunduz.org] Sent: Thursday, August 28, 2014 7:34 PM To: Yogesh. Sharma Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Help related to Postgresql for RHEL 6.5 Hi, On Thu, 2014-08-28 at 03:22 +, Yogesh. Sharma wrote: I want to upgrade RHEL 5.4 to RHEL 6.5. So, could you please let me know, which postgresql version is stable for RHEL 6.5? Any supported PostgreSQL version is available in the yum repository: http://yum.postgresql.org You can use 9.3, for example. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR DISCLAIMER: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or NEC or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of NEC or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. . --- -- 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 related to Postgresql for RHEL 6.5
Yogesh. Sharma wrote Compatibility issues of 9.3 are more. So, please guide which version is suitable. Compatibility as in the multixact issues or does your software not work with 9.3 changes? What about any supported version and the provided link is unclear? There is no way for us to evaluate suitability for your specific need unless you provide lots more info. If 9.3 scares you off then use 9.2 Are you currently using PostgreSQL? If you are referring to distro-supported versions (which Debian uses in my case) you should make that requirement specific. The PostgreSQL community feels all their officially supported releases are stable. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Help-related-to-Postgresql-for-RHEL-6-5-tp5816742p5816840.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] Help related to Postgresql for RHEL 6.5
Dear David, Are you currently using PostgreSQL? Currently we are using PostgreSQL 8.1.18 version on RHEL 5.8. Now we plan to update this to PostgreSQL 9.0 version with RHEL6.5. As in verion 9.0 I found least Compatibilities. So, please guide me. Regards, Yogesh -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G Johnston Sent: Friday, August 29, 2014 9:22 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Help related to Postgresql for RHEL 6.5 Yogesh. Sharma wrote Compatibility issues of 9.3 are more. So, please guide which version is suitable. Compatibility as in the multixact issues or does your software not work with 9.3 changes? What about any supported version and the provided link is unclear? There is no way for us to evaluate suitability for your specific need unless you provide lots more info. If 9.3 scares you off then use 9.2 Are you currently using PostgreSQL? If you are referring to distro-supported versions (which Debian uses in my case) you should make that requirement specific. The PostgreSQL community feels all their officially supported releases are stable. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Help-related-to-Postgresql-for-RHEL-6-5-tp5816742p5816840.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 DISCLAIMER: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or NEC or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of NEC or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. . --- -- 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 related to Postgresql for RHEL 6.5
On Friday, August 29, 2014 04:14:35 AM Yogesh. Sharma wrote: Dear David, Are you currently using PostgreSQL? Currently we are using PostgreSQL 8.1.18 version on RHEL 5.8. Now we plan to update this to PostgreSQL 9.0 version with RHEL6.5. As in verion 9.0 I found least Compatibilities. Any of the currently maintained PostgreSQL versions will run fine on RHEL 6.5 - that would be the latest release of any version from 9.0 up. Only you can test and find out if your application(s) will need changes to work with those versions. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Timezone difference between Oracle SYSDATE and PostgreSQL timestamp functions
Hello, We have converted Oracle SYSDATE to PostgreSQL statement_timestamp() but there is a difference in timezone. SYSDATE returns the time on the server where the database instance is running(returns operating system time) so the time depends on the OS timezone setting. while the timezone of postgreSQL statement_timestamp()/now()/clock_timestamp() depends on the DBMS setting. so I think timezone settings are different between DBMS and OS. Consider the following example SYSDATE vs CURRENT_DATE: SYSDATE returns the time on the server where the database instance is running(returns operating system time) CURRENT_DATE returns the time where the session is running In the below example sysdate and current_date return the same time but if we set the new time zone then it shows the difference in time. Example: Oracle: SQL select SYSDATE,CURRENT_DATE from dual; SYSDATE CURRENT_DATE --- --- 28-AUG-14 14:08:58 28-AUG-14 14:08:58 SQL ALTER SESSION SET TIME_ZONE = '-5:0'; SQLselect SYSDATE,CURRENT_DATE from dual; SYSDATE CURRENT_DATE --- --- 28-AUG-14 14:10:23 28-AUG-14 03:40:23 PostgreSQL: postgres=# show time zone; TimeZone -- Asia/Kolkata (1 row) postgres=# select now(); now -- 2014-08-28 14:19:51.740664+05:30 (1 row) postgres=# set time zone 'Europe/Rome'; SET postgres=# postgres=# select now(); now --- 2014-08-28 10:51:03.941594+02 (1 row) Any idea how can we set OS timezone on PostgreSQL? - Thanks and Regards, Vinayak Pokale, NTT DATA OSS Center Pune, India -- View this message in context: http://postgresql.1045698.n5.nabble.com/Timezone-difference-between-Oracle-SYSDATE-and-PostgreSQL-timestamp-functions-tp5816851.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] Timezone difference between Oracle SYSDATE and PostgreSQL timestamp functions
On 8/28/2014 10:06 PM, Vinayak wrote: Any idea how can we set OS timezone on PostgreSQL? timestamp with time zone values are stored as the equivalent of UTC and converted to the client TIME_ZONE setting for display, unless you specify some other timezone via AT TIME ZONE I'm not sure why any other behavior would be rational. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
